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 — Listy rozwijane zależne czyszczenie przy zmianach — porada 384

W tym poście nauczymy się jak przy listach rozwijanych zależnych sprawić, żeby lista zależna od listy głównej odpowiednio się podświetlała, jeśli jest wypełniona nieprawidłową wartością. W naszym przykładzie mamy dwie listy: w jednej możemy wybrać Region (Centrum, Wschód, Zachód, Północ i Południe), a w drugiej Sprzedawcę. Istotne jest, że w każdym regionie mamy innych sprzedawców. Jeśli zmienimy region, to nazwa sprzedawcy podświetli się (pokaże, że mamy błąd wynikający z tego, że danego sprzedawcy nie ma w wybranym przez nas regionie). Widać to na rys. nr 1.

Rys. nr 1 – podświetlony sprzedawca niewystępujący w danym regionie

Zagadnienie to omówimy na podstawie przykładowych danych z rys. nr 2.

Rys. nr 2 – przykładowe dane

Naszym zadaniem jest sprawienie, aby komórka z elementem listy zależnej była albo podświetlona albo wyczyszczona po zmianie regiony na głównej liście. Rozwiązanie z podświetleniem nazwy sprzedawcy uzyskamy za pomocą formatowania warunkowego. Natomiast rozwiązanie z czyszczeniem komórki otrzymamy przy użyciu kodu VBA.

Mamy przygotowane wcześniej listy (główną i zależną) oraz powiązaną z tymi listami tabelę, która wyświetla dane dla wybranych wartości. Tabelki te zostały przedstawione na rys. nr 3.

Rys. nr 3 – listy rozwijane

Najpierw musimy sprawdzić czy dana pozycja występuje. Do tego wykorzystamy funkcję PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, czyli komórka z aktualnie wybranym sprzedawcą (u nas G2). Drugi argument funkcji to przeszukiwana_tab, czyli kolumna Sprzedawcy (K2:K6). Trzeci argument to typ_porównania, wybieramy dopasowanie dokładne, czyli wpisujemy wartość 0. Zapis formuły powinien wyglądać następująco:

=PODAJ.POZYCJĘ(G2;K2:K6;0)

Jeśli danego sprzedawcy nie będzie w wybranym regionie to funkcja PODAJ.POZYCJĘ wyświetli nam błąd #N/D (rys. nr 4), co dla formatowania warunkowego oznacza, że nie nakładamy żadnego formatowania.

Rys. nr 4 – błąd wyświetlany przez funkcję PODAJ.POZYCJĘ

Jeśli zmienimy sprzedawcę na takiego, który występuje w wybranym regionie funkcja PODAJ.POZYCJE zwróci nam wartość 1, 2 itd.

Formatowanie warunkowe liczby całkowite różne od zera, traktuje jako wartość logiczną PRAWDA. Za pomocą formatowania warunkowego sprawimy, że kolorystyka z komórki G2 zniknie. Mamy napisaną formułę, którą wykorzystamy w formatowaniu warunkowym (zapis funkcji PODAJ.POZYCJĘ). Kopiujemy zapis formuły w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C. Zaznaczamy komórkę G2 i rozwijamy polecenie Formatowanie warunkowe (punkt 2 na rys. nr 5) z karty Narzędzia główne (punkt 1), następnie wybieramy polecenie Nowa reguła (punkt 3).

Rys. nr 5 – polecenie Nowa reguła

Otworzy nam się okno Nowej reguły formatowania, gdzie w polu Wybierz typ reguły, wybieramy opcję Użyj formuły do określenia komórek, które należy sformatować (punkt 1 na rys. nr 6). Następnie w polu Edytuj opis reguły wklejamy wcześniej skopiowaną formułę (punkt 2). Teraz wybieramy przycisk Formatuj, aby wybrać rodzaj formatowania (punkt 3).

Rys. nr 6 – okno Nowej reguły formatowania

Otworzy nam się okno Formatowania komórek, gdzie przechodzimy na zakładkę Wypełnienie (punkt 1 na rys. nr 7), następnie wybieramy w polu Kolor tła Brak koloru (punkt 2). Takie ustawienie formatowania zatwierdzamy przyciskiem OK.

