By Leave a Comment on Sortieren nach dem Nachnamen in Excel (einfache Anleitung)

Video ansehen – Sortieren nach Nachnamen in Excel

Wenn Sie mit Namensdatensätzen 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 Sie Daten nach dem Nachnamen in Excel sortieren möchten?,

Obwohl es nicht so einfach ist, kann es immer noch getan werden (vieles hängt auch von der Strukturierung der Daten ab).

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

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

Also los geht ‚ s!,

Dieses Tutorial behandelt:

Extrahieren und Sortieren nach Nachnamen Mit Find and Replace

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 ist.

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten diese Daten alphabetisch mit 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. Kopieren Sie ihn in die benachbarte Spalte (wenn die benachbarte Spalte nicht leer ist, fügen Sie eine neue Spalte ein und kopieren Sie diese Namen dann)
  3. Benennen Sie kopierte Spaltenüberschrift. In diesem Beispiel nenne ich ‚Nachname‘
  4. Wählen Sie alle kopierten Namen aus (wählen Sie nicht die Kopfzeile aus)
  5. Halten Sie die Steuertaste 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 was * ein (Sternchen gefolgt von einem Leerzeichen)
  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 verlassen werden.

Die obigen Schritte würden den Nachnamen beibehalten und alles davor entfernen. Dies funktioniert auch dann gut, wenn Sie mittlere Namen oder Präfixe (wie Mr. oder Ms) haben.,

Sobald Sie die Nachnamen in der benachbarten Spalte haben, können Sie den Datensatz (einschließlich der vollständigen Namen) ganz einfach alphabetisch nach dem Nachnamen 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 einfügen, 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 Header“ ausgewählt ist.,
  5. Wählen Sie in der Option ‚Sortieren nach‘ den Namen der Spalte aus, die nur den Nachnamen hat
  6. Wählen Sie in der Option ‚Sortieren nach‘ “Zellwerte ‚
  7. Wählen Sie in der Option’A bis Z‘
  8. Klicken Sie auf OK

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

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

Pro-Tipp: Wenn Sie der Meinung sind, dass Sie die ursprünglichen Daten möglicherweise zurück benötigen, müssen Sie zu jedem Zeitpunkt eine Möglichkeit haben, dieses Dataset aufzuheben. Haben Sie dazu in einer benachbarten Spalte (links oder rechts) Seriennummern vor der Sortierung. Wenn Sie nun die Originaldaten zurück benötigen, erhalten Sie sie, indem Sie basierend auf den Zahlen sortieren.

„Extrahieren“ und Alphabetisch nach Nachnamen Mit Formel

Während die Methode, die oben gezeigt (mit Suchen und Ersetzen) ist das, was ich mir lieber alle den Nachnamen und Sortieren Sie basierend auf es, eine Einschränkung ist, dass die resultierenden Daten in statischen.,

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

Wenn Sie dies nicht möchten, können Sie die Formelmethode verwenden, um Daten nach Nachnamen zu sortieren.

Angenommen, Sie haben den Datensatz wie unten gezeigt.

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

=RIGHT(A2,LEN(A2)-FIND(" ",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.

Mit der Funktion SUCHEN wird die Position des Leerzeichens ermittelt. Dieser Wert wird dann von der Gesamtlänge des Namens subtrahiert, um die Gesamtzahl der Zeichen im Nachnamen zu erhalten.

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

Sobald Sie die Spalte Nachname haben, können Sie diese Daten sortieren (dies wird in der ersten Methode im Detail 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 kann es eine Begrüßung vor dem Namen (wie Mr oder Ms.)

In einem solchen 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 es dann, um den Nachnamen zu extrahieren.

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

Hinweis: Diese beiden Formeln beruhen auf der Bedingung, dass zwischen jedem Namenselement nur ein Leerzeichen vorhanden ist., Wenn doppelte Leerzeichen oder führende / nachgestellte Leerzeichen vorhanden sind, führt diese Formel zu falschen Ergebnissen. In einem solchen Fall verwenden Sie am besten die TRIM-Funktion, um zuerst führende, nachgestellte und doppelte 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 aufzuteilen.,

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

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Nachfolgend finden Sie die Schritte zum Sortieren von Text zu Spalte nach dem Nachnamen:

  1. Wählen Sie die Spalte mit dem Namen aus (mit Ausnahme der Kopfzeile)
  2. Klicken Sie auf die Registerkarte Daten
  3. Klicken Sie in der Gruppe ‚Datenwerkzeuge‘ auf die Option Text zu Spalten., Dadurch wird der Assistent für Text in Spalten geöffnet
  4. Wählen Sie in Schritt 1 des Assistenten“Text in Spalten konvertieren“ die Option „Begrenzt“ und klicken Sie auf Weiter
  5. Wählen Sie in Schritt 2 „Leerzeichen“ als Trennzeichen (und deaktivieren Sie alles andere, falls ausgewählt).
  6. Wählen Sie in Schritt 3 die Spalte Vorname in der Datenvorschau und dann die Option „Spalten nicht importieren (überspringen)“ aus. 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 ursprünglichen Daten 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 Vor-und Nachnamen zu trennen, wenn Sie ein Komma als Trennzeichen haben.,

Mit 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 dabei, die Daten zu manipulieren, indem Muster identifiziert werden. Damit dies funktioniert, müssen Sie Flash Fill das Ergebnis zeigen, das Sie ein paar Mal erwarten.

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

Angenommen, Sie haben den folgenden Namensdatensatz.,

Unten sind die schritte zu verwenden Flash Füllen zu erhalten die letzten name und dann sortieren mit es:

  1. In zelle B2, geben sie den text ‚Maury‘. Dies ist das Ergebnis, das Sie in der Zelle erwarten.
  2. Gehe zur nächsten Zelle und gib den Nachnamen für den Namen in die benachbarte Zelle ein (Elliot in diesem Beispiel).
  3. Wählen Sie beide Zellen
  4. Bewegen Sie den Mauszeiger über den unteren rechten Teil der Auswahl. Sie werden feststellen, dass sich der Cursor in ein Plussymbol ändert.
  5. Doppelklicken Sie darauf (oder klicken und ziehen Sie es nach unten)., Dies gibt Ihnen 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

Dies gibt Ihnen das Ergebnis, das wahrscheinlich die Nachnamen in allen Zellen sein werden.

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

In solchen Fällen sollten Sie ein erwartetes Ergebnis in eine 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, mit denen Sie Daten nach dem Nachnamen sortieren können. Die beste Methode wäre, die Find – and-Replace-Technik zu verwenden, aber wenn Sie Ihre Ergebnisse dynamisch machen möchten, ist die Formelmethode der richtige Weg.

Hoffe, Sie fanden dieses tutorial hilfreich.,

Sie können auch die folgenden Excel-Tutorials mögen:

  • Sortieren nach Farbe in Excel
  • So sortieren Sie Arbeitsblätter in Excel
  • So sortieren Sie Daten in Excel mit VBA
  • Sortieren Sie Daten automatisch in alphabetischer Reihenfolge mit der Formel
  • So sortieren Sie Daten auf mehreren Ebenen in Excel

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.