So sortieren Sie in Excel nach Nachnamen (eine einfache Anleitung)

Wenn Sie mit einem Dataset von Namen arbeiten, ist das Sortieren eine der häufigsten Aufgaben, die Sie häufig ausführen müssen.

Es ist einfach, Daten alphabetisch nach dem vollständigen Namen zu sortieren, wobei Excel das erste Zeichen des Namens zum Sortieren verwendet.

Allerdings, wenn Sie möchtenDaten nach Nachnamen in Excel sortierenzusteigen, einsteigen, vorwärtskommenSortierenWie macht man?

Es ist zwar nicht so einfach, aber dennoch möglich (hängt auch stark davon ab, wie die Namensdaten strukturiert sind).

Unabhängig davon, welche Methode Sie verwenden, müssen Sie den Nachnamen irgendwie aus dem vollständigen Namen extrahieren und in eine separate Spalte einfügen.Sie können diese Spalte dann verwenden, um die Daten alphabetisch nach Nachnamen zu sortieren.

In diesem Excel-Tutorial zeige ich Ihnen, wie Sie eine Spalte mit Namen basierend auf dem Nachnamen sortieren.

Also lasst uns anfangen!

Extrahieren und sortieren Sie nach Nachnamen mit Suchen und Ersetzen

Der erste Schritt zum Sortieren nach Nachnamen besteht darin, den Nachnamen in einer separaten Spalte abzurufen.

Sie können dies tun, indem Sie alles vor dem Nachnamen durch Leerzeichen ersetzen, sodass nur der Nachname übrig bleibt.

Angenommen, Sie haben einen Datensatz wie den folgenden und möchten diese Daten alphabetisch nach Nachnamen sortieren.

So sortieren Sie nach Nachnamen:

1. Wählen Sie den Datensatz aus, der den Titel enthält (in diesem Fall ist es A1:A10).
2. kopiere es in die angrenzende Spalte (wenn die angrenzende Spalte nicht leer ist, füge eine neue Spalte ein und kopiere dann die Namen)
3. Benennen Sie die kopierten Spaltenüberschriften um.In diesem Beispiel nenne ich es "Nachname".
4. Alle kopierten Namen auswählen (keine Titel auswählen)
5. Halten Sie die Strg-Taste gedrückt und drücken Sie dann die H-Taste.Dadurch wird das Dialogfeld „Suchen und Ersetzen“ geöffnet.
6. Geben Sie im Feld Suchen nach * (ein Sternchen gefolgt von einem Leerzeichen) ein.
7. Lassen Sie das Feld Ersetzen durch leer
8. Klicken Sie auf Alle ersetzen.Dadurch werden alle Vornamen auf einmal ersetzt und Sie haben nur noch Nachnamen.

Die obigen Schritte behalten den Nachnamen bei und löschen alles davor.Dies funktioniert auch, wenn Sie einen zweiten Vornamen oder ein Präfix wie Herr oder Frau haben.

Sobald Sie Nachnamen in benachbarten Spalten haben, können Sie den Datensatz (einschließlich vollständiger Namen) einfach alphabetisch nach Nachnamen sortieren.

So sortieren Sie nach Nachnamen:

1. Wählen Sie den gesamten Datensatz mit Titeln (einschließlich vollständiger Namen und extrahierter Nachnamen).Sie können auch andere Spalten einfügen, um nach dem Namen zu sortieren
2. Klicken Sie auf die Registerkarte Daten
3. Zum Sortieren anklicken
4. Stellen Sie im Dialogfeld „Sortieren“ sicher, dass „Meine Daten haben Kopfzeilen“ aktiviert ist.
5. Wählen Sie in der Option "Sortieren nach" den Namen der Spalte mit nur dem Nachnamen aus
6. Wählen Sie in Sortieren nach Zellenwert
7. Wählen Sie in der Option "Bestellung" "A bis Z"
8. OK klicken

Die obigen Schritte sortieren den gesamten ausgewählten Datensatz basierend auf dem Nachnamen.

Wenn Sie fertig sind, können Sie die Spalte mit dem Nachnamen löschen.