Rys. nr 7 – okno Formatowania komórek

Excel wróci do okna Nowa reguła formatowania, które należy zatwierdzić przyciskiem OK.

Jak widać na rys. nr 8, Stanisław jest sprzedawcą z regionu Północ, więc formatowanie warunkowe zadziałało w ten sposób, że zmieniło kolor na Brak wypełnienia.

Rys. nr 8 – brak wypełnienia w komórce G2

Jeśli teraz w komórce G1 wybierzemy np. Zachód, nazwa Sprzedawcy podświetli się nam znowu na czerwono. Wynika to z tego, że funkcja PODAJ.POZYCJĘ zwróciła błąd (komórka H1) – rys. nr 9.

Rys. nr 9 – podświetlenie sprzedawcy nie występującego Zachód

W ten sposób uzyskaliśmy efekt podświetlania kolorystycznego w zależności czy wybrany sprzedawca występuje w danym regionie. Pokazaliśmy jak rozwiązać taki problem za pomocą formatowania warunkowego.

W drugim rozwiązaniu będzie nam potrzebny kod VBA, który jest relatywnie prosty w porównaniu do niektórych kodów. Jednak żeby dobrze go zrozumieć, trzeba się trochę orientować w VBA. Aby przejść do języka VBA korzystamy ze skrótu klawiszowego Alt+F11. Otworzy nam się okno VBA pokazane na rys. nr 10.

Rys. nr 10 – okno VBA

Interesuje nas sytuacja, kiedy zmienimy wartość w komórce G1, czyli Region. W VBA chcemy uzyskać rozwiązanie, które będzie polegało na tym, żeby komórka G2 wyczyściła się ,kiedy zmienimy wartość w komórce G1. Czyli kiedy zmienimy region, chcemy aby komórka zawierająca nazwę sprzedawcy została wyczyszczona.

Pracujemy na arkuszu p384_k, więc w panelu bocznym VBA znajdujemy odpowiednią nazwę (rys. nr 11) i klikamy na nią dwukrotnie.

Rys. nr 11 – wybór odpowiedniego arkusza

Otworzy się nam moduł kodu VBA dla tego arkusza, w którym będziemy pisać kod. W pierwszym polu zamiast domyślnego General wybieramy Worsheet (punkt 1 na rys. nr 12), a w drugiem zamiast SelectionChange wybieramy z listy rozwijanej Change (punkt 2).

Rys. nr 12 – zmiana ustawień kodu dla wybranego arkusza

Ostatnie dwie linijki kodu możemy skasować, są zbędne. Będziemy chcieli zastosować znany kod na przecięcie elementu/ wartości. Kod ten wykorzystuje funkcję Intersect, dla której podajemy dwa zakresy i sprawdzamy czy one się przecięły. Pierwszym naszym zakresem będzie wartość Target, czyli jeśli zmieni się arkusz, to jaki zakres w tym arkuszu ulegnie zmianie. Target to w skrócie zakres, który uległ zmianie. Nasze zadanie polega na sprawdzeniu, czy ten zakres ma część wspólną z drugim zakresem (z komórką G1). Drugi zakres zapisujemy jako Range("G1"). Cały zapis funkcji powinien wyglądać następująco:

Intersect(Target, Range("G1"))

Funkcja ta, jeśli znajdzie część wspólną, zwróci właśnie tą wartość. Jeśli zakresy nie będą miały części wspólnej funkcja zwróci Nothing, czyli pustą komórkę.

Ciężej jest zorientować się jaki zakres zwraca ta funkcja, więc musimy sprawdzić czy nasza funkcja zwraca nic. W tym celu do zapisu funkcji należy dopisać sformułowanie "is nothing". Zapis powinien wyglądać następująco:

Intersect(Target, Range("G1")) is nothing

Jeśli funkcja zwraca nothing, oznacza to, że przecięcia nie było. Nas interesuje odwrotna sytuacja, więc przed nazwą funkcji Intersect musimy dopisać Not. Zapis powinien wyglądać następująco:

