Obecný vzorec
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
souhrn
Chcete-li získat pozici poslední shody (tj. Posledního výskytu) vyhledávací hodnoty, můžete použít maticový vzorec založený na funkcích IF, ROW, INDEX, MATCH a MAX. V zobrazeném příkladu je vzorec v H6:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Kde "jména" je pojmenovaný rozsah C4: C11.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Podstata tohoto vzorce spočívá v tom, že sestavíme seznam čísel řádků pro daný rozsah, který odpovídá hodnotě, a poté pomocí funkce MAX získáme největší číslo řádku, které odpovídá poslední hodnotě shody. Pojmenovaný rozsah „names“ používáme pouze pro větší pohodlí.
Při práci zevnitř ven bude tato část vzorce generovat relativní sadu čísel řádků:
ROW(names)-MIN(ROW(names))+1
Výsledkem výše uvedeného výrazu je pole čísel, jako je tento:
(1;2;3;4;5;6;7;8)
Všimněte si, že dostaneme 8 čísel, což odpovídá 8 řádkům v tabulce. Na této stránce najdete podrobnosti o tom, jak tato část vzorce funguje.
Pro účely tohoto vzorce chceme pouze čísla řádků pro odpovídající hodnoty, takže k filtrování hodnot používáme funkci IF:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Výsledkem je pole, které vypadá takto:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Všimněte si, že toto pole stále obsahuje osm položek. Přežila však pouze čísla řádků, kde se hodnota v pojmenovaném rozsahu „names“ rovná „amy“ (tj. 1, 4, 7). Všechny ostatní položky v poli jsou FALSE, protože selhaly v logickém testu ve funkci IF.
Nakonec funkce IF doručí toto pole do funkce MAX. MAX vrací nejvyšší hodnotu v poli, číslo 7, což odpovídá číslu posledního řádku, kde je název „amy“. Jakmile zjistíme poslední odpovídající číslo řádku, můžeme použít INDEX k načtení hodnoty na dané pozici.
Předposlední atd.
Chcete-li získat druhou až poslední pozici, třetí až poslední, atd., Můžete přepnout z funkce MIN do funkce VELKÉ takto:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
kde k představuje „n-té největší“. Chcete-li například získat předposlední shodu ve výše uvedeném příkladu, můžete použít:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Stejně jako dříve se jedná o maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
