Počítání znaků v buňce (nebo v rozsahu buněk) pomocí vzorců v aplikaci Excel

Excel má některé úžasné textové funkce, které vám mohou pomoci při práci s textovými daty.

V v některých případech možná budete muset vypočítat celkový počet znaků v buňce / oblasti nebo kolikrát se v buňce vyskytne konkrétní znak.

I když existuje funkce LEN, která dokáže spočítat počet znaků v buňce, zbytek můžete udělat také kombinací 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 počítat celkem nebo konkrétní znaky v buňce / oblasti v aplikaci Excel.

Počítat 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 má jeden argument, kterým může být dvojitý text – uvozovky nebo odkaz na buňku, která má text.

Předpokládejme například, že máte datovou sadu, jak je zobrazeno níže, a chcete spočítat celkový počet znaků v každé buňce:

Níže je uveden vzorec, který to provede:

=LEN(A2)

Autor sama o sobě funkce LEN možná nevypadá moc, ale když ji zkombinujete s jinými vzorci, můžete udělat 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í znak, čísla, interpunkční znaménka a mezery (úvodní, koncové a dvojité mezery mezi slovy).

Protože LEN funkce spočítá každý znak v buňce, někdy můžete mít špatný výsledek, pokud 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, i když to mělo být 22. Ale protože to počítá také mezery navíc, vy získejte nesprávný výsledek.

Abyste se vyhnuli počítání mezer navíc, můžete nejprve pomocí funkce TRIM odstranit všechny úvodní koncové a dvojité mezery a poté pomocí funkce LEN získáte skutečný počet slov.

Níže uvedený vzorec to provede:

=LEN(TRIM(A2))

Spočítat všechny znaky v rozsahu buněk

Pomocí funkce LEN můžete také spočítat celkový počet znaků v celý rozsah.

Předpokládejme například, že máme stejnou datovou sadu a tentokrát bych místo toho, abych získal počet znaků v každé buňce, chtěl 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 LEN funkce přebírá celou řadu buněk a spočítá znaky v každé buňce.

výsledek LEN fu Funkce by byla:

{22; 21; 23; 23; 23; 31}

Každé z těchto čísel představuje počet znaků v buňce.

A když s ní použijete funkci SUMPRODUCT, jednoduše by přidala všechna tato čísla.

Nyní, pokud vás zajímá, proč nemůžete použít SUM namísto SUMPRODUCT, je důvod, že se jedná o pole a SUMPRODUCT zvládnou pole, ale funkce SUM ne.

Pokud však stále chcete používat SUM, můžete použít následující vzorec (ale nezapomeňte, že je třeba použít Control + Shift + Enter pro získání výsledku namísto běžného Enteru)

=SUM(LEN(A2:A7))

spočítat konkrétní znaky v buňce

Jak jsem již zmínil, skutečný nástroj funkce LEN je, když se používá v kombinaci s jinými vzorci.

A pokud chcete spočítat konkrétní znaky v buňce (může to být písmeno, číslo, speciální znak nebo znak mezery), můžete to udělat pomocí kombinace vzorců.

Předpokládejme například, že máte datovou sadu, jak je znázorněno 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 mezerové znaky a poté pomocí nich zjistit celkový počet slov v buňce.

Níže je uveden vzorec, který vám dá celkový počet znaků mezery v buňce:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

Výše uvedený vzorec počítá celkový počet znaků mezery a poté k tomuto číslu přidá 1, aby získal počet slov.

Takto funguje tento vzorec:

• Funkce SUBSTITUTE slouží k nahrazení všech mezerových znaků mezerou. Funkce LEN se poté použije k spočítání celkového počtu znaků, když nejsou mezery.
• Výsledek LEN (SUBSTITUTE (A2, ”“, ””)) se poté odečte od LEN ( A2). To nám dává celkový počet mezerových znaků, které jsou v buňce.
• Do vzorce je přidána 1 a celkový počet slov by byl o jeden více než celkový počet znaků mezery (protože dvě slova jsou oddělena jedním znakem).

Všimněte si, že v případě, že existují mezery na začátku, na konci nebo ve dvojitých mezerách, 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 vyhledání 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 reprezentují (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(SUBSTITUTE(B2,"A",""))

Výše uvedený vzorec opět používá stejnou logiku – najděte délku textu v buňce s nebo bez znak, který chcete spočítat, a poté vezměte rozdíl těchto dvou.

Ve výše uvedeném vzorci jsem napevno zakódoval znak, který chci spočítat, ale můžete jej také vložit do buňky a poté použijte 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čítání konkrétních znaků pomocí vzorce nerozlišujícího velikost písmen

Ve vzorci je jeden problém slouží 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“. Proto v buňce C5 získáte nesprávný výsledek (výsledek měl být 3).

Jak tedy můžete získejte počet znaků konkrétního znaku, pokud by mohl být v každém případě (dolní nebo horní).

Uděláte to tak, že ve svém vzorci nebudete rozlišovat velká a malá písmena. I když můžete použít složitý vzorec, jednoduše jsem přidal počet znaků v obou případech (malá a velká A).

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))+LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))

Počet znaků / číslic před a po desetinném čísle

Nevím proč, ale toto je běžný dotaz, který dostávám od svých čtenářů a viděl jsem na mnoha fórech – například na tomto fóru

Předpokládejme, že máte datovou sadu, jak je zobrazeno níže, a chcete spočítat znaky před desetinnou čárkou a za desetinnou čárkou.

Níže jsou uvedeny vzorce, které to provedou.

Počet znaků / čísel před desetinnou čárkou:

=LEN(INT(A2))

Počet znaků / čísel za desetinnou čárkou:

=LEN(A2)-FIND(".",A2)

Tyto vzorce jsou určeny pouze pro významné číslice v buňce. Pokud 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 stále poskytnou významné číslice před a za desetinnou čárkou.

Takže toto jsou některé z scénáře, ve kterých můžete pomocí vzorců spočítat znaky v buňce nebo v řadě buněk v aplikaci Excel.

Doufám, že vám tento návod byl užitečný!

Další výukové programy pro Excel, které se vám mohou líbit:

• Počítat jedinečné hodnoty v aplikaci Excel pomocí funkce COUNTIF
• Jak počítat buňky, které obsahují textové řetězce
• Jak počítat barevné buňky v aplikaci Excel
• Počítání jedinečných hodnot v aplikaci Excel pomocí funkce COUNTIF
• Jak odebrat text před nebo po konkrétním znaku v aplikaci Excel