Formula generică
=INDEX(names,MATCH(LARGE(values,F5),values,0))
rezumat
Pentru a obține numele celei de-a n-a cea mai mare valoare, puteți utiliza INDEX și MATCH cu funcția LARGE. În exemplul prezentat, formula din celula H5 este:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
unde numele (B5: B16) și scorul (D5: D16) sunt denumite intervale.
Explicaţie
Pe scurt, această formulă folosește funcția LARGE pentru a găsi cea de-a n-a cea mai mare valoare dintr-un set de date. Odată ce avem această valoare, o conectăm la o formulă standard INDEX și MATCH pentru a prelua numele asociat. Cu alte cuvinte, folosim a n-a cea mai mare valoare ca o „cheie” pentru a prelua informațiile asociate.
Funcția LARGE este o modalitate simplă de a obține a n-a cea mai mare valoare dintr-un interval. Pur și simplu furnizați un interval pentru primul argument (matrice) și o valoare pentru n ca al doilea argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Lucrând din interior spre exterior, primul pas este să obțineți „prima” cea mai mare valoare din date cu funcția LARGE:
LARGE(score,F5) // returns 93
În acest caz, valoarea în F5 este 1, deci solicităm primul scor cel mai mare (adică scorul maxim), care este 93. Acum putem simplifica formula pentru:
=INDEX(name,MATCH(93,score,0))
În interiorul funcției INDEX, funcția MATCH este configurată pentru a localiza poziția 93 în scorul de interval numit (D5: D16):
MATCH(93,score,0) // returns 3
Deoarece 93 apare în al treilea rând, MATCH returnează 3 direct la INDEX ca număr de rând, cu numele ca matrice:
=INDEX(name,3) // Hannah
În cele din urmă, funcția INDEX returnează numele din al treilea rând, „Hannah”.
Observați că preluăm valorile pentru n din intervalul F5: F7, pentru a obține scorurile 1, 2 și 3 cele mai mari pe măsură ce formula este copiată.
Recuperați grupul
Aceeași formulă de bază va funcționa pentru a prelua orice informație asociată. Pentru a obține grupul pentru cele mai mari valori, puteți schimba pur și simplu matricea furnizată la INDEX cu grupul de intervale numit :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Cu valoarea 1 în F5, LARGE va obține cel mai mare scor, iar formula va returna „A”.
Notă: cu Excel 365, puteți utiliza funcția FILTER pentru a afișa dinamic rezultatele de sus sau de jos.
Cu XLOOKUP
Funcția XLOOKUP poate fi, de asemenea, utilizată pentru a returna numele celei de-a n-a cea mai mare valoare astfel:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE returnează cea mai mare valoare, 93, direct la XLOOKUP ca valoare de căutare:
=XLOOKUP(93,score,name) // Hannah
Cu scorul de interval numit (D5: D16) ca matrice de căutare și numele (B5: B16) ca matrice de returnare, XLOOKUP returnează „Hannah” ca înainte.
Manipularea cravatelor
Duplicarea valorilor în datele numerice va crea o „legătură”. Dacă apare o egalitate în valorile clasate, de exemplu, dacă prima și a doua cea mai mare valoare sunt aceleași, LARGE va returna aceeași valoare pentru fiecare. Când această valoare este trecută în funcția MATCH, MATCH va returna poziția primului meci, așa că veți vedea același (prenume) nume returnat.
Dacă există posibilitatea legăturilor, poate doriți să implementați un fel de strategie de rupere a egalității. O abordare este de a crea o nouă coloană helper de valori care au fost ajustate pentru a rupe legăturile. Apoi utilizați valorile coloanei de asistență pentru a clasifica și prelua informații. Acest lucru face ca logica utilizată pentru a rupe legăturile să fie clară și explicită.
O altă abordare este de a rupe legăturile numai în funcție de poziție (adică prima egalitate „câștigă”). Iată o formulă care adoptă această abordare:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Aici, folosim MATCH pentru a găsi numărul 1 și construim o matrice de căutare folosind logica booleană care (1) compară toate scorurile cu valoarea returnată de LARGE:
score=LARGE(score,F5)
și (2) folosește o verificare a intervalului de expansiune dacă numele este deja în lista clasificată:
COUNTIF(H$4:H4,name)=0
Când un nume este deja în listă, acesta este „anulat” de logică, iar următoarea valoare (duplicat) este potrivită. Observați că intervalul de extindere începe pe rândul anterior, pentru a evita o referință circulară.
Această abordare funcționează în acest exemplu, deoarece nu există nume duplicate în coloana cu nume. Cu toate acestea, dacă numele duplicate apar în valori clasate, abordarea trebuie ajustată. Cea mai ușoară soluție este să vă asigurați că numele sunt unice.
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.
