Generische Formel
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Zusammenfassung
Um den Namen des n-ten größten Werts zu erhalten, können Sie INDEX und MATCH mit der Funktion LARGE verwenden. In dem gezeigten Beispiel lautet die Formel in Zelle H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
Dabei sind Name (B5: B16) und Punktzahl (D5: D16) benannte Bereiche.
Erläuterung
Kurz gesagt, diese Formel verwendet die Funktion LARGE, um den n-ten größten Wert in einem Datensatz zu ermitteln. Sobald wir diesen Wert haben, fügen wir ihn in eine Standardformel von INDEX und MATCH ein, um den zugehörigen Namen abzurufen. Mit anderen Worten, wir verwenden den n-ten größten Wert wie einen "Schlüssel", um zugehörige Informationen abzurufen.
Die LARGE-Funktion ist ein einfacher Weg, um den n-ten größten Wert in einem Bereich zu erhalten. Geben Sie einfach einen Bereich für das erste Argument (Array) und einen Wert für n als zweites Argument (k) an:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Der erste Schritt besteht darin, von innen nach außen den "1." größten Wert in den Daten mit der Funktion LARGE zu erhalten:
LARGE(score,F5) // returns 93
In diesem Fall ist der Wert in F5 1, daher fragen wir nach der 1. größten Punktzahl (dh der höchsten Punktzahl), nämlich 93. Wir können die Formel jetzt vereinfachen, um:
=INDEX(name,MATCH(93,score,0))
Innerhalb der Funktion INDEX wird die MATCH - Funktion eingerichtet , um die Position von 93 in dem benannten Bereich zu lokalisieren Punktzahl (D5: D16):
MATCH(93,score,0) // returns 3
Da 93 in der 3. Zeile angezeigt wird, gibt MATCH 3 als Zeilennummer direkt an INDEX zurück, mit dem Namen als Array:
=INDEX(name,3) // Hannah
Schließlich gibt die INDEX-Funktion den Namen in der 3. Zeile "Hannah" zurück.
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, wenn die Formel kopiert wird.
Gruppe abrufen
Dieselbe Grundformel funktioniert, um alle zugehörigen Informationen abzurufen. Um die Gruppe für die größten Werte zu erhalten, können Sie einfach die Array INDEX mit der benannten Bereich geliefert ändern Gruppe :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Mit dem Wert 1 in F5 erhält LARGE die höchste Punktzahl und die Formel gibt "A" zurück.
Hinweis: Mit Excel 365 können Sie die Funktion FILTER verwenden, um die oberen oder unteren Ergebnisse dynamisch aufzulisten.
Mit XLOOKUP
Die XLOOKUP-Funktion kann auch verwendet werden, um den Namen des n-ten größten Werts wie folgt zurückzugeben:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE gibt den größten Wert 93 direkt als Suchwert an XLOOKUP zurück:
=XLOOKUP(93,score,name) // Hannah
Mit dem benannten Bereich Score (D5: D16) als Lookup - Array und Namen (B5: B16) als Rück Array, XLOOKUP returns "Hannah" wie zuvor.
Umgang mit Krawatten
Doppelte Werte in den numerischen Daten erzeugen eine "Bindung". Wenn bei den zu bewertenden Werten ein Gleichstand auftritt, z. B. wenn der erste und der zweitgrößte Wert gleich sind, gibt LARGE für jeden den gleichen Wert zurück. Wenn dieser Wert an die MATCH-Funktion übergeben wird, gibt MATCH die Position der ersten Übereinstimmung zurück, sodass derselbe (erste) Name zurückgegeben wird.
Wenn die Möglichkeit von Unentschieden besteht, möchten Sie möglicherweise eine Strategie zum Aufbrechen von Unentschieden implementieren. Ein Ansatz besteht darin, eine neue Hilfsspalte mit Werten zu erstellen, die angepasst wurden, um Bindungen zu lösen. Verwenden Sie dann die Hilfsspaltenwerte, um Informationen zu ordnen und abzurufen. Dies macht die Logik zum Aufbrechen von Verbindungen klar und deutlich.
Ein anderer Ansatz besteht darin, Unentschieden nur auf der Grundlage der Position zu lösen (dh das erste Unentschieden "gewinnt"). Hier ist eine Formel, die diesen Ansatz verfolgt:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365.
Hier verwenden wir MATCH, um die Nummer 1 zu finden, und erstellen ein Sucharray unter Verwendung einer booleschen Logik, die (1) alle Bewertungen mit dem von LARGE zurückgegebenen Wert vergleicht:
score=LARGE(score,F5)
und (2) eine Prüfung des erweiterten Bereichs verwendet, wenn der Name bereits in der Rangliste enthalten ist:
COUNTIF(H$4:H4,name)=0
Wenn ein Name bereits in der Liste enthalten ist, wird er von der Logik "abgebrochen" und der nächste (doppelte) Wert wird abgeglichen. Beachten Sie, dass der Erweiterungsbereich in der vorherigen Zeile beginnt, um einen Zirkelverweis zu vermeiden.
Dieser Ansatz funktioniert in diesem Beispiel, da die Namensspalte keine doppelten Namen enthält. Wenn jedoch doppelte Namen in Rangwerten auftreten, muss der Ansatz angepasst werden. Die einfachste Lösung besteht darin, sicherzustellen, dass die Namen eindeutig sind.
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.