Not (Intersect(Target, Range("G1")) is Nothing)

Wykonamy teraz prosty test logiczny, czyli przed zapisem wpisujemy if, natomiast po Then. Pod zapisem tej formuły wstawiamy zakończenie funkcji If w postaci zapisu End If.

Jeśli funkcja Intersect zwraca nic, oznacza to, że nasze zakresy nie mają przecięcia. W takiej sytuacji chcemy otrzymać pusty ciąg znaków (""). Zapis powinien wyglądać następująco (rys. nr 13):

If Not (Intersect(Target, Range("G1")) is Nothing)  Then

      Range("G2") = ""

End If

Rys. nr 13 – kod VBA wykorzystujący funkcję If

Możemy łatwo sprawdzić działanie tego kodu. Dla pewności zapisujemy dane z kodu za pomocą skrótu klawiszowego Ctrl+S. Przechodzimy do naszego arkusza w Excelu, gdzie zmieniamy nazwę regionu. Jak widać na rys. nr 14, zmiana nazwy regionu na taki, w którym nie ma aktualnego sprzedawcy (brak przecięcia zakresów) sprawiło, że komórka G2 została wyczyszczona.

Rys. nr 14 – wyczyszczona komórka G2 po zmianie regionu w komórce G1

Możemy pójść o krok dalej w kodzie VBA i np. zamiast wyczyszczenia komórki (pusty ciąg znaków), możemy podać tekst "Podaj sprzedawcę". Zapis kodu wyglądałby wtedy następująco:

If Not (Intersect(Target, Range("G1")) is Nothing)  Then

      Range("G2") = "Podaj sprzedawcę"

End If

Wtedy po zmianie region w komórce G1, w komórce G2 wyświetli się napis Podaj sprzedawcę, co widać na rys. nr 15.

Rys. nr 15 – wyświetlenie napisu Podaj sprzedawcę

Jest mały problem, kiedy chcemy zmienić nazwę regionu i się rozmyślimy. VBA automatycznie po rozwinięciu listy rozwijanej (mimo że nie wybierzemy innego regionu) zadziała i wyświetli nam napis Podaj sprzedawcę.

Teraz wykonamy bardziej zaawansowane działania w celu zabezpieczenia się przed taką sytuacją. Przechodzimy do okna VBA, w którym pisaliśmy nasz kod. Możemy użyć funkcji Find. Najpierw musimy określić zakres, na którym ta funkcja ma działać(w naszym przykładzie K2:K8). Funkcja Find będzie szukała naszego wybranego sprzedawcy, czyli musimy podać zakres (Range("G2")). Jak funkcja Find znajdzie wybranego sprzedawcę to powinna zwracać odpowiedni zakres, natomiast jeśli nie znajdzie, powinna zwracać nothing, czyli nic. Zapis całej formuły powinien wyglądać następująco (rys. nr 16):

If Not (Intersect(Target, Range("G1")) is Nothing)  Then

      If Range("K2:K8").Find (Range("G2")).Value, LookIn:=xlValues) is Nothing   Then

           Range("G2") = "Podaj sprzedawcę"

      End If

End If

Rys. nr 16 – kod VBA

Zastosowanie funkcji Find w funkcji If sprawiło, że wyświetli się napis Podaj sprzedawcę tylko wtedy, kiedy faktycznie ta zmiana w komórce G1 nastąpi.


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 — Funkcja ILE.NIEPUSTYCH wszystko co powinieneś wiedzieć — porada 392

W tym poście poznasz wszystko, co powinieneś wiedzieć o funkcji ILE.NIEPUSTYCH. Zagadnienie to omówimy na podstawie przykładowych danych z rys. nr 1.

Rys. nr 1 – przykładowe dane

Funkcja ILE.NIEPUSTYCH zlicza niepuste komórki w podanym zakresie lub tablicy. Nie ma tutaj znaczenia czy w komórce mamy liczbę, tekst, czy liczbę zapisaną jako tekst. Jeśli w komórce są jakieś dane, funkcja je zliczy.

