So sortieren Sie nach dem Nachnamen in Excel (Einfache Anleitung)

Video ansehen - So sortieren Sie nach Nachnamen in Excel

Wenn Sie mit Names-Datasets arbeiten, ist das Sortieren eine der häufigsten Aufgaben, die Sie häufig ausführen müssen.

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

Aber was ist, wenn du willst Daten nach dem Nachnamen in Excel sortieren?

Es ist zwar nicht so einfach, aber dennoch machbar (viel hängt auch davon ab, wie die Namensdaten strukturiert sind).

Egal welche Methode Sie verwenden, Sie müssen irgendwie den Nachnamen aus dem vollständigen Namen extrahieren und in eine separate Spalte einfügen. In dieser Spalte können Sie dann Ihre Daten alphabetisch nach dem Nachnamen sortieren.

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

Also lasst uns anfangen!

Extrahieren und Sortieren nach Nachname mit Suchen und Ersetzen

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

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

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten diese Daten alphabetisch nach dem Nachnamen sortieren.

Nachfolgend finden Sie die Schritte zum Sortieren nach dem Nachnamen:

1. Wählen Sie den Datensatz einschließlich der Kopfzeile aus (in diesem Beispiel wäre es A1:A10)
2. In die angrenzende Spalte kopieren (wenn die angrenzende Spalte nicht leer ist, fügen Sie eine neue Spalte ein und kopieren Sie dann diese Namen)
3. Benennen Sie die kopierte Spaltenüberschrift um. In diesem Beispiel wird der Name "Nachname" sein.
4. Wählen Sie alle kopierten Namen aus (wählen Sie nicht die Kopfzeile aus)
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 * (Sternchen gefolgt von einem Leerzeichen) ein.
7. Lassen Sie das Feld Ersetzen durch leer
8. Klicken Sie auf Alle ersetzen. Dies würde sofort alle Vornamen ersetzen und Sie werden nur mit Nachnamen zurückgelassen.

Die obigen Schritte würden den Nachnamen beibehalten und alles davor entfernen. Dies funktioniert auch dann gut, wenn Sie zweite Vornamen oder Präfixe haben (wie Herr oder Frau).

Sobald Sie die Nachnamen in der angrenzenden Spalte haben, können Sie den Datensatz (einschließlich der vollständigen Namen) einfach nach dem Nachnamen alphabetisch sortieren.

Nachfolgend finden Sie die Schritte zum Sortieren nach dem Nachnamen:

1. Wählen Sie den gesamten Datensatz mit Kopfzeilen aus (einschließlich der vollständigen Namen und der extrahierten Nachnamen). Sie können auch andere Spalten einschließen, die Sie zusammen mit den Namen sortieren möchten
2. Klicken Sie auf die Registerkarte Daten
3. Klicken Sie auf Sortieren
4. Stellen Sie im Dialogfeld Sortieren sicher, dass "Meine Daten haben Kopfzeilen" ausgewählt ist.
5. Wählen Sie in der Option „Sortieren nach“ den Namen der Spalte aus, die nur den Nachnamen enthält
6. Wählen Sie in „Sortieren nach“ die Option „Zellenwerte“.
7. Wählen Sie in der Bestelloption "A bis Z"
8. OK klicken

Die obigen Schritte würden den gesamten ausgewählten Datensatz nach dem Nachnamen sortieren.

Anschließend können Sie die Spalte mit dem Nachnamen löschen.

Profi-Tipp: Wenn Sie glauben, dass Sie die Originaldaten zu einem beliebigen Zeitpunkt wieder benötigen, müssen Sie die Sortierung dieses Datensatzes aufheben. Tragen Sie dazu in einer angrenzenden Spalte (links oder rechts) vor der Sortierung Seriennummern ein. Wenn Sie nun die Originaldaten zurück benötigen, erhalten Sie diese, indem Sie nach den Zahlen sortieren.

Extrahieren und alphabetisieren Sie den Nachnamen mithilfe von Formeln

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

Das bedeutet, dass ich, wenn ich meiner Liste weitere Namen hinzufüge, denselben Vorgang erneut ausführen muss, um die Nachnamen zu erhalten.

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

Angenommen, Sie haben den unten gezeigten Datensatz.

Unten ist die Formel, die den Nachnamen aus dem vollständigen Namen extrahiert:

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

Die obige Formel basiert auf dem Muster mit einem vollständigen Namen (der in diesem Beispiel nur den Vor- und Nachnamen enthält). Das Muster ist, dass zwischen dem Vor- und Nachnamen ein Leerzeichen stehen würde.

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

Dieser Wert wird dann in der RIGHT-Funktion verwendet, um den Nachnamen zu erhalten.

Sobald Sie die Nachnamensspalte haben, können Sie diese Daten sortieren (dies wird in der ersten Methode ausführlich behandelt).

Die obige Formel würde funktionieren, wenn Sie nur Vor- und Nachnamen haben.

Aber was ist, wenn Sie auch einen zweiten Vornamen haben. Oder steht eine Anrede vor dem Namen (z. B. Herr oder Frau)

