Splošna formula
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Povzetek
Če želite poiskati položaj (vrstico) zadnje revizije datoteke v tabeli, lahko uporabite formulo, ki temelji na več Excelovih funkcijah: MAX, IF, ISERROR, ROW in INDEX.
V prikazanem primeru je formula v celici H6:
(= MAX (IF (ISERROR (ISKANJE (H5 & "*", datoteke)), 0, ROW (datoteke) -ROW (INDEX (datoteke, 1,1)) + 1)))
kjer je "datoteke" imenovani obseg C4: C11.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Kontekst
V tem primeru imamo v tabeli številne različice datotek z datumom in uporabniškim imenom. Upoštevajte, da se imena datotek ponavljajo, razen kode, ki je dodana na koncu, da predstavlja različico ("CA", "CB", "CC", "CD itd.).
Za dano datoteko želimo poiskati položaj (številko vrstice) za zadnjo revizijo. To je težavna težava, ker kode različic na koncu imen datotek otežujejo ujemanje z imenom datoteke. Prav tako privzeto formule ujemanja Excel vrnejo prvo ujemanje in ne zadnje ujemanje, zato moramo ta izziv rešiti z nekaj zapletenimi tehnikami.
Pojasnilo
V jedru te formule zgradimo seznam številk vrstic za dano datoteko. Nato s funkcijo MAX dobimo največjo številko vrstice, ki ustreza zadnji reviziji (zadnji pojav) te datoteke.
Da bi našli vse pojavitve dane datoteke, uporabimo funkcijo SEARCH, nastavljeno z zvezdico (*), da se ujema z imenom datoteke, pri čemer ne upoštevamo kod različic. SEARCH bo vrnil napako VALUE, ko besedila ni mogoče najti, zato zavijemo iskanje v ISERROR:
ISERROR(SEARCH(H5&"*",files))
Rezultat je niz TRUE in FALSE vrednosti, kot je ta:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
Zmedeno je, vendar TRUE predstavlja napako (besedilo ni najdeno), FALSE pa ujemanje. Ta rezultat matrike se kot logični preizkus posreduje v funkcijo IF. Za vrednost, če je TRUE, uporabimo nič, za vrednost, če je true, pa dobimo to kodo, ki generira relativne številke vrstic za obseg, s katerim delamo:
ROW(files)-ROW(INDEX(files,1,1))+1)
Nato funkcija IF vrne niz vrednosti, kot je ta:
(1; 0; 3; 4; 0; 0; 7; 0)
Vse številke razen nič predstavljajo ujemanja za "filename1" - tj. Številko vrstice znotraj imenovanega obsega "files", kjer se prikaže "filename1".
Na koncu uporabimo funkcijo MAX, da dobimo največjo vrednost v tej matriki, ki je v tem primeru 7.
Uporabite INDEX s to številko vrstice za pridobivanje informacij, povezanih z zadnjo revizijo (tj. Polno ime datoteke, datum, uporabnik itd.).
Brez imenovanega obsega
Imenovani obsegi omogočajo hitro in enostavno nastavitev bolj zapletene formule, saj vam naslovov celic ni treba vnašati ročno. Vendar v tem primeru uporabljamo dodatno funkcijo (INDEX), da dobimo prvo celico imenovanega obsega "datotek", kar nekoliko zaplete. Brez imenovanega obsega je formula videti tako:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
