Top-10 Listen sind das Herzstück von Dashboards! Aber sie manuell zu pflegen ist ein Albtraum.
Das Problem: Du möchtest ein Ranking der besten Verkäufer, erfolgreichsten Produkte oder schlechtesten Zeiten – und es soll sich AUTOMATISCH aktualisieren!
- Top 10 Verkäufer nach Umsatz
- Bottom 5 Produkte (am wenigsten verkauft)
- Rangliste mit dynamischen Rankings
- Größte Werte mit den dazugehörigen Namen
Die Werkzeuge: GROSS, KLEIN, RANG, XVERWEIS, INDEX/MATCH und mehr!
In diesem Tutorial lernst du: Wie du dynamische Top-10 Listen mit Formeln erstellst – ohne Sortieren & Filtern!
1. Die Grundfunktionen – GROSS & KLEIN
GROSS() gibt den Xten GRÖSSTEN Wert zurück. KLEIN() den Xten KLEINSTEN Wert.
=GROSS(Bereich, K) (K-ter größter Wert)
=KLEIN(Bereich, K) (K-ter kleinster Wert)
Praktische Beispiele:
Finde den 1. größten Umsatz:
=GROSS(A2:A100, 1) → 15000€ ✅
Finde den 3. größten Umsatz:
=GROSS(A2:A100, 3) → 12500€ ✅
Finde den 5. kleinsten Wert:
=KLEIN(A2:A100, 5) → 1200€ ✅
✅ Das Wichtigste: GROSS & KLEIN geben NUR die ZAHLEN zurück, nicht die Namen! (Das ist der Kniff!)
2. Das Problem – Nur Zahlen, keine Namen!
Szenario:
Mit =GROSS(B:B,1) bekommst du 18000€ ✅
ABER: Wer ist der beste Verkäufer? Du weißt es nicht! ❌
Lösung: Kombiniere GROSS mit XVERWEIS oder INDEX/MATCH um den Namen zu finden!
| A (Verkäufer) | B (Umsatz) |
|---|---|
| Max | 15000€ |
| Anna | 12500€ |
| Peter | 18000€ |
Mit =GROSS(B:B,1) bekommst du 18000€ ✅
ABER: Wer ist der beste Verkäufer? Du weißt es nicht! ❌
Lösung: Kombiniere GROSS mit XVERWEIS oder INDEX/MATCH um den Namen zu finden!
3. Methode 1: XVERWEIS + GROSS (Modern & Elegant)
Die beste Lösung für Excel 365! XVERWEIS findet den Namen zum Umsatz.
Formel:
Erklärung:
Ergebnis: Name des besten Verkäufers ✅
=XVERWEIS(GROSS($B$2:$B$100, ROW()-1), $B$2:$B$100, $A$2:$A$100)
Erklärung:
- GROSS($B$2:$B$100, ROW()-1) = Finde den K-ten größten Wert
- ROW()-1 = Zeilennummer als K (Row 2 = K1, Row 3 = K2, etc.)
- XVERWEIS(..., $B$2:$B$100, $A$2:$A$100) = Finde den Namen zum Wert
Ergebnis: Name des besten Verkäufers ✅
Merksatz: ROW()-1 macht die Formel "kopierbar" → Einfach nach unten ziehen!
4. Methode 2: INDEX/MATCH + GROSS (Universell)
Funktioniert in allen Excel-Versionen! Aber komplizierter als XVERWEIS.
Formel:
Ergebnis: Gleich wie XVERWEIS, aber mit INDEX/MATCH ✅
=INDEX($A$2:$A$100, MATCH(GROSS($B$2:$B$100, ROW()-1), $B$2:$B$100, 0))
Ergebnis: Gleich wie XVERWEIS, aber mit INDEX/MATCH ✅
5. Praktisches Szenario – Top 10 Verkäufer
Deine Rohdaten (Spalte A: Namen, Spalte B: Umsatz):
Gewünschtes Ergebnis (Spalte D & E):
So erstellst du die Liste:
- Max – 15000€
- Anna – 12500€
- Peter – 18000€
- ... 100 Verkäufer insgesamt
Gewünschtes Ergebnis (Spalte D & E):
| Rank | Name | Umsatz |
|---|---|---|
| 1 | =XVERWEIS(GROSS($B$2:$B$100, ROW()-1), $B$2:$B$100, $A$2:$A$100) | =GROSS($B$2:$B$100, ROW()-1) |
| 2 | Peter | 18000€ |
| 3 | Max | 15000€ |
| 10 | ... | ... |
So erstellst du die Liste:
- Spalte C: Rangplatzierungen (1-10 oder mehr)
- Spalte D: Namen mit XVERWEIS-Formel
- Spalte E: Umsätze mit GROSS-Formel
- Formeln nach unten kopieren
6. Mit FILTER für dynamische Begrenzung (Excel 365)
Nur Top 10 anzeigen – nicht Top 100! FILTER + GROSS kombiniert.
Formel für Top 10 (dynamisch):
Das ist komplex! Einfachere Alternative:
=FILTER(A2:B100, COUNTIF(INDIRECT("$B$"&ROW($A$2:$A$11)&":$B$100"&"<"&B2:B100)<=9)
Das ist komplex! Einfachere Alternative:
Nutze einfach die ROW()-1 Methode und zeige nur Zeilen 2-11 (Top 10)
7. Fallstudie 1: Bottom 5 Produkte (Kleinste Werte)
Aufgabe: Zeige die 5 am wenigsten verkauften Produkte
Daten:
Formel für Rang 1-5 (in Spalte D-E):
Dann nach unten bis D6:E6 kopieren → Bottom 5! ✅
Daten:
| Produkt (A) | Verkaufte Menge (B) |
|---|---|
| Laptop | 500 |
| Monitor | 150 |
| Tastatur | 1200 |
Formel für Rang 1-5 (in Spalte D-E):
D2: =XVERWEIS(KLEIN($B$2:$B$100, ROW()-1), $B$2:$B$100, $A$2:$A$100)
E2: =KLEIN($B$2:$B$100, ROW()-1)
Dann nach unten bis D6:E6 kopieren → Bottom 5! ✅
8. Mit RANG – Ranglisten erstellen
RANG() gibt dir die Position eines Wertes in einer Liste.
=RANG(Zahl, Bereich, [Reihenfolge])
Beispiel: Alle Verkäufer mit ihrem Rang
Formel:
Ergebnis:
In Spalte C: =RANG(B2, $B$2:$B$100, 0)
Ergebnis:
| Name | Umsatz | Rang |
|---|---|---|
| Peter | 18000€ | 1 |
| Max | 15000€ | 3 |
RANG vs. GROSS/KLEIN:
| Funktion | Was macht | Best für |
|---|---|---|
| GROSS/KLEIN | Gibt den K-ten größten/kleinsten WERT zurück | Top-10 Listen erstellen |
| RANG | Gibt die POSITION eines Wertes zurück | Ranglisten für alle Zeilen |
9. Fallstudie 2: Dashboard mit Top 10 & Rankings
Szenario: Verkaufsdashboard mit:
Layout:
Spalte A: Namen Spalte B: Umsätze Spalte C: =RANG(B2, $B$2:$B$101, 0)
Spalte E: Rang (1-10) Spalte F: =XVERWEIS(GROSS($B$2:$B$101, ROW()-1), $B$2:$B$101, $A$2:$A$101) Spalte G: =GROSS($B$2:$B$101, ROW()-1)
- Alle Verkäufer mit ihrem Rang
- Separate Top 10 Liste
Layout:
Spalte A-C: Original-Daten (Name, Umsatz, RANG)
Spalte E-G: Top 10 Liste (Rang, Name, Umsatz mit XVERWEIS + GROSS)
Spalte A: Namen Spalte B: Umsätze Spalte C: =RANG(B2, $B$2:$B$101, 0)
Spalte E: Rang (1-10) Spalte F: =XVERWEIS(GROSS($B$2:$B$101, ROW()-1), $B$2:$B$101, $A$2:$A$101) Spalte G: =GROSS($B$2:$B$101, ROW()-1)
10. Fehlerbehandlung – Was wenn Duplikate?
❓ F: Was wenn mehrere Verkäufer gleiche Umsätze haben?
A: GROSS gibt dann mehrfach den gleichen Wert zurück:
GROSS-Rang 1: 15000€
GROSS-Rang 2: 15000€ (Duplikat!)
GROSS-Rang 3: 14500€
Das ist OK! RANG behandelt Duplikate mit gleicher Position.
❓ F: Wie sortiere ich die Top-10 Liste selbst?
A: Die Formeln sortieren automatisch! Du brauchst nur:
- Zeile 2 für Rang 1
- Zeile 3 für Rang 2
- ... usw.
❓ F: Kann ich Top-20 statt Top-10 machen?
A: JA! Einfach mehr Zeilen nach unten kopieren:
- Top-10: Zeilen 2-11
- Top-20: Zeilen 2-21
- Top-100: Zeilen 2-101
11. Best Practices & Tipps
✅ Tipp 1: Nutze ROW()-1 für Kopierbarkeit
=XVERWEIS(GROSS($B$2:$B$100, ROW()-1), ...) ✅ Kopierbar
=XVERWEIS(GROSS($B$2:$B$100, 1), ...) ❌ Nicht kopierbar
Immer ROW()-1 verwenden!
✅ Tipp 2: Absolute Referenzen für Bereiche
$B$2:$B$100 ✅ Bleiben gleich beim Kopieren
B2:B100 ❌ Verschieben sich beim Kopieren
✅ Tipp 3: Kombiniere mit RANG für Vollansicht
Zeige ALLE Zeilen mit RANG, und eine separate Top-10 mit GROSS/KLEIN!
✅ Tipp 4: Bedingte Formatierung hinzufügen
Top-10 mit grünem Hintergrund formatieren → Sieht professionell aus!
✅ Tipp 5: Sortierung mit INDEX/MATCH statt Duplikaten
Wenn Duplikate ein Problem sind, nutze MATCH um erste Position zu finden!
12. Vergleich: GROSS vs. RANG vs. SORT
| Funktion | Eingabe | Ausgabe | Best für |
|---|---|---|---|
| GROSS | Bereich, K | K-ter größter Wert | Top-10 Listen |
| RANG | Wert, Bereich | Position des Wertes | Ranglisten aller Zeilen |
| SORT (365) | Bereich | Sortierte Tabelle | Komplette Sortierung |
13. Deine nächsten Schritte
- GROSS & KLEIN üben: Mit einfachen Zahlen testen
- ROW()-1 verstehen: Warum macht das die Formel kopierbar?
- XVERWEIS kombinieren: Mit GROSS um Namen zu finden
- Top-10 Liste erstellen: Mit deinen echten Daten
- RANG hinzufügen: Für Ranglisten aller Zeilen
- Formatierung: Mit Farben & Fonts professionalisieren
- In Dashboard einbauen: Mit anderen KPIs
Checkliste für dein Projekt
- Ich verstehe GROSS & KLEIN und kann sie nutzen
- Ich verstehe ROW()-1 und warum es wichtig ist
- Ich kann XVERWEIS + GROSS kombinieren
- Ich kann Index/MATCH statt XVERWEIS nutzen
- Ich kenne RANG & kann Ranglisten erstellen
- Ich kann Top-10 Listen dynamisch erstellen
- Ich kann Bottom-5 Listen (kleinste Werte) erstellen
- Ich verstehe Duplikat-Handling bei Rankings
- Ich kann Top-20, Top-100 etc. erstellen
- Ich erstelle Top-10 Listen in echten Projekten/Dashboards
Mit dynamischen Top-10 Listen erstellst du professionelle Dashboards! 🏆
Keine Kommentare:
Kommentar veröffentlichen