Generisk formel
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Sammendrag
For å få navnet på den nnd største verdien med kriterier, kan du bruke INDEX og MATCH, STOR funksjon og et filter opprettet med IF-funksjonen. I eksemplet vist er formelen i celle G5, kopiert ned,:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
der navn (B5: B16), gruppe (C5: C16) og poengsum (D5: D16) er navngitte områder. Formelen returnerer navnet tilknyttet de første, andre og tredje høyeste verdiene i gruppe A.
Merk: Dette er en matriseformel som må legges inn med kontroll + skift + enter, unntatt i Excel 365.
Forklaring
STOR-funksjonen er en enkel måte å få den nest største verdien i et område:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
I dette eksemplet kan vi bruke STOR funksjon for å få høyest poengsum, og deretter bruke poengsummen som en "nøkkel" for å hente det tilknyttede navnet med INDEX og MATCH. Legg merke til at vi plukker opp verdiene for n fra området F5: F7, for å få 1., 2. og 3. høyeste poengsum.
Vendingen i dette tilfellet er imidlertid at vi trenger å skille mellom poeng i gruppe A og gruppe B. Med andre ord, vi må bruke kriterier. Vi gjør dette med IF-funksjonen, som brukes til å "filtrere" verdier før de evalueres med STOR. For å få den største verdien (dvs. første verdi) i område2 hvor område 1 = "A", som et generelt eksempel, kan du bruke en formel som denne:
LARGE(IF(range="A",range2),1)
Merk: å bruke IF på denne måten gjør dette til en matriseformel.
Arbeidet fra innsiden og ut, det første trinnet er å få den "første" største verdien i dataene som er knyttet til gruppe A med STOR funksjon:
LARGE(IF(group="A",score),F5)
I dette tilfellet er verdien i F5 er 1, så vi ber om den øverste stillingen i gruppe A. Når IF funksjon beregnes, det tester hver verdi i det navngitte området gruppe . Den navngitte området stillingen er anordnet for verdi_hvis_sann. Dette genererer en ny matrise som returneres direkte til STOR funksjon:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Legg merke til at de eneste resultatene som overlever filteret er fra gruppe A. LARGE returnerer deretter den høyeste gjenværende poengsummen, 93, direkte til MATCH-funksjonen som en oppslagsverdi. Vi kan nå forenkle formelen til:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Nå kan vi se at MATCH-funksjonen er konfigurert, bruk samme filtrerte matrise som vi så ovenfor. IF-funksjonen filtrerer ut uønskede verdier igjen, og MATCH-delen av formelen løser seg å:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Siden 93 vises i 3. posisjon, returnerer MATCH 3 direkte til INDEX-funksjonen:
=INDEX(name,3) // Hannah
Til slutt returnerer INDEX-funksjonen navnet i 3. rad, "Hannah".
Med XLOOKUP
XLOOKUP-funksjonen kan også brukes til å løse dette problemet ved å bruke samme tilnærming som forklart ovenfor:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Som ovenfor er LARGE konfigurert til å fungere med en matrise filtrert av IF, og returnerer et resultat på 93 til XLOOKUP som oppslagsverdi:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Oppslagsmatrisen opprettes også ved å bruke IF som et filter på score fra gruppe A. Med returmatrisen oppgitt som navn (B5: B16). XLOOKUP returnerer "Hannah" som det endelige resultatet.
Merknader
- For å få navnet på den nte verdien med kriterier (dvs. begrense resultatene til gruppe A eller B), må du utvide formelen for å bruke ekstra logikk.
- I Excel 365 er FILTER-funksjonen en bedre måte å liste opp topp- eller bunnresultater dynamisk. Denne tilnærmingen vil automatisk håndtere bånd.
