0
0 Produkty w koszyku

No products in the cart.

Excel — Ile liter w tekście — porada 420

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.

Rys. nr 1 – przykładowe dane

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)

Rys. nr 2 – właściwość funkcji WIERSZ

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.

Rys. nr 3 – podejrzany wynik formuły

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.

Rys. nr 4 – rozlane wyniki funkcji WIERSZ

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.

Rys. nr 5 – wyniki funkcji FRAGMENT.TEKSTU

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).

Rys. nr 6 – przygotowany wzór wszystkich występujących w języku polskim znaków

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).

Rys. nr 7 – wyniki funkcji SZUKAJ.TEKST

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.

Rys. nr 8 – wyniki funkcji CZY.LICZBA

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.

Rys. nr 9 – wartości logiczne zamienione na liczbowe

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.

Rys. nr 10 – ilość liter w każdym wierszu

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.

Excel — Podsumowania poniżej rozlanego zakresu — porada 395

W tym poście nauczymy się jak w rozlewającej się formule dołożyć dodatkowe obliczenia podsumowania danych.

Odcinek bazuje na formule od Billa Szysza (rys. nr 1).

Rys. nr 1 – formuła Billa Szysza
Rys. nr 1 – formuła Billa Szysza

Powyższą formułę przetłumaczyłem na język polski, żeby ułatwić jej zrozumienie (rys. nr 2).

Rys. nr 2 – formuła przetłumaczona na język polski
Rys. nr 2 – formuła przetłumaczona na język polski

Mamy przykład, w którym wyciągamy konkretną liczbę ostatnich elementów, u nas 5. Ponadto jest dopisana formuła pozwalająca na dodatkowe obliczenia na tych danych, takie jak suma, średnia, min i max. Przede wszystkim możemy wybrać sobie jakie podsumowania nas interesują (fragmentator). Jak widać na rys. nr 2 formuła jest długa, mimo wykorzystania nowej funkcji ZEZWALAJ. Wykorzystujemy tutaj takie funkcje jak: TRANSPONUJ, LICZBA.KOLUMN, INDEKS, JEŻELI, SEKWENCJA, ILE.WIERSZY, SUMA, WYBIERZ, ŚREDNIA, MIN, MAX, MOD, NIE, CZY.BŁĄD, FILTRUJ, CZY.LICZBA, PODAJ.POZYCJĘ.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Excel — Funkcje CZY TEKST, CZY LICZBA itp — porada 393

W dzisiejszym poście omówimy najważniejsze zagadnienia związane z funkcjami z rodziny CZY. Poznasz rodzaje funkcji z tej rodziny oraz ich zastosowanie i właściwości (CZY.BŁĄD, CZY.LICZBA, CZY.TEKST itp.).

Wszystkie te funkcje działają na podobnych zasadach, czyli sprawdzają, czy dana wartość jest czymś konkretnym, np. liczbą, tekstem czy błędem itp. Funkcje z rodziny CZY w wyniku zwracają wartości logiczne PRAWDA lub FAŁSZ.

W przykładowych danych mamy rozpisane wartości logiczne zwracane przez różne funkcje z rodziny CZY w zależności od danych, które są sprawdzane (rys. nr 1).

Rys. nr 1 – wartości zwracane przez poszczególne funkcje z rodziny CZY
Rys. nr 1 – wartości zwracane przez poszczególne funkcje z rodziny CZY

Na początek zajmiemy się funkcjami dotyczącymi błędów takimi jak CZY.BŁ, CZY.BŁĄD, CZY.BRAK. Funkcje te będą zwracać wartości logiczne FAŁSZ, kiedy będą miały do czynienia z poprawnymi danymi (rys. nr 2).

Rys. nr 2 – zwracana wartość logiczna FAŁSZ dla poprawnych danych
Rys. nr 2 – zwracana wartość logiczna FAŁSZ dla poprawnych danych

Z kolei wartość logiczną PRAWDA zwrócą tylko w sytuacji, kiedy w danych będziemy mieli błędy (rys. nr 3).

Rys. nr 3 – zwracane wartości logiczne w odniesieniu do błędów w danych
Rys. nr 3 – zwracane wartości logiczne w odniesieniu do błędów w danych

