Generisk formel
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Sammanfattning
För att få namnet på det nionde största värdet med kriterier kan du använda INDEX och MATCH, den STORA funktionen och ett filter som skapats med IF-funktionen. I exemplet som visas är formeln i cell G5, kopierad ned:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
där namn (B5: B16), grupp (C5: C16) och poäng (D5: D16) heter områden. Formeln returnerar namnet associerat med de 1: a, 2: a och 3: e högsta värdena i grupp A.
Obs: Detta är en matrisformel som måste anges med kontroll + shift + enter, utom i Excel 365.
Förklaring
LARGE-funktionen är ett enkelt sätt att få det n: e största värdet i ett intervall:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
I det här exemplet kan vi använda LARGE-funktionen för att få högsta poäng och sedan använda poängen som en "tangent" för att hämta det associerade namnet med INDEX och MATCH. Observera att vi plockar upp värdena för n från intervallet F5: F7 för att få 1, 2 och 3 högsta poäng.
Vridningen i detta fall är dock att vi måste skilja mellan poäng i grupp A och grupp B. Med andra ord måste vi tillämpa kriterier. Vi gör detta med IF-funktionen, som används för att "filtrera" värden innan de utvärderas med STORA. För att få det största värdet (dvs. 1: a värdet) i intervall 2 där intervall 1 = "A" kan du som ett generiskt exempel använda en sådan formel:
LARGE(IF(range="A",range2),1)
Obs: att använda IF på detta sätt gör detta till en matrisformel.
Arbetar inifrån och ut, det första steget är att få det "första" största värdet i de data som är associerade med grupp A med den STORA funktionen:
LARGE(IF(group="A",score),F5)
I detta fall, är värdet i F5 1, så vi ber om det bästa resultatet i grupp A. Om IF-funktionen utvärderas, den testar varje värde i det namngivna området gruppen . Det namngivna området poäng ges för värde_om_sant. Detta genererar en ny matris som returneras direkt till LARGE-funktionen:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Lägg märke till att de enda poängen som överlever filtret är från grupp A. LARGE returnerar sedan den högsta återstående poängen, 93, direkt till MATCH-funktionen som ett uppslagsvärde. Vi kan nu förenkla formeln till:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Nu kan vi se att MATCH-funktionen är konfigurerad med samma filtrerade array som vi såg ovan. IF-funktionen filtrerar bort oönskade värden igen, och MATCH-delen av formeln löser sig att:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Eftersom 93 visas i den tredje positionen, returnerar MATCH 3 direkt till INDEX-funktionen:
=INDEX(name,3) // Hannah
Slutligen returnerar INDEX-funktionen namnet i den tredje raden, "Hannah".
Med XLOOKUP
XLOOKUP-funktionen kan också användas för att lösa detta problem med samma metod som förklarats ovan:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Som ovan är LARGE konfigurerad för att fungera med en array filtrerad av IF och returnerar ett resultat av 93 till XLOOKUP som uppslagsvärde:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Uppslagsmatrisen skapas också genom att använda IF som ett filter för poäng från grupp A. Med returmatrisen som namn (B5: B16). XLOOKUP returnerar "Hannah" som slutresultat.
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.
