Excel vzorec: Získejte n-tou shodu -

Obsah

Obecný vzorec

=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)

souhrn

Chcete-li získat pozici n-té shody (například 2. odpovídající hodnota, 3. odpovídající hodnota atd.), Můžete použít vzorec založený na funkci SMALL. V zobrazeném příkladu je vzorec v G5:

=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)

Tento vzorec vrací pozici druhého výskytu „červené“ v seznamu.

Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.

Vysvětlení

Tento vzorec používá pojmenovaný rozsah „list“, což je rozsah B5: B11.

Jádrem tohoto vzorce je funkce MALÉ, která jednoduše vrátí n-tou nejmenší hodnotu v seznamu hodnot, které odpovídají číslům řádků. Čísla řádků byla „filtrována“ příkazem IF, který aplikuje logiku shody. Při práci zevnitř ven IF porovnává všechny hodnoty v pojmenovaném rozsahu „list“ s hodnotou v B5, což vytváří pole takto:

(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)

"Hodnota, pokud je to pravda" je sada relativních čísel řádků vytvořených tímto kódem:

ROW(list)-MIN(ROW(list))+1

Výsledkem je pole jako toto:

(1;2;3;4;5;6;7)

Na této stránce najdete úplné vysvětlení.

S logickým testem, který vrací pole výsledků, funguje funkce IF jako filtr - přežijí pouze čísla řádků, která odpovídají shodě, zbytek vrátí FALSE. Výsledek vrácený IF vypadá takto:

(1;FALSE;FALSE;FALSE;5;FALSE;7)

Čísla 1, 5 a 7 odpovídají umístění „červené“ v seznamu.

Nakonec SMALL vrátí n-tou nejmenší položku v tomto seznamu, ignoruje FALSE hodnoty. V příkladu F5 obsahuje 2, takže SMALL vrátí 2. nejmenší hodnotu: 5.