Wie erstellt man in Excel eine dynamische Top-10- oder n-Liste?
In vielen Projekten und Geschäftsprozessen ist es oft erforderlich, Personen, Organisationen, Produkte oder andere Entitäten anhand ihrer Leistung oder numerischer Werte zu ranken. Eine „Top-Liste“ hebt die bestperformenden Einträge hervor – etwa die besten Schüler nach Notendurchschnitt, die erfolgreichsten Verkäufer oder Abteilungen mit den höchsten Umsätzen. Stellen Sie sich beispielsweise eine Tabelle mit Schülernoten vor: Mit einer dynamischen Top-10-Liste können Sie die punktstärksten Schüler automatisch für Auszeichnungen, Analysen oder zur Überwachung pädagogischer Ergebnisse identifizieren, wie in der folgenden Abbildung gezeigt. Durch die Erstellung einer dynamischen Top-10- oder Top-N-Liste in Excel erhalten Sie stets aktuelle Ergebnisse, sobald sich Ihre Daten ändern – das spart Zeit und vermeidet Fehler durch manuelles Ranking. Dieser Leitfaden stellt mehrere praktische Lösungen vor – darunter Formeln, PivotTables und VBA-Makros –, um Ihnen effizient beim Aufbau einer dynamischen Top-10- oder Top-N-Liste für vielfältige Datenanalyseanforderungen zu helfen.
Erstellen Sie eine dynamische Top-10-Liste in Excel
- Formeln zum Erstellen einer dynamischen Top-10-Liste
- Formeln zum Erstellen einer dynamischen Top-10-Liste mit Kriterien
Erstellen Sie eine dynamische Top-10-Liste in Office 365
- Formel zum Erstellen einer dynamischen Top-10-Liste
- Formel zum Erstellen einer dynamischen Top-10-Liste mit Kriterien
Erstellen Sie eine dynamische Top-10-Liste mit einem PivotTable
Erstellen einer dynamischen Top-10-Liste in Excel
In Excel 2019 und früheren Versionen erstellen Sie eine dynamische Top-10- (oder Top-N-) Liste, indem Sie Formeln kombinieren, um gleichzeitig die höchsten Werte sowie die zugehörigen Namen oder IDs zu extrahieren. Diese bewährte Lösung eignet sich ideal für Szenarien, in denen sich die Liste automatisch aktualisieren soll, sobald sich die zugrunde liegenden Daten ändern. Die folgenden Schritte zeigen, wie Sie dies mit klassischen Excel-Formeln umsetzen können. Diese bieten maximale Flexibilität, benötigen keine speziellen Add-Ins und sind – im Vergleich zu modernen dynamischen Arrayfunktionen – lediglich etwas aufwendiger einzurichten.
Formeln zum Erstellen einer dynamischen Top-10-Liste
1. Extrahieren Sie zunächst die obersten 10 Werte aus Ihrem Wertebereich. Geben Sie dazu die folgende Formel in eine leere Zelle ein (z. B. Zelle G2). Ziehen Sie anschließend das Ausfüllkästchen nach unten, um Ihre dynamische Top-10-Werteliste zu generieren. Siehe Screenshot:
2. Geben Sie als Nächstes in Zelle F2 die folgende Formel ein, um die zugehörigen Namen (oder IDs) der soeben extrahierten Top-Werte anzuzeigen. Da es sich hierbei um eine Matrixformel handelt, bestätigen Sie sie nach der Eingabe mit Strg + Umschalt + Enter. Diese Formel sucht die Namen, die den extrahierten Top-Werten entsprechen:
–A2:A20ist der Bereich, aus dem die Namen entnommen werden;
–B2:B20ist der Bereich der Punktzahlen oder Werte;
–G2ist der Höchstwert aus der obigen Formel;
–B1ist die Überschrift der Werteliste und dient zur Verschiebung in den ZEILEN-Berechnungen.
Diese Formel verknüpft die höchsten Werte dynamisch mit ihren Namen. Falls Ihr Wertebereich Duplikate enthält, stellt ZÄHLENWENN sicher, dass jeder übereinstimmende Name genau einmal mit seiner Punktzahl angezeigt wird.
3. Nachdem das erste Ergebnis extrahiert wurde, markieren Sie die Formel in Zelle F2 und ziehen Sie am Ausfüllkästchen nach unten, um die Formel auf so viele Zeilen zu kopieren, wie benötigt werden. So erweitert sich Ihre Ergebnisliste dynamisch und zeigt die Namen aller Top-Einträge entsprechend ihrer Punktzahlen an. Siehe Screenshot:
Entfesseln Sie die Magie von Excel mit KUTOOLS AI
- Intelligente Ausführung: Führen Sie Zelloperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Sprachbefehl.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
- VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln spielend leicht.
- Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.
Formeln zum Erstellen einer dynamischen Top-10-Liste mit Kriterien
Für bestimmte Analyseaufgaben benötigen Sie eine Top-Liste, die ausschließlich Einträge anzeigt, die spezifische Kriterien erfüllen – beispielsweise die Beschränkung der Top-Ergebnisse auf eine bestimmte Gruppe, ein Team oder eine Kategorie. So könnten Sie etwa die besten 10 Punktzahlen gezielt für „Klasse 1“ aus einem gesamten Datenblatt mit Noten mehrerer Klassen ermitteln. Im Folgenden erfahren Sie, wie Sie dafür Formeln einsetzen können:
1. Extrahieren Sie zunächst die Top-10-Werte, die Ihr festgelegtes Kriterium erfüllen (z. B. „Klasse 1“). Geben Sie dazu folgende Formel in die Zielzelle ein (z. B. J2):
2. Bestätigen Sie die Formel nach der Eingabe als Matrixformel durch Drücken von Strg + Umschalt + Enter und ziehen Sie anschließend das Ausfüllkästchen nach unten, um weitere Zellen zu füllen. Die Formel gibt die höchsten 10 Werte zurück, die Ihrer gewählten Bedingung entsprechen (z. B. alle Punktzahlen aus „Klasse 1“).
3. Um die entsprechenden Namen für diese Höchstwerte gemäß Ihren Kriterien aufzulisten, kopieren Sie die folgende Formel in Zelle I2 und drücken Sie Strg + Umschalt + Enter, um sie als Matrixformel einzugeben. Füllen Sie die Formel anschließend nach Bedarf nach unten aus, um die vollständige Namensliste zu erhalten.
Passen Sie unbedingt die Bereiche in Ihren Formeln an Ihre tatsächliche Datenstruktur an. Beachten Sie, dass die Verwendung großer Bereiche mit Matrixformeln die Leistung beeinträchtigen kann. Falls doppelte Werte in Ihren Top 10 auftreten, verarbeitet die Formel identische Noten korrekt und gibt mehrere Schülernamen aus.
Erstellen einer dynamischen Top-10-Liste in Office 365
Während ältere Excel-Versionen zur Kombination mehrerer Funktionen auf Matrixformeln angewiesen sind, führen Office 365 (und Excel 2021) dynamische Arrayfunktionen wie INDEX, SORTIEREN, SEQUENZ und FILTER ein – und vereinfachen so Ihren Arbeitsablauf erheblich. Mit diesen Funktionen erstellen Sie mühelos dynamische Top-10-Listen, minimieren Fehler und profitieren besonders bei Tabellen, die sich häufig erweitern oder verändern. Arbeiten Sie in einer Umgebung mit ständig aktualisierten Daten, beschleunigen diese Funktionen Ihre Analyse und ermöglichen schnellere Geschäftsentscheidungen.
Formel zum Erstellen einer dynamischen Top-10-Liste
Um eine dynamische Top-10-Liste mit Office 365 zu erstellen und anzuzeigen, geben Sie die folgende Formel in die gewünschte Zelle ein. Passen Sie einfach die Bereiche und Werte gemäß Ihren Anforderungen an – die Formel liefert dann automatisch stets die aktuellsten Top-10-Ergebnisse, sobald sich Ihre Daten ändern.
Drücken Sie einfach die Eingabetaste. Die vollständige Top-10-Liste wird sofort angezeigt und bleibt dynamisch – zusätzliche Daten oder geänderte Werte spiegeln sich unverzüglich in Ihrer Rangliste wider.
SORT-Funktion:
=SORT(array; [sort_index]; [sort_order]; [by_col])
- array: Der Bereich, den Sie sortieren möchten.
- [sort_index]: Nummer der Spalte, nach der sortiert werden soll. In einer typischen Notentabelle ist das meist die zweite Spalte.
- [sort_order]: Verwenden Sie 1 für aufsteigende oder -1 für absteigende Reihenfolge. Um die höchsten Werte zu erhalten, geben Sie -1 an.
- [by_col]: Gibt an, ob nach Spalten (WAHR) oder nach Zeilen (FALSCH oder nicht angegeben) sortiert werden soll.
Beispiel: SORT(A2:B20;2; -1) sortiert den Bereich A2:B20 nach der zweiten Spalte in absteigender Reihenfolge.
SEQUENCE-Funktion:
=SEQUENCE(Zeilen; [Spalten]; [Start]; [Schritt])
- Zeilen: Anzahl der zurückzugebenden Zeilen – zum Beispiel 10 für eine Top-10-Liste.
- [columns]: (Optional) Anzahl der zurückzugebenden Spalten.
- [start]: (Optional) Startwert.
- [step]: (Optional) Wert, um den erhöht wird.
Mit SEQUENCE(10) werden die Zahlen 1 bis 10 generiert und INDEX kann so die obersten 10 sortierten Ergebnisse auswählen.
Durch die Kombination dieser Funktionen erhalten Sie:=INDEX(SORT(A2:B20;2;-1);SEQUENCE(10);{1;2}) – eine dynamische, zweispaltige Top-10-Liste.
Formel zum Erstellen einer dynamischen Top-10-Liste mit Kriterien
Möchten Sie die Top 10 einer bestimmten Gruppe – beispielsweise „Klasse 1“ – extrahieren, erstellen diese erweiterten Office-365-Funktionen eine Top-N-Liste, die ausschließlich Zeilen berücksichtigt, die Ihren Kriterien entsprechen. Platzieren Sie die folgende Formel an der gewünschten Stelle und passen Sie die Bereiche sowie die Kriterienzelle nach Bedarf an:
Geben Sie einfach die Formel ein und drücken Sie die Eingabetaste. Die gefilterte und sortierte Top-10-Liste für das angegebene Kriterium wird sofort angezeigt und aktualisiert sich automatisch, sobald Sie Ihre Daten oder das Kriterium ändern.
FILTER-Funktion:
=FILTER(array; include; [if_empty])
- array: Der Zellbereich, der gefiltert werden soll.
- include: Die Bedingung (z. B. gleich einer bestimmten Klasse) für die Aufnahme.
- [if_empty]: (Optional) Gibt an, was angezeigt werden soll, wenn keine Ergebnisse die Kriterien erfüllen.
=FILTER(A2:C25;B2:B25=F2) gibt nur die Zeilen zurück, in denen der Wert in Spalte B mit dem Wert in Zelle F2 übereinstimmt.
Erstellen einer dynamischen Top-10-Liste mit einem PivotTable
PivotTable: Top-N-Ergebnisse interaktiv und automatisch anzeigen
Eine alternative Methode zum Erstellen einer dynamischen Top-N-Liste ist die Verwendung der PivotTable-Funktion in Excel. Dieser Ansatz eignet sich besonders gut für große Datensätze, interaktive Analysen – etwa zum schnellen Anpassen der Anzahl der Top-Einträge oder zum einfachen Anwenden von Filtern – sowie für Fälle, in denen komplexe Formeln vermieden werden sollen. PivotTables sind benutzerfreundlich und aktualisieren sich automatisch bei Änderungen der zugrunde liegenden Daten, was sie ideal für Dashboards oder Berichte macht, die Sie mit anderen teilen möchten.
So erstellen Sie eine dynamische Top-N-Liste mithilfe eines PivotTable:
- Klicken Sie innerhalb Ihrer Datentabelle auf eine beliebige Zelle und wechseln Sie dann zu Einfügen > PivotTable.
- Wählen Sie im PivotTable-Dialogfeld aus, wo die PivotTable platziert werden soll, und klicken Sie auf OK.
- Ziehen Sie Ihr Feld „Name“ (oder ein ähnliches Kennzeichen) in den Bereich Zeilen.
- Ziehen Sie Ihre Spalte „Punktzahl“ (oder Wertespalte) in den Bereich Werte. Standardmäßig wird meist „Summe von“ oder „Anzahl von“ verwendet – für Top-Listen benötigen Sie in der Regel „Summe“ oder „Max“. Ändern Sie gegebenenfalls die Berechnung des Wertefelds, indem Sie mit der rechten Maustaste klicken und Werte zusammenfassen nach auswählen.
- Sortieren Sie die Spalte „Punktzahl“ in absteigender Reihenfolge, indem Sie mit der rechten Maustaste auf einen Wert klicken und Sortieren > Größte zu kleinste sortieren auswählen.
- Um die Ergebnisse auf die obersten N Einträge zu beschränken, klicken Sie auf den Dropdown-Pfeil bei den Zeilenbeschriftungen, wählen Sie Wertfilter > Top 10..., geben Sie die gewünschte Anzahl (z. B. Top 10) sowie das Feld an, nach dem gefiltert werden soll, und klicken Sie auf OK.
Ihre PivotTable zeigt nun die dynamische Top-10-Liste (oder eine beliebige von Ihnen festgelegte N-Zahl) an. Um die Anzahl der Top-N-Ergebnisse zu ändern, rufen Sie einfach erneut die Filtereinstellungen auf. Sollten sich Ihre Daten ändern, aktualisieren Sie die PivotTable – und die Rangliste wird sofort angepasst.
Vorteile dieses Ansatzes sind die schnelle Einrichtung, die einfache Sortierung und vielfältige interaktive Anpassungsmöglichkeiten. Allerdings fügen PivotTables keine zugehörigen Zeilen aus anderen Spalten automatisch hinzu, es sei denn, diese befinden sich bereits im Zeilen- oder Wertebereich. Fortgeschrittene Benutzer können Berichte durch Gruppierungen, die Erstellung von Slicern oder die Integration des Top-N-Filters in Dashboards noch gezielter anpassen.
Erstellen einer dynamischen Top-10-Liste mithilfe von VBA
VBA-Makro: Generiert und aktualisiert automatisch eine Top-N-Liste
Die Verwendung eines VBA-Makros eignet sich ideal für Anwender, die mit umfangreichen oder häufig aktualisierten Daten arbeiten und die Erstellung sowie Aktualisierung einer dynamischen Top-N-Liste automatisieren möchten. Makros reduzieren repetitive Aufgaben effizient und gewährleisten dabei höchste Konsistenz. So können Sie eine Routine einrichten, die Ihre Daten sortiert und bei jedem Durchlauf automatisch nur die obersten N Zeilen an einen festgelegten Ort kopiert.
Gehen Sie zur Verwendung eines VBA-Makros zum Erstellen einer dynamischen Top-N-Liste wie folgt vor:
- Klicken Sie auf Entwicklertools > Visual Basic, um den VBA-Editor zu öffnen. (Falls die Registerkarte „Entwicklertools“ nicht sichtbar ist, gehen Sie zu Datei > Optionen > Menüband und Symbolleisten anpassen und aktivieren Sie „Entwicklertools“.)
- Klicken Sie im VBA-Fenster auf Einfügen > Modul, um ein neues Modul hinzuzufügen.
- Fügen Sie den folgenden VBA-Code in das Modul ein:
Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
Dim DataRange As Range
Dim OutputRange As Range
Dim N As Integer
Dim ws As Worksheet, tempWS As Worksheet
Dim xTitleId As String
Dim LastCol As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
' Create a temporary worksheet to avoid sorting original data
Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
DataRange.Copy tempWS.Range("A1")
' Determine last column for sorting key
LastCol = DataRange.Columns.Count
' Sort in temporary sheet
tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
' Copy headers and top N rows to output
tempWS.Rows(1).Copy Destination:=OutputRange
tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
' Optional: Delete temporary sheet
Application.DisplayAlerts = False
tempWS.Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub4. Um das Makro auszuführen, stellen Sie sicher, dass Ihre Daten ordnungsgemäß in einer Tabelle mit Überschriften angeordnet sind. Drücken Sie F5 oder klicken Sie auf die 
-Schaltfläche im VBA-Editor. Sie werden aufgefordert:
- Wählen Sie Ihren Datenbereich aus – einschließlich der Überschriften, um eine korrekte Sortierung zu gewährleisten.
- Wählen Sie die Zelle aus, in die die Ergebnisse eingefügt werden sollen.
- Geben Sie die Zahl N ein (z. B. 10 für die Top 10).
Das Makro kopiert die obersten N Einträge – inklusive der Überschriften – an den von Ihnen angegebenen Ort.
Es empfiehlt sich, dies zunächst in einer Sicherungskopie oder einem Duplikat Ihrer Arbeitsmappe zu testen. Sollten Fehler auftreten – beispielsweise aufgrund einer falschen Bereichsauswahl –, führen Sie das Makro erneut aus und stellen Sie sicher, dass Ihre Bereiche und Ihre Datenstruktur korrekt sind.
Diese Lösung eignet sich ideal zur Automatisierung repetitiver Berichtsaufgaben, zum Erstellen von Dashboards oder zur schnellen Aktualisierung von Top-N-Berichten – ganz ohne manuelle Formeln oder Sortiervorgänge. Zudem lässt sich das VBA-Skript für komplexere Ranglogiken individuell anpassen, beispielsweise durch die Sortierung nach einer bestimmten Spalte oder den Export der Ergebnisse in eine andere Arbeitsmappe.
Fehlerbehebung: Sollte das Makro nicht wie erwartet funktionieren, vergewissern Sie sich, dass Ihre Datentabelle korrekte Überschriften enthält, passen Sie gegebenenfalls die Datentypen an, um Sortierprobleme zu vermeiden, und stellen Sie sicher, dass in jeder Abfrage die Zellbezüge korrekt ausgewählt wurden. Speichern Sie Ihre Arbeit stets vor dem Ausführen von Makros, um unbeabsichtigte Datenänderungen zu verhindern.
Zusammenfassend bietet Excel vielfältige Möglichkeiten, dynamische Top-N-Listen zu erstellen und zu pflegen – von klassischen Formeln über leistungsstarke Office-365-Funktionen und PivotTables für interaktive Analysen bis hin zu VBA-Makros für fortgeschrittene Automatisierung. Wählen Sie die Methode, die am besten zu Ihrem Arbeitsablauf und dem Umfang Ihrer Daten passt: Formeln eignen sich ideal für die meisten manuellen Analysen, Office-365-Funktionen überzeugen durch maximale Einfachheit und Leistung, PivotTables liefern schnelle und flexible Zusammenfassungen, und VBA erweist sich als besonders wertvoll bei der Automatisierung umfangreicher, repetitiver Rangaufgaben. Achten Sie stets auf die Integrität Ihrer Formeln oder Ihres Codes und passen Sie Zellbezüge rechtzeitig an etwaige Änderungen in Ihrer Datenstruktur an, während sich Ihr Projekt weiterentwickelt.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins