Die Summe der Top-3-Werte 5

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.



Kommentar erstellen

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

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

    • Martin Weiß Beitragsautor

      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ß Beitragsautor

      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