Všeobecný vzorec
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
Zhrnutie
Ak chcete spočítať celkový počet slov v bunke, môžete použiť vzorec založený na funkciách LEN a SUBSTITUTE s pomocou funkcie TRIM. V zobrazenom príklade je vzorec v bunke C5, skopírovaný nadol,:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1
Vzorec vráti počet slov v bunke B5.
Vysvetlenie
Excel nemá vyhradenú funkciu na počítanie slov v bunke. Avšak s trochou dômyselnosti môžete takýto vzorec vytvoriť pomocou funkcií SUBSTITUTE a LEN s pomocou programu TRIM, ako je to znázornené v príklade. Na vysokej úrovni používa tento vzorec funkciu LEN na spočítanie počtu znakov v bunke, s medzerami aj bez nich, potom pomocou tohto rozdielu zistí počet slov. Funguje to, pretože počet slov sa rovná počtu medzier + 1, pokiaľ je medzi každým slovom jedna medzera.
Prvá časť vzorca počíta znaky v bunke B5 po odstránení medzery:
=LEN(TRIM(B5)) // normalize space, count characters
Vo vnútri LEN funkcia TRIM najskôr odstráni všetky medzery medzi slovami alebo na začiatku alebo na konci textu. To je dôležité, pretože všetky medzery navyše zahodia počet slov. V tomto prípade neexistujú žiadne medzery navyše, takže funkcia TRIM vráti pôvodný text priamo do funkcie LEN, ktorá vráti 30:
LEN("All Quiet on the Western Front") // returns 30
V tejto chvíli máme:
=30-LEN(SUBSTITUTE(B5," ",""))+1
Ďalej pomocou funkcie SUBSTITUTE odstránime z textu všetky medzery:
SUBSTITUTE(B5," ","") // strip all space
Všimnite si, že SUBSTITUTE je nakonfigurovaný tak, aby hľadal medzeru ("") a nahradil ju prázdnym reťazcom (""). Predvolene nahradí všetky medzery SUBSTITUTE. Výsledok sa doručí priamo do funkcie LEN, ktorá vráti počet:
LEN("AllQuietontheWesternFront") // returns 25
LEN vráti 25, počet znakov zostávajúcich po odstránení všetkého medzery. Teraz môžeme vzorec zjednodušiť na:
=30-25+1 // returns 6
ktorý vráti 6 ako konečný výsledok, počet slov v bunke B5.
Zaobchádzanie s prázdnymi bunkami
Vzorec v príklade vráti hodnotu 1, aj keď je bunka prázdna alebo obsahuje iba medzeru. Stáva sa to preto, že po spočítaní medzier medzi slovami pridávame bezpodmienečne 1. Na ochranu pred týmto problémom môžete upraviť vzorec, ako je uvedené nižšie:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)
Všimnite si, že sme nahradili 1 týmto výrazom:
LEN(TRIM(B5))>0
Tento kód najskôr orezá B5, potom skontroluje dĺžku. Ak B5 obsahuje text, funkcia LEN vráti kladné číslo a výraz vráti hodnotu TRUE. Ak je B5 prázdny alebo obsahuje iba medzeru, vráti TRIM prázdny reťazec ("") do LEN. V takom prípade vráti LEN nulu (0) a výraz vráti FALSE. Trik spočíva v tom, že hodnoty TRUE a FALSE sa pri akejkoľvek matematickej operácii vyhodnotia na 1, respektíve na nule. Výsledkom je, že výraz pridá iba 1, ak je v B5 text. V opačnom prípade pridá nulu (0). Túto logiku je možné zapísať aj takto:
IF(LEN(TRIM(B5))>0,1,0)
a vysledok by bol rovnaky. Vyššie uvedený výraz je jednoducho kompaktnejší.
