Chcete získať počet slov v programe Excel? Verte či neverte, Excel nemá vstavaný počítadlo slov.
Ale nebojte sa.
Ľahko to za vás urobí množstvo skvelých funkcií Excelu (alebo trochu VBA, ak máte chuť).
V tomto tutoriále ukážem niekoľko spôsobov, ako počítať slová v programe Excel pomocou jednoduchých vzorcov. A na konci sa bude zaoberať aj technikou vytvorenia vlastného vzorca pomocou jazyka VBA, ktorý vám rýchlo poskytne počet slov pre akýkoľvek text v akejkoľvek bunke.
Vzorec na získanie počtu slov v programe Excel
Predtým, ako vám poskytnem presný vzorec, si rýchlo priblížime logiku, aby sme získali počet slov.
Predpokladajme, že mám vetu uvedenú nižšie, pre ktorú chcem zistiť počet slov.
Aj keď Excel nemôže počítať počet slov, môže počítať počet medzier vo vete.
Aby sme dostali počet slov, môžeme tieto medzery spočítať namiesto slov a pripočítať 1 k celkovému počtu (pretože počet medzier by bol o jeden menší ako počet slov).
Teraz môžu existovať dve možnosti:
- Medzi každým slovom je jedna medzera
- Medzi slovami je viac medzier.
Pozrime sa teda, ako spočítať celkový počet slov v každom prípade.
Príklad 1 - Ak je medzi slovami jediná medzera
Povedzme, že mám v bunke A1 nasledujúci text: Pustite mačku z tašky
Na spočítanie počtu slov používam vzorec:
= LEN (A1) -LEN (NÁHRADA (A1, "", ""))+1
V dôsledku toho sa vráti „7“.
Tento vzorec funguje takto:
- LEN (A1) - Táto časť vzorca vráti 26, čo je celkový počet znakov v texte v bunke A1. Obsahuje znaky textu aj medzery.
- SUBSTITUTE (A1, ”„, ””) - Táto časť vzorca odstráni z textu všetky medzery. Výsledok by teda v tomto prípade bol Nechajte tofutagag.
- LEN (NÁHRADA (A1, ”“,“”) - Táto časť vzorca počíta celkový počet znakov v texte, ktoré neobsahujú medzery. Výsledkom by teda bolo 20.
- LEN (A1) -LEN (NÁHRADA (A1, ”“,“”)) - To by odpočítalo dĺžku textu bez medzier od dĺžky textu s medzerami. Vo vyššie uvedenom príklade by to bolo 26-20, čo je 6.
- = LEN (A1) -LEN (NÁHRADA (A1, ”“,“”))+1 - K celkovému výsledku pripočítame 1, pretože celkový počet medzier je o jeden menší ako celkový počet slov. Napríklad v dvoch slovách je jedna medzera a v troch slovách dve medzery.
Teraz to funguje dobre, ak máte medzi slovami iba jeden znak medzery. Ale nefungovalo by to, keby ste mali medzi slovami viac ako jednu medzeru.
V takom prípade použite vzorec v nasledujúcom príklade.
Príklad 2: Keď je medzi slovami viac medzier
Povedzme, že máte nasledujúci text: Pustite mačku z tašky
V tomto prípade je medzi slovami viac medzier.
Aby sme získali počet slov, musíme najskôr odstrániť všetky medzery (tak, aby medzi dvoma slovami bol iba jeden znak medzery) a potom spočítať celkový počet medzier.
Tu je vzorec, ktorý nám poskytne správny počet slov:
= LEN (TRIM (A1))-LEN (SUBSTITUTE (A1, "", ""))+1
Je to podobný vzorec, ako sa používa vo vyššie uvedenom príklade, s malou zmenou - použili sme tu aj funkciu TRIM.
Funkcia Excel TRIM odstráni všetky úvodné, koncové a nadbytočné medzery (okrem medzier medzi slovami).
Zvyšok vzorca funguje rovnako (ako je vysvetlené v príklade 1).
Poznámka: Ak medzi slovami nie sú medzery, považuje sa za jedno slovo.
Použitie vlastnej funkcie VBA na počítanie slov v programe Excel
Aj keď vyššie uvedené vzorce fungujú skvele, ak potrebujete často počítať počet slov, môžete pomocou VBA vytvoriť vlastnú funkciu (nazýva sa aj funkcia definovaná používateľom).
Výhodou použitia vlastnej funkcie je, že ju môžete vytvoriť raz a potom ju použiť ako každú inú bežnú funkciu programu Excel. Takže namiesto vytvorenia dlhého komplexného vzorca, ako sme urobili v dvoch vyššie uvedených príkladoch, máte jednoduchý vzorec, ktorý preberá odkaz na bunku a okamžite vám poskytne počet slov.
Tu je kód, ktorý vytvorí túto vlastnú funkciu na získanie počtu slov v programe Excel.
Funkcia WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function
Po vytvorení môžete funkciu WordCount používať rovnako ako všetky ostatné bežné funkcie programu Excel.
Vo vyššie uvedenom kóde pre vlastnú funkciu som použil funkciu TRIM pracovného hárka na odstránenie medzier medzi úvodnými, koncovými a dvojitými medzerami. To zaisťuje, že všetky tri bunky poskytnú rovnaký výsledok, pretože sa počítajú iba slová, nie dvojité medzery.
Ako funguje tento vzorec:
Vyššie uvedený kód VBA najskôr používa funkciu TRIM na odstránenie všetkých úvodných, koncových a dvojitých medzier z textového reťazca v odkazovanej bunke.
Akonáhle má vyčistený reťazec, použije funkciu SPLIT vo VBA na rozdelenie textového reťazca na základe oddeľovača, ktorý sme určili ako medzerník. Každé slovo je teda oddelené tak, ako je uložené v samostatnej položke v premennej Výsledok.
Potom pomocou funkcie UBOUND spočítame celkový počet položiek, ktoré boli uložené v premenných Výsledok. Pretože VBA má základ 0, musíme pridať 1, aby sme získali celkový počet slov.
To znamená, že Výsledok (0) uloží prvé slovo, Výsledok (1) uloží druhé slovo a podobne. Pretože toto počítanie začína od 0, musíme pridať 1, aby sme získali skutočný počet slov.
Kam vložiť tento kód?
Pri vytváraní vlastnej funkcie musíte vložiť kód do VB editora zošita (čo je zadná časť zošita, kde môžete písať kód na automatizáciu úloh a vytváranie vlastných funkcií).
Nasledujú kroky na vloženie kódu pre funkciu „GetNumeric“ do zošita.
- Prejdite na kartu Vývojár.
- Kliknite na možnosť Visual Basic. Tým sa otvorí editor VB v backende.
- Na table Project Explorer v editore VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita, do ktorého chcete vložiť kód. Ak sa Project Explorer nezobrazuje, choďte na kartu Zobraziť a kliknite na Project Explorer.
- Prejdite na položku Vložiť a kliknite na modul. Tým sa vloží objekt modulu do vášho zošita.
- Skopírujte a prilepte kód do okna modulu.
Keď skopírujete kód do okna kódu, môžete sa vrátiť späť na pracovný hárok a použiť túto funkciu rovnako ako všetky ostatné bežné funkcie programu Excel.
Stačí napísať = Slovo a ukáže vám vzorec v zozname.
Vyžaduje jeden argument, ktorým je odkaz na bunku, a okamžite vám v ňom poskytne počet slov.
