Obecný vzorec
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
souhrn
Chcete-li spočítat, kolikrát se konkrétní slovo (nebo jakýkoli podřetězec) objeví v rozsahu buněk, můžete použít vzorec založený na funkcích SUBSTITUTE, LEN a SUMPRODUCT. V zobrazeném příkladu je vzorec v C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Poznámka: Vzorec na této stránce počítá látky v rozsahu. Například pokud buňka obsahuje dvě instance slova, přispěje 2 k celkovému počtu. Pokud chcete pouze spočítat buňky, které obsahují konkrétní slovo, podívejte se na tento jednoduchý vzorec založený na funkci COUNTIF.
Vysvětlení
V obecné verzi vzorce představuje rng rozsah ke kontrole a txt je slovo nebo podřetězec, který se má počítat.
V zobrazeném příkladu je rozsah pro kontrolu B5: B8 a C2 obsahuje text (slovo nebo podřetězec), který se má počítat.
Pro každou buňku v rozsahu SUBSTITUTE odstraní podřetězec z původního textu a LEN vypočítá délku textu bez podřetězce. Toto číslo se poté odečte od délky původního textu. Výsledkem je počet znaků, které byly odstraněny SUBSTITUTE.
Poté je počet odstraněných znaků vydělen délkou podřetězce. Pokud má tedy podřetězec nebo slovo 5 znaků a po odebrání z původního textu chybí 10 znaků, víme, že se podřetězec / slovo v původním textu objevil dvakrát.
Protože výše uvedený výpočet je zabalen do funkce SUMPRODUCT, výsledkem je pole, které obsahuje číslo pro každou buňku v rozsahu. Tato čísla představují počet výskytů dílčího řetězce v každé buňce. V tomto příkladu pole vypadá takto: (1; 1; 0; 1)
Nakonec SUMPRODUCT sečte všechny položky v poli, aby získal celkový výskyt podřetězce v rozsahu buněk.
Ignorování případu
SUBSTITUTE je funkce citlivá na velká a malá písmena, takže při provádění náhrady bude odpovídat velikosti písmen. Pokud potřebujete spočítat výskyt velkých i malých písmen slova nebo podřetězce, použijte funkci HORNÍ uvnitř SUBSTITUTE k převedení textu na velká písmena před spuštěním substituce:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
