Generisk formel
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Sammendrag
For å få navnet til den nndestørste verdien, kan du bruke INDEX og MATCH med STOR funksjon. I eksemplet vist er formelen i celle H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
der navn (B5: B16), og poengsum (D5: D16) er kalt områder.
Forklaring
I et nøtteskall bruker denne formelen LARGE-funksjonen til å finne den nest største verdien i et datasett. Når vi har den verdien, kobler vi den til en standard INDEX- og MATCH-formel for å hente det tilknyttede navnet. Med andre ord bruker vi den nth største verdien som en "nøkkel" for å hente tilknyttet informasjon.
LARGE-funksjonen er en grei måte å få den nest største verdien i et område. Bare gi et område for det første argumentet (array), og en verdi for n som det andre argumentet (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Arbeidet fra innsiden og ut, det første trinnet er å få den "første" største verdien i dataene med STOR funksjon:
LARGE(score,F5) // returns 93
I dette tilfellet er verdien i F5 1, så vi ber om den første største poengsummen (dvs. toppscoren), som er 93. Vi kan nå forenkle formelen til:
=INDEX(name,MATCH(93,score,0))
Inne i INDEX-funksjonen, blir den MATCH funksjon satt opp for å finne posisjonen til 93 i det navngitte området stillingen (D5: D16):
MATCH(93,score,0) // returns 3
Siden 93 vises i 3. rad, returnerer MATCH 3 direkte til INDEX som radnummer, med navn som matrise:
=INDEX(name,3) // Hannah
Til slutt returnerer INDEX-funksjonen navnet i 3. rad, "Hannah".
Legg merke til at vi plukker opp verdiene for n fra området F5: F7, for å få 1., 2. og 3. høyeste poengsum når formelen kopieres ned.
Hent gruppe
Den samme grunnleggende formelen vil fungere for å hente all tilknyttet informasjon. For å få gruppen for de største verdiene, kan du bare endre array leveres til INDEX med navngitte området gruppen :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Med verdien 1 i F5 vil LARGE få den høyeste poengsummen, og formelen vil returnere "A".
Merk: med Excel 365 kan du bruke FILTER-funksjonen til å liste topp- eller bunnresultater dynamisk.
Med XLOOKUP
XLOOKUP-funksjonen kan også brukes til å returnere navnet på den niende største verdien slik:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE returnerer den største verdien, 93, direkte til XLOOKUP som oppslagsverdi:
=XLOOKUP(93,score,name) // Hannah
Med navngitte området poengsum (D5: D16) som oppslags array, og navn (B5: B16) som avkastningen array, XLOOKUP returnerer "Hannah" som før.
Håndtering av bånd
Dupliserte verdier i de numeriske dataene vil skape en "tie". Hvis det forekommer uavgjort i verdiene som blir rangert, for eksempel hvis den første og nest største verdien er den samme, vil LARGE returnere den samme verdien for hver. Når denne verdien blir overført til MATCH-funksjonen, vil MATCH returnere plasseringen til den første kampen, så du vil se det samme (første) navnet returneres.
Hvis det er mulighet for tilknytning, kan det være lurt å implementere en slags tie-breaking strategi. En tilnærming er å lage en ny hjelpekolonne med verdier som er justert for å bryte bånd. Bruk deretter hjelpekolonneverdiene til å rangere og hente informasjon. Dette gjør logikken som brukes til å bryte båndene klar og eksplisitt.
En annen tilnærming er å bryte bånd bare basert på posisjon (dvs. det første slipset "vinner"). Her er en formel som tar den tilnærmingen:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Merk: dette er en matriseformel og må angis med kontroll + skift + enter, unntatt i Excel 365.
Her bruker vi MATCH for å finne tallet 1, og vi konstruerer et oppslagsarray ved hjelp av boolsk logikk som (1) sammenligner alle poeng med verdien som returneres av STOR:
score=LARGE(score,F5)
og (2) bruker et utvidende områdekontroll om navnet allerede er på rangert liste:
COUNTIF(H$4:H4,name)=0
Når et navn allerede er i listen, blir det "kansellert" av logikken, og neste (dupliserte) verdi blir matchet. Legg merke til at det utvidende området starter på forrige rad, for å unngå en sirkulær referanse.
Denne tilnærmingen fungerer i dette eksemplet fordi det ikke er dupliserte navn i navnekolonnen. Imidlertid, hvis dupliserte navn forekommer i rangerte verdier, må tilnærmingen justeres. Den enkleste løsningen er å sørge for at navnene er unike.
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.
