Generische Formel
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Zusammenfassung
Um Daten zu filtern, um die Top-n-Werte anzuzeigen, die bestimmte Kriterien erfüllen, können Sie die Funktion FILTER zusammen mit den Funktionen LARGE und IF verwenden. In dem gezeigten Beispiel lautet die Formel in F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
Dabei werden Daten (B5: D16), Gruppe (C5: C16) und Punktzahl (D5: D16) als Bereiche bezeichnet.
Erläuterung
Diese Formel verwendet die Funktion FILTER, um Daten basierend auf einem logischen Test abzurufen, der mit den Funktionen LARGE und IF erstellt wurde. Das Ergebnis sind die Top 3 in Gruppe B.
Die Funktion FILTER wendet Kriterien mit dem Argument include an. In diesem Beispiel werden Kriterien mit einer booleschen Logik wie folgt erstellt:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Die linke Seite des Ausdrucks zielt auf Punktzahlen ab, die größer oder gleich der dritthöchsten Punktzahl in Gruppe B sind:
score>=LARGE(IF(group="b",score),3)
Die IF-Funktion wird verwendet, um sicherzustellen, dass LARGE nur mit Scores der Gruppe B arbeitet. Da wir insgesamt 12 Punkte haben, gibt IF ein Array mit 12 Ergebnissen wie folgt zurück:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Beachten Sie, dass die einzigen Bewertungen, die die Operation überleben, aus Gruppe B stammen. Alle anderen Bewertungen sind FALSCH. Dieses Array wird als Array-Argument direkt an LARGE zurückgegeben:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignoriert die FALSE-Werte und gibt die dritthöchste Punktzahl zurück, 83.
Wir können die Formel jetzt vereinfachen, um:
=FILTER(data,(score>=83)*(group="b"))
was sich auflöst:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Schließlich gibt FILTER Datensätze für Mason, Annie und Cassidy zurück, die in den Bereich F5: H7 fallen.
Ergebnisse nach Punktzahl sortieren
Standardmäßig gibt FILTER übereinstimmende Datensätze in derselben Reihenfolge zurück, in der sie in den Quelldaten angezeigt werden. Um die Ergebnisse in absteigender Reihenfolge nach Punktzahl zu sortieren, können Sie die ursprüngliche FILTER-Formel wie folgt in die SORT-Funktion einbetten:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Hier gibt FILTER die Ergebnisse als Array-Argument direkt an die SORT-Funktion zurück. Der Sortierindex ist auf 3 (Punktzahl) und die Sortierreihenfolge auf -1 für absteigende Reihenfolge eingestellt.
