Generische Formel
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Zusammenfassung
Um Daten mit Zahlen und Text in alphabetischer Reihenfolge dynamisch zu sortieren, können Sie eine Formel verwenden, um einen numerischen Rang in einer Hilfsspalte zu generieren, und dann INDEX und MATCH verwenden, um Werte basierend auf dem Rang anzuzeigen. In dem gezeigten Beispiel lautet die Formel in C5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
wobei "Daten" der benannte Bereich B5: B13 ist.
Erläuterung
Diese Formel generiert zuerst einen Rangwert unter Verwendung eines Ausdrucks, der auf COUNTIF basiert:
=COUNTIF(data,"<="&B5)
was hier näher erläutert wird. Wenn die Daten alle Textwerte oder alle numerischen Werte enthalten, ist der Rang korrekt. Wenn die Daten jedoch sowohl Text als auch Zahlen enthalten, müssen wir den Rang aller Textwerte "verschieben", um die numerischen Werte zu berücksichtigen. Dies geschieht mit dem zweiten Teil der Formel hier:
+(COUNT(data)*ISTEXT(B7))
Hier verwenden wir die COUNT-Funktion, um eine Anzahl numerischer Werte in den Daten zu erhalten, und multiplizieren dann das Ergebnis mit dem logischen Ergebnis von ISTEXT, das prüft, ob der Wert Text ist und entweder TRUE oder FALSE zurückgibt. Dadurch wird das COUNT-Ergebnis effektiv gelöscht, wenn mit einer Zahl in der aktuellen Zeile gearbeitet wird.
Umgang mit Duplikaten
Wenn Daten Duplikate enthalten, kann die Formel wie unten gezeigt geändert werden, um Werten, die mehrmals vorkommen, einen sequentiellen Rang zuzuweisen:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Diese Version passt die Logik der anfänglichen COUNTIF-Funktion an und fügt ein weiteres COUNTIF mit einem erweiterten Verweis auf inkrementelle Duplikate hinzu.
Sortierte Werte anzeigen
Zum Abrufen und Anzeigen von Werten, die nach dem berechneten Rangwert in alphabetischer Reihenfolge sortiert sind, enthält E5 die folgende INDEX- und MATCH-Formel:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
Dabei ist "Daten" der benannte Bereich B5: B13 und "Rang" der benannte Bereich C5: C13.
Weitere Informationen zur Funktionsweise dieser Formel finden Sie im Beispiel hier.
Umgang mit Leerzeichen
Leere Zellen erzeugen einen Rang von Null. Angenommen, Sie möchten leere Zellen ignorieren, funktioniert dies einwandfrei, da die obige INDEX- und MATCH-Formel bei 1 beginnt. Am Ende der sortierten Werte werden jedoch # N / A-Fehler angezeigt, einer für jede leere Zelle. Eine einfache Möglichkeit, dies zu handhaben, besteht darin, die INDEX- und MATCH-Formel wie folgt in IFERROR zu verpacken:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