Funkcja CZY.BŁĄD zwraca wartość logiczną PRAWDA dla każdego rodzaju błędu w Excelu, natomiast funkcje CZY.BŁ i CZY.BRAK rozpoznają inne rodzaje błędów.

Teraz omówimy funkcję CZY.FORMUŁA, która zwraca wartość logiczną PRAWDA wszędzie tam, gdzie w sprawdzanej komórce jest formuła, czyli w uproszczeniu zapis zaczyna się od znaku = (równa się). Natomiast wartość logiczną FAŁSZ otrzymamy wszędzie tam, gdzie nie mamy do czynienia z formułą (rys. nr 4).

Rys. nr 4 – wartości logiczne zwracane przez funkcję CZY.FORMUŁA
Rys. nr 4 – wartości logiczne zwracane przez funkcję CZY.FORMUŁA

Kolejna funkcja CZY.LOGICZNA sprawdza, czy wynikiem są wartości logiczne. Kiedy wynikiem jest wartość logiczna, funkcja CZY.LOGICZNA zwraca nam wartość logiczną PRAWDA, w przeciwnym razie otrzymamy wartość logiczną FAŁSZ. Jak widać na rys. nr 5 wartość logiczną PRAWDA otrzymaliśmy tylko dla wartości logicznych, we wszystkich pozostałych przypadkach funkcja zwróciła FAŁSZ.

Rys. nr 5 – wartości logiczne zwracane przez funkcje CZY.LOGICZNA
Rys. nr 5 – wartości logiczne zwracane przez funkcje CZY.LOGICZNA

Kolejną omawianą funkcją będzie CZY.LICZBA. Tutaj pojawiają się drobne problemy, ponieważ np. liczba zapisana jako tekst dla tej funkcji nie jest liczbą i w efekcie zwraca wartość logiczną FAŁSZ. Natomiast kiedy mamy do czynienia z datą lub godziną, funkcja CZY.LICZBA  zwróci wartość logiczną PRAWDA, ponieważ dla Excela data i godzina są liczbami przedstawionymi w innej postaci po użyciu odpowiedniego formatowania liczbowego (rys. nr 6).

Rys. nr 6 – wartości logiczne zwracane przez funkcję CZY.LICZBA
Rys. nr 6 – wartości logiczne zwracane przez funkcję CZY.LICZBA

Teraz omówimy funkcje CZY.NIEPARZYSTE i CZY.PARZYSTE, które są ze sobą powiązane i sprawdzają czy dane w komórkach są wartościami nieparzystymi lub parzystymi. W tych funkcjach często otrzymujemy w wynikach błędy #ARG!. Dzieje się tak w sytuacji, kiedy sprawdzana wartość nie jest w ogóle liczbą lub Excel nie potrafi sobie takich danych przekształcić na liczbę (rys. nr 7). Co ciekawe funkcje te będą sprawdzały liczby zapisane jako tekst mimo, że funkcja CZY.LICZBA zwracała w tej sytuacji wartość logiczną FAŁSZ, czyli nie brała ich pod uwagę. Istotne jest również, że te funkcje sprawdzają parzystość i nieparzystość zarówno liczb dodatnich jak i ujemnych. Obie funkcje pomijają część dziesiętną (po przecinku) w swoich wynikach. Funkcje te nie ignorują błędów w danych lecz zwracają wartości tożsame z błędami w sprawdzanych danych.

Rys. nr 7 – wartości logiczne zwracane przez funkcje CZY.NIEPARZYSTE i CZY.PARZYSTE
Rys. nr 7 – wartości logiczne zwracane przez funkcje CZY.NIEPARZYSTE i CZY.PARZYSTE

