Samouczek wyjaśnia, jak liczyć znaki w programie Excel. Nauczysz się formuł, aby uzyskać całkowitą liczbę znaków w zakresie i policzyć tylko określone znaki w komórce lub w kilku komórkach.

W naszym poprzednim samouczku wprowadziliśmy funkcję Excel LEN, która pozwala zliczać całkowitą liczbę znaków w komórce.

Formuła LEN jest przydatna sama w sobie, ale w połączeniu z innymi funkcjami, takimi jak SUMA, SUMPRODUCT i SUBSTITUTE, może obsługiwać znacznie bardziej złożone zadania. W dalszej części tego samouczka przyjrzymy się bliżej kilku podstawowym i zaawansowanym formułom do liczenia znaków w programie Excel.

• Policz całkowitą liczbę znaków w zakresie
• Policz określone znaki w komórce
• Policz określony znak w zakresie

Jak policzyć wszystkie znaki w zakresie

Kiedy to polega na zliczeniu całkowitej liczby znaków w kilku komórkach, natychmiastowym rozwiązaniem, które przychodzi na myśl, jest uzyskanie liczby znaków w każdej komórce, a następnie dodanie tych liczb:

=LEN(A2)+LEN(A3)+LEN(A4)

Lub

=SUM(LEN(A2),LEN(A3),LEN(A4))

Powyższe formuły mogą działać dobrze w małym zakresie. Aby policzyć wszystkie znaki w większym zakresie, lepiej wymyślmy coś bardziej zwięzłego, np. Funkcję SUMPRODUCT, która mnoży tablice i zwraca sumę iloczynów.

Oto ogólny Excel formuła do liczenia znaków w zakresie:

A Twoja rzeczywista formuła może wyglądać podobnie do tego:

=SUMPRODUCT(LEN(A1:A7))

Innym sposobem liczenia znaków w zakresie jest użycie funkcji DŁ w połączeniu z SUMA:

=SUM(LEN(A1:A7))

W przeciwieństwie do SUMPRODUCT funkcja SUMA domyślnie nie oblicza tablic i musisz nacisnąć Ctrl + Shift + Enter, aby przekształcić go w formułę tablicową.

Jak pokazano na poniższym zrzucie ekranu, formuła SUMA zwraca tę samą całkowitą liczbę znaków:

Jak działa ta formuła zliczania znaków zakresu

Jest to jedna z najprostszych formuł do liczenia znaków w programie Excel. Funkcja DŁ oblicza długość ciągu dla każdej komórki w określonym zakresie i zwraca je jako tablicę liczb. Następnie SUMPRODUCT lub SUM sumuje te liczby i zwraca całkowitą liczbę znaków.

W powyższym przykładzie zsumowana jest tablica 7 liczb reprezentujących długości ciągów w komórkach od A1 do A7:

Jak liczyć określonych znaków w komórce

Czasami zamiast liczyć wszystkie znaki w komórce, może być konieczne policzenie tylko wystąpień określonej litery, cyfry lub specjalnego symbolu.

Aby policz, ile razy dany znak pojawia się w komórce, użyj funkcji LEN razem z SUBSTITUTE:

Aby lepiej zrozumieć formułę, rozważ następujący przykład.

Załóżmy, że prowadzisz bazę danych dostarczonych towarów, w której każdy typ pozycji ma swój własny, niepowtarzalny identyfikator. Każda komórka zawiera kilka elementów oddzielonych przecinkiem, spacją lub innym ogranicznikiem. Zadanie polega na policzeniu, ile razy dany unikalny identyfikator pojawia się w każdej komórce.

Zakładając, że lista dostarczonych towarów znajduje się w kolumnie B (zaczynając od B2), a liczymy liczbę „A „, formuła jest następująca:

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

Jak działa ta formuła zliczania znaków w Excelu

Aby zrozumieć logikę formuły, podzielmy ją na mniejsze części:

W rezultacie otrzymasz liczbę „usuniętych” „znaków, co jest równe całkowitej liczbie wystąpień tego znaku w komórce.

Zamiast określać znak, który chcesz policzyć w formule, możesz wpisać go w jakiejś komórce, a następnie odwołać się do tej komórki w formule. W ten sposób Twoi użytkownicy będą mogli liczyć wystąpienia dowolnego innego znaku, który wprowadzą w tej komórce, bez modyfikowania formuły:

Formuła programu Excel uwzględniająca wielkość liter do zliczania określonych znaków w komórce

Jeśli potrzebujesz liczby znaków bez uwzględniania wielkości liter, osadz funkcję UPPER wewnątrz SUBSTITUTE, aby przekonwertować określony znak na wielkie litery przed prowadzenie zmiany. Pamiętaj, aby w formule wprowadzić wielką literę.

Na przykład, aby policzyć elementy „A” i „a” w komórce B2, użyj tej formuły:

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

Innym sposobem jest użycie zagnieżdżonych funkcji zastępczych:

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

Jak widać na poniższym zrzucie ekranu, obie formuły bezbłędnie zliczają duże i małe litery określonego znaku:

W niektórych przypadkach , może być konieczne policzenie wielu różnych znaków w tabeli, ale możesz nie chcieć za każdym razem modyfikować formuły. W takim przypadku umieść jedną funkcję zastępczą w innej, wpisz znak, który chcesz policzyć w jakiejś komórce (w tym przykładzie D1) i przekonwertuj wartość tej komórki na wielkie i małe litery, używając funkcji UPPER i LOWER:

