Wie erstellt man eine dynamische Top-10- oder N-Liste in Excel?
In vielen Projekten und Geschäftsprozessen ist es oft notwendig, Personen, Organisationen, Produkte oder andere Entitäten auf der Grundlage ihrer Leistung oder numerischen Werte zu bewerten. Eine „Top-Liste“ dient dazu, die bestleistenden Einträge hervorzuheben, wie zum Beispiel die besten Schüler nach Noten, die besten Verkäufer oder die Abteilungen mit den höchsten Umsätzen. Zum Beispiel könnten Sie eine Tabelle mit Schülernoten haben und möchten die Top-10-Schüler dynamisch extrahieren, um Preise zu vergeben, Analysen durchzuführen oder Bildungsergebnisse zu überwachen, wie im folgenden Screenshot dargestellt. Das Erstellen einer dynamischen Top-10- oder N-Liste in Excel ermöglicht es Ihnen, aktualisierte Ergebnisse automatisch zu sehen, wenn sich Ihre Daten ändern, was Zeit spart und Fehler durch manuelles Bewerten reduziert. Dieser Leitfaden stellt mehrere praktische Lösungen vor – darunter Formeln, Pivot-Tabellen und VBA-Makros – um Ihnen zu helfen, eine dynamische Top-10- oder N-Liste effizient für verschiedene Datenanalyseanforderungen zu erstellen.
Erstellen einer dynamischen Top-10-Liste in Excel
- Formeln zur Erstellung einer dynamischen Top-10-Liste
- Formeln zur Erstellung einer dynamischen Top-10-Liste mit Kriterien
Erstellen einer dynamischen Top-10-Liste in Office 365
- Formel zur Erstellung einer dynamischen Top-10-Liste
- Formel zur Erstellung einer dynamischen Top-10-Liste mit Kriterien
Erstellen einer dynamischen Top-10-Liste mit einer Pivot-Tabelle
Erstellen einer dynamischen Top-10-Liste in Excel
In Excel 2019 und früheren Versionen besteht die Erstellung einer dynamischen Top-10- (oder Top-N-) Liste darin, Formeln zu kombinieren, um gleichzeitig die höchsten Werte und ihre zugehörigen Namen oder IDs zu extrahieren. Diese Lösung wird häufig verwendet und eignet sich für Situationen, in denen Sie möchten, dass die Liste automatisch aktualisiert wird, wenn sich Ihre Daten ändern. Die folgenden Schritte erläutern, wie dies mit klassischen Excel-Formeln erreicht werden kann. Diese Formeln bieten Flexibilität und erfordern keine speziellen Excel-Add-ins, aber der Einrichtungsprozess ist etwas komplizierter im Vergleich zu einigen modernen dynamischen Array-Funktionen.
Formeln zur Erstellung einer dynamischen Top-10-Liste
1. Beginnen Sie damit, die Top-10-Werte aus Ihrem Wertebereich zu extrahieren. Geben Sie die folgende Formel in eine leere Zelle ein (z. B. Zelle G2). Nachdem Sie die Formel eingegeben haben, ziehen Sie das Ausfüllkästchen nach unten, um Ihre dynamische Top-10-Werteliste zu generieren. Siehe Screenshot:
2. Als nächstes, um die entsprechenden Namen (oder IDs) anzuzeigen, die diesen Top-Werten zugeordnet sind, geben Sie die folgende Formel in Zelle F2 ein. Dies ist eine Array-Formel, daher drücken Sie nach der Eingabe Strg + Umschalt + Enter, um sie zu bestätigen. Diese Formel findet die Namen, die den gerade extrahierten Top-Werten entsprechen:
- A2:A20 ist der Bereich, aus dem die Namen gezogen werden sollen;
- B2:B20 ist der Bereich der Punkte oder Werte;
- G2 ist der Top-Wert aus der obigen Formel;
- B1 ist die Kopfzeile der Werteliste und wird für Offset-Berechnungen in ZEILE verwendet.
Diese Formel verknüpft die höchsten Werte dynamisch mit ihren Namen. Wenn Ihr Wertebereich Duplikate enthält, stellt ANZAHLWENN sicher, dass jeder passende Name nur einmal mit seiner Note erscheint.
3. Nachdem Sie das erste Ergebnis extrahiert haben, wählen Sie die Formel in Zelle F2 aus und ziehen Sie das Ausfüllkästchen nach unten, um die Formel in so viele Zeilen zu kopieren, wie nötig. Dadurch wird Ihr Ergebnis erweitert, um die Namen aller Top-Einträge dynamisch anzuzeigen, die diesen Noten entsprechen. Siehe Screenshot:
Entfesseln Sie die Magie von Excel mit Kutools AI
- Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
- VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
- Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.
Formeln zur Erstellung einer dynamischen Top-10-Liste mit Kriterien
Bei einigen Analyseaufgaben benötigen Sie möglicherweise eine Top-Liste, die nur Einträge anzeigt, die bestimmte Kriterien erfüllen – wie zum Beispiel die Begrenzung der Top-Ergebnisse auf eine bestimmte Gruppe, ein Team oder eine Kategorie. Beispielsweise möchten Sie vielleicht die Top-10-Noten nur für „Klasse 1“ aus einem Gesamtdatenblatt identifizieren, das Noten aus mehreren Klassen enthält. Hier erfahren Sie, wie Sie Formeln für dieses Szenario verwenden können:
1. Beginnen Sie damit, die Top-10-Werte zu extrahieren, die Ihr spezifisches Kriterium (z. B. „Klasse 1“) aus dem Datensatz erfüllen. Geben Sie diese Formel in die Zielzelle ein (z. B. J2):
2. Drücken Sie nach Eingabe der Formel Strg + Umschalt + Enter, um sie als Array-Formel zu bestätigen, und ziehen Sie dann das Ausfüllkästchen nach unten, um andere Zellen zu füllen. Die Formel gibt die höchsten 10 Werte zurück, die Ihrer gewählten Bedingung entsprechen (z. B. alle Noten aus „Klasse 1“).
3. Um die entsprechenden Namen für diese Top-Werte unter Ihren Kriterien aufzulisten, kopieren und fügen Sie die folgende Formel in Zelle I2 ein und drücken Sie Strg + Umschalt + Enter als Array-Formel. Füllen Sie dann nach Bedarf nach unten aus, um die vollständige Namensliste zu generieren.
Passen Sie die Bereiche in den Formeln unbedingt an Ihre tatsächliche Dateneinrichtung an. Beachten Sie, dass die Verwendung großer Datenbereiche mit Array-Formeln die Leistung beeinträchtigen kann. Wenn sich in Ihrer Top-10 doppelte Werte befinden, handhabt die Formel wiederholte Noten angemessen und gibt bei gleichen Noten mehrere Schüleramen aus.
Erstellen einer dynamischen Top-10-Liste in Office 365
Während frühere Versionen von Excel erfordern, dass mehrere Funktionen mit Array-Formeln kombiniert werden, führt Office 365 (und Excel 2021) dynamische Array-Funktionen wie INDEX, SORTIEREN, SEQUENZ und FILTER ein, die den Workflow erheblich vereinfachen. Diese Funktionen erleichtern den Aufbau dynamischer Top-10-Listen, reduzieren Fehler und sind besonders nützlich für Tabellen, die häufig wachsen oder sich ändern. Wenn Sie in einer Umgebung mit ständig aktualisierenden Daten arbeiten, können diese Funktionen Ihre Analyse optimieren und schnellere Geschäftsentscheidungen ermöglichen.
Formel zur Erstellung einer dynamischen Top-10-Liste
Um eine dynamische Top-10-Liste mithilfe von Office 365 zu extrahieren und anzuzeigen, geben Sie die folgende Formel in Ihre gewünschte Ausgabezelle ein. Alles, was Sie tun müssen, ist, die Bereiche und Zahlen entsprechend Ihren Bedürfnissen anzupassen, und die Formel zeigt automatisch die neuesten Top-10-Ergebnisse an, sobald sich Ihre Daten ändern.
Drücken Sie einfach die Eingabetaste. Die vollständige Top-10-Liste erscheint sofort und bleibt dynamisch, sodass zusätzliche Daten oder geänderte Noten sofort in Ihrer Rangliste widergespiegelt werden.
SORTIEREN-Funktion:
=SORTIEREN(Array; [sort_index]; [sort_order]; [by_col])
- Array: Der Bereich, den Sie sortieren möchten.
- [sort_index]: Nummer der zu sortierenden Spalte. Bei einer typischen Notentabelle ist dies oft die zweite Spalte.
- [sort_order]: Verwenden Sie 1 für aufsteigende oder -1 für absteigende Reihenfolge. Um Top-Noten zu erhalten, verwenden Sie -1.
- [by_col]: Ob nach Spalten (WAHR) oder nach Zeilen (FALSCH oder weggelassen) sortiert werden soll.
Beispiel: SORTIEREN(A2:B20;2;-1) sortiert A2:B20 nach der zweiten Spalte in absteigender Reihenfolge.
SEQUENZ-Funktion:
=SEQUENZ(Zeilen; [Spalten]; [Start]; [Schritt])
- Zeilen: Anzahl der zurückzugebenden Zeilen, z. B. 10 für eine Top-10-Liste.
- [Spalten]: (Optional) Anzahl der zurückzugebenden Spalten.
- [Start]: (Optional) Startwert.
- [Schritt]: (Optional) Inkrementwert.
SEQUENZ(10) generiert die Zahlen 1 bis 10, sodass INDEX die Top-10 sortierten Ergebnisse auswählt.
In Kombination ergibt =INDEX(SORTIEREN(A2:B20;2;-1);SEQUENZ(10);{1;2}) eine dynamische, zweispaltige Top-10-Liste.
Formel zur Erstellung einer dynamischen Top-10-Liste mit Kriterien
Wenn Sie die Top-10 für eine bestimmte Gruppe, wie „Klasse 1“, extrahieren müssen, können diese erweiterten Office 365-Funktionen eine Top-N-Liste erstellen, während nur diejenigen Zeilen berücksichtigt werden, die Ihre Kriterien erfüllen. Platzieren Sie die folgende Formel an Ihrer gewünschten Position und passen Sie die Bereiche und die Kriterienzelle nach Bedarf an:
Drücken Sie nach Eingabe der Formel einfach die Eingabetaste. Die Top-10-Liste, gefiltert und nach den angegebenen Kriterien sortiert, wird sofort angezeigt und aktualisiert jedes Mal, wenn Sie Ihre Daten oder Kriterien ändern.
FILTER-Funktion:
=FILTER(Array; einschließen; [wenn_leer])
- Array: Der zu filternde Zellbereich.
- einschließen: Die Bedingung (z. B. gleich einer bestimmten Klasse) für die Aufnahme.
- [wenn_leer]: (Optional) 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 Spalte B dem Wert in F2 entspricht.
Erstellen einer dynamischen Top-10-Liste mit einer Pivot-Tabelle
Pivot-Tabelle: Automatische interaktive Anzeige der Top-N-Ergebnisse
Eine alternative Möglichkeit, eine dynamische Top-N-Liste zu erstellen, ist die Verwendung der Excel-Pivot-Tabellenfunktion. Diese Methode eignet sich besonders für große Datensätze, interaktive Analysen (wie das schnelle Ändern der Anzahl der Top-Elemente oder das Anwenden von Filtern) oder wenn Sie komplexe Formeln vermeiden möchten. Pivot-Tabellen sind benutzerfreundlich und aktualisieren sich automatisch, wenn sich Daten ändern, was sie ideal für Dashboards oder Berichte macht, die mit anderen geteilt werden.
So erstellen Sie eine dynamische Top-N-Liste mit einer Pivot-Tabelle:
- Klicken Sie irgendwo innerhalb Ihrer Datentabelle, gehen Sie dann zu Einfügen > PivotTable.
- Wählen Sie im Dialogfeld PivotTable aus, wo Sie die Pivot-Tabelle platzieren möchten, und klicken Sie auf OK.
- Ziehen Sie Ihr „Name“-Feld (oder ähnliches Identifikationsfeld) in den Zeilenbereich.
- Ziehen Sie Ihre „Note“-Spalte (oder Wertspalte) in den Werte-Bereich. Standardmäßig wird sie meist auf „Summe von“ oder „Anzahl von“ eingestellt – für Top-Listen möchten Sie normalerweise „Summe“ oder „Max“. Ändern Sie die Wertfelddarstellung bei Bedarf, indem Sie mit der rechten Maustaste klicken und Summiere Werte nach auswählen.
- Sortieren Sie die „Note“-Spalte in absteigender Reihenfolge, indem Sie mit der rechten Maustaste auf einen Wert klicken und Sortieren > Größte zu kleinste auswählen.
- Um auf die Top-N-Ergebnisse zu begrenzen, klicken Sie auf den Dropdown-Pfeil bei Zeilenbeschriftungen, wählen Sie Wertfilter > Top 10..., setzen Sie die Zahl (z. B., Top 10) und das zu filternde Feld, und klicken Sie auf OK.
Ihre Pivot-Tabelle zeigt nun die dynamische Top-10 (oder ein beliebiges N, das Sie angeben) an. Um die Top-N-Liste zu ändern, öffnen Sie einfach erneut die Filtereinstellungen. Wenn sich Ihre Daten ändern, aktualisieren Sie die Pivot-Tabelle, um die Rankings sofort zu aktualisieren.
Die Vorteile dieses Ansatzes umfassen schnelle Einrichtung, einfaches Sortieren und interaktive Anpassung. Pivot-Tabellen können jedoch nicht automatisch entsprechende Zeilen aus anderen Spalten hinzufügen, es sei denn, diese wurden im Zeilen- oder Werte-Bereich enthalten. Fortgeschrittene Benutzer können Berichte weiter anpassen, indem sie gruppieren, Slicer erstellen oder den Top-N-Filter in Dashboards integrieren.
Erstellen einer dynamischen Top-10-Liste mit VBA
VBA-Makro: Automatisches Generieren und Aktualisieren einer Top-N-Liste
Die Verwendung eines VBA-Makros eignet sich gut für Benutzer, die mit umfangreichen oder häufig aktualisierten Daten arbeiten, wo die automatisierte Extraktion und Aktualisierung einer dynamischen Top-N-Liste erforderlich ist. Makros sind ideal, um repetitive Aufgaben zu reduzieren und Konsistenz sicherzustellen. Sie können eine Routine erstellen, die Ihre Daten sortiert und nur die Top-N-Zeilen an einen bestimmten Ort kopiert, jedes Mal, wenn sie ausgeführt wird.
Um ein VBA-Makro zur Erstellung einer dynamischen Top-N-Liste zu verwenden, befolgen Sie diese Schritte:
- Klicken Sie auf Entwickler > Visual Basic, um den VBA-Editor zu öffnen. (Wenn Sie die Entwickler-Registerkarte nicht sehen, gehen Sie zu Datei > Optionen > Menüband anpassen und aktivieren Sie „Entwickler“.)
- Klicken Sie im VBA-Fenster auf Einfügen > Modul, um ein neues Modul hinzuzufügen.
- Fügen Sie den folgenden VBA-Code im 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, Folgendes zu tun:
- Wählen Sie Ihren Datenbereich aus (einschließlich Überschriften für korrektes Sortieren).
- Wählen Sie die Ausgabezelle aus, um die Ergebnisse einzufügen.
- Geben Sie die Zahl N ein (z. B. 10 für Top 10).
Das Makro kopiert die Top-N-Einträge (einschließlich Überschriften) an den von Ihnen angegebenen Ort.
Es wird empfohlen, dies zunächst in einer Sicherungskopie Ihres Arbeitsbuchs zu testen. Falls Fehler auftreten (z. B. falscher Bereich ausgewählt), führen Sie das Makro erneut aus und stellen Sie sicher, dass Ihre Bereiche und Datenstruktur korrekt sind.
Diese Lösung ist ideal für die Automatisierung repetitiver Berichtsaufgaben, das Erstellen von Dashboards oder das schnelle Aktualisieren von Top-N-Berichten ohne manuelle Formeln oder Sortierung. Sie können das VBA-Skript weiter anpassen, um komplexere Ranking-Logiken zu implementieren, wie z. B. das Sortieren nach einer bestimmten Spalte oder das Exportieren von Ergebnissen in eine andere Arbeitsmappe.
Fehlerbehebung: Wenn das Makro nicht wie erwartet funktioniert, überprüfen Sie, ob Ihre Datentabelle korrekte Überschriften enthält, korrigieren Sie die Datentypen, um Sortierprobleme zu vermeiden, und stellen Sie sicher, dass die Zellbezüge in jedem Prompt genau ausgewählt sind. Speichern Sie Ihre Arbeit immer, bevor Sie Makros ausführen, um versehentliche Datenänderungen zu verhindern.
Zusammengefasst unterstützt Excel verschiedene Methoden zur Generierung und Pflege einer dynamischen Top-N-Liste – von traditionellen Formeln bis hin zu leistungsstarken Office 365-Funktionen, Pivot-Tabellen für interaktive Analysen und VBA-Makros für fortgeschrittene Automatisierung. Wählen Sie die Methode, die am besten zu Ihrem Arbeitsablauf und Ihrer Datengröße passt. Die Verwendung von Formeln ist für die meisten manuellen Analysen wirksam, Office 365-Funktionen bieten die größte Einfachheit und Leistung, Pivot-Tabellen sind großartig für schnelle, flexible Zusammenfassungen, und VBA ist besonders nützlich, um große, repetitive Ranking-Aufgaben zu automatisieren. Überprüfen Sie immer die Integrität Ihrer Formeln oder Codes und passen Sie Zellbezüge an, um Änderungen in Ihrer Datenstruktur zu berücksichtigen, während Ihr Projekt fortschreitet.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins