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.
Wenn man nun aber gleich die Summe der Top-Werte einer langen Liste benötigt? Im heutigen Artikel zeige ich dir 4 Möglichkeiten, wie man so etwas per Formel bewerkstelligt.
So geht’s:
Beispieldatei herunterladen
Die heutige Beispieltabelle zeigt eine Liste von 15 Werten, aus der die Summe der drei größten Werte gebildet werden soll:
Die Beispielliste mit 15 Werten
Variante 1: Der etwas umständliche Weg
Dazu berechne ich erst einmal mit Hilfe der Funktion KGRÖSSTE in drei Hilfszellen 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. Am Ende wird dann alles mit einer einfachen SUMME-Funktion addiert. Damit komme ich auf folgendes Ergebnis:
Variante 1: Hilfszellen mit KGRÖSSTE
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.
Variante 2: Der etwas komfortablere Weg
Die Herangehensweise ist ähnlich, wie bereits oben gezeigt, nur dass alles in einer Formel zusammengefasst wird. Und für den Parameter k verwende ich keine fixen Werte, sondern nutze stattdessen die ZEILE-Funktion:
{=SUMME(KGRÖSSTE(B2:B16;ZEILE(1:3)))}
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. ZEILE(1:3) umfasst drei Zeilen und liefert somit die Werte 1, 2 und 3. Umschlossen wird Ganze von der SUMME-Funktion.
Da normalerweise aber immer nur ein Wert pro Zelle dargestellt werden kann, meine ZEILE-Funktion aber 3 Werte liefert, muss die Formel mit Strg+Umschalt+Eingabe abgeschlossen werden. Dadurch wird eine Array-Formel erstellt, was man an den umschließenden geschweiften Klammern erkennt (diese dürfen nicht von Hand eingegeben werden!):
Variante 2: Eine Array-Formel aus SUMME und KGRÖSSTE
Wie man sieht, ist diese Variante deutlich komfortabler, denn man spart sich die Hilfszellen. Aber die Eingabe als Array-Formel ist sicherlich etwas gewöhnungsbedürftig. Es sei denn, man ist Anwender von Microsoft 365. Denn hier kann die Formel ganz normal und ohne diese Tastenkombination eingegeben werden, Excel nutzt im Hintergrund die Array-Funktion.
Variante 3: Der noch komfortablere Weg
Wer auf die umständliche Eingabe mit der Array-Formel verzichten möchte, kann anstelle von SUMME die SUMMENPRODUKT-Funktion verwenden. Diese arbeitet bereits als Array-Formel, die Tastenkombination Strg+Umschalt+Eingabe ist also nicht erforderlich. Der Rest der Formel ist identisch mit der Variante 2:
=SUMMENPRODUKT(KGRÖSSTE(B3:B17;ZEILE(1:3)))
Variante 3: SUMMENPRODUKT und KGRÖSSTE
Variante 4: Nur für Microsoft 365-Anwender
Diese Variante ist nur eine kleine Abwandlung von Variante 3. Anstelle der ZEILE-Funktion wird die neue SEQUENZ-Funktion verwendet:
=SUMMENPRODUKT(KGRÖSSTE(B3:B17;SEQUENZ(3)))
Diese Funktion kann in vielen Szenarien sehr hilfreich sein, weitere Beispiele für ihren Einsatz findest du hier.
In unserem Fall nutzen wir SEQUENZ einfach dazu, um 3 fortlaufende Zahlen zu generieren, beginnend ab 1. Ansonsten ist diese Variante grundsätzlich identisch mit Variante 3.
Variante 4: Abgewandelte Version mit SEQUENZ
Der Vorteil bei der SEQUENZ-Funktion ist, dass man anstelle der festen Zahl 3 auch einen Zellbezug angeben könnte, in dem man dann den gewünschten Wert einträgt. So ließen sich sehr flexibel und komfortabel unterschiedliche Top-Werte berechnen.
Natürlich gibt es auch noch andere Möglichkeiten, um die Summe von Top-3-Werten zu berechnen. Man könnte zum Beispiel auch eine Pivot-Tabelle verwenden, und sich somit die Formeln komplett sparen. Wie so oft in Excel ist es also eine Geschmacksfrage, für welche Lösung man sich entscheidet.
Kennst du noch andere Varianten zur Lösung dieses Problems? Dann lass es uns in den Kommentaren wissen!
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.
Hallo Martin,
vielen Dank für die sehr interessante Gegenüberstellung – wie immer so erklärt, dass man die Erkenntnisse auch leicht auf andere Anwendungsfälle übertragen kann.
Eine weitere Möglichkeit wäre die Tabelle mithilfe PowerQuery erst zu sortieren, danach auf die ersten drei Zeilen filtern und die Summe zu ziehen. Ist für dein Beispiel vielleicht etwas überdimensioniert, aber meistens kriege ich die Werte ja aus irgendeinem Vorsystem und somit könnte ich „Code“ und Daten trennen…
Grüße,
Matthias
Hallo Matthias,
richtig, Power Query ist ganz oft eine gute Alternative – und generell auch einer meiner Favoriten 😉
Schöne Grüße,
Martin
Die Tipps mit der eingebauten ZEILE-Funktion gefallen mir sehr gut.
Die anderen Tipps natürlich ebenso.
Danke für den Beitrag.
Hallo Gerhard,
Danke fürs Lesen und für Dein Feedback.
Schöne Grüße,
Martin