Die in Excel integrierte Datensortierung ist erstaunlich, aber nicht dynamisch. Wenn Sie Daten sortieren und dann Daten hinzufügen, müssen Sie sie erneut sortieren.
Daten in alphabetischer Reihenfolge sortieren
In diesem Beitrag zeige ich Ihnen verschiedene Möglichkeiten, Daten mithilfe von Formeln in alphabetischer Reihenfolge zu sortieren. Das bedeutet, dass Sie Daten hinzufügen können und diese automatisch für Sie sortiert werden.
Wenn die Daten ausschließlich aus Text ohne Duplikate bestehen
Angenommen, Sie haben Daten wie unten gezeigt:
In diesem Beispiel liegen alle Daten im Textformat vor (keine Zahlen, Leerzeichen oder Duplikate). Um dies zu sortieren, verwende ich eine Hilfsspalte. Verwenden Sie in der Spalte neben den Daten die folgende ZÄHLENWENN-Formel:
=ZÄHLENWENN($A$2:$A$9,"<="&A2)
Diese Formel vergleicht einen Textwert mit allen anderen Textwerten und gibt seinen relativen Rang zurück. In Zelle B2 wird beispielsweise 8 zurückgegeben, da 8 Textwerte vorhanden sind, die kleiner oder gleich dem Text "US" (alphabetische Reihenfolge) sind.
Verwenden Sie nun zum Sortieren der Werte die folgende Kombination aus INDEX-, MATCH- und ROWS-Funktionen:
=INDEX($A$2:$A$9,VERGLEICH(ZEILEN($B$2:B2),$B$2:$B$9,0))
Diese Formel extrahiert einfach die Namen in alphabetischer Reihenfolge. In der ersten Zelle (C2) wird nach dem Ländernamen mit der niedrigsten Nummer gesucht (Australien hat 1). In der zweiten Zelle wird Kanada (das die Nummer 2 hat) zurückgegeben und so weiter…
Allergisch gegen Helfersäulen??
Hier ist eine Formel, die ohne die Hilfsspalte dasselbe tut.
=INDEX($A$2:$A$9,MATCH(ROWS($A$2:A2),ZÄHLENWENN($A$2:$A$9,"<="&$A$2:$A$9),0))
Dies ist eine Array-Formel, also verwenden Sie Strg + Umschalt + Eingabetaste statt Enter.
Die Entschlüsselung überlasse ich Ihnen.
Probieren Sie es selbst aus… Beispieldatei herunterladen
Diese Formel funktioniert gut, wenn Sie Text- oder alphanumerische Werte haben.
Aber es scheitert kläglich, wenn:
- Sie haben Duplikate in den Daten (versuchen Sie, US zweimal einzugeben).
- Die Daten enthalten Leerzeichen.
- Sie haben eine Mischung aus Zahlen und Text (versuchen Sie, 123 in eine der Zellen einzugeben).
Wenn Daten eine Mischung aus Zahlen, Text, Duplikaten und Leerzeichen sind
Das ist jetzt etwas knifflig. Ich werde 4 Hilfsspalten verwenden, um Ihnen zu zeigen, wie es funktioniert (und Ihnen dann eine riesige Formel geben, die es ohne die Hilfsspalten macht). Angenommen, Sie haben Daten wie unten gezeigt:
Sie können sehen, dass es doppelte Werte gibt, Leerzeichen und Zahlen. Daher werde ich Hilfsspalten verwenden, um jedes dieser Probleme anzugehen.
Helferspalte 1
Geben Sie die folgende ZÄHLENWENN-Formel in die Hilfsspalte 1 ein
=ZÄHLENWENN($A$2:$A$9,"<="&A2)
Diese Formel bewirkt Folgendes:
- Es gibt 0 für Leerzeichen zurück.
- Bei Duplikaten wird dieselbe Nummer zurückgegeben.
- Text und Zahlen werden parallel verarbeitet und diese Formel gibt dieselbe Zahl für Text und Zahl zurück (zum Beispiel erhalten 123 und Indien beide 1).
Helferspalte 2
Geben Sie die folgende IS-Funktion in die Hilfsspalte 2 ein:
=--ISNUMMER(A2)
Helferspalte 3
Geben Sie die folgende Formel in die Hilfsspalte 3 ein:
=--ISBLANK(A2)
Helferspalte 4
Geben Sie die folgende Formel in die Hilfsspalte 4 ein
=WENN(ISNUMMER(A2),B2,WENN(ISBLANK(A2),B2,B2+$C$10))+$D$10
Die Idee dieser Formel besteht darin, Leerzeichen, Zahlen und Textwerte zu trennen.
- Wenn die Zelle leer ist, gibt sie den Wert in Zelle B2 zurück (der immer 0 wäre) und fügt den Wert in Zelle D10 hinzu. Kurz gesagt, es wird die Gesamtzahl der leeren Zellen in den Daten zurückgegeben
- Wenn die Zelle ein numerischer Wert ist, wird der Vergleichsrang zurückgegeben und die Gesamtzahl der Leerzeichen hinzugefügt. Zum Beispiel gibt es für 123 2 zurück (1 ist der Rang von 123 in den Daten und es gibt 1 leere Zelle)
- Wenn es sich um Text handelt, gibt es den Vergleichsrang zurück und addiert die Gesamtzahl der numerischen Werte und Leerzeichen. Für Indien wird beispielsweise der Vergleichsrang des Textes im Text (der 1 ist) hinzugefügt und die Anzahl der leeren Zellen und die Anzahl der numerischen Werte hinzugefügt.
Endergebnis - sortierte Daten
Jetzt verwenden wir diese Hilfsspalten, um die sortierte Liste zu erhalten. Hier ist die Formel:
=IFERROR(INDEX($A$2:$A$9,MATCH(SMALL($E$2:$E$9,ROWS($F$2:F2)+$D$10),$E$2:$E$9,0)) ,"")
Diese Sortiermethode wird jetzt narrensicher. Ich habe Ihnen die Methode für 8 Elemente gezeigt, Sie können sie jedoch auf so viele Elemente erweitern, wie Sie möchten.
Probieren Sie es selbst aus… Beispieldatei herunterladen
Eine Formel, um alles zu sortieren (ohne Hilfsspalten)
Wenn Sie mit extremen Formeln umgehen können, finden Sie hier eine All-in-One-Formel, die Daten in alphabetischer Reihenfolge sortiert (ohne Hilfsspalte).
Hier ist die Formel:
=IFERROR(INDEX($A$2:$A$9,MATCH(SMALL(NOT($A$2:$A$9="")*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2: $9 A$,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(--ISNUMBER($A $2:$A$9))),ROWS($A$2:A2)+SUM(--ISBLANK($A$2:$A$9))),NICHT($A$2:$A$9="")*WENN (ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<=" &$A$2:$A$9)+SUMME(--ISNUMMER($A$2:$A$9))),0)),"")
Geben Sie diese Formel in eine Zelle ein und ziehen Sie sie nach unten, um die sortierte Liste zu erhalten. Da dies eine Array-Formel ist, verwenden Sie auch Strg + Umschalt + Eingabetaste statt Enter.
Diese Formel hat einen praktischen Nutzen. Was denken Sie? Ich würde gerne von dir lernen. Hinterlassen Sie Ihre Fußabdrücke im Kommentarbereich!
