Die Summe der Top-3-Werte 7

Mit einem kleinen Trick lässt sich die Summe einer Top-X-Liste berechnen
 

Nicht nur zu Zeiten von Olympischen Spielen sind Ranglisten sehr beliebt. Vor allem (aber nicht nur) in der Welt der Wirtschaft werden wir ständig mit irgendwelchen Top-Listen konfrontiert: Welcher Verkäufer macht den meisten Umsatz, welche Abteilung hat am meisten Budget eingespart oder wo gibt es die schnellsten Finanzämter?

Das Erstellen solcher Hitlisten ist mit Excel kein Problem: Die Funktionen RANG.GLEICH (bzw. RANG), KGRÖSSTE, und KKLEINSTE helfen bei der Ermittlung von Rangfolgen.

Wie aber berechnet man z.B. die Summe der 3 größten (oder 5 kleinsten) Werte einer langen Liste?

So geht’s:

Die folgende Beispieltabelle zeigt eine Liste von 15 Werten (du kannst sie bei Bedarf hier herunterladen). Nun soll die Summe der drei größten Werte gebildet werden:

Die Beispieltabelle mit der Summe aller Werte

Die Beispieltabelle mit der Summe aller Werte

Möglichkeit 1: Der mühsame Weg

Dazu berechne ich in einer separaten Spalte mit Hilfe der Funktion KGRÖSSTE die drei größten Werte:
=KGRÖSSTE(Bereich; k)
Der zweite Parameter k wird durch den gewünschten Rang ersetzt: Für den größten Wert ist k = 1, für den zweitgrößten ist k = 2 usw. Damit komme ich auf folgendes Ergebnis:

KGRÖSSTE-Funktion (Variante 1)

KGRÖSSTE-Funktion (Variante 1)

Bei nur drei Werten lässt sich k ja noch mit wenig Aufwand manuell anpassen. Wenn du hingegen eine Liste von 10.000 Einträgen hast und die Top-100 addieren möchtest, wird es schon ziemlich zäh.

Möglichkeit 2: Der nicht ganz so mühsame Weg

Die Herangehensweise ist grundsätzlich die gleiche, wie bereits oben gezeigt. Lediglich verwende ich für den Parameter k keinen fixen Wert, sondern eine Formel:
=KGRÖSSTE(Bereich; ZEILE(Bezug))
Die ZEILE-Funktion gibt immer die Zeilennummer des angegebenen Bezugs zurück. Wenn ich mit ZEILE(1:1) als Bezug die komplette erste Zeile angebe, dann liefert die ZEILE-Funktion logischerweise den Wert 1 zurück. Beim Kopieren der Formel nach unten wird daraus ZEILE(2:2), ZEILE(3:3) usw. Und damit ändert sich auch der k-Wert automatisch.

Daher sieht die verbesserte Version meiner Beispieltabelle jetzt so aus:

KGRÖSSTE und ZEILE-Funktion (Variante 2)

KGRÖSSTE und ZEILE-Funktion (Variante 2)

Schon deutlich weniger Arbeit, vor allem bei großen Listen. Aber es ist eben immer noch eine Hilfsspalte notwendig. Das geht doch sicherlich noch besser, oder?

Möglichkeit 3: Der elegante Weg

Wenn ich nun irgendwie die SUMME-, die KGRÖSSTE- und die ZEILE-Funktion kombinieren könnte, dann wäre die Hilfsspalte überflüssig. Und genau das machen wir mit Hilfe einer Array-Funktion:
{=SUMME(KGRÖSSTE(B2:B16;ZEILE(1:3)))}
WICHTIG
Die geschweiften Klammern in einer Array-Funktion dürfen nicht von Hand eingegeben werden. Stattdessen muss die Eingabe zwingend mit der Tastenkombination Strg+Umschalt+Enter abgeschlossen werden, sonst funktioniert’s nicht!

(Weitere Anwendungsbeispiele für Array-Funktionen findest hier, hier oder auch hier)

Damit haben wir mit nur einer einzigen Formel und ohne überflüssige Hilfsspalten das gewünschte Ergebnis:

Die elegante Lösung mit der Array-Funktion

Die elegante Lösung mit der Array-Funktion

Und wenn du statt der Top-3 in deiner Liste die Top-10 benötigst, musst du nur den Teil in der ZEILE-Funktion anpassen auf ZEILE(1:10)

War doch gar nicht so schwer, oder?
 

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.

7 Gedanken zu “Die Summe der Top-3-Werte

    • Martin Weiß Autor des Beitrags

      Hallo Luschi,

      vielen Dank für den guten Hinweis. Die SUMMENPRODUKT-Variante ist tatsächlich noch eleganter.

      Schöne Grüße,
      Martin

  • Michael

    Hallo Martin,
    Deine Lösung gefällt mir sehr gut.
    Ist es bei der Formel:
    =SUMME(KGRÖSSTE(B2:B16;ZEILE(1:3)))
    möglich, in der Funktion Zeile den zweiten Paramater auf eine Zelle zu setzen?
    Dann wäre man in der Lage, ohne die Formel zu ändern, die Top 5 oder 10 Werte zu summieren, indem man einfach den Wert in der Zelle verändert.

    Gruß
    Michael

    • Martin Weiß Autor des Beitrags

      Hallo Michael,

      ja, das wäre z.B. wie folgt möglich:
      {=SUMME(KGRÖSSTE(B2:B16;ZEILE(INDIREKT(A1))))}

      Die Annahme ist, dass in A1 so etwas steht wie “1:3” (ohne die Anführungszeichen). Du musst dann A1 allerdings als Text formatieren, ansonsten wird das als Uhrzeit interpretiert und funktioniert nicht.

      Schöne Grüße,
      Martin

      • Michael

        Hallo Martin,
        Passt, vielen Dank!
        Indirekt hatte ich gar nicht im Sinn, da ich diese Funktion bisher immer mit Bezügen wie A1 usw. verbunden habe.

        Gruß
        Michael

  • Theresia

    Hallo,

    ich bin mit der Möglichkeit SUMMENPRODUKT und KGRÖSSTE schon mal deutlich weiter gekommen.
    In meiner Tabelle habe ich aber das Problem, dass ich dies berechnen möchte pro Verein. Das heißt ich möchte die drei größten Punktzahlen zusammenrechnen je Verein.
    Aktuell sieht meine Formel so aus: {=SUMMENPRODUKT(KGRÖSSTE(E2:E75;ZEILE(1:3)))}. Die Formel rechnet auch richtig.

    Gruß
    Theresia

    • Martin Weiß Autor des Beitrags

      Hallo Theresia,

      wenn Deine Formel richtig rechnet, wo genau ist dann das Problem?

      Schöne Grüße,
      Martin