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
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
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 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
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.
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.
Hallo!
Die gezeigte Lösung „Rang-Berechnung innerhalb einer Gruppe“ hat mir sehr geholfen meine Liste mit fast zehntausend Zeilen aufzuarbeiten. Echt klasse!
Jedoch bringt das Ergebnis teilweise zweimal oder öfter den zweiten Platz (Rang) und dafür keinen Ersten weil beide die gleiche Punktzahl haben.
Wie kann ich erreichen, dass dann z.B. Zweimal der erste Platz angezeigt wird?
Hallo Armin,
eine gute Frage, für die ich aber leider keine Antwort parat habe. Vielleicht hat ein anderer Leser hier noch eine Idee.
Schöne Grüße,
Martin
Um das Problem zu lösen müssen zusätzlich „gleiche Ergebnisse“ mit „=C4“ ermittelt werden. Das geht so:
SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13=C4))
Weil das Ergebnis mindestens einen Treffer erzielt, aber vom vorherigen Ergebnis abgezogen wird, muss 1 addiert werden:
SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13=C4))+1
Insgesamt sieht die Lösung dann so aus:
=SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13<=C4))-SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13=C4))+1
Meine Antwort war zwar richtig, aber um drei Ecken gedacht. Es geht natürlich einfacher, wenn man gleich nur die kleineren Werte sucht und anschließend 1 addiert:
=SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13<C4))+1
Grüße aus Halle
Martin
Hallo Namenskollege,
vielen Dank für diese schöne Ergänzung, die Lösung liegt manchmal so nah!
Schöne Grüße,
Martin
Vielen Dank für deine Lösung. In meiner Fragestellung funktioniert die Formel leider ab „Rang 3“ nicht mehr. Sprich mehrere Werte belegen den 3.Rang, hier gibt die Formel jedoch nicht erwartungsgemäß „3“ aus, sondern Rang 2 + die Anzahl der gleichen Werte von Rang 3. In meinem Fall sieht das Ranking dann so aus: 1,2, 5 (…). Gibt es die Möglichkeit, auch eine fortlaufende Unterscheidung zu berechnen, also nicht nur für Rang 1 + 2?
Vereinfacht ausgedrückt, die Ränge 1 bis n können von einer beliebigen Anzahl an Werten belegt sein, sprich der 2. Rang kann 3x belegt, der 3. Rang 5x belegt, der 4. Rang 10x belegt sein usw.
VG
Johannes
=SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13<=C4))-SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13=C4))+1
Hi Martin!
Vielen Dank für deinen Tipp! Du leistest hier wirklich einen echten Mehrwert!
@Armin:
Wenn ich Werte doppelt in einer Liste habe, arbeite ich mit „verfremdeten“ Werten und addiere zu dem Originalwert den millionsten Zeilenwert (Also z.B. D5=A5+ZEILE()/1000000. Schon hat Excel nur noch ganz, ganz wenige doppelte.
Gruß,
Sebastian
Hallo Sebastian,
danke für dein nettes Feedback und den Tipp!
Schöne Grüße,
Martin
Hallo Martin, toller Tipp!
Wie kann ich die Rangfolge umkehren? D.h. längste Zeit = Rang 1.
Gruß
Jan
Hallo Jan,
ganz einfach: Statt „<=" verwendest du ">=“ in den Formeln. Also:
=SUMMENPRODUKT(($B$4:$B$13=B4)*($D$4:$D$13>=D4))
für die Sortierung innerhalb einer Kategorie und
=SUMMENPRODUKT(($B$4:$B$13=B4)*($C$4:$C$13=C4)*($D$4:$D$13>=D4))
für die Sortierung in beiden Kategorien.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für Deine super Erklärung.
Wie kann ich die Rangfolge von nicht zusammenhängenden Zellen bestimmen?
VG
Andreas
Hallo Andreas,
das geht, indem du die nicht zusammenhängenden Zellen markierst (z.B. mit Strg+Anklicken) und dann für diesen Bereich einen Namen vergibst. Und in der Formel beziehst du dich dann auf diesen Namen.
Schöne Grüße,
Martin
Hallo Zusammen,
diese Formel hat mir einige Stunden manuelles Ränge-sortieren gespart.
Eine Frage habe ich dennoch:
Ist es möglich, in einem Array / einer Spalte auf eine definierte Zeile zu verweisen, Beispiel:
=SUMMENPRODUKT(($N$2:$N$4727=N218)*($Y$2:$Y$4727=N218)*($F$2:$F$4727>=F218))
Siehe im zweiten Array das „=N218“
Viele Grüße
GL
Hallo Geraldine,
ich bin mir nicht sicher, ob ich das Problem richtig verstanden habe. Wenn du „Zeile“ sagst, dann heißt das, dass du in mehreren Spalten nebeneinander die Vergleichswerte hast? Oder meinst du eine einzelne feste Zelle? Falls letzteres der Fall ist: Ja, du musst halt nur einen absoluten Zellenbezug setzen. Also statt N128 eben $N$128
Schöne Grüße,
Martin
Hallo Martin
Deine Erklärungen hier helfen mir wirklich sehr. Vielen Dank für die tolle Arbeit.
Ich habe trotzdem noch ein Problem, dass ich nicht gelöst kriege. Und zwar bin ich in einem Sportschützenverein und möchte sämtliche Ranglisten im Excel automatisch erstellen. Alle „normalen“ Ranglisten, in welchen „nur“ die Altersgruppe, Nuller und Tiefschuss berücksichtig werden, habe ich hingekriegt.
Wo ich aber verzweifle, ist bei einer Rangliste wo der erstplatzierte jener ist nach den gesamt Punktzahl. Der Zweitplatzierte, der mit dem besten Tiefschuss Ergebnis ist, wenn er nicht schon aufgrund seiner gesamt Punktzahl erstplatziert ist. Der Drittplatzierte ist jener mit dem zweitbesten Ergebnis bei der gesamt Punktzahl, wenn er nicht schon vorher rangiert wurde. Der Viertplatzierte ist jener mit dem zweitbesten Tiefschuss Ergebnis, wenn er nicht schon vorher rangiert wurde und so weiter.
Vielleicht hast du mir da einen Tipp, mit welchen Formeln ich die Rangliste hinkriegen könnte.
Grüsse Ray
Hallo Martin,
ich habe eine Rangliste in die zwei Kriterien einfließen sollen, die über Funktionen berechnet werden. Ich komme da nicht weiter, weil Excel mir wegen der Funktion in der Zelle sagt, dass zu viele Argumente einbezogen werden werden. Hier der Fall ganz kurz:
7 Punktrichter geben Ihre Wertung. Die höchste und niedrigste Wertung werden gestrichen und die „mittleren“ fünf Wertungen werden addiert. Haben jetzt zwei Akteure die gleiche Punktzahl, so werden die Streichresultate zur Wertung addiert (also alle 7 Wertungen) und die jetzt höhere Punktzahl ergibt den höheren Rang. Ich habe dazu auch eine Funktion mit Rang.Gleich + Zählenwenn erstellt, die funktioniert nicht, weil Excel moniert: Zu viele Argumente. Ich glaube, dass es damit zusammenhängt, dass ich zuerst auf die Spalte mit Summenformel für die 5 Wertungen zugreife und dann auf die Spalte mit der Gesamtpunktzahl und hier ja Funktionen in den Zellen sind und keine reinen Zahlen. Kannst Du mir einen Tipp geben?
Gruss Alexander
Hallo Alexander,
da dies eine etwas komplexere Angelegenheit zu sein scheint, wird es vermutlich nicht ohne Zwischenschritte gehen. Einen konkreten Tipp kann ich dir an dieser Stelle leider auch nicht geben. Die Fehlermeldung „Zu viele Argumente“ hat aber nichts damit zu tun, dass sie auf Formelzellen zugreift. Es spielt keine Rolle, ob in einer Zelle direkt eine Zahl steht oder diese Zahl per Formel berechnet wurde. Du hast also höchstwahrscheinlich nur irgendwo einen Wurm in der Klammersetzung und solltest hier nochmal ansetzen.
Schöne Grüße,
Martin
Hallo Martin,
mein Name ist Peter Bück, und durchstöbere Deine Excel-Tips regelmäßig und bin begeistet, was da alles möglich ist, wenngleich ich leider nicht gut genug bin um mir anhand Deiner Tips und Vorlagen ein Excel-Problem zu lösen.
Wie sich unschwer erkenne läßt bin ich oft auf Zwift mit meinem virtuellen Fahrrad unterwegs. Dort gibt es verschiedene Anstiege, die man „hochfahren“ kann.
Ich würde nun gerne eine Art Rangliste erstellen wollen, wo ersichtlich ist, wann (Datum) und in welchem JAhr ich bei den bestimmten Anstiegen die Bestzeit gefahren bin.
Tabelle: (Daten) Auswertung:
Datum Anstieg Bestzeit Anstieg Datum Jahr Bestzeit
1.1.22 Anstieg1 32:30 Anstieg1 (Datum) (Jahr) beste Zeit
1.1.23 Anstieg1 34:44 Anstieg2 (Datum) (Jahr) beste Zeit
2.2.23 Anstieg1 33:22 Anstieg3 (Datum) (Jahr) beste Zeit
2.4.23 Anstieg2 15:01
3.4.21 Anstieg2 14:59
5.6.23 Anstieg3 45:33
Vielleicht kannst Du mir an dieser Stelle weiterhelfen, da ich weder mit KKLEINSTE und/oder SUMMENPRODUKT hier weiterkomme.
Vielen Dank für Deine Hilfe und vorab Schöne Weihnachten
Gruß aus Bayern
Peter
Hallo Peter,
für diese Aufgabe würde ich auf die MINWENNS-Funktion zurückgreifen. Damit lässt sich das Minimum auf der Basis von einschränkenden Kriterien berechnen. Zuerst würde ich das Minimum der Bestzeit pro Anstieg berechnen (Zelle I2 in dem Bild). Danach das Datum in G2, hier aber mit den Kriterien Anstieg und Bestzeit aus Spalte I.
Schöne Grüße und auch dir schöne Weihnachten,
Martin
Sehr geehrter Herr Weiss
Mit Hilfe der RANG.GLEICH-Funktion kann ich eine Sport-Rangliste erstellen, wenn alle Teams unterschiedlich viele Punkte haben.
Falls zwei Teams die gleiche Punktzahl haben, sollte zuerst das Ergebnis der direkten Begenung und bei Unentschieden die Toredifferenz zum Tragen kommen. Wie man dies programmiert, habe ich nirgends gefunden, auch in „19 Gedanken zu…“ nicht. Wahrscheinlich ist es schwierig. Was meinen Sie dazu?
Freundliche Grüsse aus der Schweiz
Renato Gelpi
Sehr geehrter Herr Gelpi,
das lässt sich leider nicht so einfach lösen. Sie müssten vermutlich versuchen, über Hilfsspalten für die gewünschten Kriterien Zusatzpunkte (evtl. im Nachkommabereich) zu vergeben und somit eine künstliche Rangfolge erstellen.
Mit einer konkreten Lösung kann ich jedoch leider auch nicht dienen.
Schöne Grüße,
Martin