Všeobecný vzorec
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Zhrnutie
Ak chcete získať pozíciu poslednej zhody (tj. Posledného výskytu) vyhľadávanej hodnoty, môžete použiť maticový vzorec založený na funkciách IF, ROW, INDEX, MATCH a MAX. V zobrazenom príklade je vzorec v H6:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Kde „mená“ je pomenovaný rozsah C4: C11.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Podstata tohto vzorca je, že zostavíme zoznam čísel riadkov pre daný rozsah, ktoré sa zhodujú s hodnotou, a potom pomocou funkcie MAX dostaneme najväčšie číslo riadku, ktoré zodpovedá poslednej zhodnej hodnote. Pomenovaný rozsah „names“ používame iba pre uľahčenie.
Pri práci zvnútra von bude táto časť vzorca generovať relatívnu množinu čísel riadkov:
ROW(names)-MIN(ROW(names))+1
Výsledkom vyššie uvedeného výrazu je pole čísel, ako je tento:
(1;2;3;4;5;6;7;8)
Všimnite si, že dostaneme 8 čísel, čo zodpovedá 8 riadkom v tabuľke. Na tejto stránke nájdete podrobnosti o fungovaní tejto časti vzorca.
Na účely tohto vzorca potrebujeme iba čísla riadkov na zodpovedajúce hodnoty, takže na filtrovanie hodnôt používame funkciu IF:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Výsledkom bude pole, ktoré vyzerá takto:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Toto pole stále obsahuje osem položiek. Zachovali sa však iba čísla riadkov, kde sa hodnota v pomenovanom rozsahu „names“ rovná „amy“ (tj. 1, 4, 7). Všetky ostatné položky v poli sú FALSE, pretože zlyhali v logickom teste vo funkcii IF.
Nakoniec funkcia IF doručí toto pole do funkcie MAX. MAX vráti najvyššiu hodnotu v poli, číslo 7, čo zodpovedá číslu posledného riadku, kde je názov „amy“. Keď poznáme posledné zodpovedajúce číslo riadku, môžeme pomocou INDEXU načítať hodnotu na tejto pozícii.
Predposledný atď.
Ak sa chcete dostať z druhej na poslednú pozíciu, z tretej na poslednú atď., Môžete prepnúť z funkcie MIN do funkcie LARGE takto:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
kde k predstavuje „n-té najväčšie“. Napríklad na získanie predposledného zápasu vo vyššie uvedenom príklade môžete použiť:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Rovnako ako predtým ide o vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
