Všeobecný vzorec
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Zhrnutie
Ak chcete spočítať, koľkokrát sa konkrétne slovo (alebo ľubovoľný podreťazec) objaví v rozsahu buniek, môžete použiť vzorec založený na funkciách SUBSTITUTE, LEN a SUMPRODUCT. V zobrazenom príklade je vzorec v C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Poznámka: Vzorec na tejto stránke počíta počet slov v rozsahu. Ak napríklad bunka obsahuje dve inštancie slova, prispeje k celkovému počtu 2. Ak chcete iba spočítať bunky, ktoré obsahujú konkrétne slovo, pozrite si tento jednoduchý vzorec založený na funkcii COUNTIF.
Vysvetlenie
V generickej verzii vzorca predstavuje rng rozsah, ktorý sa má skontrolovať, a txt je slovo alebo podreťazec, ktorý sa má počítať.
V zobrazenom príklade je rozsah na kontrolu B5: B8 a C2 obsahuje text (slovo alebo podreťazec), ktorý sa má spočítať.
Pre každú bunku v rozsahu SUBSTITUTE odstráni podreťazec z pôvodného textu a LEN vypočíta dĺžku textu bez podreťazca. Toto číslo sa potom odčíta od dĺžky pôvodného textu. Výsledkom je počet znakov, ktoré boli odstránené používateľom SUBSTITUTE.
Potom je počet odstránených znakov vydelený dĺžkou podreťazca. Takže ak má podreťazec alebo slovo 5 znakov a po odstránení z pôvodného textu chýba 10 znakov, vieme, že podreťazec / slovo sa v pôvodnom texte objavilo dvakrát.
Pretože vyššie uvedený výpočet je zabalený do funkcie SUMPRODUCT, výsledkom je pole, ktoré obsahuje číslo pre každú bunku v rozsahu. Tieto čísla predstavujú počet výskytov podreťazca v každej bunke. V tomto príklade pole vyzerá takto: (1; 1; 0; 1)
Nakoniec SUMPRODUCT sčíta všetky položky v poli, aby získal celkový výskyt podreťazca v rozsahu buniek.
Ignorovanie prípadu
SUBSTITUTE rozlišuje veľké a malé písmená, takže pri uskutočňovaní zámeny sa bude zhodovať s veľkými a malými písmenami. Ak potrebujete počítať veľké aj malé výskyty slova alebo podreťazca, pomocou funkcie HORNÉ vo vnútri SUBSTITUTE môžete pred spustením zámeny previesť text na veľké písmená:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