Profi-Tipps: Immer wenn Sie denken, dass Sie zu den ursprünglichen Daten zurückkehren müssen, müssen Sie eine Möglichkeit haben, diesen Datensatz zu unsortieren.Dazu stehen in benachbarten Spalten (links oder rechts) vor der Sortierung Sequenznummern.Wenn Sie jetzt die Originaldaten wiederherstellen müssen, können Sie sie durch Sortieren nach Zahlen erhalten.

Extrahieren und alphabetisch sortieren nach Nachnamen mit Formel

Während ich die oben gezeigte Methode (mit Suchen und Ersetzen) bevorzuge, um alle Nachnamen zu erhalten und danach zu sortieren, besteht eine der Einschränkungen darin, dass die resultierenden Daten statisch sind.

Das bedeutet, dass ich, wenn ich der Liste weitere Vornamen hinzufüge, denselben Prozess erneut durchlaufen muss, um den Nachnamen zu erhalten.

Wenn Sie dies nicht möchten, können Sie die Daten mithilfe einer Formelmethode nach Nachnamen sortieren.

Angenommen, Sie haben den unten gezeigten Datensatz.

Hier ist die Formel, um den Nachnamen aus dem vollständigen Namen zu extrahieren:

= RECHTS (A2, LEN (A2) -FINDEN ("", A2))

Die obige Formel basiert auf einem Muster mit vollständigen Namen (in diesem Beispiel nur Vor- und Nachnamen).Das Muster ist, dass zwischen dem Vor- und Nachnamen ein Leerzeichen steht.

Die FIND-Funktion wird verwendet, um die Position des Leerzeichens zu ermitteln.Dieser Wert wird dann von der Gesamtlänge des Vornamens abgezogen, um die Gesamtzahl der Zeichen im Nachnamen zu erhalten.

Verwenden Sie dann diesen Wert in der RIGHT-Funktion, um den Nachnamen zu erhalten.

Sobald Sie die Nachnamenspalte haben, können Sie diese Daten sortieren (dies wird in der ersten Methode beschrieben).

Die obige Formel funktioniert, wenn Sie nur den Vor- und Nachnamen haben.

Aber was ist, wenn Sie auch einen zweiten Vornamen haben?oder kann eine Anrede vor dem Namen haben (z. B. Mr. oder Ms.)

In diesem Fall müssen Sie die folgende Formel verwenden:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Die obige Formel findet die Position des letzten Leerzeichens und verwendet sie dann, um den Nachnamen zu extrahieren.

Ich schlage vor, dass Sie in allen Fällen die zweite Formel verwenden, sie ist einfacher und behandelt alle Fälle (solange der Nachname am Ende des Vornamens steht).

Hinweis: Diese beiden Formeln beruhen auf der Bedingung, dass zwischen jedem Namenselement nur ein Leerzeichen steht.Diese Formel liefert falsche Ergebnisse, wenn doppelte Leerzeichen oder führende/nachgestellte Leerzeichen vorhanden sind.In diesem Fall ist es am besten, zuerst die Funktion TRIM zu verwenden, um führende, nachgestellte und doppelte Leerzeichen zu entfernen und dann die obige Formel zu verwenden.

Obwohl dies wie ein komplizierter Ansatz erscheinen mag, besteht der Vorteil der Verwendung einer Formel darin, dass die Ergebnisse dynamisch werden.Wenn Sie der Liste weitere Namen hinzufügen, müssen Sie nur die Formel kopieren und Sie erhalten den Nachnamen.

Text in Spalten verwenden

Text in Spalten ist eine weitere einfache Möglichkeit, Zellen in Excel zu teilen.

Sie können ein Trennzeichen (z. B. ein Komma oder Leerzeichen) angeben und damit den Inhalt einer Zelle teilen.Nachdem Sie die Elemente in separate Spalten aufgeteilt haben, können Sie die Spalte mit den Nachnamen verwenden, um die Daten alphabetisch zu sortieren.

Angenommen, Sie haben einen Datensatz, der so aussieht:

Hier sind die Schritte zum Sortieren nach Nachnamen mit Text to Column:

