Den Rang innerhalb einer Gruppe berechnen

Wenn die normale RANG-Funktion nicht weiterhilft: Rang-Berechnung innerhalb von Gruppen oder Kategorien.
 

Vor geraumer Zeit habe ich gezeigt, wie man in einer Werteliste einfache Rangfolgen berechnet.

Einige Leser wollten nun wissen, wie man so etwas macht, wenn die Liste nicht “flach” ist, sondern Gruppierungsmerkmale enthält. In Umsatzlisten könnten das Regionen sein, im Sport Altersklassen, Geschlecht oder ähnliches.

Der heutige Artikel zeigt eine Möglichkeit, Rangfolgen innerhalb solcher Kategorien zu berechnen.

Und so geht’s:

Die Beispieldatei zum Artikel kannst du dir bei Bedarf hier herunterladen.

Die einfache Rang-Berechnung

Zur Erinnerung: Für die stinknormale Rang-Berechnung liefert Excel die praktische Funktion RANG.GLEICH (bis einschließlich Excel 2007 gibt es stattdessen die Funktion RANG):
=RANG(Zahl;Bezug;Reihenfolge)

Damit wird der Rang einer Zahl innerhalb einer Liste ermittelt. Der optionale Parameter “Reihenfolge” legt fest, ob die Rangermittlung aufsteigend oder absteigend erfolgen soll. In meiner Beispieltabelle mit den 3000-Meter-Zeiten von 10 bekannten Top-Athleten wird die jeweilige Gesamtrangfolge berechnet:

Einfache Rang-Berechnung

Einfache Rang-Berechnung

Hier wird als Parameter für die Reihenfolge der Wert 1 angegeben, damit die schnellste Zeit die niedrigste Rangzahl erhält. Wird stattdessen 0 angegeben oder der Parameter weggelassen, wird für die schnellste Zeit der höchste Rang vergeben.

Soweit, so gut. Was macht man aber, wenn die Teilnehmerliste in verschiedene Gruppen aufgeteilt ist und der Rang nur innerhalb der jeweiligen Gruppe berechnet werden soll?

Rang-Berechnung innerhalb einer Gruppe

Mit der RANG.GLEICH-Funktion käme man hier nur weiter, wenn die Liste nach den Gruppen sortiert ist und die Formeln sich nur über den jeweiligen Gruppenbereich erstrecken. Das ist jedoch umständlich und fehleranfällig.

Ich habe meine Teilnehmerliste also um eine Spalte “Kategorie” erweitert und möchte nun den Rang des Läufers innerhalb seiner Kategorie bestimmen.

Die Lösung führt wieder einmal über die Allzweckwaffe SUMMENPRODUKT.

Rang-Berechnung innerhalb einer Kategorie

Rang-Berechnung innerhalb einer Kategorie

Zur Verdeutlichung sind die Datensätze einer Kategorie farblich markiert.

=SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13<=C4))

Im ersten Teil der Funktion wird die Zugehörigkeit zur jeweiligen Kategorie überprüft. Da es sich bei der SUMMENPRODUKT-Funktion um eine Array-Funktion handelt, wird für jeden einzelnen Datensatz die komplette Tabelle durchgerechnet und die Ergebnisse dann addiert und damit der Rang berechnet.

Wem sich die Arbeitsweise dieser SUMMENPRODUKT-Funktion nicht gleich auf Anhieb erschließt, kann das auch "zu Fuß" durchzurechnen. Zur leichteren Orientierung habe ich die Tabelle nach der Kategorienspalte sortiert und für die jeweils ersten drei Datensätze jeder Kategorie die Bestandteile der SUMMENPRODUKT-Funktion aufgedröselt berechnet.

Wichtig:
Die eingesetzten Formeln sind Array-Formeln und müssen zwingend mit Strg+Umschalt+Eingabe abgeschlossen werden, damit sie korrekt funktionieren. Sie werden dann automatisch mit den geschweiften Klammern versehen.

SUMMENPRODUKT zu Fuß

SUMMENPRODUKT zu Fuss

Für den ersten Datensatz sieht es also wie folgt aus:

In Spalte G wird berechnet, ob der jeweilige Datensatz dem Wert in Zelle B4 entspricht, also "Hobbit". Das Ergebnis ist WAHR, wenn die Zeile die Kategorie "Hobbit" enthält, ansonsten eben FALSCH.
In Spalte H wird berechnet ob der Wert der aktuellen Zeile kleiner oder gleich dem ersten Wert in Zeile 4 ist.
In Spalte I werden dann G und H multipliziert, wobei WAHR dem Wert 1 und FALSCH dem Wert 0 (Null) entspricht. Zum Schluss addiere ich in I 15 alle Ergebnisse und erhalte somit den Rang für den ersten Datensatz. Analog dazu werden diese Berechnungen für alle anderen Zeilen durchgeführt.

Das sieht jetzt vielleicht alles sehr kompliziert aus, ist es aber nicht. Denn diese Arbeit macht ja die SUMMENPRODUKT-Funktion automatisch, das hier sollte nur dazu dienen, die Technik dahinter ein wenig besser zu verstehen. Und das Gute daran ist, dass die Sortierung der Liste überhaupt keine Rolle spielt, sie dient hier nur der leichteren Veranschaulichung.

Rang-Berechnung in mehrstufigen Gruppen

Auf oben beschriebene Weise lassen sich auch mehrstufige Gruppen abbilden und die entsprechenden Rangfolgen berechnen. Wenn es also innerhalb einer Kategorie weitere Unterkategorien gibt, für die der Rang bestimmt werden soll, muss die SUMMENPRODUKT-Funktion nur um einen zusätzlichen Kriterienblock erweitert werden:

Rang über mehrstufige Kategorien

Rang über mehrstufige Kategorien

Das Prinzip ist exakt das gleiche wie oben beschrieben, nur eben mit einem weiteren Kriterium. Auch wenn hier die Lösung für das Problem nicht gleich offensichtlich war zeigt sich wieder einmal, wie mächtig und vielseitig die SUMMENPRODUKT-Funktion ist.

Kennst du noch andere Wege, um den Rang innerhalb von Gruppen zu bestimmen? Dann lass es uns unten in den Kommentaren wissen.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.


Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.