Generische Formel
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Zusammenfassung
Um Elemente in einer Liste nach einem oder mehreren Kriterien zu ordnen, können Sie die COUNTIFS-Funktion verwenden. In dem gezeigten Beispiel lautet die Formel in E5:
=COUNTIFS(groups,C5,scores,">"&D5)+1
Dabei ist "Gruppen" der benannte Bereich C5: C14 und "Scores" der benannte Bereich D5: D14. Das Ergebnis ist ein Rang für jede Person in ihrer eigenen Gruppe.
Hinweis: Obwohl die Daten im Screenshot nach Gruppen sortiert sind, funktioniert die Formel mit unsortierten Daten einwandfrei.
Erläuterung
Obwohl Excel über eine RANK-Funktion verfügt, gibt es keine RANKIF-Funktion, um einen bedingten Rang auszuführen. Mit der Funktion COUNTIFS können Sie jedoch problemlos einen bedingten RANG erstellen.
Die COUNTIFS-Funktion kann eine bedingte Zählung anhand von zwei oder mehr Kriterien durchführen. Kriterien werden in Bereich / Kriterien-Paaren eingegeben. In diesem Fall beschränkt das erste Kriterium die Anzahl auf dieselbe Gruppe unter Verwendung des benannten Bereichs "Gruppen" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Dies allein gibt die Gesamtzahl der Gruppenmitglieder in der Gruppe "A" zurück, die 5 beträgt.
Das zweite Kriterium beschränkt die Anzahl auf nur Punkte, die höher sind als die "aktuelle Punktzahl" von D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Die beiden Kriterien arbeiten zusammen, um Zeilen zu zählen, in denen die Gruppe A und die Punktzahl höher ist. Für den Vornamen in der Liste (Hannah) gibt es in Gruppe A keine höheren Werte, daher gibt COUNTIFS Null zurück. In der nächsten Reihe (Edward) gibt es drei Punkte in Gruppe A, die höher als 79 sind, also gibt COUNTIFS 3 zurück. Und so weiter.
Um einen korrekten Rang zu erhalten, addieren wir einfach 1 zu der von COUNTIFS zurückgegebenen Zahl.
Rangfolge umkehren
Um die Rangfolge und den Rang in der Reihenfolge umzukehren (dh der kleinste Wert ist Rang 1), verwenden Sie einfach den Operator kleiner als ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Anstatt Punkte größer als D5 zu zählen, zählt diese Version Punkte, die kleiner als der Wert in D5 sind, wodurch die Rangfolge effektiv umgekehrt wird.
Duplikate
Wie bei der RANK-Funktion weist die Formel auf dieser Seite doppelten Werten denselben Rang zu. Wenn beispielsweise einem bestimmten Wert ein Rang von 3 zugewiesen wird und die Instanzen zwei Instanzen des Werts in den Daten enthalten, erhalten beide Instanzen einen Rang von 3, und der nächste zugewiesene Rang ist 5. Um das Verhalten nachzuahmen Mit der Funktion RANK.AVG, die in einem solchen Fall einen durchschnittlichen Rang von 3,5 zuweisen würde, können Sie einen "Korrekturfaktor" mit einer Formel wie der folgenden berechnen:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Das Ergebnis dieser obigen Formel kann zum ursprünglichen Rang hinzugefügt werden, um einen durchschnittlichen Rang zu erhalten. Wenn ein Wert keine Duplikate enthält, gibt der obige Code Null zurück und hat keine Auswirkung.
