Obecný vzorec
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
souhrn
Chcete-li najít pozici (řádek) poslední revize souboru v tabulce, můžete použít vzorec založený na několika funkcích aplikace Excel: MAX, IF, ISERROR, ROW a INDEX.
V zobrazeném příkladu je vzorec v buňce H6:
(= MAX (IF (ISERROR (SEARCH (H5 & "*", files)), 0, ROW (files) -ROW (INDEX (files, 1,1)) + 1)))
kde „soubory“ je pojmenovaný rozsah C4: C11.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Kontext
V tomto příkladu máme několik verzí souborů uvedených v tabulce s datem a uživatelským jménem. Názvy souborů se opakují, kromě kódu připojeného na konci, který představuje verzi („CA“, „CB“, „CC“, „CD“ atd.).
U daného souboru chceme vyhledat pozici (číslo řádku) pro poslední revizi. To je složitý problém, protože kódy verzí na konci názvů souborů znesnadňují shodu názvu souboru. Ve výchozím nastavení vrátí Excel vzorce shody také první shodu, nikoli poslední shodu, takže musíme tuto výzvu vyřešit pomocí některých složitých technik.
Vysvětlení
V jádru tohoto vzorce vytvoříme seznam čísel řádků pro daný soubor. Poté použijeme funkci MAX k získání největšího čísla řádku, které odpovídá poslední revizi (poslednímu výskytu) daného souboru.
Abychom našli všechny výskyty daného souboru, použijeme funkci SEARCH nakonfigurovanou pomocí zástupného znaku asterisk (*) tak, aby odpovídal názvu souboru, ignorujeme kódy verzí. SEARCH vyvolá chybu HODNOTY, když není nalezen text, takže zabalíme vyhledávání do ISERROR:
ISERROR(SEARCH(H5&"*",files))
Výsledkem je pole TRUE a FALSE hodnot, jako je tato:
(NEPRAVDA; PRAVDA; NEPRAVDA; NEPRAVDA; PRAVDA; PRAVDA; NEPRAVDA; PRAVDA)
Je to matoucí, ale TRUE představuje chybu (text nebyl nalezen) a FALSE představuje shodu. Tento výsledek pole se přenese do funkce IF jako logický test. Pro hodnotu TRUE použijeme nulu a pro hodnotu TRUE dodáme tento kód, který generuje relativní čísla řádků pro rozsah, se kterým pracujeme:
ROW(files)-ROW(INDEX(files,1,1))+1)
Funkce IF pak vrátí pole hodnot, jako je tato:
(1; 0; 3; 4; 0; 0; 7; 0)
Všechna čísla kromě nuly představují shody pro „název_souboru1“ - tj. Číslo řádku uvnitř pojmenovaného rozsahu „soubory“, kde se objeví „název_souboru1“.
Nakonec použijeme funkci MAX k získání maximální hodnoty v tomto poli, což je v tomto příkladu 7.
Použijte INDEX s tímto číslem řádku k načtení informací souvisejících s poslední revizí (tj. Celé jméno souboru, datum, uživatel atd.).
Bez pojmenovaného rozsahu
Díky pojmenovaným rozsahům je rychlejší a snazší nastavit složitější vzorec, protože nemusíte zadávat adresy buněk ručně. V tomto případě však používáme extra funkci (INDEX) k získání první buňky pojmenovaného rozsahu „soubory“, což věci trochu komplikuje. Bez pojmenovaného rozsahu vypadá vzorec takto:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
