Generisk formel
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Resumé
For at få navnet på den nth største værdi, kan du bruge INDEX og MATCH med LARGE-funktionen. I det viste eksempel er formlen i celle H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
hvor navn (B5: B16) og score (D5: D16) er navngivet områder.
Forklaring
I en nøddeskal bruger denne formel LARGE-funktionen til at finde den nth største værdi i et datasæt. Når vi først har den værdi, tilslutter vi den til en standard INDEX- og MATCH-formel for at hente det tilknyttede navn. Med andre ord bruger vi den nth største værdi som en "nøgle" til at hente tilknyttet information.
LARGE-funktionen er en ligetil måde at få den nth største værdi i et interval. Angiv blot et interval for det første argument (array) og en værdi for n som det andet argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Arbejder indefra og ud, det første skridt er at få den "1." største værdi i dataene med LARGE-funktionen:
LARGE(score,F5) // returns 93
I dette tilfælde er værdien i F5 1, så vi beder om den 1. største score (dvs. topscoren), som er 93. Vi kan nu forenkle formlen til:
=INDEX(name,MATCH(93,score,0))
Inde i INDEX-funktionen er MATCH-funktionen indstillet til at lokalisere positionen 93 i den nævnte områdescore (D5: D16):
MATCH(93,score,0) // returns 3
Da 93 vises i 3. række, returnerer MATCH 3 direkte til INDEX som række nummer med navn som array:
=INDEX(name,3) // Hannah
Endelig returnerer INDEX-funktionen navnet i 3. række, "Hannah".
Bemærk, at vi samler værdierne for n fra området F5: F7 for at få 1., 2. og 3. højeste score, da formlen kopieres ned.
Hent gruppe
Den samme grundlæggende formel fungerer for at hente eventuelle tilknyttede oplysninger. For at få gruppen til de største værdier, kan du blot ændre array leveret til INDEX med det navngivne område gruppe :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Med værdien 1 i F5 får LARGE den højeste score, og formlen returnerer "A".
Bemærk: med Excel 365 kan du bruge FILTER-funktionen til at liste de øverste eller nederste resultater dynamisk.
Med XLOOKUP
XLOOKUP-funktionen kan også bruges til at returnere navnet på den nth største værdi som denne:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE returnerer den største værdi, 93, direkte til XLOOKUP som opslagsværdi:
=XLOOKUP(93,score,name) // Hannah
Med det navngivne område score (D5: D16) som opslag array, og navn (B5: B16) som afkastet array, XLOOKUP returnerer "Hannah" som før.
Håndtering af bånd
Duplikatværdier i de numeriske data skaber en "tie". Hvis der forekommer uafgjort i de værdier, der rangeres, for eksempel hvis den første og næststørste værdi er den samme, returnerer LARGE den samme værdi for hver. Når denne værdi overføres til MATCH-funktionen, returnerer MATCH positionen for den første kamp, så du vil se det samme (første) navn returneret.
Hvis der er mulighed for bånd, kan det være en god idé at implementere en form for tie-breaking-strategi. En tilgang er at skabe en ny hjælpekolonne af værdier, der er blevet justeret til at bryde bånd. Brug derefter hjælpekolonnens værdier til at rangordne og hente oplysninger. Dette gør logikken, der bruges til at bryde bånd, klar og eksplicit.
En anden tilgang er at bryde bånd kun baseret på position (dvs. det første uafgjort "vinder"). Her er en formel, der tager denne tilgang:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Her bruger vi MATCH til at finde tallet 1, og vi konstruerer et opslagsarray ved hjælp af boolsk logik, der (1) sammenligner alle scoringer med den værdi, der returneres af LARGE:
score=LARGE(score,F5)
og (2) bruger en ekspanderende rækkevidde, hvis navnet allerede er på den rangerede liste:
COUNTIF(H$4:H4,name)=0
Når et navn allerede er på listen, annulleres det af logikken, og den næste (duplikerede) værdi matches. Bemærk, at udvidelsesområdet starter på den forrige række for at undgå en cirkulær reference.
Denne fremgangsmåde fungerer i dette eksempel, fordi der ikke er duplikatnavne i kolonnen Navn. Hvis duplikatnavne forekommer i rangerede værdier, skal tilgangen imidlertid justeres. Den nemmeste løsning er at sikre, at navne er unikke.
Bemærkninger
- For at få navnet på den n-værdi med kriterier (dvs. begrænse resultaterne til gruppe A eller B) skal du udvide formlen for at bruge yderligere logik.
- I Excel 365 er FILTER-funktionen en bedre måde at liste top- eller bundresultater dynamisk på. Denne tilgang håndterer automatisk bånd.
