Excel má niekoľko úžasných textových funkcií, ktoré vám môžu pomôcť pri práci s textovými údajmi.
V niektorých prípadoch môže byť potrebné vypočítať celkový počet znakov v bunke/rozsahu alebo počet výskytov konkrétneho znaku v bunke.
Aj keď existuje funkcia LEN, ktorá dokáže spočítať počet znakov v bunke, ostatné môžete urobiť aj kombináciou vzorcov (ako uvidíme neskôr v príkladoch).
V tomto návode sa budem zaoberať rôznymi príkladmi, kde môžete v bunke/rozsahu v programe Excel počítať celkový počet alebo konkrétne znaky.
Spočítajte všetky znaky v bunke
Ak chcete jednoducho získať celkový počet všetkých znakov v bunke, môžete použiť funkciu LEN.
Funkcia LEN používa jeden argument, ktorým môže byť text v úvodzovkách alebo odkaz na bunku s textom.
Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete spočítať celkový počet znakov v každej bunke:
Nasleduje vzorec, ktorý to urobí:
= LEN (A2)
Funkcia LEN sama o sebe nemusí vyzerať veľmi, ale keď ju skombinujete s inými vzorcami, dokáže niekoľko úžasných vecí (napríklad získať počet slov v bunke alebo rozdeliť meno a priezvisko).
Poznámka: Funkcia LEN spočíta všetky znaky v bunke, či už ide o špeciálny znak, číslice, interpunkčné znamienka a medzery (úvodné, koncové a dvojité medzery medzi slovami).
Pretože funkcia LEN počíta každý znak v bunke, niekedy môžete dostať nesprávny výsledok v prípade, že máte v bunke medzery.
Napríklad v nižšie uvedenom prípade funkcia LEN vráti 25 pre text v bunke A1, pričom malo byť 22. Ale keďže počíta aj ďalšie medzery, získate nesprávny výsledok.
Aby ste sa vyhli započítaniu ďalších medzier, môžete najskôr použiť funkciu TRIM na odstránenie všetkých medzier, koncových a dvojitých medzier a potom pomocou funkcie LEN zistiť skutočný počet slov.
Nasledujúci vzorec to urobí:
= DĹŽKA (Orezanie (A2))
Spočítajte všetky znaky v rozsahu buniek
Na počítanie celkového počtu znakov v celom rozsahu môžete použiť aj funkciu LEN.
Predpokladajme napríklad, že máme rovnakú množinu údajov, a tentokrát by som namiesto zisťovania počtu znakov v každej bunke chcel vedieť, koľko ich je v celom rozsahu.
Môžete to urobiť pomocou nasledujúceho vzorca:
= SUMPRODUCT (LEN (A2: A7)))
Vysvetlím, ako tento vzorec funguje.
Vo vyššie uvedenom vzorci časť funkcie LEN zaberá celý rozsah buniek a počíta znaky v každej bunke.
Výsledkom funkcie LEN bude:
{22;21;23;23;23;31}
Každé z týchto čísel predstavuje počet znakov v bunke.
A keď s ním použijete funkciu SUMPRODUCT, všetky tieto čísla sa jednoducho sčítajú.
Ak sa teraz pýtate, prečo nemôžete použiť SUM namiesto SUMPRODUCT, dôvodom je, že toto je pole a SUMPRODUCT zvládne pole, ale funkcia SUM nie.
Ak však napriek tomu chcete používať SUM, môžete použiť nasledujúci vzorec (ale pamätajte na to, že na získanie výsledku namiesto bežného klávesu Enter musíte použiť kombináciu klávesov Ctrl + Shift + Enter)
= SUM (LEN (A2: A7))
Počet konkrétnych znakov v bunke
Ako som už spomenul, skutočná užitočnosť funkcie LEN je, keď sa používa v kombinácii s inými vzorcami.
A ak chcete do bunky počítať konkrétne znaky (môže to byť písmeno, číslo, špeciálny znak alebo medzera), môžete to urobiť pomocou kombinácie vzorcov.
Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete spočítať celkový počet slov v každej bunke.
Aj keď neexistuje žiadny vstavaný vzorec na získanie počtu slov, môžete spočítať medzery a potom ho použiť na zistenie celkového počtu slov v bunke.
Nasleduje vzorec, ktorý vám poskytne celkový počet medzier v bunke:
= LEN (A2) -LEN (NÁHRADA (A2, "", ""))+1
Vyššie uvedený vzorec spočíta celkový počet medzier a potom k tomuto číslu pridá 1, aby sa získal počet slov.
Tento vzorec funguje takto:
- Funkcia SUBSTITUTE sa používa na nahradenie všetkých medzier prázdnymi znakmi. Funkcia LEN sa potom použije na spočítanie celkového počtu znakov, ak nie sú žiadne medzery.
- Výsledok LEN (NÁHRADA (A2, "", ""))) sa potom odpočíta od LEN (A2). To nám dáva celkový počet medzier, ktoré sú v bunke.
- Do vzorca sa pridá 1 a celkový počet slov by bol o jeden viac ako celkový počet medzier (pretože dve slová sú oddelené jedným znakom).
Všimnite si toho, že v prípade medzier, začiatočných alebo koncových medzier, získate nesprávny výsledok. V takom prípade je najlepšie použiť funkciu TRIM spolu s funkciou LEN.
Rovnakú logiku môžete použiť aj na nájdenie konkrétneho znaku alebo slova alebo frázy v bunke.
Predpokladajme napríklad, že mám množinu údajov, ako je uvedené nižšie, kde mám rôzne dávky, kde každá dávka obsahuje abecedu a číslo, ktoré ju reprezentujú (napríklad A1, J2 atď.)
Nasleduje vzorec, ktorý vám poskytne celkový počet prípadov, kedy bola dávka s abecedou A vytvorená každý mesiac:
= LEN (B2) -LEN (NÁHRADA (B2, "A", ""))
Vyššie uvedený vzorec opäť používa rovnakú logiku - nájdite dĺžku textu v bunke so znakom, ktorý chcete počítať, a bez neho, a potom vezmite rozdiel medzi týmito dvoma.
Vo vyššie uvedenom vzorci som naprogramoval znak, ktorý chcem počítať, ale môžete ho tiež vložiť do bunky a potom použiť odkaz na bunku. Je to pohodlnejšie, pretože vzorec sa aktualizuje pri ďalšej zmene textu v bunke.
Počítajte konkrétne znaky pomocou vzorca, ktorý nerozlišuje malé a veľké písmená
Existuje jeden problém so vzorcom používaným na počítanie konkrétnych znakov v bunke.
Funkcia SUBSTITUTE rozlišuje veľké a malé písmena. To znamená, že „A“ sa nerovná „a“. Preto v bunke C5 dostanete nesprávny výsledok (výsledok mal byť 3).
Ako teda môžete získať počet znakov konkrétneho znaku, keď to v každom prípade mohlo byť (dolné alebo horné).
Urobíte to tak, že vo vzorci nerozlišujete malé a veľké písmená. Aj keď môžete ísť na zložitý vzorec, jednoducho som pridal počet znakov v oboch prípadoch (malé aj veľké A).
= LEN (B2) -LEN (SUBSTITUTE (B2, "A", ""))+LEN (B2) -LEN (SUBSTITUTE (B2, "a", "")))
Počítajte znaky/číslice pred a za desatinnou čiarkou
Neviem prečo, ale toto je bežný dotaz, ktorý dostávam od svojich čitateľov a ktorý som videl na mnohých fórach - napríklad v tomto
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete počítať znaky pred desatinnou čiarkou a za desatinnou čiarkou.
Nasledujú vzorce, ktoré to urobia.
Počítajte znaky/čísla pred desatinnou čiarkou:
= LEN (INT (A2))
Počítajte znaky/čísla za desatinnou čiarkou:
= LEN (A2) -FIND (".", A2)
Uvedomte si, že tieto vzorce sú určené iba pre významné číslice v bunke. V prípade, že máte počiatočné alebo koncové nuly alebo ste na zobrazenie väčšieho/menšieho počtu čísel použili vlastné formátovanie čísel, vyššie uvedené vzorce vám stále poskytnú číslice pred desatinnou čiarkou a za ňou.
Toto sú teda niektoré zo scenárov, v ktorých môžete pomocou vzorcov počítať znaky v bunke alebo v rozsahu buniek v programe Excel.
Dúfam, že vám návod pomohol!