Ostatnimi omawianymi funkcjami będą CZY.TEKST, CZY.NIE.TEKST i CZY.PUSTA. Pierwsze dwie funkcje są swoją odwrotnością, czyli jeśli jedna funkcja zwraca wartość logiczną FAŁSZ to druga zwróci wartość logiczną PRAWDA i odwrotnie. Funkcje te rozpoznają również błędy. Istotne jest, że wyniki funkcji, które zwracają pusty ciąg tekstowy są rozpoznawane przez te funkcje i traktowane jako tekst, czyli funkcja CZY.TEKST zwróci wartość logiczną PRAWDA. W sytuacji kiedy mamy wklejony pusty ciąg tekstowy funkcja CZY.PUSTA zwróci wartość logiczną FAŁSZ, ponieważ taka komórka tak naprawdę nie jest pusta mimo, że nie wyświetla się żadna wartość (rys. nr 8). Funkcja CZY.PUSTE zwraca wartość logiczną PRAWDA tylko wtedy, gdy faktycznie mamy pustą komórkę, czyli nic nie jest w nią wpisane.

Rys. nr 8 – wartości logiczne zwracane przez funkcje CZY.NIE.TEKST, CZY.TEKST i CZY.PUSTA
Rys. nr 8 – wartości logiczne zwracane przez funkcje CZY.NIE.TEKST, CZY.TEKST i CZY.PUSTA

Podsumowując, wszystkie funkcje z rodziny CZY zwracają wartości logiczne PRAWDA lub FAŁSZ w zależności czy komórka / wartość spełnia określone w nich kryterium. Funkcjom tym można podać tylko jedną wartość, ale podana wartość może być zakresem bądź tablicą. W takiej sytuacji funkcje te będą zwracały analogicznie zakres bądź tablicę wartości logicznych PRAWDA i FAŁSZ.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Excel — Czy tekst zawiera cyfry — porada #378

W dzisiejszym poście nauczymy się sprawdzać dane tekstowe pod kątem tego, czy zawierają cyfry. Chodzi o to, by sprawdzić czy dany tekst (kod) zawiera cyfry. Jeśli tekst rzeczywiście zawiera cyfry, może to oznaczać, że był wydawany po określonej dacie albo dotyczy to konkretnego działu lub usługi. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.

Rys. nr 1 – przykładowe dane
Rys. nr 1 – przykładowe dane

W tym celu możemy użyć funkcji ZNAJDŹ, która pozwala wyszukać jeden tekst w innym lub funkcji SZUKAJ.TEKST, która działa na takiej samej zasadzie. Różnica między funkcjami polega na tym, że funkcja SZUKAJ.TEKST nie rozróżnia wielkości liter.

W naszym przykładzie wykorzystamy funkcję ZNAJDŹ, która ma krótszy zapis. Pierwszym argumentem funkcji jest szukany_tekst, czyli jakaś cyfra. Drugi argument funkcji to w_tekście, czyli komórka w jakiej chcemy znaleźć tą cyfrę (u nas komórka A2). Zapis funkcji powinien wyglądać następująco:

=ZNAJDŹ(1;A2)

Powyższą formułę zatwierdzamy i otrzymamy wynik przedstawiony na rys. nr 2.

Rys. nr 2 – wynik funkcji ZNAJDŹ
Rys. nr 2 – wynik funkcji ZNAJDŹ

Funkcja ZNAJDŹ zwróci nam numer pozycji szukaj cyfry, oczywiście pod warunkiem, że szukana cyfra pojawia się w przeszukiwanym tekście. Nas nie interesuje pojedyncza cyfra, tylko wiele cyfr. Ponadto jeśli funkcja ZNAJDŹ nie znajdzie szukanej cyfry to zwróci nam błąd argumentu — #ARG! (rys. nr 3).

Rys. nr 3 – błąd zwrócony przez funkcję ZNAJDŹ w sytuacji kiedy nie ma w tekście szukanej cyfry
Rys. nr 3 – błąd zwrócony przez funkcję ZNAJDŹ w sytuacji kiedy nie ma w tekście szukanej cyfry

Tak jak wspominaliśmy nie szukamy jednej cyfry, ale szukamy dowolnej cyfry, więc musimy stworzyć tablicę wszystkich możliwych cyfr i zapisać ją jako pierwszy argument funkcji. Ułatwieniem jest to że w funkcji wpisujemy konkretne cyfry a nie tekst. Zapis funkcji z tablicą powinien wyglądać następująco:

