Formula generică
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
rezumat
Pentru a găsi poziția (rândul) ultimei revizuiri de fișiere într-un tabel, puteți utiliza o formulă bazată pe mai multe funcții Excel: MAX, IF, ISERROR, ROW și INDEX.
În exemplul prezentat, formula din celula H6 este:
(= MAX (IF (ISERROR (SEARCH (H5 & "*", files)), 0, ROW (files) -ROW (INDEX (files, 1,1)) + 1)))
unde „fișiere” este intervalul numit C4: C11.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Context
În acest exemplu, avem o serie de versiuni de fișiere listate într-un tabel cu o dată și un nume de utilizator. Rețineți că numele fișierelor sunt repetate, cu excepția codului atașat la final pentru a reprezenta versiunea („CA”, „CB”, „CC”, „CD” etc.).
Pentru un fișier dat, dorim să localizăm poziția (numărul rândului) pentru ultima revizuire. Aceasta este o problemă dificilă, deoarece codurile de versiune de la sfârșitul numelor fișierelor îngreunează asocierea cu numele fișierului. De asemenea, în mod implicit, formulele de potrivire Excel vor returna primul meci, nu ultimul meci, așa că trebuie să rezolvăm acea provocare cu câteva tehnici dificile.
Explicaţie
La baza acestei formule, construim o listă de numere de rând pentru un fișier dat. Apoi folosim funcția MAX pentru a obține cel mai mare număr de rând, care corespunde ultimei revizuiri (ultima apariție) a acelui fișier.
Pentru a găsi toate aparițiile unui fișier dat, folosim funcția CĂUTARE, configurată cu asteriscul (*) wildcard pentru a se potrivi cu numele fișierului, ignorând codurile de versiune. SEARCH va afișa o eroare VALUE atunci când textul nu este găsit, așa că încheiem căutarea în ISERROR:
ISERROR(SEARCH(H5&"*",files))
Acest lucru are ca rezultat o serie de valori ADEVĂRATE și FALSE ca aceasta:
(FALSE; ADEVĂRATE; FALSE; FALSE; ADEVARE; ADEVĂRATE; FALSE; ADEVĂRATE)
Este confuz, dar TRUE reprezintă o eroare (textul nu a fost găsit), iar FALSE reprezintă o potrivire. Acest rezultat al matricei este introdus în funcția IF ca test logic. Pentru valoare dacă este ADEVĂRAT, folosim zero, iar pentru valoare dacă este adevărat, furnizăm acest cod, care generează numere de rând relative pentru intervalul cu care lucrăm:
ROW(files)-ROW(INDEX(files,1,1))+1)
Funcția IF returnează apoi o serie de valori ca aceasta:
(1; 0; 3; 4; 0; 0; 7; 0)
Toate numerele, cu excepția zero, reprezintă potrivirile pentru „fișierul1” - adică numărul rândului din intervalul numit „fișiere” unde apare „fișierul1”.
În cele din urmă, folosim funcția MAX pentru a obține valoarea maximă în această matrice, care este 7 în acest exemplu.
Utilizați INDEX cu acest număr de rând pentru a prelua informații legate de ultima revizuire (adică numele complet al fișierului, data, utilizatorul etc.).
Fără interval numit
Intervalele denumite facilitează configurarea rapidă și ușoară a unei formule mai complexe, deoarece nu trebuie să introduceți manual adresele celulei. Cu toate acestea, în acest caz, folosim o funcție suplimentară (INDEX) pentru a obține prima celulă din intervalul numit „fișiere”, ceea ce complică puțin lucrurile. Fără intervalul numit, formula arată astfel:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
