Formula generică
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
rezumat
Pentru a obține poziția ultimei potriviri (adică a ultimei apariții) a unei valori de căutare, puteți utiliza o formulă matrice bazată pe funcțiile IF, ROW, INDEX, MATCH și MAX. În exemplul prezentat, formula din H6 este:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Unde „nume” este intervalul numit C4: C11.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
Esențialul acestei formule este că construim o listă de numere de rând pentru un interval dat, care se potrivește cu o valoare, și apoi folosim funcția MAX pentru a obține cel mai mare număr de rând, care corespunde ultimei valori potrivite. Folosim gama denumită „nume” numai pentru comoditate.
Lucrând din interior spre exterior, această parte a formulei va genera un set relativ de numere de rând:
ROW(names)-MIN(ROW(names))+1
Rezultatul expresiei de mai sus este o serie de numere ca aceasta:
(1;2;3;4;5;6;7;8)
Observați că obținem 8 numere, care corespund celor 8 rânduri din tabel. Consultați această pagină pentru detalii despre modul în care funcționează această parte a formulei.
În scopul acestei formule, dorim doar numere de rând pentru potrivirea valorilor, așa că folosim funcția IF pentru a filtra valorile astfel:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Acest lucru are ca rezultat o matrice care arată astfel:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Rețineți că această matrice conține încă opt elemente. Cu toate acestea, au supraviețuit doar numerele de rând în care valoarea din intervalul numit „nume” este egală cu „amy” (adică 1, 4, 7). Toate celelalte elemente din matrice sunt FALSE, deoarece au eșuat testul logic în funcția IF.
În cele din urmă, funcția IF oferă această matrice funcției MAX. MAX returnează cea mai mare valoare din matrice, numărul 7, care corespunde ultimului număr de rând unde numele este „amy”. Odată ce cunoaștem ultimul număr de rând care se potrivește, putem folosi INDEX pentru a extrage o valoare în acea poziție.
De la al doilea la ultim, etc.
Pentru a obține a doua până la ultima poziție, a treia la ultima, etc. puteți trece de la funcția MIN la funcția LARGE astfel:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
unde k reprezintă „al nouălea cel mai mare”. De exemplu, pentru a obține al doilea ultim meci în exemplul de mai sus, puteți utiliza:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
La fel ca înainte, aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
