Die Summe der Top-3-Werte 17

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?
 

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.



Schreibe einen Kommentar

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

17 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

    • Der Tourenplaner

      Danke für die hilfreiche Formel, die mir mein Hauptproblem gelöst hat. Für meine Zwecke musste ich sie noch erweitern und habe mich u. a. aus den untenstehenden Kommentaren bedient. Das Ergebnis teile ich gerne.

      Folgende Anpassungen waren nötig:

      1. Aus der Liste sollten nur die Datensätze ausgewertet werden, die in anderen zwei anderen Spalten bestimmte Kriterien erfüllen, dafür verwende ich die Funktion Filter() und ich habe in der Beispieldatei zwei Spalten eingefügt, der aufzusummierende Wert steht jetzt in Spalte C. A und B sind Kriterien, nach denen gefiltert wird. Da zwei Bedingungen erfüllt sein sollten habe ich zwei Wahrheitswerte multipliziert: (A:A=“I“)*(B:B=“a“). Falls nur eine Bedingung wahr sein muss müsste ein Plus dazwischen (das wäre dann ein „oder“)

      2. ich will nicht die größten sondern die kleinsten, also kkleinste(). kgrösste() müsste aber auch gehen

      3. die Anzahl soll aus einer Formel berechnet werden. Die Formel ist hier nicht relevant, darum habe ich hier einfach einen Wert der Zelle E2 übernommen.

      Herausgekommen ist das hier:
      =SUMMENPRODUKT(KKLEINSTE(FILTER($C:$C;(A:A=“I“)*(B:B=“a“));ZEILE(INDIREKT(„1:“&E2))))
      Die Formel scheint mir korrekte Ergebnisse zu liefern.

      Gruß kurz vor Feierabend

      Der Tourenplaner

      • Der Tourenplaner

        und für alle, die genauso beschränkt sind wie ich, hier noch ein Problem, das ich eine Weile untersucht habe: Manchmal gab es die Fehlermeldung „Wert“, manchmal funktionierte die Formel prächtig. Die Ursache war, dass die Zahl der ausgewerteten Treffer, (in der Formel als E2 angegeben) aus einer Formel errechnet wurde. Diese Formel gab teilweise Zahlen mit Nachkommastellen aus. Wenn man um E2 eine Rundungsfunktion schreibt („GANZZAHL(E2)“ oder „RUNDEN(E2;0)“ oder „AUFRUNDEN(E2;0)“ ohne die Anführungsstriche) und wenn die Zahlen immer nie negativ sind dann tut die Formel zuverlässig ihren Dienst.

        Die verwendete „FILTER()“-Funktion ist allerdings glaub ich nur in neueren Excelversionen vorhanden.

        • Martin Weiß Autor des Beitrags

          Hallo Tourenplaner,

          vielen Dank für deine Formel und die Erläuterungen. Ich bin sicher, das hilft einigen Lesern sehr weiter.

          Was die FILTER-Funktion angeht: Die gibt es tatsächlich nur unter Microsoft 365/Office 365 und in der brandneuen Version Excel 2021.

          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

  • Florian

    Hallo Martin,
    Die Lösung gefällt mir.
    Was ist nun wenn es innerhalb der Spalte leere Zellen gibt?
    Ich habe das Problem, dass mir dann #ZAHL! angezeigt wird.
    Auch wenn ich alle leeren Zellen durch 0 ersetze, kann Excel wohl nicht mit der Formel rechnen.
    Hast du eine Idee?

    Viele Grüße

    • Martin Weiß Autor des Beitrags

      Hallo Florian,

      dieses Problem kann ich nicht nachvollziehen. Die Formeln liefern bei mir auch dann korrekte Ergebnisse, wenn leere Zellen in der Liste enthalten sind. Hast du dir die Beispieldatei heruntergeladen?

      Schöne Grüße,
      Martin

  • Christian

    Hallo Martin,
    sehr hilfreich. Danke. Jetzt habe ich aber eine Frage dazu. Der Formelteil „Zeile(1:3) sorgt ja dafür das die 3 höchsten Ergebnisse summiert werden. Gibt es hier eine Möglichkeit diesen Teil durch eine Variable zu ersetzen? Im Prinzip möchte ich Zeile(1:x) wobei x=der Wert einer anderen Zelle ist. Ich hab schon verschiedene Möglichkeiten probiert, hab aber keine Lösung gefunden.
    Vielen Dank
    Christian

    • Christian

      Hallo Martin,
      habe die Antwort gefunden. Hatte nicht gesehen, das du dieses Problem schon mal beantwortet hast..
      Jetzt habe ich aber festgestellt, das diese Formel nur richtig rechnet, wenn im Zellbereich auch mindestens die in ZEILE definierte Anzahl an Werten steht. Stehen weniger Werte in dem Zellbereich kommt der Fehler #Zahl.
      Hast du eine Idee?.

      • Martin Weiß Autor des Beitrags

        Hallo Christian,

        ja, du musst die Formel nur mit der WENNFEHLER-Funktion umschließen, z.B.
        =WENNFEHLER(SUMME(KGRÖSSTE(B2:B16;ZEILE(1:3)));“-„)

        Schöne Grüße,
        Martin

  • Jacques Casez

    Hallo, ich habe eine für mich große Herausforderung.

    Ich habe eine Excel Tabelle und dort stehen in Spalten Zahlen. Ich würde gerne in dieser Spalte die SUMME bilden, dabei aber immer das „schlechteste“ Ergebnis (die niedrigste Zahl) ignorieren und nicht mitberechnen lassen. Geht so etwas? Beispielsweise stehen in einer Spalte die Zahlen 5,4,3,2,1 dann lautet das Ergebnis 14 da die Zahl 1 nicht mitberechnet wird. Es könnte aber auch so aussehen: 5,4,3,1,1 dann würde nur eine Zahl 1 gewertet werden und das Ergebnis wäre 13! Danke für Eure Hilfe; LG aus Darmstadt

    • Martin Weiß Autor des Beitrags

      Hallo Jacques,

      die einfachste Lösung wäre meiner Meinung nach folgende:
      Du berechnest zuerst die komplette Summe über alle Werte. Und davon ziehst du den kleinsten Wert wieder ab. Also beispielsweise

      =SUMME(A1:G1)-MIN(A1:G1)

      Schöne Grüße,
      Martin