Generisk formel
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Sammanfattning
För att få namnet på det nionde största värdet kan du använda INDEX och MATCH med LARGE-funktionen. I exemplet som visas är formeln i cell H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
där namn (B5: B16) och poäng (D5: D16) heter områden.
Förklaring
I ett nötskal använder denna formel funktionen STORA för att hitta det n: e största värdet i en uppsättning data. När vi väl har det värdet ansluter vi det till en standardformel INDEX och MATCH för att hämta tillhörande namn. Med andra ord använder vi det n: e största värdet som en "nyckel" för att hämta tillhörande information.
LARGE-funktionen är ett enkelt sätt att få det n: e största värdet i ett intervall. Ange bara ett intervall för det första argumentet (array) och ett värde för n som det andra argumentet (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Arbetar inifrån och ut, det första steget är att få det "första" största värdet i data med STOR funktion:
LARGE(score,F5) // returns 93
I det här fallet är värdet i F5 1, så vi ber om den första största poängen (dvs. topppoängen), vilket är 93. Vi kan nu förenkla formeln till:
=INDEX(name,MATCH(93,score,0))
Inne i funktionen INDEX är MATCH funktionen inrättats för att lokalisera positionen av 93 i det namngivna området poäng (D5: D16):
MATCH(93,score,0) // returns 3
Eftersom 93 visas i den tredje raden, returnerar MATCH 3 direkt till INDEX som radnummer, med namn som array:
=INDEX(name,3) // Hannah
Slutligen returnerar INDEX-funktionen namnet i den tredje raden, "Hannah".
Observera att vi plockar upp värdena för n från intervallet F5: F7, för att få 1: a, 2: a och 3: e högsta poäng när formeln kopieras.
Hämta grupp
Samma grundformel fungerar för att hämta all associerad information. För att få gruppen för de största värdena, kan du enkelt ändra matrisen levereras till index med det namngivna området gruppen :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Med värdet 1 i F5 får LARGE den högsta poängen och formeln returnerar "A".
Obs! Med Excel 365 kan du använda FILTER-funktionen för att lista topp- eller bottenresultat dynamiskt.
Med XLOOKUP
XLOOKUP-funktionen kan också användas för att returnera namnet på det nionde största värdet så här:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE returnerar det största värdet, 93, direkt till XLOOKUP som uppslagsvärde:
=XLOOKUP(93,score,name) // Hannah
Med det namngivna området poäng (D5: D16) som lookup array, och namn (B5: B16) som return array, XLOOKUP avkastning "Hannah" som tidigare.
Hantering av slipsar
Dubbla värden i numeriska data skapar en "tie". Om en slips uppträder i värdena som rankas, till exempel om de första och näst största värdena är desamma, returnerar LARGE samma värde för varje. När detta värde överförs till MATCH-funktionen, returnerar MATCH positionen för den första matchen, så du kommer att se samma (förnamn) returneras.
Om det finns möjlighet till band, kanske du vill implementera någon form av tie-breaking strategi. Ett tillvägagångssätt är att skapa en ny hjälpkolumn med värden som har justerats för att bryta banden. Använd sedan hjälpkolumnvärdena för att rangordna och hämta information. Detta gör logiken som används för att bryta band tydlig och tydlig.
Ett annat tillvägagångssätt är att bryta band endast baserat på position (dvs. det första bandet "vinner"). Här är en formel som tar den metoden:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Obs: detta är en matrisformel och måste anges med kontroll + shift + enter, utom i Excel 365.
Här använder vi MATCH för att hitta siffran 1, och vi konstruerar en uppslagsmatris med boolesk logik som (1) jämför alla poäng med värdet som returneras av STOR:
score=LARGE(score,F5)
och (2) använder en expanderande intervallkontroll om namnet redan finns i den rankade listan:
COUNTIF(H$4:H4,name)=0
När ett namn redan finns i listan "avbryts" det av logiken och nästa (dubbla) värde matchas. Lägg märke till att det expanderande intervallet börjar på föregående rad för att undvika en cirkulär referens.
Detta tillvägagångssätt fungerar i det här exemplet eftersom det inte finns några dubblettnamn i namnkolumnen. Men om dubblettnamn förekommer i rankade värden måste metoden justeras. Den enklaste lösningen är att se till att namnen är unika.
Anteckningar
- För att få namnet på nth-värdet med kriterier (dvs. begränsa resultaten till grupp A eller B) måste du utöka formeln för att använda ytterligare logik.
- I Excel 365 är FILTER-funktionen ett bättre sätt att lista topp- eller bottenresultat dynamiskt. Detta tillvägagångssätt kommer automatiskt att hantera band.
