Kapitel 17 · 17.16
Die Top 5 pro Kategorie anzeigen
Die meistverkauften Produkte pro Abteilung, die größten Kunden pro Region, die geschäftigsten Tage pro Monat: Sie möchten nicht die Top 5 der gesamten Liste, sondern die Top 5 innerhalb einer Kategorie. KGRÖSSTE arbeitet mit der ganzen Spalte und liefert bei gleichen Beträgen zweimal dasselbe Produkt. In Euro-Office lösen Sie das mit einer einzigen Formel.
Als Beispiel nehmen wir den Jahresumsatz pro Produkt von Bloemisterij Het Zonnetje: in Spalte A das Produkt, in B die Kategorie (Rozen, Tulpen, Planten) und in C der Umsatz, von Zeile 2 bis einschließlich 18. In F1 steht die Kategorie, die Sie sehen möchten: Rozen.
Die Top 5 in einer Formel
Geben Sie in H2 ein:
=ÜBERNEHMEN(SORTIEREN(FILTER(A2:C18;B2:B18=F1);3;-1);5)
Lesen Sie die Formel von innen nach außen:
- FILTER(A2:C18;B2:B18=F1) behält nur die Zeilen der Kategorie in F1, mit allen drei Spalten.
- SORTIEREN(...;3;-1) sortiert diese Zeilen nach der dritten Spalte (dem Umsatz), absteigend (-1).
- ÜBERNEHMEN(...;5) behält die obersten 5 Zeilen.
Drücken Sie Enter: Die fünf Produkte mit Kategorie und Umsatz erscheinen in H2 bis J6. Geben Sie in F1 Planten ein, ändert sich die Liste sofort.

Gleiche Beträge sind kein Problem: Im Beispiel haben Grand Prix und Ecuadoriaanse roos beide 3.400 Umsatz, und beide stehen in der Liste. Mit zweimal KGRÖSSTE zusammen mit INDEX und VERGLEICH würde zweimal Grand Prix erscheinen.
Nur die Beträge, pro Kategorie nebeneinander
Möchten Sie nur die fünf höchsten Beträge, zum Beispiel für mehrere Kategorien nebeneinander, verwenden Sie KGRÖSSTE auf einer gefilterten Liste. Tragen Sie die Kategorien in F1, G1 und weiter ein, markieren Sie F2:G6, geben Sie ein:
=WENNFEHLER(KGRÖSSTE(FILTER($C$2:$C$18;$B$2:$B$18=F$1);ZEILEN($1:1));"")
und drücken Sie Strg+Enter. FILTER behält nur die Beträge der Kategorie in Zeile 1. ZEILEN($1:1) ergibt in der ersten Zeile 1 und in den Zeilen darunter 2, 3, 4 und 5: den größten, den zweitgrößten Wert und so weiter. WENNFEHLER lässt die Zelle leer, wenn eine Kategorie weniger als fünf Produkte hat; Tulpen hat nur vier.
Hinweis: Auf Excel-Websites findet man oft =KGRÖSSTE(WENN($B$2:$B$18=F$1;$C$2:$C$18);ZEILEN($1:1)). Diese Formel liefert in Euro-Office mit einfachem Enter ein falsches Ergebnis: WENN betrachtet dann nicht die ganze Spalte, sodass Sie die größten Beträge aller Kategorien erhalten oder #ZAHL!. Schließen Sie die Formel mit Strg+Umschalt+Enter ab, erhält sie geschweifte Klammern {…} und das Ergebnis stimmt. FILTER wie oben ist einfacher.
Nur der höchste Betrag
Für eine einzelne Zahl ist es einfacher: =MAXWENNS(C2:C18;B2:B18;F1) liefert den höchsten Umsatz innerhalb der Kategorie, hier 6.150.
Stolperfallen
- Weniger als 5 Produkte in einer Kategorie: Ohne WENNFEHLER liefert KGRÖSSTE #ZAHL! in den Zeilen, die es nicht gibt. ÜBERNEHMEN hat damit kein Problem und zeigt einfach weniger Zeilen an.
- Die Formel mit ÜBERNEHMEN bleibt leer: In den 5 Zeilen und 3 Spalten, in die das Ergebnis geschrieben werden soll, steht bereits etwas. Siehe Listen, die sich selbst aktualisieren, mit FILTER, EINDEUTIG und SORTIEREN.
- Eine Kollegin oder ein Kollege mit Excel 2019 oder älter sieht bei ÜBERNEHMEN, SORTIEREN und FILTER #NAME?.
