Zum Hauptinhalt springen

Entdecken Sie noch heute die neue, kostenlose Version von Kutools für Outlook!

Wie erstellt man eine dynamische Top-10- oder n-Liste in Excel?

Author: Xiaoyang Last Modified: 2025-05-28

Eine Top-Liste wird verwendet, um Unternehmen oder Einzelpersonen auf Basis von Werten zu ranken. Angenommen, Sie haben eine Liste mit den Noten einer Klasse und möchten nun eine dynamische Top-10-Liste der Schüler wie im folgenden Screenshot erstellen. In diesem Artikel werde ich einige Formeln vorstellen, mit denen Sie eine Top-10- oder n-Liste in einem Excel-Arbeitsblatt erstellen können.

Erstellen einer dynamischen Top-10-Liste in Excel

In Excel 2019 und früheren Versionen sollten Sie die folgenden Formeln verwenden, um die Top-10-Liste oder die Top-10-Liste mit Kriterien zu extrahieren:

Formeln zur Erstellung einer dynamischen Top-10-Liste

1. Zuerst sollten Sie die Top-10-Werte aus dem Datenbereich extrahieren. Verwenden Sie dazu die folgende Formel in einer leeren Zelle – G2, und ziehen Sie den Ausfüllkursor nach unten, um die Top-10-Werte zu erhalten, siehe Screenshot:

=GROSS($B$2:$B$20,ZEILEN(B$2:B2))
Hinweis: In dieser Formel ist B2:B20 der Datenbereich, aus dem Sie die Top-10-Werte abrufen möchten, und B2 ist die erste Zelle des Datenbereichs.

apply a formula to extract the top 10 values

2. Anschließend wenden Sie die folgende Formel in Zelle F2 an und drücken gleichzeitig die Tasten Strg + Umschalt + Eingabetaste, um das erste Ergebnis zu erhalten, siehe Screenshot:

=INDEX($A$2:$A$20,KLEINSTE(WENN($B$2:$B$20=G2,ZEILE($B$2:$B$20)-ZEILE($B$1)),ZÄHLENWENN($G$2:G2,G2)))
Hinweis: In der obigen Formel ist A2:A20 der Datenbereich, aus dem Sie die Namen der Top-10-Werte abrufen möchten, B2:B20 ist der Datenbereich, der alle Werte enthält, G2 ist die Zelle des größten Wertes, der aus Spalte B extrahiert wurde, und B1 ist die Kopfzeile der Werteliste.

use a formula to get relative item

3. Nachdem Sie das erste Ergebnis erhalten haben, wählen Sie die Formelzelle aus und ziehen Sie den Ausfüllkursor nach unten, um die anderen Namen auf einmal zu erhalten, siehe Screenshot:

drag and fill the formula to other cells

a screenshot of kutools for excel ai

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.
Erweitern Sie Ihre Excel-Fähigkeiten mit KI-gestützten Tools. Laden Sie jetzt herunter und erleben Sie eine Effizienz wie nie zuvor!
Formeln zur Erstellung einer dynamischen Top-10-Liste mit Kriterien

Manchmal müssen Sie möglicherweise die Top-10-Liste basierend auf bestimmten Kriterien erstellen. Zum Beispiel, um die Top-10-Namen und -Punktzahlen von Klasse 1 wie im folgenden Screenshot gezeigt zu erhalten.

create a dynamic top 10 list with criteria

1. Um die Top-10-Liste zu erhalten, sollten Sie auch die Top-10-Punktzahlen mit dieser Formel extrahieren:

=GROSS(WENN($B$2:$B$25=$F$2,$C$2:$C$25),ZEILE(I2)-ZEILE(I$1))

2. Drücken Sie dann gleichzeitig die Tasten Strg + Umschalt + Eingabetaste, um das erste Ergebnis zu erhalten, und ziehen Sie diese Formel nach unten, um andere Werte anzuzeigen, siehe Screenshot:

apply a formula to extract the top 10 values based on criteria

3. Kopieren Sie anschließend die folgende Formel in Zelle I2, und drücken Sie gleichzeitig die Tasten Strg + Umschalt + Eingabetaste, um das erste Ergebnis zu extrahieren. Ziehen Sie die Formel dann nach unten, um sie in andere Zellen zu füllen, und die Top-10-Namen werden angezeigt.

=INDEX($A$2:$A$25,KLEINSTE(WENN(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ZEILE($C$2:$C$25)-ZEILE($C$1)),ZÄHLENWENN(J2:$J$2,J2)))

Erstellen einer dynamischen Top-10-Liste in Office 365

Die oben genannten Formeln können schwierig zu verstehen sein. Wenn Sie Office 365 verwenden, können Sie mit den Funktionen INDEX, SORTIEREN und SEQUENZ einfache Formeln erstellen, um diese Aufgabe zu erledigen.

Formel zur Erstellung einer dynamischen Top-10-Liste

Um die Top-10-Liste der Daten zu erhalten, verwenden Sie bitte die folgende Formel:

=INDEX(SORTIEREN(A2:B20,2,-1),SEQUENZ(10),{1,2})

Drücken Sie dann einfach die Eingabetaste, und alle Daten der Top-10-Liste werden auf einmal angezeigt, siehe Screenshot:

use a formula to create a dynamic top 10 list in Office 365

Tipps:

SORTIEREN-Funktion:

=SORTIEREN(Bereich, [sort_index], [sort_order], [by_col])

  • Bereich: Der Zellbereich, den Sie sortieren möchten;
  • [sort_index]: Die Spalten- oder Reihennummer, nach der der Bereich sortiert werden soll. Zum Beispiel, um nach der zweiten Spalte des Datenbereichs zu sortieren, wäre der Sortierindex 2;
  • [sort_order]: Die Zahl 1 (oder weggelassen) gibt an, dass in aufsteigender Reihenfolge sortiert wird; die Zahl -1, in absteigender Reihenfolge;
  • [by_col]: Die Richtung der Sortierung. WAHR, nach Spalten sortieren, FALSCH oder weggelassen, nach Zeilen sortieren.

