Generische Formel
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Zusammenfassung
Um den Namen des n-ten größten Werts mit Kriterien zu erhalten, können Sie INDEX und MATCH, die Funktion LARGE und einen mit der Funktion IF erstellten Filter verwenden. In dem gezeigten Beispiel lautet die Formel in Zelle G5, die nach unten kopiert wurde:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
Dabei sind Name (B5: B16), Gruppe (C5: C16) und Punktzahl (D5: D16) benannte Bereiche. Die Formel gibt den Namen zurück, der den 1., 2. und 3. höchsten Werten in Gruppe A zugeordnet ist.
Hinweis: Dies ist eine Array-Formel, die mit Strg + Umschalt + Eingabe eingegeben werden muss, außer in Excel 365.
Erläuterung
Die LARGE-Funktion ist eine einfache Möglichkeit, den n-ten größten Wert in einem Bereich zu erhalten:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
In diesem Beispiel können wir die LARGE-Funktion verwenden, um die höchste Punktzahl zu erhalten, und dann die Punktzahl wie einen "Schlüssel" verwenden, um den zugehörigen Namen mit INDEX und MATCH abzurufen. Beachten Sie, dass wir die Werte für n aus dem Bereich F5: F7 übernehmen, um die 1., 2. und 3. höchste Punktzahl zu erhalten.
Die Wendung in diesem Fall ist jedoch, dass wir zwischen Punktzahlen in Gruppe A und Gruppe B unterscheiden müssen. Mit anderen Worten, wir müssen Kriterien anwenden. Wir tun dies mit der IF-Funktion, mit der Werte "gefiltert" werden, bevor sie mit LARGE ausgewertet werden. Als generisches Beispiel können Sie eine Formel wie die folgende verwenden, um den größten Wert (dh den ersten Wert) in Bereich 2 zu erhalten, wobei Bereich 1 = "A" ist:
LARGE(IF(range="A",range2),1)
Hinweis: Wenn Sie IF auf diese Weise verwenden, wird dies zu einer Array-Formel.
Der erste Schritt besteht darin, von innen nach außen den "ersten" größten Wert in den Daten zu ermitteln, die der Gruppe A mit der Funktion LARGE zugeordnet sind:
LARGE(IF(group="A",score),F5)
In diesem Fall ist der Wert in F5 1, so dass wir für die Bestnote in der Gruppe A fragen Wenn die IF - Funktion ausgewertet wird, prüft er jeden Wert in der benannten Bereich Gruppe . Die benannte Bereich Punktzahl für Dann_Wert zur Verfügung gestellt. Dadurch wird ein neues Array generiert, das direkt an die Funktion LARGE zurückgegeben wird:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Beachten Sie, dass die einzigen Bewertungen, die den Filter überleben, aus Gruppe A stammen. LARGE gibt dann die höchste verbleibende Bewertung (93) direkt als Suchwert an die MATCH-Funktion zurück. Wir können die Formel jetzt vereinfachen, um:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Jetzt können wir sehen, dass die MATCH-Funktion mit demselben gefilterten Array konfiguriert ist, das wir oben gesehen haben. Die IF-Funktion filtert erneut unerwünschte Werte heraus und der MATCH-Teil der Formel wird aufgelöst in:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Da 93 an dritter Stelle erscheint, gibt MATCH 3 direkt an die INDEX-Funktion zurück:
=INDEX(name,3) // Hannah
Schließlich gibt die INDEX-Funktion den Namen in der 3. Zeile "Hannah" zurück.
Mit XLOOKUP
Die XLOOKUP-Funktion kann auch verwendet werden, um dieses Problem mit demselben oben erläuterten Ansatz zu lösen:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Wie oben ist LARGE so konfiguriert, dass es mit einem nach IF gefilterten Array arbeitet, und gibt ein Ergebnis von 93 als Suchwert an XLOOKUP zurück:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Das Lookup-Array wird auch erstellt, indem IF als Filter für Scores aus Gruppe A verwendet wird. Das Return-Array wird als Name angegeben (B5: B16). XLOOKUP gibt "Hannah" als Endergebnis zurück.
Anmerkungen
- Um den Namen des n-ten Werts mit Kriterien zu erhalten (dh die Ergebnisse auf Gruppe A oder B zu beschränken), müssen Sie die Formel erweitern, um zusätzliche Logik zu verwenden.
- In Excel 365 ist die Funktion FILTER eine bessere Möglichkeit, die oberen oder unteren Ergebnisse dynamisch aufzulisten. Dieser Ansatz behandelt automatisch Krawatten.
