Excel má několik úžasných textových funkcí, které vám mohou pomoci při práci s textovými daty.
V některých případech může být nutné vypočítat celkový počet znaků v buňce/rozsahu nebo počet výskytů konkrétního znaku v buňce.
I když existuje funkce LEN, která dokáže spočítat počet znaků v buňce, zbytek můžete provést také pomocí kombinace vzorců (jak uvidíme dále v příkladech).
V tomto tutoriálu se budu zabývat různými příklady, kde můžete v buňce/rozsahu v aplikaci Excel počítat celkové nebo konkrétní znaky.
Spočítejte všechny znaky v buňce
Pokud chcete jednoduše získat celkový počet všech znaků v buňce, můžete použít funkci LEN.
Funkce LEN přebírá jeden argument, kterým může být text v uvozovkách nebo odkaz na buňku s buňkou, která má text.
Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete spočítat celkový počet znaků v každé buňce:
Níže je uveden vzorec, který to provede:
= DÉLKA (A2)
Sama o sobě funkce LEN nemusí vypadat moc, ale když ji zkombinujete s jinými vzorci, dokáže několik úžasných věcí (například získat počet slov v buňce nebo rozdělit jméno a příjmení).
Poznámka: Funkce LEN spočítá všechny znaky v buňce, ať už jde o speciální znaky, čísla, interpunkční znaménka a mezery (úvodní, koncové a dvojité mezery mezi slovy).
Protože funkce LEN počítá každý znak v buňce, někdy může dojít k nesprávnému výsledku v případě, že máte v buňce mezery navíc.
Například v níže uvedeném případě funkce LEN vrací 25 pro text v buňce A1, zatímco mělo být 22. Ale protože počítá i znaky mezery navíc, dostanete špatný výsledek.
Abyste se vyhnuli počítání nadbytečných mezer, můžete nejprve pomocí funkce TRIM odstranit všechny úvodní, koncové a dvojité mezery a poté pomocí funkce LEN získat skutečný počet slov.
Níže uvedený vzorec to udělá:
= DÉLKA (OŘEZ (A2))
Spočítejte všechny znaky v řadě buněk
Pomocí funkce LEN můžete také spočítat celkový počet znaků v celém rozsahu.
Předpokládejme například, že máme stejnou datovou sadu a tentokrát místo zjišťování počtu znaků v každé buňce chci vědět, kolik jich je v celém rozsahu.
Můžete to udělat pomocí následujícího vzorce:
= SUMPRODUCT (LEN (A2: A7)))
Dovolte mi vysvětlit, jak tento vzorec funguje.
Ve výše uvedeném vzorci část funkce LEN přebírá celý rozsah buněk a počítá znaky v každé buňce.
Výsledkem funkce LEN bude:
{22;21;23;23;23;31}
Každé z těchto čísel představuje počet znaků v buňce.
A když s ním použijete funkci SUMPRODUCT, jednoduše by sečetla všechna tato čísla.
Pokud vás nyní zajímá, proč nemůžete použít SUM místo SUMPRODUCT, důvodem je, že toto je pole a SUMPRODUCT zvládá pole, ale funkce SUM ne.
Pokud však přesto chcete použít SUM, můžete použít níže uvedený vzorec (ale nezapomeňte, že k získání výsledku místo běžného Enteru musíte použít Control + Shift + Enter)
= SUM (DÉLKA (A2: A7))
Počítejte konkrétní znaky v buňce
Jak jsem zmínil, skutečná užitečnost funkce LEN je, když se používá v kombinaci s jinými vzorci.
A pokud chcete počítat konkrétní znaky v buňce (může to být písmeno, číslo, speciální znak nebo mezerník), můžete to udělat pomocí kombinace vzorců.
Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete spočítat celkový počet slov v každé buňce.
I když neexistuje žádný vestavěný vzorec pro získání počtu slov, můžete spočítat znaky mezer a poté pomocí nich zjistit celkový počet slov v buňce.
Níže je uveden vzorec, který vám poskytne celkový počet mezer v buňce:
= LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))+1
Výše uvedený vzorec počítá celkový počet mezer a poté k tomuto číslu přidá 1, aby se získal počet slov.
Tento vzorec funguje takto:
- Funkce SUBSTITUTE se používá k nahrazení všech mezer prázdnými znaky. Funkce LEN se pak používá k počítání celkového počtu znaků, pokud nejsou žádné mezery.
- Výsledek LEN (SUBSTITUTE (A2, "", "")) se poté odečte od LEN (A2). To nám dává celkový počet mezer, které jsou v buňce.
- Do vzorce se přidá 1 a celkový počet slov bude o jedno více než celkový počet mezer (protože dvě slova jsou oddělena jedním znakem).
Všimněte si, že v případě, že existují nějaké úvodní, koncové nebo dvojité mezery, dostanete špatný výsledek. V takovém případě je nejlepší použít funkci TRIM spolu s funkcí LEN.
Stejnou logiku můžete také použít k nalezení konkrétního znaku, slova nebo fráze v buňce.
Předpokládejme například, že mám datovou sadu, jak je uvedeno níže, kde mám různé dávky, kde každá dávka má abecedu a číslo, které ji reprezentuje (například A1, J2 atd.)
Níže je uveden vzorec, který vám poskytne celkový počet případů, kdy byla dávka s abecedou A vytvořena každý měsíc:
= LEN (B2) -LEN (NÁHRADA (B2, "A", ""))
Výše uvedený vzorec opět používá stejnou logiku - najděte délku textu v buňce se znakem, který chcete počítat, a bez něj, a poté vezměte rozdíl těchto dvou.
Ve výše uvedeném vzorci jsem napevno zakódoval znak, který chci počítat, ale můžete jej také vložit do buňky a poté použít odkaz na buňku. Díky tomu je to pohodlnější, protože vzorec by se aktualizoval při příští změně textu v buňce.
Počítejte konkrétní znaky pomocí vzorce nerozlišujícího malá a velká písmena
Existuje jeden problém se vzorcem používaným k počítání konkrétních znaků v buňce.
Funkce SUBSTITUTE rozlišuje velká a malá písmena. To znamená, že „A“ se nerovná „a“. To je důvod, proč v buňce C5 získáte špatný výsledek (výsledek měl být 3).
Jak tedy můžete získat počet znaků konkrétního znaku, když to v každém případě mohlo být (dolní nebo horní).
Toho docílíte tím, že váš vzorec nerozlišuje velká a malá písmena. I když můžete jít na složitý vzorec, jednoduše jsem přidal počet znaků v obou případech (malá a velká písmena A).
= LEN (B2) -LEN (SUBSTITUTE (B2, "A", ""))+LEN (B2) -LEN (SUBSTITUTE (B2, "a", "")))
Počítejte znaky/číslice před a za desetinnou čárkou
Nevím proč, ale toto je běžný dotaz, který dostávám od svých čtenářů a který jsem viděl na mnoha fórech - například v tomto
Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete počítat znaky před desetinnou čárkou a za ní.
Níže jsou uvedeny vzorce, které to provedou.
Počítejte znaky/čísla před desetinnou čárkou:
= DÉLKA (INT (A2))
Počítat znaky/čísla za desetinnou čárkou:
= LEN (A2) -FIND (".", A2)
Všimněte si, že tyto vzorce jsou určeny pouze pro významné číslice v buňce. V případě, že máte počáteční nebo koncové nuly nebo jste použili vlastní formátování čísel k zobrazení více/méně čísel, výše uvedené vzorce vám přesto poskytnou významné číslice před a za desetinnou čárkou.
Toto jsou tedy některé ze scénářů, ve kterých můžete pomocí vzorců počítat znaky v buňce nebo v rozsahu buněk v Excelu.
Doufám, že vám návod přišel užitečný!
