Allgemeine Formel
=RANK(A1,Werte)+COUNTIF(exp_rng,A1)-1
Erläuterung
Um Daten, die nur numerische Werte enthalten, dynamisch zu sortieren, können Sie eine Hilfsspalte und eine mit den Funktionen RANK und COUNTIF erstellte Formel verwenden. In dem gezeigten Beispiel ist die Formel in D5:
=RANK(C5,Umsatz)+COUNTIF($C$5:C5,C5)-1
wobei „Verkauf“ der benannte Bereich C5:C11 ist.
Wie diese Formel funktioniert
Hinweis: Diese Formel ist der Aufbau für eine Formel, die Daten mit einer vordefinierten Sortierreihenfolge in einer Hilfsspalte extrahieren und anzeigen kann. Ein Beispiel hier. In excel sortieren formel für alphabetische Ordnung.
Der Kern dieser Formel ist die RANK-Funktion, mit der ein Rang von Verkaufswerten erzeugt wird, bei dem die höchste Zahl auf Platz 1 steht:
=RANK(C5,Umsatz)
Hier verwendet RANK aus Gründen der Übersichtlichkeit den genannten Bereich „Umsatz“ (C5:C11). Standardmäßig ordnet RANK 1 dem höchsten Wert zu, 2 dem zweithöchsten Wert, und so weiter. Dies funktioniert perfekt, solange numerische Werte eindeutig sind. Um jedoch mit numerischen Werten umzugehen, die Duplikate enthalten, müssen wir die Funktion COUNTIF verwenden, um Verbindungen zu lösen. Dies geschieht, indem das Ergebnis dieses Ausschnitts zu dem von RANK zurückgegebenen Wert hinzugefügt wird:
COUNTIF($C$5:C5,C5,C5)-1
Beachten Sie, dass der Bereich als gemischte Referenz eingegeben wird, die sich erweitert, wenn die Formel in der Tabelle kopiert wird. Wie geschrieben, beinhaltet diese Referenz die aktuelle Zeile, so dass wir 1 abziehen, um das erste Auftreten „null herauszunehmen“. Das bedeutet, dass der Ausdruck für jeden Zahlenwert Null zurückgibt, bis ein Duplikat gefunden wird. In der zweiten Instanz gibt der Ausdruck 1 zurück, in der dritten Instanz 2 und so weiter. Dies bricht effektiv die Verbindungen und ermöglicht es der Formel, eine fortlaufende Liste von Zahlen ohne Lücken zu erstellen.
Sobald die Formel vorhanden ist, können die Daten nach der Hilfsspalte sortiert werden. Es kann auch mit INDEX über die Werte in der Hilfsspalte abgerufen werden.
Hinweis: Diese Formel ist an ein Beispiel aus dem ausgezeichneten Buch Control + Shift + Enter von Mike Girvin angelehnt.
Verwandte Formeln
Einfache Text-Sortierformel
Um Textwerte in alphabetischer Reihenfolge dynamisch zu sortieren, können Sie eine Formel verwenden, die auf der Funktion COUNTIF basiert. In dem gezeigten Beispiel lautet die Formel in C5: = COUNTIF („Länder, „<=“ & B5), wobei „Länder“ der benannte Bereich B4 ist:……
Sortieren von Zahlen aufsteigend oder absteigend
- Um eine Liste von Zahlen dynamisch in aufsteigender Reihenfolge zu sortieren, können Sie eine einfache Formel basierend auf der Funktion SMALL mit einem expandierenden Bereich verwenden.
- In dem gezeigten Beispiel lautet die Formel in Zelle C5: = KLEIN („Daten“, „ROWS“ („$B$5:B5“)), wobei „Daten“ der Name…..
- Sortierte Werte mit Hilfsspalte anzeigen
- Um mit einer Hilfsspalte sortierte Werte abzurufen und anzuzeigen, können Sie eine INDEX- und MATCH-Formel verwenden, mit ein wenig Hilfe der Funktion ROWS. In dem gezeigten Beispiel lautet die Formel in F5: = INDEX ( Umsatz, MATCH ( ROWS ( $D$5:$D5), Sortierung, 0 )), die…..
Zufällige Sortierformel
Um bestehende Werte mit einer Formel zufällig zu sortieren, können Sie eine INDEX- und MATCH-Formel zusammen mit Hilfsspalten verwenden, wie im Screenshot gezeigt. In dem gezeigten Beispiel lautet die Formel in E5: = INDEX („Namen“, MATCH („ROWS“ („$D$5:$D5“), Sortierung, 0 ))…..
Sortieren und Extrahieren von eindeutigen Werten
Um eindeutige Werte aus einer Liste von Daten dynamisch zu sortieren und zu extrahieren, können Sie eine Array-Formel verwenden, um einen Rang in einer Hilfsspalte festzulegen, und dann eine speziell konstruierte INDEX- und MATCH-Formel verwenden, um eindeutige Werte zu extrahieren. In dem gezeigten Beispiel ist die…..
Verwandte Funktionen
Excel RANK-Funktion
Die Funktion Excel RANK gibt den Rang eines numerischen Wertes im Vergleich zu einer Liste anderer numerischer Werte zurück. RANK kann Werte von den größten bis zu den kleinsten (d.h. Top-Umsatz) sowie von den kleinsten bis zu den größten (d.h. schnellsten Zeit) Werten mit Hilfe eines…..
Excel COUNTIF-Funktion
COUNTIF ist eine Funktion zum Zählen von Zellen, die ein einzelnes Kriterium erfüllen. COUNTIF kann verwendet werden, um Zellen mit Datum, Zahl und Text zu zählen, die bestimmten Kriterien entsprechen. Die Funktion COUNTIF unterstützt logische Operatoren (>,…..
Verwandte Videos
Wie man Werte mit der RANK-Funktion bewertet
Die RANK-Funktion bietet Ihnen die Möglichkeit, Werte mit einer einfachen Funktion zu bewerten, die den Rang dynamisch berechnet. Wenn sich ein Wert ändert, erhältst du automatisch einen neuen Rang. …
Wie man die Funktion COUNTIF verwendet
Die Funktion COUNTIF ist sehr nützlich, wenn Sie z.B. überfällige Rechnungen, Mitarbeiter im Engineering oder Bestellungen über 100 Euro zählen möchten. In diesem kurzen Video erfahren Sie, wie Sie es verwenden können.
