Generička formula
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Sažetak
Da biste pronašli položaj (redak) posljednje revizije datoteke u tablici, možete upotrijebiti formulu koja se temelji na nekoliko Excel funkcija: MAX, IF, ISERROR, ROW i INDEX.
U prikazanom primjeru formula u stanici H6 je:
(= MAX (AKO (ISERROR (PRETRAGA (H5 & "*", datoteke)), 0, RED (datoteke) -RED (INDEKS (datoteke, 1,1)) + 1)))
gdje je "datoteke" imenovani raspon C4: C11.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Kontekst
U ovom primjeru imamo niz verzija datoteka navedenih u tablici s datumom i korisničkim imenom. Imajte na umu da se imena datoteka ponavljaju, osim koda dodanog na kraju da predstavlja verziju ("CA", "CB", "CC", "CD" itd.).
Za datu datoteku želimo pronaći položaj (broj retka) za zadnju reviziju. Ovo je lukav problem, jer kodovi verzija na kraju naziva datoteka otežavaju podudaranje s nazivom datoteke. Također, prema zadanim postavkama, Excelove formule podudaranja vraćaju prvo podudaranje, a ne posljednje podudaranje, pa taj izazov moramo zaobići nekim nezgodnim tehnikama.
Obrazloženje
U srži ove formule gradimo popis brojeva redaka za datu datoteku. Zatim koristimo funkciju MAX da bismo dobili najveći broj retka, koji odgovara zadnjoj reviziji (zadnjem pojavljivanju) te datoteke.
Da bismo pronašli sve pojave dane datoteke, koristimo funkciju SEARCH, konfiguriranu sa zamjenskim znakom zvjezdice (*) da odgovara nazivu datoteke, zanemarujući kodove verzije. SEARCH će baciti vrijednost VALUE kada tekst nije pronađen, pa pretragu premotavamo u ISERROR:
ISERROR(SEARCH(H5&"*",files))
Rezultat je niz TRUE i FALSE vrijednosti poput ove:
(NETOČNO; TAČNO; NETOČNO; NETOČNO; ISTINO; ISTINO; NETOČNO; ISTINITO)
Zbunjujuće je, ali TRUE predstavlja pogrešku (tekst nije pronađen), a FALSE predstavlja podudaranje. Ovaj rezultat niza unosi se u funkciju IF kao logički test. Za vrijednost if TRUE koristimo nulu, a za value if true isporučujemo ovaj kod koji generira relativne brojeve redaka za raspon s kojim radimo:
ROW(files)-ROW(INDEX(files,1,1))+1)
Funkcija IF tada vraća niz vrijednosti poput ove:
(1; 0; 3; 4; 0; 0; 7; 0)
Svi brojevi osim nule predstavljaju podudaranja za "filename1" - tj. Broj retka unutar imenovanog raspona "files" gdje se pojavljuje "filename1".
Konačno, koristimo funkciju MAX da bismo dobili maksimalnu vrijednost u ovom polju, koja je 7 u ovom primjeru.
Upotrijebite INDEX s ovim brojem retka za dohvaćanje podataka koji se odnose na zadnju reviziju (tj. Puno ime datoteke, datum, korisnik itd.).
Bez imenovanog dometa
Imenovani rasponi omogućuju brzo i jednostavno postavljanje složenijih formula, jer adrese ćelija ne morate unositi ručno. Međutim, u ovom slučaju koristimo dodatnu funkciju (INDEX) za dobivanje prve ćelije imenovanog raspona "datoteke", što malo komplicira. Bez imenovanog raspona, formula izgleda ovako:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