=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1), ""), LOWER($D$1),""))

Ewentualnie przekonwertuj zarówno komórkę źródłową, jak i komórkę zawierającą znak na wielkie lub małe litery. Na przykład:

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2), UPPER($C$1),""))

Zaletą tego podejścia jest to, że niezależnie od tego, czy w komórce, do której istnieje odwołanie, wprowadzana jest wielka czy mała litera, formuła zliczania znaków nie uwzględnia wielkości liter zwróci właściwą liczbę:

Policz wystąpienia określonego tekstu lub podciągu w komórce

Jeśli chcesz policzyć ile razy określona kombinacja znaków (tj. określony tekst lub podciąg) pojawia się w danej komórce, np. „A2” lub „SS”, następnie podziel liczbę znaków zwracanych przez powyższe formuły przez długość podłańcucha .

Formuła uwzględniająca wielkość liter:

=(LEN(B2)-LEN(SUBSTITUTE(B2, $C$1,"")))/LEN($C$1)

Formuła bez rozróżniania wielkości liter:

=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),"")))/LEN($C$1)

Gdzie B2 to komórka zawierająca cały ciąg tekstowy, a C1 to tekst (podciąg), który chcesz policzyć.

Aby uzyskać szczegółowe wyjaśnienie wzoru, zobacz Jak policzyć określony tekst / słowa w komórce.

Jak policzyć określony znak ( s) w zakresie

Teraz, gdy znasz już formułę programu Excel do liczenia znaków w komórce, możesz chcieć ją ulepszyć, aby dowiedzieć się, ile razy dany znak występuje w zakresie. W tym celu weźmiemy formułę Excel LEN, aby policzyć określony znak w komórce omówionej w poprzednim przykładzie i umieścić go w funkcji SUMPRODUCT, która może obsługiwać tablice:

W tym przykładzie formuła przyjmuje następujący kształt:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))

A oto kolejna formuła do liczenia znaków w zakresie Excela:

=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))

W porównaniu z pierwszą formułą najbardziej oczywistą różnicą jest użycie SUMA zamiast SUMA PRODUKTU. Inną różnicą jest to, że wymaga naciśnięcia Ctrl + Shift + Enter, ponieważ w przeciwieństwie do SUMPRODUCT, który jest przeznaczony do przetwarzania tablic, SUM może obsługiwać tablice tylko wtedy, gdy jest używany w formule tablicowej.

Jeśli nie chcesz kodować na stałe znaku w formule, możesz oczywiście wpisać go w jakiejś komórce, na przykład D1, i odwołać się do tej komórki w formule liczby znaków:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1,"")))

Jak działa ta formuła liczenia znaków

Jak być może pamiętasz, funkcja SUBSTITUTE służy do zastąpienia wszystkich wystąpień określonego znaku (w tym przykładzie „A”) pustym ciągiem tekstowym ( „”).

Następnie przekazujemy ciąg tekstowy zwracany przez SUBSTITUTE do funkcji Excel LEN, aby obliczała długość ciągu bez liter A ”. Następnie odejmujemy liczbę znaków od całkowitej długości ciągu tekstowego. Wynikiem tych obliczeń jest tablica zliczeń znaków, z jedną liczbą znaków na komórkę.

Na koniec funkcja SUMPRODUCT sumuje liczby w tablicy i zwraca całkowitą liczbę określonego znaku w zakresie.

Formuła uwzględniająca wielkość liter w celu zliczania określonych znaków w zakresie

Wiesz już, że PODSTAW jest funkcją rozróżniającą wielkość liter, co sprawia, że w naszej formule programu Excel uwzględniana jest również wielkość liter.

Aby formuła ignorowała wielkość liter, postępuj zgodnie z metodami przedstawionymi w poprzednim przykładzie: Formuła bez uwzględniania wielkości liter w celu zliczania określonych znaków w komórce.

W szczególności możesz użyć jednego z następujące formuły do liczenia określonych znaków w zakresie, ignorując wielkość liter:

Poniższy zrzut ekranu przedstawia ostatnią formułę w akcji:

W ten sposób można policzyć znaki w programie Excel za pomocą funkcji LEN funkcjonować.Jeśli chcesz wiedzieć, jak liczyć słowa, a nie pojedyncze znaki, w następnym artykule znajdziesz kilka przydatnych formuł. Prosimy o cierpliwość!

W międzyczasie możesz pobrać przykładowe skoroszyty z liczbą znaków formułę omówioną w tym samouczku i zapoznaj się z listą powiązanych zasobów na końcu strony. Dziękuję za przeczytanie i do zobaczenia wkrótce!

• Funkcja Excel LEN – formuły obliczające długość komórki, czyli całkowitą liczbę znaków w komórce, ze spacjami lub bez.
• Jak liczyć słowa w programie Excel – w formułach uwzględniających wielkość liter i bez rozróżniania wielkości liter w celu zliczenia wszystkich lub określonych słów w komórce lub zakresie komórek.
• Funkcje LICZNIK i LICZBA – liczenie komórek z liczbami i niepustymi komórkami.
• Funkcja LICZ.JEŻELI – formuły do warunkowego liczenia komórek w programie Excel.
• Funkcja LICZ.WARUNKI – zliczanie komórek według więcej niż jednego kryterium.