Możemy również w argumentach funkcji dodawać wartości zahardkorowane. Również w takim przypadku funkcja ILE.NIEPUSTYCH je zliczy (rys. nr 2). Zapis dodanych w argumentach funkcji wartości powinien wyglądać następująco:

=ILE.NIEPUSTYCH(A4:B4;"1")

Rys. nr 2 – zliczanie wartości podanych w argumentach funkcji

Analogicznie możemy do argumentu funkcji wpisać tablicę danych. Zapis takiej formuły powinien wyglądać następująco:

=ILE.NIEPUSTYCH(A4:B4;"1";{1;2;3;"A"})

Po zatwierdzeniu powyższej formuły funkcja doliczy nam elementy tablicy, co widać na rys. nr 3. Otrzymamy 7 elementów.

Rys. nr 3 – zliczanie elementów tablicy

Funkcja ILE.NIEPUSTYCH potrafi również zliczać datę i czas. Doskonale radzi sobie również ze zliczaniem wartości logicznych PRAWDA i FAŁSZ (rys. nr 4).

Rys. nr 4 – zliczanie dat, czasu oraz wartości logicznych

Funkcja ta zlicza oczywiście wszystkie rodzaje wartości liczbowych. Zlicza również błędy, nie są one ignorowane, jak to często bywa w Excelu (rys. nr 5).

Rys. nr 5 – zliczanie wartości liczbowych oraz błędów w Excelu

Teraz zajmiemy się pustymi komórkami. Mamy przykład w wierszu nr 14, gdzie komórki wyglądają na puste, a jednak funkcja ILE.NIEPUSTYCH zwraca nam wartość 1. Wynika to z tego, że w jednej z komórek mamy zapisaną formułę dla pustego ciągu tekstowego. Taki zapis wygląda następująco: ="" (rys. nr 6).

Rys. nr 6 – komórka wyglądająca na pustą

Dodatkowo jeśli skopiujemy taki zapis, następnie klikniemy na komórkę obok prawym przyciskiem myszy i z podręcznego menu wybierzemy polecenie Opcje wklejania a następnie Wartość  (rys. nr 7), to funkcja ta zliczy kolejny element.

Rys. nr 7 – polecenie Opcje wklejania

Otrzymamy wtedy zliczone 2 komórki przedstawione na rys. nr 8.

Rys. nr 8 – zliczone komórki zawierające formułę pustego ciągu tekstowego

Jest to często mylące, ponieważ nie zawsze możemy sprawdzić każdą komórkę osobno, czy na pewno jest pusta. Możemy mieć w komórce np. wynik funkcji logicznej, która może zwracać pusty ciąg tekstowy. Wtedy mimo, że komórka wizualnie wygląda na pustą, zostanie zliczona w funkcji ILE.NIEPUSTYCH jako wypełniona komórka.

Ponadto, gdy zrobimy test logiczny, która odwołuje się do pustej komórki, to funkcja ILE.NIEPUSTYCH policzy nam tą komórkę z formułą. Zapis takiego testu powinien wyglądać następująco:

=JEŻELI(A13="Puste";B15)

Otrzymamy wtedy zwróconą wartość 1, czyli komórkę z formułą (rys. nr 9).

Rys. nr 9 – zliczanie komórek z formułą

Dodatkowo musimy zwrócić uwagę, że wpisane w komórkę dwa podwójne cudzysłowy to nie jest pusty ciąg tekstowy, a zwykły tekst, czyli taka komórka nie jest pusta dla funkcji ILE.NIEPUSTYCH. Pusty ciąg tekstowy musimy poprzedzić znakiem równa się (=).

Podsumowując, funkcja ILE.NIEPUSTYCH liczy ilość wypełnionych komórek w zakresach. Tak jak przy innych funkcjach mamy ograniczoną ilość takich zakresów (maksymalnie 255). Ponadto są zliczane błędy oraz wyniki funkcji zwracające pusty ciąg tekstowy (rys. nr 10).

Rys. nr 10 – podsumowanie właściwości funkcji ILE.NIEPUSTYCH

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 — Funkcja ZEZWALAJ LET spojrzenie Bill Szysz — porada 391

