Policz znaki w komórce (lub zakresie komórek) za pomocą formuł w programie Excel

Excel ma kilka niesamowitych funkcji tekstowych, które mogą pomóc podczas pracy z danymi tekstowymi.

W W niektórych przypadkach może być konieczne obliczenie całkowitej liczby znaków w komórce / zakresie lub liczby przypadków wystąpienia określonego znaku w komórce.

Chociaż istnieje funkcja LEN, która może policzyć liczbę znaków w komórce, resztę możesz zrobić również za pomocą kombinacji formuł (jak zobaczymy w dalszej części przykładów).

W tym samouczku omówię różne przykłady, w których możesz policzyć łącznie lub określone znaki w komórce / zakresie w programie Excel.

Policz wszystkie znaki w komórce

Jeśli po prostu chcesz uzyskać całkowitą liczbę wszystkich znaków w komórce, możesz użyć funkcji DŁ.

Funkcja DŁ pobiera jeden argument, którym może być podwójny -quotes lub odwołanie do komórki zawierającej tekst.

Na przykład załóżmy, że masz zbiór danych, jak pokazano poniżej i chcesz policzyć całkowitą liczbę znaków w każdej komórce:

Poniżej znajduje się wzór, który to zrobi:

=LEN(A2)

Przez sama w sobie funkcja LEN może nie wyglądać zbyt wiele, ale gdy połączysz ją z innymi formułami, może zrobić kilka wspaniałych rzeczy (takich jak pobranie liczby słów w komórce lub podzielenie imienia i nazwiska).

Uwaga: funkcja LEN policzy wszystkie znaki w komórce, czy to znak specjalny, cyfry, znaki interpunkcyjne i znaki spacji (początkowe, końcowe i podwójne spacje między słowami).

Ponieważ LEN funkcja liczy każdy znak w komórce, czasami możesz uzyskać zły wynik, jeśli w komórce są dodatkowe spacje.

Na przykład w poniższym przypadku funkcja DŁ zwraca 25 dla tekstu w komórce A1, chociaż powinno być 22. Ale ponieważ liczy również dodatkowe znaki spacji, ty uzyskać zły wynik.

Aby uniknąć zliczania dodatkowych spacji, możesz najpierw użyć funkcji TRIM, aby usunąć wszelkie początki, końcowe i podwójne spacje, a następnie użyj na nim funkcji LEN, aby uzyskać rzeczywistą liczbę słów.

Poniższa formuła zrobi to:

=LEN(TRIM(A2))

Policz wszystkie znaki w zakresie komórek

Możesz także użyć funkcji DŁ, aby zliczyć całkowitą liczbę znaków w cały zakres.

Załóżmy na przykład, że mamy ten sam zbiór danych i tym razem zamiast sprawdzać liczbę znaków w każdej komórce, chcę wiedzieć, ile jest ich w całym zakresie.

Możesz to zrobić za pomocą poniższego wzoru:

=SUMPRODUCT(LEN(A2:A7)))

Pozwól, że wyjaśnię, jak działa ta formuła.

W powyższym wzorze część LEN funkcji zajmuje cały zakres komórek i zlicza znaki w każdej komórce.

wynik LEN fu nkcja wyglądałaby następująco:

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

Każda z tych liczb reprezentuje liczbę znaków w komórce.

A kiedy użyjesz z nią funkcji SUMPRODUCT, po prostu dodasz wszystkie te liczby.

Jeśli zastanawiasz się, dlaczego nie możesz użyć SUMA zamiast SUMPRODUCT, powodem jest to, że jest to tablica, a SUMPRODUCT może obsłużyć tablicę, ale funkcja SUMA nie.

Jeśli jednak nadal chcesz użyć SUMA, możesz użyć poniższej formuły (ale pamiętaj, że musisz użyć Control + Shift + Enter, aby otrzymać wynik zamiast zwykłego Enter)

=SUM(LEN(A2:A7))

Policz określone znaki w komórce

Jak wspomniałem, prawdziwe narzędzie funkcji LEN jest wtedy, gdy jest używana w połączeniu z innymi formułami.

A jeśli chcesz policzyć określone znaki w komórce (może to być litera, cyfra, znak specjalny lub znak spacji), możesz to zrobić za pomocą kombinacji formuł.

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej i chcesz policzyć całkowitą liczbę słów w każdej komórce.

Chociaż nie ma wbudowanej formuły do obliczenia liczby słów, możesz policzyć znaki spacji, a następnie użyć jej, aby poznać sumę liczba słów w komórce.

