Automatische Sortierung der Daten in alphabetischer Reihenfolge nach Formel

Automatische Sortierung der Daten in alphabetischer Reihenfolge mit Hilfe der Formel

Die in Excel eingebaute Datensortierung ist erstaunlich, aber sie ist nicht dynamisch. Wenn Sie Daten sortieren und dann Daten hinzufügen, müssen Sie sie neu sortieren.

Daten in alphabetischer Reihenfolge sortieren

In diesem Beitrag zeige ich Ihnen verschiedene Möglichkeiten, Daten nach Formeln alphabetisch zu sortieren. Das bedeutet, dass Sie Daten hinzufügen können und diese automatisch für Sie sortiert werden.
Wenn die Daten alle Text ohne Duplikate sind. Um excel sortieren formel oder alphabetisch, ist gar nicht viel nötig.
Angenommen, Sie haben eine Datei, wie unten gezeigt:
In diesem Beispiel sind alle Daten im Textformat (keine Zahlen, Leerzeichen oder Duplikate). Um dies zu sortieren, werde ich eine Hilfsspalte verwenden. Verwenden Sie in der Spalte neben den Daten die folgende COUNTIF-Formel:
=COUNUNTIF($A$2:$A$9,“<=“&A2)

Excel Daten sortieren

Diese Formel vergleicht einen Textwert mit allen anderen Textwerten und gibt seinen relativen Rang zurück. In Zelle B2 gibt sie beispielsweise 8 zurück, da es 8 Textwerte gibt, die kleiner oder gleich dem Text’US‘ (alphabetische Reihenfolge) sind.
Um die Werte zu sortieren, verwenden Sie nun die folgende Kombination aus den Funktionen INDEX, MATCH und ROWS:
=INDEX($A$2:$A$9,MATCH(ROWS($B$2:B2),$B$2:$B$9,0)))
Diese Formel extrahiert einfach die Namen in alphabetischer Reihenfolge. In der ersten Zelle (C2) sucht sie nach dem Ländernamen mit der niedrigsten Nummer (Australien hat 1). In der zweiten Zelle gibt es Kanada zurück (das die Nummer 2 hat) und so weiter….
Allergisch gegen Helfersäulen???

Hier ist eine Formel, die das Gleiche ohne die Hilfsspalte tut.

=INDEX($A$2:$A$9,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,“<=“&$A$2:$A$9),0))))
Dies ist eine Array-Formel, also verwenden Sie Control + Shift + Enter anstelle von Enter.
Ich werde es dir überlassen, den Code zu entschlüsseln.
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 zu setzen).
    In den Daten sind Leerzeichen vorhanden.
  • Du hast eine Mischung aus Zahlen und Text (versuche 123 in eine der Zellen einzufügen).
  • Wenn Daten eine Mischung aus Zahlen, Text, Duplikaten und Leerzeichen sind.
  • Jetzt ist dieser hier etwas knifflig. Ich werde 4 Hilfsspalten verwenden, um dir zu zeigen, wie es funktioniert (und dir dann eine riesige Formel geben, die es ohne die Hilfsspalten macht).
  • Angenommen, Sie haben eine Datei, wie unten gezeigt:
  • Du kannst sehen, dass es doppelte Werte, Leerzeichen und Zahlen gibt.
  • Daher werde ich Hilfsspalten verwenden, um jedes dieser Probleme zu lösen.

Helfer Spalte 1

Geben Sie in Hilfsspalte 1 die folgende COUNTIF-Formel ein
=COUNUNTIF($A$2:$A$9,“<=“&A2)

Diese Formel bewirkt folgendes:
Es wird 0 für Leerzeichen zurückgegeben.
Im Falle von Duplikaten gibt es die gleiche Nummer zurück.
Text und Zahlen werden parallel verarbeitet und diese Formel gibt die gleiche Zahl für Text und Zahl zurück (z.B. 123 und Indien erhalten beide 1).
Helfer Spalte 2
Geben Sie in Hilfsspalte 2 die folgende IS-Funktion ein:
=–ISNUMBER(A2)

Helfer Spalte 3
Geben Sie in Hilfsspalte 3 die folgende Formel ein:
=–ISBLANK(A2)

Helfer Spalte 4

Geben Sie in Hilfsspalte 4 die folgende Formel ein
=IF(ISNUMBER(A2),B2,IF(ISBLANK(A2),B2,B2+$C$10))+$D$10

Die Idee für diese Formel ist es, 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 addiert den Wert in Zelle D10. Kurz gesagt, es wird die Gesamtzahl der leeren Zellen in den Daten zurückgegeben.
Wenn die Zelle ein numerischer Wert ist, gibt sie den Vergleichsrang zurück und addiert die Gesamtzahl der Leerzeichen. 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 Zahlenwerte und Leerzeichen. Beispielsweise wird für Indien 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
Nun werden wir diese Hilfsspalten verwenden, um die sortierte Liste zu erhalten. Hier ist die Formel:
=IFROR(INDEX($A$2:$A$9,MATCH(SMALL($E$2:$E$9,ROWS($F$2:F2)+$D$10),$E$2:$E$9,0))),““)

Diese Art der Sortierung wird nun narrensicher. Ich habe dir die Methode für 8 Elemente gezeigt, aber du kannst sie auf beliebig viele Elemente erweitern.
Probieren Sie es selbst aus…. Beispieldatei herunterladen
Eine Formel zum Sortieren (ohne Hilfsspalten)
Wenn Sie mit extremen Formeln umgehen können, finden Sie hier eine All-in-One-Formel, die Daten in alphabetischer Reihenfolge (ohne Hilfsspalte) sortiert.

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:$A$9,“<=“&$A$2:$A$9),COUNTIF($A$2:$A$9,“<=“&$A$2:$A$9), COUNTIF($A$2:$A$9,“<=“&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9))A2)+SUM(–ISBLANK($A$2:$A$9))))), NICHT($A$2:$A$9=““)*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,“<=“&$A$2:$A$9),COUNTIF($A$2:$A$9,“<=“&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9)).
Geben Sie diese Formel in eine Zelle ein und ziehen Sie sie nach unten, um die sortierte Liste zu erhalten. Da es sich hierbei um eine Array-Formel handelt, verwenden Sie Control + Shift + Enter anstelle von Enter.
Diese Formel hat einen praktischen Nutzen. Was denkst du denn? Ich würde gerne von dir lernen. Hinterlasse deine Fußspuren im Kommentarbereich!