=ZNAJDŹ({0,1,2,3,4,5,6,7,8,9;A2)

Kolejność cyfr w zapisie nie ma znaczenia. Jeśli przy takim zapisie podejrzymy sobie wyniki funkcji w trybie edycji komórki za pomocą klawisza F9 otrzymamy tablicę wyników przedstawioną na rys. nr 4.

Rys. nr 4 – podgląd wyników funkcji ZNAJDŹ
Rys. nr 4 – podgląd wyników funkcji ZNAJDŹ

Funkcja ZNAJDŹ zwróci nam błędy w przypadku, kiedy danej cyfry nie znajdzie oraz pozycję (numer miejsca w tekście), kiedy dana cyfra wystąpi w tekście. Z podglądu wyników formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. Dla nas istotne jest tylko, czy cyfry zostały znalezione, a nie potrzebujemy pozycji danej cyfry.

Skorzystamy tutaj z funkcji CZY.LICZBA, która zwróci nam wartości logiczne PRAWDA i FAŁSZ. Zapis funkcji powinien wyglądać następująco:

=CZY.LICZBA(ZNAJDŹ({0,1,2,3,4,5,6,7,8,9;A2))

Kiedy podejrzymy sobie wyniki formuły w trybie edycji komórki za pomocą klawisza F9 otrzymamy tablicę wartości logicznych przedstawioną na rys. nr 5, gdzie w sytuacji kiedy cyfra zostanie znaleziona funkcja zwróci nam wartość logiczną PRAWDA, natomiast w odwrotnej sytuacji otrzymamy wartość logiczną FAŁSZ.

Rys. nr 5 – Wartości logiczne zwrócone przez funkcję CZY.LICZBA
Rys. nr 5 – Wartości logiczne zwrócone przez funkcję CZY.LICZBA

Z podglądu wyników formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. Wartości logiczne może być ciężko zliczyć, więc za pomocą podwójnej negacji zamieniamy tablicę wartości logicznych na tablicę wartości 0 i 1. Zapis funkcji powinien wyglądać następująco:

=–CZY.LICZBA(ZNAJDŹ({0,1,2,3,4,5,6,7,8,9;A2))

Kiedy podejrzymy sobie wyniki formuły w trybie edycji komórki za pomocą klawisza F9 otrzymamy tablicę wartości 0 i 1 przedstawioną na rys. nr 6.

Rys. nr 6 – tablica wartości 0 i 1 po zastosowaniu podwójnej negacji
Rys. nr 6 – tablica wartości 0 i 1 po zastosowaniu podwójnej negacji

Wartości logiczne FAŁSZ zostają zamienione na wartość 0, natomiast wartości logiczne PRAWDA na wartość 1. Z podglądu wyników formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. Na tym etapie wystarczy zsumować te wartości, korzystając z funkcji SUMA.ILOCZYNÓW. Używamy tej funkcji, aby nie trzeba było pamiętać, aby zatwierdzać naszą formułę, jako tablicową (skrót klawiszowy Ctrl+Shift+Enter). Zapis funkcji powinien wyglądać następująco:

=SUMA.ILOCZYNÓW(–CZY.LICZBA(ZNAJDŹ({0,1,2,3,4,5,6,7,8,9;A2)))

Otrzymamy wynik przedstawiony na rys. nr 7, czyli otrzymamy ilość znalezionych cyfr w tekście.

Rys. nr 7 – wynik funkcji SUMA.ILOCZYNÓW (ilość znalezionych cyfr w tekście)
Rys. nr 7 – wynik funkcji SUMA.ILOCZYNÓW (ilość znalezionych cyfr w tekście)

Po skopiowaniu formuły na wiersze poniżej otrzymamy wyniki przedstawione na rys. nr 8.

Rys. nr 8 – wyniki funkcji SUMA.ILOCZYNÓW dla całej tabeli
Rys. nr 8 – wyniki funkcji SUMA.ILOCZYNÓW dla całej tabeli

Co łatwo zauważyć w wierszu 6 pojawia się nam błąd. Wynika on z tego, że funkcja ta zwraca tylko pierwsze wystąpienie danej cyfry, więc w wierszu nr 6 mamy tylko 2 różne cyfry (0 i 1), funkcja nie bierze pod uwagę powtarzającej się cyfry 0. Podsumowując funkcja sprawdza czy w tekście jest dany znak, a nie ile razy on występuje.

Na koniec musimy zrobić test logiczny, żeby sprawdzić czy wartość otrzymana z naszej formuły jest większa od 0. Zapis powinien wyglądać następująco:

=SUMA.ILOCZYNÓW(–CZY.LICZBA(ZNAJDŹ({0,1,2,3,4,5,6,7,8,9;A2)))>0

Dzięki użyciu testu logicznego otrzymamy wartość logiczną PRAWDA dla sytuacji kiedy cyfry występują w tekście lub wartość logiczną FALSZ, kiedy cyfry nie występują (rys. nr 9).

Rys. nr 9 – sprawdzenie czy tekst zawiera cyfrę
Rys. nr 9 – sprawdzenie czy tekst zawiera cyfrę

Naszym zadaniem było sprawdzenie czy dany tekst zawiera jakąś cyfrę. Nie jest istotne ile jest tych cyfr oraz jaką cyfrę zawiera tekst.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Jak znaleźć elementy z listy A, które zawierają elementy z listy B formuła — widzowie #117

Załóżmy, że masz trudne zadanie wyciągnięcia wszystkich elementów listy A, które zawierają elementy z listy B. 

Widzowie 117 - Jak znaleźć wartości z listy A, które zawierają elementy listy B 01

Czyli w prostym przykładzie na liście B, mamy elementy „ewa” i „stefan”. Dlatego z listy A musimy wyciągnąć elementy „ewa” oraz „ewa mokra” bo zawierają element z listy B („ewa”). Musimy wyciągnąć również elementy „stefan” i „nowak stefan”, bo zawierają element z listy B („stefan”). Pozostałych elementów listy A nie wyciągamy, bo nie mają wspólnych części z listą B.
Ręcznie proces jest zrozumiały, ale czasochłonny przy dłuższych listach, jak więc to zrobić w Excelu, żeby wyciągnąć interesujące nas elementy za pomocą formuł w Excelu? (w filmie widzowie 118 – możesz zobaczyć jak zrobić to za pomocą filtrów zaawansowanych, co jest prostszą czynnością, ale nie może być dynamiczne jak formuły).

Będziemy potrzebowali kolumny pomocniczej. Zaczniemy od funkcji ZNAJDŹ, która pozwoli nam zidentyfikować, czy któryś z elementów listy B znajduje się w danym elemencie listy A.

=ZNAJDŹ($D$2:$D$3;A2)

Widzowie 117 - Jak znaleźć wartości z listy A, które zawierają elementy listy B 02

Funkcja ZNAJDŹ zwraca numer znaku od którego szukany tekst (element listy B) znajduje się w konkretnym elemencie listy A (odwołanie do komórki A2). Jeśli go nie znajdzie zwraca błąd argumentu.

Ponieważ szukamy elementów z listy (tablicy) funkcja ZNAJDŹ zwraca nam również listę (tablicę), jeśli podejrzymy jej wynik klawiszem F9 w trybie edycji komórki to zobaczymy 2 wyniki — {1\#ARG!} (tyle mamy elementów listy B). Oznacza to, że w pierwszym elemencie listy A został odnaleziony pierwszy element listy B (na 1 znaku), a drugi element listy B już nie został odnaleziony.

Nam zależy na tym, żeby wiedzieć, czy chociaż jeden element został odnaleziony, czyli musimy policzyć wszystkie liczby. Możemy to zrobić korzystając z funkcji CZY.LICZBA , która zwróci nam wartość PRAWDA czy podana jej wartość jest liczbą oraz wartość FAŁSZ, jeśli podana jej wartość nie jest liczba (np.: jest błędem). 

=CZY.LICZBA(ZNAJDŹ($D$2:$D$3;A2))

Widzowie 117 - Jak znaleźć wartości z listy A, które zawierają elementy listy B 03

Czyli dla pierwszego elementu będziemy mieli wynik {PRAWDA\FAŁSZ}. Wartości PRAWDA i FAŁSZ może być ciężko zliczyć poza tym chcemy uniknąć zatwierdzania naszej formuły jako formuły tablicowej, więc zamieniamy wartości logiczne na 0 i 1 za pomocą podwójnej negacji (dwóch znaków minus), a następnie wstawiamy jest do funkcji SUMA.ILOCZYNÓW, żeby zsumować całą listę naszych wyników.

=SUMA.ILOCZYNÓW(–CZY.LICZBA(ZNAJDŹ($D$2:$D$3;A2)))

Widzowie 117 - Jak znaleźć wartości z listy A, które zawierają elementy listy B 04

Teraz mamy 0 gdy, żaden element z listy B nie został odnaleziony w elemencie listy A, a wartości większe od 0 (u nas tylko 1), gdy chociaż jeden element z listy B został znaleziony w konkretnym elemencie listy A. Ale jeszcze nie skończyliśmy obliczeń, bo zwracane wartości (rys. 4) nie pomogą nam łatwo odnaleźć konkretnych elementów listy A, dlatego chcemy je zamienić na numer wiersza w liście A. Możemy to zrobić za pomocą funkcji JEŻELI i funkcji WIERSZ. Funkcja JEŻELI posłuży nam do tego, żeby dodatkowo rozróżnić interesujące nas elementy i te niechciane, bo jeśli wynik wcześniejszych obliczeń będzie większy od zera to zwróci numer wiersza (WIERSZ() – WIERSZ($A$1)), a jeśli będzie równy zero to zwróci FAŁSZ (trzeci argument funkcji JEŻELI pominięty.

=JEŻELI(SUMA.ILOCZYNÓW(–CZY.LICZBA(ZNAJDŹ($D$2:$D$3;A2)));WIERSZ()-WIERSZ($A$1))

Widzowie 117 - Jak znaleźć wartości z listy A, które zawierają elementy listy B 05

Jeśli funkcji WIERSZ nie podamy żadnej wartości, to zwróci numer wiersza arkusza, w którym aktualnie się znajduje, a ponieważ nam zależy na numerze wiersza listy A, a nie arkusza, to musimy jeszcze odjąć numer wiersza nagłówka (WIERSZ($A$1)).
Jak mamy już ponumerowane interesujące nas elementy, to wyciągnięcie ich jest relatywnie proste. Przede wszystkim będziemy potrzebować funkcji INDEKS, żeby wiedzieć skąd wyciągamy elementy oraz dodatkowo funkcji MIN.K lub MAX.K, żeby wyciągać poszczególne elementy po kolei. Jedynym odrobinę trudniejszym elementem jest wyznaczenie kolejnych ‘k’. Robi się to najczęściej za pomocą funkcji ILE.WIERSZY z dynamicznie rozrastającym się zakresem – pierwsza część zakresu jest zablokowana bezwzględnie, a druga względnie ($F$2:F2). Cała formuła przyjmie postać:

=INDEKS($A$2:$A$7;MIN.K($B$2:$B$7;ILE.WIERSZY($F$2:F2)))

Widzowie 117 - Jak znaleźć wartości z listy A, które zawierają elementy listy B 06

Pojawiać się tutaj będą błędy (#LICZBA!), gdy przeciągniemy formułę niżej niż mamy interesujących nas elementów. Najprościej poradzić sobie z tym funkcją JEŻELI.BŁĄD, ale jeśli nie chcemy, żeby Excel za każdym razem obliczał formułę wewnątrz funkcji JEŻELI.BŁĄD, to możemy policzyć wszystkie wartości większe od zera w kolumnie pomocniczej (=LICZ.JEŻELI($B$2:$B$7;">0")), a następnie sprawdzać, czy nie zeszliśmy już dalej niż jest interesujących nas elementów:

=JEŻELI(ILE.WIERSZY($F$2:F2)>$H$2;"";INDEKS($A$2:$A$7;MIN.K($B$2:$B$7;ILE.WIERSZY($F$2:F2))))

Widzowie 117 - Jak znaleźć wartości z listy A, które zawierają elementy listy B 07

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP