W tym poście nauczymy się zliczać wszystkie litery w tekście, czy też dowolne znaki.
Najwięcej problemu może być z literami, bo mamy litery małe i wielkie, poza tym w polskim języku mamy litery takie jak ą, ę, ś, ć, ń, ź, ż, ł, ó, czyli wszystkie te litery z ogonkami i kreseczkami. Najprostszą metodą na wykonanie tego zadania jest wypisanie wszystkich możliwych znaków w postaci wielkich liter. Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
W pierwszym kroku musimy nasz tekst rozbić na poszczególne znaki. Do tego celu potrzebujemy ciągu liczbowego od 1 do wartości odpowiadającej długości naszego tekstu. Najpierw użyjemy funkcji DŁ, która zwraca liczbę znaków w ciągu znaków. My jednak potrzebujemy zbudować ciąg wartości. Użyjemy tutaj właściwości funkcji WIERSZ, która polega na tym, że jeśli w argumencie odwołanie zaznaczymy zakres A1:A8, to funkcja zwróci tyle wartości, ile zaznaczyliśmy wierszy w tym zakresie. Przykładowy zapis formuły to =WIERSZ(A1:A8) (rys. nr 2)
Zaznaczyliśmy w zakresie 8 wierszy, więc funkcja WIERSZ w Excelu tablicowym rozlewa się na te właśnie wiersze. Musimy dla tej funkcji zbudować odpowiedni zakres. Potrzebujemy do tego funkcji ADR.POŚR (adres pośredni). Możemy tutaj wykluczyć kolumny, interesują nas tylko wiersze. Argumentem funkcji jest adres_tekst, czyli zaczynami od "1:", od pierwszego wiersza, a dwukropek oznacza, że będziemy szli dalej do kolejnych wierszy. Ten tekst łączymy z funkcją DŁ dla tekstu z komórki A2. Zapis formuły powinien wyglądać następująco:
=ADR.POŚR("1:"&DŁ(A2))
Jeśli podejrzymy sobie wynik formuły funkcji ADR.POŚR za pomocą skrótu klawiszowego F9 otrzymamy ilość znaków 1:11, co widać na rys. nr 3.
Wychodzimy z podglądu formuły za pomocą skrótu klawiszowego Ctrl+Z. Powyższą funkcję musimy włożyć do funkcji WIERSZ, bo interesuje nas liczba wierszy z tego zakresu. Zapis formuły powinien wyglądać następująco:
=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))
Po zatwierdzeniu formuły otrzymamy dane przedstawione na rys. nr 4.
W Excelu tablicowym wyniki rozlewają się automatycznie na odpowiednią ilość komórek (wierszy). W kolejnym kroku musimy dołożyć funkcję FRAGMENT.TEKSTU. Pierwszym argumentem funkcji jest tekst, czyli tekst z komórki A2. Drugi argument to liczba.początkowa, czyli tablica danych, którą zbudowaliśmy za pomocą funkcji WIERSZ (W Excelu tablicowym moglibyśmy użyć funkcji SEKWENCJA, która byłaby prostsza). Trzeci argument funkcji to liczba_znaków, czyli wartość 1, bo chcemy każdy znak wyciągnąć pojedynczo. Zapis formuły powinien wyglądać następująco:
=FRAGMENT.TEKSTU(A2; WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1)
Wyniki funkcji zostały przedstawione na rys. nr 5.
Otrzymaliśmy tablicę, w której każdy znak z tekstu został umieszczony w osobnym wierszu. Teraz będziemy chcieli sprawdzić, czy te znaki znajdują się w przygotowanym przez nas wzorze (rys. nr 6).
Standardowo powinniśmy użyć funkcji ZNAJDŹ, jednak nie możemy tego zrobić, bo ona uwzględnia wielkość liter. Więc musimy użyć drugiej funkcji wyszukującej, czyli SZUKAJ.TEKST, która nie zwraca uwagi na wielkość liter. Pierwszym argumentem funkcji jest szukany_tekst, czyli litery uzyskane z funkcji FRAGMENT.TEKSTU. Drugi argument funkcji to obejmujący_tekst, czyli odwołanie do komórki I2 zablokowane bezwzględnie, gdzie wypisaliśmy wszystkie możliwe znaki. Zapis formuły powinien wyglądać następująco;
=SZUKAJ.TEKST(FRAGMENT.TEKSTU(A2; WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1);$I$2)
Po zatwierdzeniu formuły otrzymamy rozlane wartości, gdzie dana litera została znaleziona (rys. nr 7).
Otrzymaliśmy liczbę 20, która odpowiada literce A, potem L ma przypisaną liczbę 28, ponownie A, czyli 20. Następnie pojawia się błąd argumentu #ARG!, który wynika z tego, że w naszych wypisanych literach nie było spacji. Żeby pozbyć się tych błędów musimy użyć funkcji CZY.LICZBA, której argumentem jest wartość, czyli wyniki z naszej poprzedniej formuły. Zapis formuły powinien wyglądać następująco:
=CZY.LICZBA(SZUKAJ.TEKST(FRAGMENT.TEKSTU(A2; WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1); $I$2))
Otrzymamy wyniki z wartościami logicznymi przedstawione na rys. nr 8.
Otrzymaliśmy wynik PRAWDA wszędzie tam, gdzie wcześniej mieliśmy liczbę, a wynik FAŁSZ, kiedy w danych był błąd argumentu. Pozostaje nam policzyć wszystkie wartości logiczne PRAWDA. Użyjemy do tego funkcji SUMA.ILOCZYNÓW, ale najpierw musimy zamienić wartości logiczne na liczby 1 i 0 (1‑PRAWDA, 0 — FAŁSZ). Możemy to zrobić za pomocą podwójnej negacji, czyli wpisujemy przed formułą dwa znaki minus. Zapis formuły powinien wyglądać następująco:
=–CZY.LICZBA(SZUKAJ.TEKST(FRAGMENT.TEKSTU(A2; WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1); $I$2))
Po zatwierdzeniu formuły otrzymamy wyniki przedstawione na rys. nr 9.
Teraz wkładamy naszą formułę do funkcji SUMA.ILOCZYNÓW. Zapis formuły powinien wyglądać następująco:
=SUMA.ILOCZYNÓW(–CZY.LICZBA(SZUKAJ.TEKST(FRAGMENT.TEKSTU(A2; WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1); $I$2)))
Po zatwierdzeniu formuły i skopiowaniu na wiersze poniżej otrzymamy ilość znaków w każdym wierszu, co widać na rys. nr 10.
Jeśli w formule nie chcemy mieć odwołania do komórki I2, to możemy wstawić tekst do naszej formuły w podwójnym cudzysłowie. Taka formuła też będzie działać prawidłowo. Zapis formuły powinien wtedy wyglądać następująco:
=SUMA.ILOCZYNÓW(–CZY.LICZBA(SZUKAJ.TEKST(FRAGMENT.TEKSTU(A2; WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1);"ĄĘÓŚŁŻŹĆŃQWERTYUIOPASDFGHJKLZXCVBNM")))
Jeśli chcielibyśmy, aby takie znaki jak pojedynczy cudzysłów, czy kropka, przecinek itp. Też się zliczały, wystarczy, że dopiszemy je do naszego ciągu znaków z komórki I2.
Podsumowując, dzięki temu, że korzystamy tutaj z funkcji SZUKAJ.TEKST wystarczy jeden raz wypisać wszystkie litery. Nie ma znaczenia, czy są to wielkie, czy małe litery.
