Obecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
souhrn
Chcete-li spočítat počet konkrétních slov nebo klíčových slov, která se v dané buňce objeví, můžete použít vzorec založený na funkcích SEARCH, ISNUMBER a SUMPRODUCT. V zobrazeném příkladu je vzorec v C5:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
kde „klíčová slova“ je pojmenovaný rozsah E5: E9.
Vysvětlení
Poznámka: Pokud se klíčové slovo v dané buňce objeví více než jednou, započítá se pouze jednou. Jinými slovy, vzorec počítá pouze instance různých klíčových slov.
Jádrem tohoto vzorce je přístup ISNUMBER + SEARCH k hledání textu v buňce, který je zde vysvětlen podrobněji. V tomto případě hledáme v každé buňce všechna slova v pojmenovaném rozsahu „klíčová slova“ (E5: E9). Děláme to tak, že předáme rozsah do SEARCH jako argument find_text. Protože jsme předali řadu 5 položek:
("green";"orange";"white";"blue";"pink")
ve výsledku získáme pole 5 položek:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Čísla odpovídají shodám a #HODNOTA! chyba znamená, že nebyla nalezena žádná shoda. V tomto případě, protože nás nezajímá, kde byl text nalezen v buňce, použijeme ISNUMBER k převodu pole na TRUE a FALSE hodnoty:
(FALSE;FALSE;TRUE;FALSE;TRUE)
A dvojitý zápor (-), který je změní na 1 s a nuly:
(0;0;1;0;1)
Funkce SUMPRODUCT pak jednoduše vrátí součet pole, v tomto případě 2.
Zpracování prázdných klíčových slov
Pokud rozsah klíčových slov obsahuje prázdné buňky, vzorec nebude fungovat správně, protože funkce SEARCH vrací nulu při hledání prázdného řetězce (""). Chcete-li filtrovat prázdné buňky v rozsahu klíčových slov, můžete použít následující variantu:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Poznámka: tato verze je maticový vzorec a musí být zadána pomocí kláves Control + Shift + Enter.