In einem solchen Fall müssen Sie die folgende Formel verwenden:

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

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

Ich empfehle Ihnen, in allen Fällen die zweite Formel zu verwenden, und sie ist narrensicherer und kann alle Fälle behandeln (solange der Nachname am Ende des Namens steht).

Hinweis: Diese beiden Formeln basieren auf der Bedingung, dass zwischen jedem Namenselement nur ein Leerzeichen steht. Falls doppelte Leerzeichen oder führende/nachgestellte Leerzeichen vorhanden sind, führt diese Formel zu falschen Ergebnissen. In einem solchen Fall ist es am besten, die Funktion TRIM zu verwenden, um zuerst alle führenden, nachfolgenden und doppelten Leerzeichen zu entfernen und dann die obige Formel zu verwenden.

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

Verwenden von Text in Spalten

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

Sie können das Trennzeichen (wie Komma oder Leerzeichen) angeben und damit den Inhalt der Zelle teilen. Sobald Sie die geteilten Elemente in separaten Spalten haben, können Sie die Spalte mit dem Nachnamen verwenden, um die Daten alphabetisch zu sortieren.

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Im Folgenden sind die Schritte aufgeführt, um Text in Spalte zu verwenden, um nach dem Nachnamen zu sortieren:

1. Wählen Sie die Spalte mit dem Namen (ohne Kopfzeile) aus.
2. Klicken Sie auf die Registerkarte Daten
3. Klicken Sie in der Gruppe "Datentools" auf die Option Text in Spalten. Dies öffnet den Text-in-Spalten-Assistenten
4. Wählen Sie in Schritt 1 des „Assistenten zum Konvertieren von Text in Spalten“ die Option „Getrennt“ und klicken Sie auf Weiter
5. Wählen Sie in Schritt 2 "Leerzeichen" als Trennzeichen (und deaktivieren Sie alles andere, wenn ausgewählt) und klicken Sie dann 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 „Spalten nicht importieren (überspringen)“. Dadurch wird sichergestellt, dass der Vorname nicht Teil des Ergebnisses ist und Sie nur den Nachnamen erhalten.
7. Ändern Sie auch in Schritt 3 die Zielzelle in diejenige, die an die Originaldaten angrenzt. Dadurch wird sichergestellt, dass Sie den Nachnamen separat erhalten und die ursprünglichen Namensdaten intakt sind.
8. Klicken Sie auf Fertig stellen

Sobald Sie das Ergebnis haben, können Sie nach dem Nachnamen sortieren.

Sie können auch Text in Spalten verwenden, um Vor- und Nachnamen zu trennen, wenn Sie ein Komma als Trennzeichen verwenden.

Verwenden von Flash Fill

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

Flash Fill wurde in Excel 2013 eingeführt und hilft bei der Manipulation der Daten durch das Identifizieren von Mustern. Damit dies funktioniert, müssen Sie Flash Fill das erwartete Ergebnis einige Male anzeigen.

Sobald es das Muster identifiziert hat, wird es schnell den Rest der Arbeit für Sie erledigen.

Angenommen, Sie haben das Dataset mit den folgenden Namen.

Unten sind die Schritte, um Flash Fill zu verwenden, um den Nachnamen zu erhalten und ihn dann zu sortieren:

1. Geben Sie in Zelle B2 den Text „Maury“ ein. Dies ist das Ergebnis, das Sie in der Zelle erwarten.
2. Gehen Sie zur nächsten Zelle und geben Sie den Nachnamen für den Namen in der angrenzenden Zelle ein (in diesem Beispiel Elliot).
3. Wählen Sie beide Zellen aus
4. Bewegen Sie den Cursor über den unteren rechten Teil der Auswahl. Sie werden feststellen, dass sich der Cursor in ein Plus-Symbol verwandelt.
5. Doppelklicken Sie darauf (oder klicken Sie darauf und ziehen Sie es nach unten). Dadurch erhalten Sie ein Ergebnis in den Zellen (wahrscheinlich nicht das gewünschte Ergebnis)
6. Klicken Sie auf das Symbol AutoFill-Optionen.
7. Klicken Sie auf Flash Fill

Dadurch erhalten Sie das Ergebnis, bei dem es sich wahrscheinlich um die Nachnamen in allen Zellen handelt.

Ich sage wahrscheinlich, da Flash Fill in einigen Fällen möglicherweise nicht funktioniert. Da es darauf ankommt, ein Muster zu erkennen, ist dies möglicherweise nicht immer möglich. Oder manchmal ist das entschlüsselte Muster nicht das richtige.

In solchen Fällen sollten Sie ein erwartetes Ergebnis in ein oder zwei weitere Zellen eingeben und dann die Schritte 4-7 ausführen.

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

Dies sind also vier verschiedene Möglichkeiten, um Daten nach dem Nachnamen zu sortieren. Die beste Methode wäre, die Methode Suchen und Ersetzen zu verwenden, aber wenn Sie Ihre Ergebnisse dynamisch gestalten möchten, ist die Formelmethode der richtige Weg.

Ich hoffe, Sie fanden dieses Tutorial nützlich.