W tym poście zajmiemy się bardzo skomplikowanym rozwiązaniem wykorzystującym funkcje ZEZWALAJ  (angielskie LET).  Zadanie to omówimy na podstawie rozwiązania Bila Szysza, który jest znany z rozwiązywania bardzo skomplikowanych formuł, zajmujących niekiedy kilka linijek i wykorzystujących wiele funkcji.

Wykorzystane tutaj zostaną takie funkcje jak:

  • ZEZWALAJ
  • INDEKS
  • ADR.POŚR
  • JEŻELI
  • WYBIERZ
  • ILE.WIERSZY
  • LICZBA.KOLUMN
  • MOD
  • SEKWENCJA
  • FILTRUJ
  • NIE
  • CZY.BŁĄD

Bill Szysz przygotował rozwiązanie do zadania wymyślonego przez Mike’a Girvina dostępnego pod linkiem https://www.youtube.com/watch?v=Q9NjdogVBo8 w języku angielskim.

Zadanie polega na łączeniu różnych zakresów, a dokładniej różnych list danych w jedną. Jest to bardzo skomplikowane rozwiązanie (rys. nr 1).

rys. nr 1 — skomplikowane rozwiązanie

Zachęcam do obejrzenia filmu w celu poznania szczegółów tego rozwiązania. W tym filmie przetłumaczyłem wykorzystane formuły i wyjaśniam dokładnie ich mechanizm oraz zależności między poszczególnymi funkcjami. Pojedynczo stosowane funkcje są raczej proste, cała trudność polega na odpowiednim połączeniu wielu funkcji w jedną spójną całość.


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 — Funkcja ILE.LICZB wszystko co powinieneś wiedzieć porada 390

W tym poście poznamy wszystko, co musicie wiedzieć o funkcji ILE.LICZB, aby jej swobodnie używać i mieć pewność, że zwraca nam poprawne wyniki. Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.

Rys. nr 1 – przykładowe dane

Wydawało by się, że to prosta funkcja do obliczania ilości konkretnych liczb w komórkach, ale w Excelu z różnymi liczbami zdarzają się problemy. Na przykład jeśli zapiszemy liczbę z kropką (.), to Excel nie rozpozna tej liczby i potraktuje ją jako tekst. Prawidłowy zapis liczb w polskim Excelu używa przecinka do oddzielenia miejsc dziesiętnych (,). Jeśli za pomocą funkcji PODSTAW zamienimy w tej liczbie kropkę na przecinek, to Excel zapisze tę liczbę prawidłowo, ale nadal będzie traktował ją jako tekst (rys. nr 2). To, że Excel rozpoznaje liczbę jako tekst możemy łatwo rozpoznać, ponieważ wyrównuje ją do lewej strony komórki.

Rys. nr 2 – zamiana kropki na przecinek przy użyciu funkcji PODSTAW

Jeśli zapiszemy funkcję ILE.LICZB dla tego zakresu, nie zwróci ona żadnej wartości (rys. nr 3). Zapis takiej funkcji powinien wyglądać następująco: ILE.LICZB(A2:B2).

Rys. nr 3 – funkcja ILE.LICZB dla tekstu zwraca wartość 0 (zero)

Istotne jest, że wszystkie funkcje tekstowe zwracają tekst, a nie liczbę. Aby uzyskać z tekstu wartość liczbową, musimy wykonać na nim dowolne działanie matematyczne, np. do funkcji PODSTAW dodać wartość zero. Zapis formuły powinien wyglądać następująco:

=PODSTAW(A2:B2)+0

Po zatwierdzeniu takiej formuły tekst zostanie zamieniony na liczbę i wtedy funkcja ILE.LICZB zadziała prawidłowo (rys. nr 4).

Rys. nr 4 – działanie funkcji ILE.LICZB po zamianie tekstu na liczbę

Interesujące jest, że jeśli w argumencie funkcji ILE.LICZB zapiszemy wartość "1" (w podwójnych cudzysłowach), to Excel ją znajdzie, zostanie ona zliczona. Zapis formuły powinien wyglądać następująco:

=ILE.LICZB(A2:B2;"1")

