Formula generică
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
rezumat
Pentru a obține numele celei de-a n-a cea mai mare valoare cu criterii, puteți utiliza INDEX și MATCH, funcția LARGE și un filtru creat cu funcția IF. În exemplul prezentat, formula din celula G5, copiată, este:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
unde numele (B5: B16), grupul (C5: C16) și scorul (D5: D16) sunt denumite intervale. Formula returnează numele asociat cu prima, a doua și a treia cea mai mare valoare din grupa A.
Notă: Aceasta este o formulă matrice care trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Explicaţie
Funcția LARGE este o modalitate ușoară de a obține a n-a cea mai mare valoare dintr-un interval:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
În acest exemplu, putem folosi funcția LARGE pentru a obține cel mai mare scor, apoi putem folosi scorul ca o „cheie” pentru a recupera numele asociat cu INDEX și MATCH. Observați că preluăm valorile pentru n din intervalul F5: F7, pentru a obține scorurile 1, 2 și 3 cele mai mari.
Cu toate acestea, răsucirea în acest caz este că trebuie să distingem între scorurile din grupa A și grupa B. Cu alte cuvinte, trebuie să aplicăm criterii. Facem acest lucru cu funcția IF, care este utilizată pentru a „filtra” valorile înainte ca acestea să fie evaluate cu LARGE. Ca exemplu generic, pentru a obține cea mai mare valoare (adică prima valoare) în intervalul 2, unde intervalul 1 = "A", puteți utiliza o formulă de genul acesta:
LARGE(IF(range="A",range2),1)
Notă: utilizarea IF în acest fel face din aceasta o formulă matrice.
Lucrând din interior spre exterior, primul pas este să obțineți "prima" cea mai mare valoare din datele asociate cu grupul A cu funcția LARGE:
LARGE(IF(group="A",score),F5)
În acest caz, valoarea din F5 este 1, deci solicităm scorul maxim în grupa A. Când funcția IF este evaluată, testează fiecare valoare din grupul de intervale numit . Intervalul numit punctajul este prevăzut pentru Value_if_true. Aceasta generează o nouă matrice, care este returnată direct la funcția LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Observați că singurele scoruri care supraviețuiesc filtrului provin din grupa A. LARGE apoi returnează cel mai mare scor rămas, 93, direct la funcția MATCH ca valoare de căutare. Acum putem simplifica formula pentru:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Acum putem vedea că funcția MATCH este configurată folosind aceeași matrice filtrată pe care am văzut-o mai sus. Funcția IF filtrează din nou valorile nedorite, iar porțiunea MATCH a formulei se rezolvă la:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Deoarece 93 apare în poziția a 3-a, MATCH revine 3 direct la funcția INDEX:
=INDEX(name,3) // Hannah
În cele din urmă, funcția INDEX returnează numele din al treilea rând, „Hannah”.
Cu XLOOKUP
Funcția XLOOKUP poate fi, de asemenea, utilizată pentru a rezolva această problemă, utilizând aceeași abordare explicată mai sus:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Ca mai sus, LARGE este configurat să funcționeze cu o matrice filtrată de IF și returnează un rezultat de 93 la XLOOKUP ca valoare de căutare:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Matricea de căutare este, de asemenea, creată utilizând IF ca filtru pentru scorurile din grupa A. Cu matricea returnată furnizată ca nume (B5: B16). XLOOKUP returnează „Hannah” ca rezultat final.
Note
- Pentru a obține numele celei de-a n-a valori cu criterii (adică limitați rezultatele la grupa A sau B) va trebui să extindeți formula pentru a utiliza logică suplimentară.
- În Excel 365, funcția FILTER este o modalitate mai bună de a afișa dinamic rezultatele de sus sau de jos. Această abordare va gestiona automat cravatele.
