Den Rang innerhalb einer Gruppe berechnen 19

Artikelbild-194
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.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Armin Antworten abbrechen

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

19 Gedanken zu “Den Rang innerhalb einer Gruppe berechnen

  • Avatar-Foto
    Armin

    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?

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Martin Springer

        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

        • Avatar-Foto
          Martin Springer

          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

          • Avatar-Foto
            Martin Weiß

            Hallo Namenskollege,

            vielen Dank für diese schöne Ergänzung, die Lösung liegt manchmal so nah!

            Schöne Grüße,
            Martin

          • Avatar-Foto
            Johannes

            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

      • Avatar-Foto
        Sebastian

        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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Andreas

    Hallo Martin,

    vielen Dank für Deine super Erklärung.

    Wie kann ich die Rangfolge von nicht zusammenhängenden Zellen bestimmen?

    VG
    Andreas

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Geraldine

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Ray

    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

  • Avatar-Foto
    Dr. Alexander Kaul

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Peter B.

    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

    • Avatar-Foto
      Martin Weiß

      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