Generisk formel
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Resumé
For at få navnet på den nth største værdi med kriterier, kan du bruge INDEX og MATCH, den store funktion og et filter oprettet med IF-funktionen. I det viste eksempel er formlen i celle G5, kopieret ned,:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
hvor navn (B5: B16), gruppe (C5: C16) og score (D5: D16) er navngivet områder. Formlen returnerer det navn, der er knyttet til den 1., 2. og 3. højeste værdi i gruppe A.
Bemærk: Dette er en matrixformel, der skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Forklaring
LARGE-funktionen er en nem måde at få den nth største værdi i et interval:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
I dette eksempel kan vi bruge STOR funktion til at få en højeste score og derefter bruge scoren som en "nøgle" til at hente det tilknyttede navn med INDEX og MATCH. Bemærk, at vi samler værdierne for n fra området F5: F7 for at få 1., 2. og 3. højeste score.
Vendingen i dette tilfælde er imidlertid, at vi skal skelne mellem scores i gruppe A og gruppe B. Med andre ord er vi nødt til at anvende kriterier. Vi gør dette med IF-funktionen, som bruges til at "filtrere" værdier, før de evalueres med STOR. For at få den største værdi (dvs. 1. værdi) i interval2, hvor interval 1 = "A", som et generisk eksempel, kan du bruge en formel som denne:
LARGE(IF(range="A",range2),1)
Bemærk: Brug IF på denne måde gør dette til en matrixformel.
Arbejder indefra og ud, det første trin er at få den "1." største værdi i de data, der er knyttet til gruppe A med den STORE funktion:
LARGE(IF(group="A",score),F5)
I dette tilfælde er værdien i F5 er 1, så vi beder om det højeste pointtal i gruppe A. Når funktionen HVIS evalueres, det tester hver værdi i det navngivne område gruppen . Den navngivne område score er fastsat for værdi_hvis_sand. Dette genererer et nyt array, som returneres direkte til LARGE-funktionen:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Bemærk de eneste scores, der overlever filteret, er fra gruppe A. LARGE returnerer derefter den højeste resterende score, 93, direkte til MATCH-funktionen som en opslagsværdi. Vi kan nu forenkle formlen til:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Nu kan vi se, at MATCH-funktionen er konfigureret ved hjælp af det samme filtrerede array, som vi så ovenfor. IF-funktionen filtrerer igen uønskede værdier ud, og MATCH-delen af formlen løser:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Da 93 vises i 3. position, returnerer MATCH 3 direkte til INDEX-funktionen:
=INDEX(name,3) // Hannah
Endelig returnerer INDEX-funktionen navnet i 3. række, "Hannah".
Med XLOOKUP
XLOOKUP-funktionen kan også bruges til at løse dette problem ved hjælp af samme fremgangsmåde som beskrevet ovenfor:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Som ovenfor er LARGE konfigureret til at arbejde med et array filtreret af IF og returnerer et resultat på 93 til XLOOKUP som opslagsværdien:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Opslagsarray oprettes også ved at bruge IF som et filter på scores fra gruppe A. Med returarrayet angivet som navn (B5: B16). XLOOKUP returnerer "Hannah" som det endelige resultat.
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.
