Obecný vzorec
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
souhrn
Pokud potřebujete spočítat, kolikrát se v buňce objeví konkrétní slovo (nebo jakýkoli podřetězec), můžete použít vzorec, který používá SUBSTITUTE a LEN. V obecné formě výše uvedeného vzorce představuje „text“ buňku obsahující text a „slovo“ představuje počítané slovo nebo podřetězec. V příkladu používáme tento vzorec:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Vysvětlení
B4 je buňka, ve které počítáme slova, a C4 obsahuje podřetězec (slovo nebo jakýkoli podřetězec), který počítáte.
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.
Nakonec se počet odstraněných znaků dělí délkou podřetězce. Pokud má tedy podřetězec 5 znaků a po jeho odstranění z původního textu chybí 10 znaků, víme, že se podřetězec v původním textu objevil dvakrát.
Manipulační pouzdro
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:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Protože tento vzorec před provedením náhrady převede podřetězec a text na velká písmena, bude s textem v každém případě fungovat stejně dobře.
Řešení problémů
Počítání slov v Excelu je složité, protože Excel nepodporuje regulární výrazy. Ve výsledku je obtížné přesně zacílit slova, která chcete přesně spočítat, a přitom ignorovat podřetězce a další dílčí shody (tj. Najít „lišku“, ale ne „lišky“). Interpunkce a variace případu činí tento problém docela náročným.
Jedním řešením je použít v prvním kroku jiný vzorec ve sloupci pomocníka k „normalizaci textu“. Poté pomocí vzorce na této stránce spočítejte slova zalomená mezerami, abyste získali přesný počet (tj. Můžete hledat „liška“ v normalizovaném textu.
Poznámka: tento přístup je pouze tak dobrý jako normalizovaný text, který jste schopni vytvořit, a možná budete muset mnohokrát upravit normalizační vzorec, abyste dosáhli požadovaného výsledku.