Po zatwierdzeniu formuły zwróci nam ona kolejną wartość (rys. nr 5).

Rys. nr 5 – działanie formuły z zapisem wartości liczbowej jako tekst

W przykładowych danych w wierszu nr 4, mamy zapisane liczby jako tekst (w podwójnych cudzysłowach lub poprzedzone pojedynczym cudzysłowem). Sprawia to, że funkcja ILE.LICZB nie wykrywa żadnych wartości liczbowych i zwraca wartość zero (0). Widać to na rys. nr 6.

Rys. nr 6 – działanie funkcji ILE.LICZB w stosunku do liczb zapisanych jako tekst

Funkcja ILE.LICZB jest w stanie policzyć daty i czas (wiersz nr 6) w przykładowych danych). Dla Excela data i czas to nic innego jak liczby, co widać na rys. nr 7 po zmianie formatowania z Daty na Ogólne.

Rys. nr 7 – data i czas w postaci liczb i działanie funkcji ILE.LICZB

Kolejny omawiany przykład dotyczy wartości logicznych PRAWDA i FAŁSZ. Funkcja ILE.LICZB nie liczy takich wartości, nie interpretuje tych wartości jako 1 (PRAWDA) i 0 (FAŁSZ). Ale jeżeli wpiszemy wartość logiczną np. PRAWDA, jako jeden z argumentów funkcji, to funkcja go zliczy. Zapis formuły powinien wyglądać następująco:

=ILE.LICZB(A8:B8;PRAWDA)

Po zatwierdzeniu takiej formuły funkcja ILE.LICZB zwróci nam wartość 1 (rys. nr 8).

Rys. nr 8 – działanie funkcji ILE.LICZB w odniesieniu do wartości logicznych

Jest to dla nas istotne, ponieważ mamy często do czynienia z funkcjami logicznymi, np. funkcją JEŻELI, która zwraca wartości logiczne (PRAWDA lub FAŁSZ). Zapiszmy jako kolejny argument funkcji ILE.LICZB funkcję JEŻELI dla prostego testu czy 1>2. Jeżeli warunek jest spełniony chcemy aby funkcja zwróciła wartość logiczną Prawda, a jeżeli nie – Fałsz. Zapis formuły powinien wyglądać następująco:

=ILE.LICZB(A8:B8;PRAWDA;JEŻELI(1>2;Prawda, Fałsz))

Po zatwierdzeniu powyższej formuły funkcja ILE.LICZB zliczy kolejną wartość (rys. nr 9).

Rys. nr 9 – zliczanie wartości logicznej z funkcji JEŻELI

Istotne jest, że z np. funkcji JEŻELI musimy otrzymać pojedynczą wartość, ponieważ funkcja ILE.LICZB nie zlicza tablic. Czyli funkcja liczb zliczy nam wyniki testów logicznych, ale jeżeli zrobimy operację która zamieni zwykły test logiczny na tablicę, to funkcja ILE.LICZB nie zliczy tego wyniku. Zapis takiej formuły powinien wyglądać następująco:

=ILE.LICZB(A2:B2;{2}>3)

Po zatwierdzeniu takiej formuły otrzymamy dane przedstawione na rys. nr 10, w których funkcja ILE.LICZB nie zliczyła wyniku testu.

Rys. nr 10 – nie zliczanie tablic przez funkcję ILE.LICZB

Standardowe liczby są zliczanie bezproblemowo przez funkcję ILE.LICZB, co widać na rys. nr 11.

Rys. nr 11 – zliczanie standardowych liczb

W sytuacji, kiedy w danych mamy błędy, to w wyniku funkcji ILE.LICZB są one ignorowane. (rys. nr 12). Ważne jest, że funkcja ta zwraca wartość 0, a nie błąd.

Rys. nr 12 – ignorowanie błędów przez funkcję ILE.LICZB

Analogicznie wygląda sytuacja, kiedy w danych mamy puste komórki. Wtedy funkcja ILE.LICZB je ignoruje i zwraca wartość 0 (rys. nr 13).

Rys. nr 13 – ignorowanie pustych komórek przez funkcję ILE.LICZB

