Formula generică
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
rezumat
Pentru a căuta cea mai recentă versiune de fișier dintr-o listă, puteți utiliza o formulă bazată pe funcția CĂUTARE împreună cu funcțiile ISNUMBER și FIND. În exemplul prezentat, formula din celula G7 este:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
unde „fișiere” este intervalul numit B5: B11.
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 se repetă cu un contor la sfârșit ca număr de revizuire - 001, 002, 003 etc.
Având un nume de fișier, dorim să preluăm numele ultimei sau ultimei revizuiri. Există două provocări:
- Provocarea este că codurile de versiune de la sfârșitul numelor de fișiere fac mai dificilă potrivirea cu numele fișierului.
- În mod implicit, formulele de potrivire Excel vor returna primul meci, nu ultimul meci.
Pentru a depăși aceste provocări, trebuie să folosim câteva tehnici dificile.
Explicaţie
Această formulă folosește funcția CĂUTARE pentru a găsi și a prelua ultimul nume de fișier care se potrivește. Valoarea căutării este 2, iar căutarea_vector este creată cu aceasta:
1/(ISNUMBER(FIND(G6,files)))
În interiorul acestui fragment, funcția FIND caută valoarea în G6 în intervalul numit „fișiere” (B5: B11). Rezultatul este o matrice ca aceasta:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Aici, numărul 1 reprezintă o potrivire, iar eroarea #VALUE reprezintă un nume de fișier care nu se potrivește. Această matrice intră în funcția ISNUMBER și apare astfel:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Valorile de eroare sunt acum FALSE, iar numărul 1 este acum ADEVĂRAT. Acest lucru depășește provocarea nr. 1, acum avem o matrice care arată clar care fișiere din listă conțin numele fișierului de interes.
Apoi, matricea este utilizată ca numitor cu 1 ca numărător. Rezultatul arată astfel:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
care intră în căutare ca vectorul de căutare. Aceasta este o soluție dificilă pentru provocarea nr. 2. Funcția LOOKUP funcționează numai în modul de potrivire aproximativ și ignoră automat valorile erorilor. Aceasta înseamnă că 2 ca valoare de căutare, VLOOKUP va încerca să găsească 2, să nu reușească și să revină la numărul precedent (în acest caz se potrivește cu ultimul 1 în poziția 7). În cele din urmă, LOOKUP folosește 7 ca un index pentru a recupera al 7-lea fișier din lista de fișiere.
Gestionarea căutărilor necompletate
În mod ciudat, funcția FIND returnează 1 dacă valoarea de căutare este un șir gol (""). Pentru a vă proteja împotriva unei potriviri false, puteți înfășura formula în IF și puteți testa o căutare goală:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
