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
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)
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)
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
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:
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,
hier noch 1 Lösung ohne ‚Strg+Umschalt+Enter‘
=SUMMENPRODUKT(KGRÖSSTE(B2:B16;ZEILE(1:3)))
Gruß von Luschi
Hallo Luschi,
vielen Dank für den guten Hinweis. Die SUMMENPRODUKT-Variante ist tatsächlich noch eleganter.
Schöne Grüße,
Martin
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
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.
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
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
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
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
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
Hallo Theresia,
wenn Deine Formel richtig rechnet, wo genau ist dann das Problem?
Schöne Grüße,
Martin
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
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
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
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?.
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
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
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