Obecný vzorec
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
souhrn
Chcete-li spočítat celkový počet slov v buňce, můžete použít vzorec založený na funkcích LEN a SUBSTITUTE s pomocí funkce TRIM. V zobrazeném příkladu je vzorec v buňce C5 zkopírovaný dolů:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1
Vzorec vrací počet slov v buňce B5.
Vysvětlení
Excel nemá vyhrazenou funkci pro počítání slov v buňce. S trochou vynalézavosti však můžete vytvořit takový vzorec pomocí funkcí SUBSTITUTE a LEN s pomocí TRIM, jak je znázorněno v příkladu. Na vysoké úrovni používá tento vzorec funkci LEN k počítání počtu znaků v buňce, s mezerami i bez nich, poté pomocí rozdílu vypočítá počet slov. To funguje, protože počet slov se rovná počtu mezer + 1, pokud je mezi každým slovem jedna mezera.
První část vzorce počítá znaky v buňce B5 po odstranění mezery:
=LEN(TRIM(B5)) // normalize space, count characters
Uvnitř LEN funkce TRIM nejprve odstraní všechny mezery mezi slovy nebo na začátku nebo na konci textu. To je důležité, protože všechny mezery navíc způsobí snížení počtu slov. V tomto případě neexistují žádné mezery navíc, takže TRIM vrátí původní text přímo do funkce LEN, která vrátí 30:
LEN("All Quiet on the Western Front") // returns 30
V tomto okamžiku máme:
=30-LEN(SUBSTITUTE(B5," ",""))+1
Dále použijeme funkci SUBSTITUTE k odstranění všech mezer z textu:
SUBSTITUTE(B5," ","") // strip all space
Všimněte si, že SUBSTITUTE je nakonfigurován tak, aby hledal znak mezery ("") a nahradil jej prázdným řetězcem (""). Ve výchozím nastavení nahradí SUBSTITUTE všechny mezery. Výsledek je doručen přímo do funkce LEN, která vrátí počet:
LEN("AllQuietontheWesternFront") // returns 25
LEN vrátí 25, počet znaků zbývajících po odstranění veškerého prostoru. Nyní můžeme vzorec zjednodušit na:
=30-25+1 // returns 6
který vrací 6 jako konečný výsledek, počet slov v buňce B5.
Nakládání s prázdnými buňkami
Vzorec v příkladu vrátí 1, i když je buňka prázdná nebo obsahuje pouze mezeru. Stává se to proto, že po spočítání mezer mezi slovy přidáváme bezpodmínečně 1. Abyste se tomuto problému vyhnuli, můžete upravit vzorec, jak je znázorněno níže:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)
Všimněte si, že jsme nahradili 1 tímto výrazem:
LEN(TRIM(B5))>0
Tento kód nejprve ořízne B5, poté zkontroluje délku. Pokud B5 obsahuje text, vrátí funkce LEN kladné číslo a výraz vrátí hodnotu PRAVDA. Pokud je B5 prázdný nebo obsahuje pouze mezeru, vrátí TRIM prázdný řetězec ("") do LEN. V takovém případě vrátí funkce LEN nulu (0) a výraz vrátí hodnotu FALSE. Trik spočívá v tom, že PRAVDA a NEPRAVDA se při jakékoli matematické operaci vyhodnotí na 1, respektive na nulu. Výsledkem je, že výraz přidá pouze 1, když je v B5 text. Jinak přidá nulu (0). Tuto logiku lze také zapsat příkazem funkce IF takto:
IF(LEN(TRIM(B5))>0,1,0)
a výsledek by byl stejný. Výše uvedený výraz je jednoduše kompaktnější.
