Excel ma kilka niesamowitych funkcji tekstowych, które mogą pomóc w pracy z danymi tekstowymi.
W niektórych przypadkach może być konieczne obliczenie całkowitej liczby znaków w komórce/zakresie lub liczby wystąpień określonego znaku w komórce.
Chociaż istnieje funkcja LEN, która może policzyć liczbę znaków w komórce, możesz również zrobić resztę 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żna policzyć wszystkie lub określone znaki w komórce / zakresie w programie Excel.
Policz wszystkie znaki w komórce
Jeśli chcesz po prostu uzyskać całkowitą liczbę wszystkich znaków w komórce, możesz użyć funkcji LEN.
Funkcja LEN przyjmuje jeden argument, którym może być tekst w podwójnych cudzysłowach lub odwołanie do komórki zawierającej tekst.
Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, i chcesz policzyć całkowitą liczbę znaków w każdej komórce:
Poniżej znajduje się formuła, która to zrobi:
=DŁ(A2)
Sama funkcja LEN może nie wyglądać zbytnio, ale gdy połączysz ją z innymi formułami, może zrobić kilka wspaniałych rzeczy (takich jak zliczanie słów w komórce lub dzielenie imienia i nazwiska).
Uwaga: funkcja LEN zliczy 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ż funkcja LEN zlicza każdy znak w komórce, czasami możesz uzyskać błędny wynik, jeśli w komórce znajdują się dodatkowe spacje.
Na przykład w poniższym przypadku funkcja LEN zwraca 25 dla tekstu w komórce A1, podczas gdy powinno być 22. Ale ponieważ liczy również dodatkowe znaki spacji, otrzymujesz błędny wynik.
Aby uniknąć zliczania dodatkowych spacji, możesz najpierw użyć funkcji TRIM, aby usunąć wszelkie spacje wiodące, końcowe i podwójne, a następnie użyć funkcji LEN, aby uzyskać rzeczywistą liczbę słów.
Poniższa formuła zrobi to:
=DŁ(PRZYC(A2))
Policz wszystkie znaki w zakresie komórek
Możesz również użyć funkcji LEN, aby policzyć całkowitą liczbę znaków w całym zakresie.
Załóżmy na przykład, że mamy ten sam zestaw danych i tym razem zamiast pobierać 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:
=PROD.SUMA(DŁ(A2:A7)))
Pozwólcie, że wyjaśnię, jak działa ta formuła.
W powyższym wzorze część LEN funkcji zajmuje cały zakres komórek i liczy znaki w każdej komórce.
Wynik działania funkcji LEN będzie następujący:
{22;21;23;23;23;31}
Każda z tych liczb reprezentuje liczbę znaków w komórce.
A kiedy użyjesz z nim funkcji SUMPRODUCT, po prostu doda wszystkie te liczby.
Teraz, jeśli zastanawiasz się, dlaczego nie możesz użyć SUM zamiast SUMPRODUCT, powodem jest to, że jest to tablica, a SUMPRODUCT może obsługiwać tablicę, ale funkcja SUM nie.
Jeśli jednak nadal chcesz używać SUMA, możesz użyć poniższej formuły (ale pamiętaj, że musisz użyć klawiszy Control + Shift + Enter, aby uzyskać wynik zamiast zwykłego Enter)
=SUMA(DŁ(A2:A7))
Policz określone znaki w komórce
Jak wspomniałem, prawdziwą użytecznością funkcji LEN jest użycie jej 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, aby uzyskać liczbę słów, możesz policzyć znaki spacji, a następnie użyć ich do poznania całkowitej liczby słów w komórce.
Poniżej znajduje się formuła, która poda całkowitą liczbę znaków spacji w komórce:
=DŁ(A2)-DŁ(ZAMIENNIK(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 zamiany wszystkich znaków spacji na spację. Funkcja LEN jest następnie używana do zliczania całkowitej liczby znaków, gdy nie ma znaków spacji.
- Wynik LEN(SUBSTITUTE(A2,”””)) jest następnie odejmowany od LEN(A2). Daje nam to całkowitą liczbę znaków spacji znajdujących się w komórce.
- 1 jest dodawane do wzoru, 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).
Zwróć uwagę, że w przypadku wystąpienia jakichkolwiek początkowych, końcowych lub podwójnych spacji otrzymasz błędny wynik. W takim przypadku najlepiej użyć funkcji TRIM wraz z funkcją LEN.
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, w którym mam różne partie, gdzie każda partia ma alfabet i numer do jej reprezentowania (np. A1, J2 itp.)
Poniżej znajduje się formuła, która poda całkowitą liczbę przypadków utworzenia partii z alfabetem A w każdym miesiącu:
=DŁ(B2)-DŁ(ZAMIENNIK(B2;"A";"))
Powyższa formuła ponownie wykorzystuje tę samą logikę - znajdź długość tekstu w komórce ze znakiem i bez znaku, który chcesz policzyć, a następnie weź różnicę między tymi dwoma.
W powyższym wzorze zakodowałem na sztywno znak, który chcę policzyć, ale możesz również umieścić go w komórce, a następnie użyć odwołania do komórki. Jest to wygodniejsze, ponieważ formuła zostanie zaktualizowana przy następnej zmianie tekstu w komórce.
Policz określone znaki przy użyciu formuły nieuwzględniającej wielkości liter
Istnieje jeden problem z formułą używaną 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 otrzymujesz błędny wynik w komórce C5 (wynik powinien być 3).
Jak więc uzyskać liczbę znaków konkretnej postaci, skoro mogła być w każdym przypadku (dolna lub górna).
Robisz to, ustawiając niewrażliwość na wielkość liter w formule. Chociaż możesz wybrać złożoną formułę, po prostu dodałem liczbę znaków w obu przypadkach (małe i wielkie A).
=DŁ(B2)-DŁ(ZAMIENNIK(B2,"A",""))+DŁ(B2)-DŁ(ZAMIANA(B2,"a",""))
Policz znaki/cyfry przed i po dziesiętnym
Nie wiem dlaczego, ale jest to częste pytanie, które otrzymuję od moich czytelników i widziałem na wielu forach - takich jak to
Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz policzyć znaki przed i po przecinku.
Poniżej znajdują się formuły, które to zrobią.
Policz znaki/liczby przed przecinkiem:
=DŁ(WEW.(A2))
Policz znaki/liczby po przecinku:
=DŁ(A2)-ZNAJDŹ(".",A2)
Zauważ, że te formuły dotyczą tylko cyfr znaczących w komórce. W przypadku, gdy masz wiodące lub końcowe zera lub użyłeś niestandardowego formatowania liczb, aby pokazać więcej / mniej liczb, powyższe formuły nadal podałyby cyfry znaczące przed i po przecinku.
Oto niektóre scenariusze, w których można używać formuł do liczenia znaków w komórce lub zakresie komórek w programie Excel.
Mam nadzieję, że samouczek okazał się przydatny!
