Schneller, höher, weiter:
Egal, ob es um Sportwettkämpfen oder um die Ermittlung des besten Verkäufers in einem Unternehmen geht, es gibt viele Anwendungsfälle, bei denen ein Ranking bzw. eine Rangliste zu erstellen ist.
Excel bietet für genau diesen Zweck eine einfache und praktische Funktion, die ich heute vorstellen möchte:
- RANG.GLEICH (ab Excel 2010)
- RANG (bis Excel 2007)
Und so geht’s:
Die Rangfolge ermitteln
Ich habe schon in einigen Artikeln gezeigt, dass man mit den Funktionen KKLEINSTE bzw. KGRÖSSTE eine Liste der Größe nach sortiert ausgeben kann. In manchen Fällen willst du jedoch wissen, welchen Rang ein bestimmtes Element auf deiner Liste einnimmt.
Nehmen wir also an, du hast eine Umsatzliste mit vielen Verkäufern und möchtest jetzt wissen, wer die Top 5-Verkäufer sind. Hier kommt die RANG.GLEICH-Funktion ins Spiel. (Hinweis: in Excel 2007 gibt es stattdessen die Funktion RANG. Beide Funktionen kommen in unseren Beispielen aber zum gleichen Ergebnis)
So sieht unsere Beispiel-Tabelle aus:
Umsatzübersicht nach Verkäufern
Um nun den jeweiligen Rang des einzelnen Verkäufers zu ermitteln, kommt die Funktion RANG.GLEICH zum Einsatz.
=RANG.GLEICH(Zahl; Liste; [Reihenfolge])
Gibt man für den Parameter „Reihenfolge“ den Wert 0 (Null) ein oder lässt in weg, erhält das größte Element in der Liste den Rang 1, die Liste ist also absteigend sortiert:
Rangliste (Version 1)
Verwenden wir stattdessen als Reihenfolge den Wert 1, wird der Rang aufsteigend ermittelt, das heißt, der kleinste Wert in der Liste bekommt den Rang 1:
Rangliste (Version 2)
Wenn wir nun unsere Umsatzliste noch sortieren, wird eine weitere Besonderheit deutlich. Haben mehrere Werte nämlich den gleichen Rang, wird für den nächsten Wert der folgende Rang überspungen.
In unserem Beispiel gibt es also zweimal den Rang 2 und zweimal den Rang 7, folglich werden die Ränge 3 und 8 nicht vergeben:
Sortierte Rangliste
Auf diesem Weg lassen sich z.B. auch Ergebnisse aus Sportwettkämpfen in der gewohnten Rangfolge darstellen:
Sportwettkampf
Damit steht der nächsten Leichtathletik-WM nichts mehr im Wege!
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.
Guten Tag
Ich habe fünf Ranglisten von fünf Skirennen! Pro Skirennen werden Punkte vergeben! Der Sieger erhält 30 Punkte und dann geht es pro Rang einen Punkt runter! Ab Rang 31 gibt es null Punkte
Nun will ich aus diesen fünf Einzelrennen den Gesamtcup erstellen lassen
Ist das zu machen?
nö
Guten Tag
Ich habe in unserem Managerspiel mit zehn Spielern eine Rangliste erstellt. Nun möchte ich den Spielern Punkte zuteilen, Sieger 10 Punkte und der Verlierer 1 Punkt.
Ist das machbar?
Hallo Michael,
ja, das sollte machbar sein: Einfach eine Referenztabelle mit zwei Spalten anlegen. Spalte 1 = Rang, Spalte 2 = Punkte. Und dann in der Rangliste per SVERWEIS die Punkte zum jeweiligen Rang aus dieser Referenztabelle auslesen.
Schöne Grüße,
Martin
Suiiiiiiiiiiiiiii
Ich habe in unserem Managerspiel mit zehn Spielern eine Rangliste erstellt. Nun möchte ich der Rangliste den Spielern Punkte zuordnen, der Sieger 10 Punkte der letzte 1 Punkt.
Ist das mit Excel machbar?
Wie heißt denn die Formel? Ich bin nicht gerade der Excel Freak. Die Formel für die Rangliste habe ich ja, aber was kommt noch in die RANG Formel damit ich die Punkte zuordnen kann?
Schöne Grüße
Michael
Hallo Michael,
wenn die Punktvergabe tatsächlich absteigend nach Rang sein soll, kannst du das auch mit einer zweiten RANG-Formel erledigen, also ohne zusätzliche Referenztabelle und SVERWEIS. Das könnte dann so aussehen:
Die Formel in Spalte C macht nichts anderes, als den umgekehrten Rang zu berechnen. Und damit hättest du genau die Punktzahl. Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Elf des Tages
1
7
4
1
4
1
7
4
7
7
Elf des Tages
8
1
5
8
5
8
1
5
1
1
43
Die Formel passt wenn die Platzierung von 1 bis 10 ohne mehrfach Platzierung ist.
Bei einer mehrfach Platzierung stimmt die Endsumme von 55 nicht mehr. Wo ist da der Fehler?
Schöne Grüße Michael
Hallo Michael,
ich kann jetzt zwar den Werten aus deinen Listen nicht ganz folgen. Aber bei Mehrfachplatzierungen wird vermutlich doch die Lösung mit SVERWEIS und einer Referenztabelle helfen:
Schöne Grüße,
Martin
Elf des Tages Platzierung
1
7
4
1
4
1
7
4
7
7
Elf des Tages Punkte richtig wäre
8 9 10+9+8=27/3=9
1 2,5 4+3+2+1=10/4=2,5
5 6 7+6+5=18/3=6
8 9
5 6
8 9
1 2,5
5 6
1 2,5
1 2,5
43
Bei richtig wäre meinte ich mit Mehrfachplatzierung und den entsprechenden Punkten.
Schöne Grüße Michael
Danke für den Tipp!
Aber: Wie kann ich aus einer Liste von Teilnehmern mit dem Rang in der Spalte nach dem Namen in einem anderen Reiter die Plätze 1-3 ausgeben?
Hallo Thorsten,
du hast also im Blatt Tabelle1 die Rangliste, beispielsweise mit Namen in Spalte A2:A10 und Rang in Spalte C2:C10. Und möchtest nun die Namen der ersten drei Ränge im Blatt Tabelle2 ausgeben.
Sofern es keine doppelten Ränge gibt, kannst du dazu die Kombination INDEX + VERGLEICH verwenden:
=INDEX(Tabelle1!$A$2:$A$10;VERGLEICH(1;Tabelle1!$C$2:$C$10;0))
=INDEX(Tabelle1!$A$2:$A$10;VERGLEICH(2;Tabelle1!$C$2:$C$10;0))
=INDEX(Tabelle1!$A$2:$A$10;VERGLEICH(3;Tabelle1!$C$2:$C$10;0))
Bei mehrfach vorkommenden Rängen wird es komplizierter, da braucht es Matrixformeln, um alle Namen auszugeben.
Schöne Grüße,
Martin
Guten Tag, zu dem Leichtathletik-WM-Beispiel habe ich folgende Frage: Die Rangfolge soll innerhalb der verschiedenen Altersklassen UND getrennt nach Geschlecht erfolgen. Ich habe eine Spalte C „Geschlecht“, eine Spalte E „Alter“ und eine Spalte K „Gesamtpunkte“ aller 300 Kinder. Die letzte Spalte N soll nun die Platzierung innerhalb des Jahrgangs beinhalten. Ich habe es mit WENN, UND, SVERWEIS, RANG.GLEICH und SUMMENPRODUKT versucht… Ich gebe auf… Jahrgänge: <=8, =9, =10 … bis =14
Vielen Dank für die Hilfe.
Hallo Frau Tesch,
so etwas in der Art habe ich in diesem Artikel beschrieben:
https://www.tabellenexperte.de/den-rang-innerhalb-einer-gruppe-berechnen/
Schöne Grüße,
Martin
Hallo Herr Weiß,
vielen Dank für Ihre Hilfe.
Ich habe folgendes eingegeben:
=SUMMENPRODUKT(($F$4:$F$300=F4)*($C$4:$C$300=C4)*($K$4:$K$300<=K4))
Spalte F: 7 Jahrgänge: <8, 9, 10, 11, 12, 13, 14
Spalte C: Geschlecht: m oder w
Spalte K: erreichte Punktzahl
Ziel: Ermittlung der Jahrgangsbesten (m und w getrennt)
Leider habe ich mein Ziel noch nicht erreicht. Was mache ich falsch?
Viele Grüße
Ines
Habe es jetzt so gelöst:
=WENN((K4=K4)))
Dankeschön!
Gruß
Hallo Martin,
ich habe mir für Simracing eine Meisterschaft Tabelle erstellt wie beschrieben, Namen, Rang, Punkte wo wir 14 Fahrer sind und der erste bekommt 28 Punkte der zweite 26 usw. absteigend. Die bei Platz 1 mit 28 und Platz zwei mit 26 Punkten wird angezeigt das beide auf dem ersten Platz sind, gibt es dafür eine Lösung ?
Hallo Wolfgang,
ich verstehe nicht ganz, warum bei dir beide mit Platz 1 angezeigt werden, obwohl es unterschiedliche Punktzahlen gibt. Wie sieht denn deine Formel aus?
Schöne Grüße,
Martin
Hallo Herr Weiß,
ja, ich bin das Problem vor dem Computer. Von Excel wenig Ahnung.
Ich bin mir sicher, dass Sie mir (wahrscheinlich ohne große Mühe) weiterhelfen können.
Ich werde im März ein Kniffel-Turnier mit ca. 20 Teilnehmern ausrichten. Gespielt werden 10 Runden über das Jahr verteilt. Mein Problem ist, dass bei ergebnisgleichen Spielen der gleiche Rang vergeben werden soll. Genauer : wir spielen nicht gegen einen Gegner, sondern pro Runde um die höchste Punktezahl. Diese soll dann umgesetzt werden in das Ranking.
Und wenn z.B. es einen vierten Platz zweimal geben sollte, dann muss Platz 5 entfallen und es danach mit Platz 6 weitergehen.
Meine Frage : gibt es für so etwas Mustertabellen ? Oder: worauf muss ich beim Tabellenbau achten ?
Um eine kurze und einleuchtende Antwort wäre ich sehr dankbar
Mit Gruß
Rainer Münstermann
Hallo Herr Münstermann,
wir alle sind manchmal das Problem vor dem Computer 😉
Die im Artikel beschriebene Funktion RANG.GLEICH macht genau das, was Sie möchten: Wenn ein Rang zweimal vorhanden ist, entfällt automatisch der nächste Rang. Man sieht es auch in den Screenshots.
Schöne Grüße,
Martin
Hallo Herr Weiß,
Sorry erstmal für den späten Dank von mir. Nach einigen Fehlversuchen hat’s dann
mit Ihrer Hilfe auch geklappt ! Super …mit ’nem kleinen Haken:
in Ihrem Beispeilbild „sortierte Rangliste“ ist die Funktion Rang(0) genau die richtige für mich.(rote Kästchen)
Bedeutet ja auch : 1x Gold und 2x Silber. Okay ! Aber die Funktion Rang(1), die ich für die Zuteilung von Wertungspunkten benötige, zeigt für Gold zwar die „Goldpunkte an, aber für Silber werden nur „Bronzepunkte“ angezeigt. (blaue Kästchen)
Die beiden Zweitplatzierten müssten 11 Punkte bekommen. Wie kriege ich das hin?
Falls ich diese Frage zu umständlich gestellt habe, kann ich mich auch gern mit Ihnen telefonisch in Verbindung setzen.(Rufnummer ?). Anbei aus dem letzten Jahr als Beispiel die Tabelle von Match 3 :
http://www.knoesel-kassel.de/2019/2019Matches1-4.htm
in der 2mal 8 Punkte und 2mal 3 Punkte vergeben werden. Damals alles noch Handarbeit.
Vielleicht ist das ja auch ein bisschen zu viel gewünscht…
Mit Gruß aus Kassel
Rainer Münstermann
Hallo Herr Münstermann,
die Funktion liefert ja keine Punkte zurück, sondern die Rangfolge. Um die gewünschten Punkte je Rang vergeben zu können, würde ich eine separate Punktetabelle pflegen, die je Rang die zu vergebenden Punkte enthält. Danach können Sie wie gehabt mit der RANG-Funktion den Rang bestimmen und daneben über einen SVERWEIS zum Rang die gewünschte Punkte aus der Punktetabelle dazuholen.
Schöne Grüße,
Martin
Hallöchen,
dankeschön dafür. Ich habe eine ähnliche Liste.
Allerdings sollen pro Verkäufer die TOP 40 Kunden ausgespuckt werden.
Ich verzweifel irgendwie und komme auf keine Lösung.
Vielleicht kann mir ja jemand helfen 🙂
Dankeschön & viele Grüße Mela
Hallo Mela,
in so einem Fall würde ich mich nicht mit Formeln herumquälen, sondern eine Pivot-Tabelle einsetzen. Denn hier gibt es schon einen eingebauten „Top-10“-Filter, der natürlich auch auf Top-40 ausgeweitet werden kann.
Schöne Grüße,
Martin
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine Rückmeldung. Darauf bin ich gestern dann tatsächlich auch noch gekommen 😀
Allerdings habe ich nun das Problem, dass ich für insg. 28 Verkäufer die Top-Kunden in einer Liste benötige. Das habe ich nicht rausfinden können und habe jetzt quasi 28 einzelne Blätter gemacht, die Pivot wieder rausgenommen und müsste nun alle Daten aus den 28 Blätter in ein Blatt kopieren.
Vielleicht gibt’s hier ja auch noch eine einfachere Löstung. Werde diese Art Liste künftig wohl öfters brauchen.
In jedem Fall: DANKE dass du geantwortet hast 🙂
Schöne Grüße; Mela
Hallo Mela,
es kommt nur darauf an, dass du den Wertefilter im richtigen Feld setzt, dann funktioniert es auch in einer einzigen Pivot-Tabelle. Angenommen, du hast folgende Felder:
Verkäufer und Kunde im Zeilenbereich
Umsatz im Wertebereich
Den Top-10-Filter setzt du dann auf das Feld „Kunde“, dann sollte es passen.
Schöne Grüße,
Martin
Hallo,
ich komme einfach nicht weiter, bei meinem Problem.
Würde gerne alle Mitglieder eines Teams auflisten, wobei ein Spieler in mehreren Teams sein darf.
Gerd Team 1 Team 2 Team 3 Team 4
Hans Team 2 Team 3 Team 5 Team 4
Peter Team 4 Team 1 Team 5 Team 3
Steve Team 4 Team 2 Team 1 Team 5
Leo Team 3 Team 5 Team 2 Team 1
Name Name Name Name
Team 1
Team 2
Team 3
Team 4
Team 5
Kann ich das mit Index und Vergleich abbilden? Meine bisherigen Versuchen sind alle gescheitert.
LG,
Lena
Hallo Lena,
ich fürchte, mit INDEX und VERGLEICH dürfte das beim gegebenen Tabellenaufbau sehr schwierig werden. Zumindest fällt mir spontan keine Lösung dafür ein. Ich würde stattdessen die Ausgangstabelle per Power Query in eine flache Liste transformieren, also nur zwei Spalten Name + Team. Und dann ließen sich über eine TEXTVERKETTEN-Funktion alle Namen je Team zusammenfassen.
Aber vielleicht hat ja ein anderer Leser noch eine bessere Idee.
Schöne Grüße,
Martin
Hallo Herr Weiß,
Ich werte gefahrenen Rundenzeiten bei einer Rennsimulation aus und habe eine Rangliste der Top 25 Strecken erstellt. Platz 1 ist die Strecke mit den meisten gefahrenen Zeiten, usw.Beispiel:
P1 Nürburgring 43 Zeiten
P2 Hockenheimring 41 Zeiten
usw.
Wenn es nun mehrere Strecken mit gleich vielen Zeiten gibt, weist Google Docs für alle die gleiche Platzierung in der Rangliste aus. Soweit noch ok. Es wird allerdings auch jedes Mal der gleiche Streckenname angezeigt.
Beispiel:
P17 Falkenberg 4 Zeiten
P17 Falkenberg 4 Zeiten
P17 Falkenberg 4 Zeiten
Natürlich sind es 3 verschiedene Strecken wo 4 Zeiten gefahren wurden. Ich grüble schon lange an einer Lösung wie ich die weiteren platzierten Strecken namentlich anzeigen kann.
Ich hoffe es klingt nicht nach zu viel Kauderwelsch.
Das wäre die Formel dazu die den Streckennamen ermittelt:
=INDEX($A$4:$A$136;VERGLEICH(H22;$B$4:$B$136;0))
Haben Sie da eine Idee?
Vielen Dank!
Jörg
Hallo Herr Unkelbach,
es gibt möglicherweise eine Formellösung in Kombination mit KGRÖSSTE, ähnlich wie in dem Artikel „Besser als SVERWEIS“ beschrieben. Aber das werden meisten komplexe und schwer durchschaubare Gebilde. Ich empfehle in solchen Fällen immer, sich stattdessen mal eine Pivot-Tabelle anzusehen. Dort sind solche Dinge meist viel einfacher abzubilden und ohne komplexe Formeln.
Was da in Google Docs möglich ist, kann ich aber nicht beurteilen.
Schöne Grüße,
Martin
Hallo Martin, vielen Dank für Deine Tipps. Ich habe auf einem Tabellenblatt mehrere Pivots mit Punkten und den entsprechenden Rängen erstellt (2 Spalten). Immer, wenn ich die Daten aktualisiere, verschwindet in der Rang-Spalte der Pivot der Rang – stattdessen erscheint wieder die zugrunde liegende Punktzahl. Diese hatte ich in der Pivot als Spalte eingefügt und dann mit Wertefeldeinstellung und der Rang-Funktion absteigend anzeigen lassen. Da es 8 Pivots auf einem Blatt sind, müsste ich die nach jeder Aktualisierung immer wieder neu einstellen. Hast Du eine Idee, wie ich das verhindern kann?
Hallo Ina,
dieses Verhalten irritiert mich sehr, denn bisher habe ich noch nie erlebt, dass beim Aktualisieren der Pivot-Tabelle die Wertfeldeinstellungen zurückgesetzt werden. Daher bin ich selbst gerade etwas ratlos…
Mit welcher Excel-Version arbeitest du denn?
Schöne Grüße,
Martin
wir möchten im Schützenverein ein Freundschaftsschiessen mit einem Nachbarverein durchführen, jetzt sind wir auf der suche nach einem Programm zur Auswertung der Resultate das Programm ist 2 Probeschuss 5 Einzelschuss und 3 Serienschuss hat da jemand eine einfache Lösung
Hallo Beat,
eine fertige Lösung habe ich auch nicht, aber die hier vorgestellten RANG-Funktionen dürften eine gute Ausgangsbasis sein.
Schöne Grüße,
Martin
Moin zusammen,
schon mal sehr hilfreich eure Beiträge, vielen Dank dafür 🙂
Ich würde gern wissen, wie man es einstellen kann, wenn ich von links nach rechts (A1:K1) die Namen habe
und von A2:K2 unterschiedlich große Werte.
Dafür würde ich mir gern in einer leeren Zelle den Namen mit dem höchsten Wert anzeigen lassen.
Wer kann mir hier weiterhelfen? Wäre sehr dankbar
Hab’s rausgefunden!
Ich habe die WENN Formel genutzt (wenn(…;wenn(…;wenn…)))
Aus 11 verschiedenen Namen, von links nach rechts, darunter die Werte und dann will ich
in Zelle XY den Namen mit dem höchsten Wert haben, folgendes hab ich „erformelt“:
.=IF(CT52=1;CT48;IF(CU52=1;CU48;IF(CV52=1;CV48;IF(CW52=1;CW48;IF(CX52=1;CX48;IF(CY52=1;CY48;IF(CZ52=1;CZ48;IF(DA52=1;DA48;IF(DB52=1;DB48;IF(DC52=1;DC48;IF(DD52=1;DD48;““))))))))))).
Das selbe hab ich dann mit dem Zweitplatzierten gemacht nur statt der =1 eine =2 und eine =3 für den
Drittplatzierten genommen und so weiter 🙂
Hallo Matshek,
ja, das ist eine Variante. Bei sehr vielen Namen wird es nur schnell unübersichtlich. Etwas einfacher wäre es so:
In der Zeile 3 wird ganz normal der Rang des jeweiligen Wertes berechnet. Und in den Zellen B6:B8 steht dann die INDEX-Formel, in der man mit Hilfe der VERGLEICH-Funktion den dazugehörigen Namen ermittelt.
Schöne Grüße,
Martin
Versuche gerade aus Werten einer REIHE, die nicht konsekutiv sind, eine RANG.GLEICH Bestimmung zu erreichen:
=RANG.GLEICH(D2;D2:G2:J2:M2;0)
also den Wert D2 im Rang zu D2 G2 J2 M2
Hallo Ulrich,
das funktioniert, wenn du für die genannten Zellen einen Namen vergibst. Also:
Markiere bei gedrückter Strg-Taste nacheinander D2, G2, J2 und M2 und lege dann über das Menü „Formeln | Namen definieren“ einen beliebigen Namen fest (z.B. Suchbereich).
Dann verwendest du diesen Namen in deiner Formel:
=RANG.GLEICH(D2;Suchbereich;0)
Schöne Grüße,
Martin
Super, danke – klappt prima in einer Zeile.
Kann man diese Referenzen auch in Folgezeilen bernehmen?
Hallo Ulrich,
das sollte funktionieren. Du musst nur im Namensmanager die absoluten Zeilenbezüge in relative ändern. Also in meinem Beispiel die Dollarzeichen vor der 2 entfernen.
Statt
=Tabelle1!$D$2;Tabelle1!$G42;Tabelle1!$J$2;Tabelle1!$M$2
heißt es dann:
=Tabelle1!$D2;Tabelle1!$G2;Tabelle1!$J2;Tabelle1!$M2
Damit passt sich die Zeilennummer im Suchbereich automatisch an, wenn du die Rangformel in der nächsten Zeile einträgst.
Schöne Grüße,
Martin
Super, wäre ich nie drauf gekommen!
Hallo, ich muss eine Rangliste für den Schützenverein erstellen.
Das ist nicht das Problem.
Was ich nicht weiss, wie man den Rang berechnet, wenn gleiche Punktzahl ist. Den dann geht es wie folgt.
1. die meisten 10 er im ersten Durchgang wenn gleich dann der 2. Durchgang und wenn auch gleich, dann der 3. Durchgang.
Wie mache ich dies?
Besten Dank für die Hilfe
Hallo Floh,
ich habe leider keine Ahnung, wie deine Tabelle genau aussieht. Aber ich stelle mir vor, dass du zuerst die normalen Ränge berechnest. Danach müsstest du mit ZÄHLENWENN pro Durchgang und Schützen die 10er bestimmen. Teile die Summe der 10er z.B. durch 100, damit du eine möglichst kleine Dezimalzahl erhältst und addiere diesen Wert dann zum zuvor berechneten Rang. Dann solltest du hoffentlich einen eindeutigen Rang erhalten.
Schöne Grüße,
Martin
Hallo Martin, ein gutes neues Jahr erstmal.
ich versuche eine Rangliste für ca. 150 Teilnehmer zu erstellen (Schützen)
Wettbewerb 1 am 20.01.2023
Wettbewerb 2 am 22.01.2023
Wettbewerb 3……..
Wettbewerb 4……
Leider bekomme ich immer die Fehlermeldung „es liegt ein Zirkelbezug vor.
Gibt es nicht irgendwo eine fertige Rangliste zum downloaden??
Darf auch gerne etwas kosten.
WOW
Hallo Martin, ich scheitere am Ranking wenn negative Zahlen dabei sind und ich die Abweichung eines Wertes als Ranking darstellen will. Gibt es dafür eine Lösung?
Danke und viele Grüße!
André
Hallo André,
du könntest negative Abweichungen mit Hilfe der Funktion ABS in einen positiven Wert umwandeln. Damit werden alle Abweichungen, positive wie negative, gleich behandelt und das Ranking sollte funktionieren.
Schöne Grüße,
Martin
klemmt
Hallo Herr Weiß
Vielen Dank für diesen Artikel. Genau die Lösung die ich gesucht habe.
MFG
Hallo Herr von Schwartzenberg,
vielen Dank für Ihr Feedback, freut mich sehr zu hören.
Schöne Grüße,
Martin