1. Spalten mit Namen auswählen (ohne Kopfzeilen)
2. Klicken Sie auf die Registerkarte Daten
3. Klicken Sie in der Gruppe Datentools auf die Option Text in Spalten.Dadurch wird der Text-zu-Spalten-Assistent geöffnet
4. Wählen Sie in Schritt 1 des Assistenten zum Konvertieren von Text in Spalten die Option Getrennt aus und klicken Sie auf Weiter
5. Wählen Sie in Schritt 2 „Leerzeichen“ als Trennzeichen (deaktivieren Sie alles andere, wenn es aktiviert ist) und klicken Sie auf die Schaltfläche „Weiter“.
6. Wählen Sie in Schritt 3 die Vornamensspalte in der Datenvorschau aus und wählen Sie dann die Option "Spalte nicht importieren (überspringen)".Dadurch wird sichergestellt, dass der Vorname nicht Teil des Ergebnisses ist, Sie erhalten nur den Nachnamen.
7. Ändern Sie auch in Schritt 3 die Zielzelle in die Zelle neben den Originaldaten.Dadurch wird sichergestellt, dass Sie nur den Nachnamen erhalten und dass die ursprünglichen Namensdaten vollständig sind.
8. Klicken Sie auf Fertig stellen

Sobald Sie die Ergebnisse haben, können Sie nach Nachnamen sortieren.

Sie können auch Text-in-Spalte verwenden, um Vor- und Nachnamen zu trennen, wenn Kommas als Trennzeichen verwendet werden.

Mit Glitzer füllen

Eine weitere schnelle Möglichkeit, den Nachnamen zu erhalten, ist die Verwendung der Flash Fill-Funktion.

Flash Fill wurde in Excel 2013 eingeführt und hilft dabei, Daten zu manipulieren, indem Muster identifiziert werden.Dazu müssen Sie Flash Fill mehrmals anzeigen, um das erwartete Ergebnis zu erhalten.

Sobald es das Muster erkennt, erledigt es schnell den Rest für Sie.

Angenommen, Sie haben den folgenden Datensatz mit Namen.

Hier sind die Schritte, um den Nachnamen mit Flash Fill zu erhalten und ihn zum Sortieren zu verwenden:

1. Geben Sie in Zelle B2 den Text „Maury“ ein.Das erwartet man in einer Zelle.
2. Gehen Sie in die nächste Zelle und geben Sie den Nachnamen des Namens in die angrenzende Zelle ein (in diesem Fall Elliot).
3. Wählen Sie zwei Zellen aus
4. Bewegen Sie den Mauszeiger über den unteren rechten Teil der Auswahl.Sie werden feststellen, dass sich der Cursor in ein Plus-Symbol ändert.
5. Doppelklicken Sie darauf (oder klicken und ziehen Sie nach unten).Dadurch erhalten Sie ein Ergebnis in der Zelle (unwahrscheinlich das gewünschte)
6. Klicken Sie auf das Symbol AutoFill-Optionen.
7. Klicken Sie auf Schnelles Ausfüllen

Dadurch erhalten Sie ein Ergebnis, das der Nachname in allen Zellen sein kann.

Ich sage vielleicht, weil Flash Fill in einigen Fällen möglicherweise nicht funktioniert.Da dies vom Erkennungsmodus abhängt, ist dies möglicherweise nicht immer der Fall.Oder manchmal ist das entschlüsselte Muster möglicherweise nicht das richtige Muster.

Geben Sie in diesem Fall das erwartete Ergebnis in eine oder mehrere Zellen ein und führen Sie dann die Schritte 4-7 aus.

Nachdem Sie alle Nachnamen in der Spalte haben, können Sie die Daten basierend auf diesen Nachnamen sortieren.

Daher können Sie die Daten auf vier verschiedene Arten nach Nachnamen sortieren.Der beste Weg ist die Verwendung von Suchen- und Ersetzen-Techniken, aber wenn Sie Ihre Ergebnisse dynamisch gestalten möchten, sind Formelmethoden der richtige Weg.

Ich hoffe, Sie fanden dieses Tutorial nützlich.