Všeobecný vzorec
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Zhrnutie
Ak chcete vyhľadať pozíciu (riadok) poslednej revízie súboru v tabuľke, môžete použiť vzorec založený na niekoľkých funkciách programu Excel: MAX, IF, ISERROR, ROW a INDEX.
V zobrazenom príklade je vzorec v bunke H6:
(= MAX (AK (ISERROR (SEARCH (H5 & "*", súbory)), 0, RIADOK (súbory) -ROW (INDEX (súbory, 1,1)) + 1)))
kde „súbory“ je pomenovaný rozsah C4: C11.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Kontext
V tomto príklade máme niekoľko verzií súborov uvedených v tabuľke s dátumom a menom používateľa. Upozorňujeme, že názvy súborov sa opakujú, okrem kódu, ktorý je na konci pripojený a predstavuje verziu („CA“, „CB“, „CC“, „CD“ atď.).
Pre daný súbor chceme vyhľadať pozíciu (číslo riadku) pre poslednú revíziu. Toto je zložitý problém, pretože kódy verzií na konci názvov súborov sťažujú zhodu s názvom súboru. V predvolenom nastavení vzorce zápasu Excel tiež vrátia prvý zápas, nie posledný zápas, takže túto výzvu musíme obísť pomocou niektorých zložitých techník.
Vysvetlenie
V jadre tohto vzorca vytvoríme zoznam čísel riadkov pre daný súbor. Potom pomocou funkcie MAX dostaneme najväčšie číslo riadku, ktoré zodpovedá poslednej revízii (poslednému výskytu) daného súboru.
Na vyhľadanie všetkých výskytov daného súboru používame funkciu SEARCH nakonfigurovanú pomocou zástupného znaku asterisk (*) tak, aby zodpovedal názvu súboru, pričom ignorujeme kódy verzií. Ak nenájdete text, funkcia SEARCH spôsobí chybu HODNOTY, a preto zabalíme vyhľadávanie do ISERRORU:
ISERROR(SEARCH(H5&"*",files))
Výsledkom je pole TRUE a FALSE hodnôt, ako je táto:
(NEPRAVDA; PRAVDA; NEPRAVDA; NEPRAVDA; PRAVDA; PRAVDA; NEPRAVDA; PRAVDA)
Je to mätúce, ale TRUE predstavuje chybu (text sa nenašiel) a FALSE predstavuje zhodu. Tento výsledok poľa sa prenesie do funkcie IF ako logický test. Pre hodnotu, ak má hodnotu TRUE, používame nulu a pre hodnotu, ak je to pravda, dodávame tento kód, ktorý generuje relatívne čísla riadkov pre rozsah, s ktorým pracujeme:
ROW(files)-ROW(INDEX(files,1,1))+1)
Funkcia IF potom vráti pole hodnôt, ako je táto:
(1; 0; 3; 4; 0; 0; 7; 0)
Všetky čísla okrem nuly predstavujú zhody pre „názov súboru1“ - tj. Číslo riadku v pomenovanom rozsahu „súbory“, kde sa zobrazuje „názov súboru1“.
Nakoniec použijeme funkciu MAX na získanie maximálnej hodnoty v tomto poli, ktorá je v tomto príklade 7.
Použite INDEX s týmto číslom riadku na načítanie informácií týkajúcich sa poslednej revízie (tj. Celého názvu súboru, dátumu, používateľa atď.).
Bez pomenovaného rozsahu
Pomenované rozsahy umožňujú rýchle a ľahké nastavenie zložitejšieho vzorca, pretože nemusíte zadávať adresy buniek ručne. V tomto prípade však používame funkciu extra (INDEX) na získanie prvej bunky pomenovaného rozsahu „súbory“, čo to trochu komplikuje. Bez pomenovaného rozsahu vzorec vyzerá takto:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