Poniżej znajduje się formuła, która podaje całkowitą liczbę znaków spacji w komórce:

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

Powyższa formuła zlicza całkowitą liczbę znaków spacji, a następnie dodaje 1 do tej liczby, aby uzyskać liczbę słów.

Oto jak działa ta formuła:

• Funkcja SUBSTITUTE służy do zastąpienia wszystkich znaków spacji spacją. Funkcja DŁ jest następnie używana do zliczania całkowitej liczby znaków, gdy nie ma znaków spacji.
• Wynik funkcji DŁ (SUBSTITUTE (A2, „”, ””)) jest następnie odejmowany od DŁ ( A2), co daje nam całkowitą liczbę znaków spacji, które znajdują się w komórce.
• 1 jest dodawane do formuły, a całkowita liczba słów byłaby o jeden większa niż całkowita liczba znaków spacji (ponieważ dwa słowa są oddzielone jednym znakiem).

Zauważ, że w przypadku, gdy są jakieś spacje wiodące, końcowe lub podwójne, otrzymasz zły wynik. W takim przypadku najlepiej jest użyć funkcji TRIM razem z funkcją DŁ.

Możesz również użyć tej samej logiki, aby znaleźć określony znak, słowo lub frazę w komórce.

Załóżmy na przykład, że mam zestaw danych, jak pokazano poniżej, gdzie mam różne partie, gdzie każda partia ma alfabet i liczbę, które ją reprezentują (na przykład A1, J2 itp.)

Poniżej znajduje się wzór, który podaje całkowitą liczbę razy, gdy partia zawierająca alfabet A została utworzona w każdym miesiącu:

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

W powyższym wzorze ponownie zastosowano tę samą logikę – znajdź długość tekstu w komórce zi bez znak, który chcesz policzyć, a następnie weź różnicę z tych dwóch.

W powyższym wzorze zakodowałem na stałe znak, który chcę policzyć, ale możesz również umieścić go w komórce, a następnie użyj odwołania do komórki. Jest to wygodniejsze, ponieważ formuła będzie aktualizowana przy następnej zmianie tekstu w komórce.

Policz określone znaki za pomocą formuły bez rozróżniania wielkości liter

Jest jeden problem z formułą służy do zliczania określonych znaków w komórce.

Funkcja SUBSTITUTE rozróżnia wielkość liter. Oznacza to, że „A” nie jest równe „a”. Dlatego w komórce C5 otrzymujesz nieprawidłowy wynik (wynik powinien wynosić 3).

Więc jak możesz uzyskaj liczbę znaków dla określonego znaku, jeśli w jakimkolwiek przypadku mógł to być (mały lub duży).

Robisz to, czyniąc formułę niewrażliwą na wielkość liter. Chociaż możesz wybrać złożoną formułę, po prostu dodałem liczbę znaków w obu przypadkach (małe i duże litery A).

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

Policz znaki / cyfry przed i po przecinku

Nie wiem dlaczego, ale jest to typowe zapytanie, które otrzymuję od moich czytelników i widziałem na wielu forach – takich jak to.

Załóżmy, że masz zbiór danych, jak pokazano poniżej i chcesz policzyć znaki przed i po przecinku.

Poniżej znajdują się formuły, które to umożliwią.

Policz znaki / cyfry przed przecinkiem:

=LEN(INT(A2))

Policz znaki / cyfry po przecinku:

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

Pamiętaj, że te formuły są przeznaczone tylko dla znaczących cyfr w komórce. W przypadku, gdy masz na początku lub na końcu zera lub użyłeś niestandardowego formatowania liczb, aby wyświetlić więcej / mniej liczb, powyższe formuły nadal będą dawać znaczące cyfry przed i po przecinku.

Oto niektóre z scenariusze, w których możesz używać formuł do liczenia znaków w komórce lub zakresie komórek w programie Excel.

Mam nadzieję, że samouczek okazał się przydatny!

Inne samouczki programu Excel, które mogą Ci się spodobać:

• Zliczanie unikalnych wartości w programie Excel za pomocą funkcji COUNTIF
• Jak liczyć komórki zawierające ciągi tekstowe
• Jak liczyć kolorowe komórki w programie Excel
• Policz unikalne wartości w programie Excel za pomocą funkcji LICZ.JEŻELI
• Jak usunąć tekst przed lub po określonym znaku w programie Excel