Tak samo zostanie zignorowany pusty ciąg tekstowy zapisany "" (dwoma podwójnymi cudzysłowami). Ale może się zdarzyć sytuacja, że zapiszemy kolejny argument funkcji ILE.LICZB i postawimy znak ; (średnik), a następni8e zamkniemy nawias formuły. Zapis takiej funkcji wyglądałby następująco:

=ILE.LICZB(A14:B14;"";)

Po zatwierdzeniu takiej formuły okaże się, że funkcja ILE.LICZB zliczy nam jedną pustą komórkę, ponieważ zinterpretuje pusty argument jako wartość zero (rys. nr 14).

Rys. nr 14 – zliczenie pustego argumentu przez funkcję ILE.LICZB

Co istotne, funkcja ILE.LICZB może pracować na wielu zakresach. Jeżeli będziemy chcieli zliczyć wszystkie występujące w danych liczby, wystarczy że w argumentach funkcji podamy poszczególne zakresy. Funkcja ta może pracować na 255 zakresach, podobnie jak funkcja SUMA. Zapis takiej formuły powinien wyglądać następująco:

=ILE.LICZB(A2:B2;A4:B4;A6:B6;A8:B16)

Po zatwierdzeniu powyższej formuły otrzymamy wynik przedstawiony na rys. nr 15.

Rys. nr 15 – zliczanie liczb z wielu zakresów

Funkcja ILE.LICZB nie ma skrótu klawiszowego jak funkcja SUMA, aby policzyć od razu całe zakresy (kolumny i wiersze w tabeli), ale w karcie Narzędzia główne w grupie poleceń autosumowania mamy do niej dostęp. Zaznaczamy zakres tabeli, jaki chcemy zliczyć, rozwijamy polecenie Autosumowanie (punkt 2 na rys. nr 16) z karty Narzędzia główne (punkt 1), a następnie wybieramy polecenie Zliczanie (punkt 3).

Rys. nr 16 – ścieżka dostępu do polecenia Zliczanie

Otrzymamy zliczone liczby w zaznaczonym zakresie danych przedstawione na rys. nr 17.

Rys. nr 17 – zliczone dane

Dzięki wykorzystaniu polecenia Zliczanie, możemy podobnie jak w funkcji SUMA, zliczyć wartości w komórkach, w poszczególnych wierszach i kolumnach. Według naszych danych z rys. nr 17 możemy łatwo sprawdzić ile miesięcy pracuje dla nas np. Ewa (zliczone wiersze) lub ile osób pracowało dla nas w Marcu (zliczone kolumny).

Musimy zwrócić uwagę, na komórkę K14, w której nie zliczamy wszystkich danych, tak jak to robiła funkcja SUMA.  Tutaj funkcja ILE.LICZB patrzy tylko na ostatni wiersz, dlatego mamy wynik 4. Zapis funkcji w tej komórce wygląda następująco:

=ILE.LICZB(G14:J14)

Z tego zapisu wynika, że funkcja podlicza ilość liczb w ostatnim wierszu a nie w całym zakresie. Aby uzyskać informację o ilości liczb w całym zakresie musimy ręcznie zmienić zakres w argumencie funkcji. Zapis formuły powinien wyglądać następująco:

=ILE.LICZB(G2:J13)

Po zatwierdzeniu takiej formuły otrzymamy sumaryczną ilość liczb w całym zakresie danych przedstawioną na rys. nr 18.

Rys. nr 18 – zliczona ilość liczb w całym zakresie danych

Na rys. nr 19 mamy przedstawione podsumowanie funkcji ILE.LICZB. Funkcja ta liczy ilość komórek z liczbami w zakresach danych. Potrafi zliczyć liczby, daty, czas. Pomija tekst, liczby zapisane jako tekst, wartości logiczne, błędy oraz puste komórki. Funkcja ta nie ma skrótu klawiszowego, ale można ją znaleźć w grupie poleceń Autosumowanie na karcie Narzędzia główne.

Rys. nr 19 – najważniejsze informacje o funkcji ILE.LICZB


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