In der Formel verwenden wir die SORTIEREN-Funktion wie folgt:

SORTIEREN(A2:B20,2,-1): Das bedeutet, den Zellbereich A2:A20 nach der zweiten Spalte in absteigender Reihenfolge zu sortieren.

SEQUENZ-Funktion:

=SEQUENZ(Zeilen, [Spalten], [Start], [Schritt])

  • Zeilen: Die Anzahl der zurückzugebenden Zeilen,
  • [Spalten]: Die Anzahl der zurückzugebenden Spalten. Wenn weggelassen, wird eine einzelne Spalte zurückgegeben.
  • [Start]: Die erste Zahl in der Sequenz. Wenn weggelassen, beginnt sie bei 1.
  • [Schritt]: Das Inkrement zwischen jeder Zahl. Wenn es ausgeschlossen wird, beträgt jedes Inkrement 1.

In dieser Formel verwenden wir SEQUENZ(10), um eine Liste von 1 bis 10 zu erstellen.

Schließlich setzen Sie sowohl die Funktionen SORTIEREN als auch SEQUENZ in die INDEX-Funktion ein: =INDEX(SORTIEREN(A2:B20,2,-1),SEQUENZ(10),{1,2}). Dies gibt die ersten 10 Datensätze aus dem Datenbereich zurück und gibt Spalten 1 und 2 zurück.

Formel zur Erstellung einer dynamischen Top-10-Liste mit Kriterien

Um die Top-10-Liste mit Kriterien anzuzeigen, sollten Sie die FILTER-Funktion in die SORTIEREN-Funktion einbetten, wie hier:

=INDEX(SORTIEREN(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENZ(10),{1,3})

Drücken Sie einfach die Eingabetaste, und alle Daten der Top-10-Liste basierend auf den angegebenen Kriterien werden auf einmal angezeigt, siehe Screenshot:

another formula to create a dynamic top 10 list with criteria in office 365

Tipps:

FILTER-Funktion:

=FILTER(Bereich, einschließen, [wenn_leer])

  • Bereich: Der Zellbereich, der gefiltert werden soll.
  • einschließen: Die Bedingung, die Sie verwenden, um den Bereich zu filtern, um ein Array von WAHR oder FALSCH-Ergebnissen zu erhalten, sodass die WAHR-Werte im Filter beibehalten werden.
  • [wenn_leer]: Der Wert, der angezeigt werden soll, wenn keine übereinstimmenden Ergebnisse zurückgegeben werden.

In dieser Formel: =FILTER(A2:C25,B2:B25=F2) wird verwendet, um im Bereich A2:C25 zu filtern, wo die Werte von B2:B25 gleich der spezifischen Zelle F2 sind.

Beste Büroproduktivitätswerkzeuge

🤖 Kutools AI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Benutzerdefinierte Formeln erstellen  |  Daten analysieren und Diagramme generieren  |  Erweiterte Funktionen aufrufen…
Beliebte Funktionen: Finden, Hervorheben oder Doppelte markieren   |  Leere Zeilen löschen   |  Spalten oder Zellen kombinieren ohne Datenverlust   |   Runden ...
Erweiterte SVERWEIS: Mehrfachkriterien SVERWEIS  |   Mehrfachwert SVERWEIS  |   Mehrblatt-SVERWEIS   |   Fuzzy Match ....
Erweiterte Dropdown-Liste: Schnell Dropdown-Liste erstellen   |  Abhängige Dropdown-Liste   |  Mehrfachauswahl Dropdown-Liste ....
Spaltenmanager: Eine bestimmte Anzahl von Spalten hinzufügen  |  Spalten verschieben  |  Sichtbarkeitsstatus ausgeblendeter Spalten umschalten  |  Bereiche & Spalten vergleichen ...
Hervorgehobene Funktionen: Gitterfokus   |  Entwurfsansicht   |   Erweiterte Formelleiste   |  Arbeitsmappe & Arbeitsblatt-Manager   |  AutoText-Bibliothek (Auto Text)   |  Datumsauswahl   |  Daten zusammenführen   |  Zellen verschlüsseln/entschlüsseln   |  E-Mail senden nach Liste   |  Spezialfilter   |   Spezialfilter (Filtern fett/kursiv/durchgestrichen...) ...
Top15 Werkzeugsets: 12 Textwerkzeuge (Text hinzufügen, Bestimmte Zeichen löschen, ...)   |   50+ Diagramm typen (Gantt-Diagramm, ...)   |   40+ Praktische Formeln (Alter basierend auf dem Geburtsdatum berechnen, ...)   |   19 Einfügewerkzeuge (QR-Code einfügen, Bild aus Pfad einfügen, ...)   |   12 Konvertierungswerkzeuge (In Wörter umwandeln, Währungsumrechnung, ...)   |   7 Konsolidieren & Aufteilen Werkzeuge (Erweiterte Zeilen zusammenführen, Zellen aufteilen, ...)   |   ... und mehr

Verbessern Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über300 erweiterte Funktionen zur Steigerung der Produktivität und Zeitersparnis.  Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...

Office Tab bringt eine Registerkartenoberfläche zu Office und macht Ihre Arbeit viel einfacher

  • Aktivieren Sie die Bearbeitung und das Lesen mit Registerkarten in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters, anstatt in neuen Fenstern.
  • Steigert Ihre Produktivität um50 % und reduziert täglich hunderte von Mausklicks für Sie!