Excel — Zliczanie tylko tekstu, bez pustych ciągów tekstowych — porada 394

W tym poście nauczymy się jak zliczyć komórki zawierające tekst, ale nie puste ciągi tekstowe. Aby lepiej zrozumieć to zagadnienie dobrze było by obejrzeć dwie poprzednie porady o numerach 392 ( https://exceliadam.pl/excel/excel-funkcja-ile-niepustych-wszystko-co-powinienes-wiedziec ) i 393 ( https://exceliadam.pl/excel/excel-funkcje-czy-tekst-czy-liczba-itp-porada-393 ).

W poradzie 393 omawialiśmy funkcje z rodziny CZY, między innymi funkcje CZY.PUSTE, CZY.TEKST i CZY.NIE.TEKST. Zwracaliśmy wtedy uwagę jakie wyniki dają wyżej wymienione funkcje w stosunku do różnych danych (rys. nr 1). Np. liczby zapisane jako tekst również zostaną zliczone do komórek wypełnionych tekstem.

Rys. nr 1 – wartości zwracane przez funkcje z rodziny CZY w zależności od danych bazowych
Rys. nr 1 – wartości zwracane przez funkcje z rodziny CZY w zależności od danych bazowych

Problem z tymi funkcjami pojawia się, kiedy mamy do czynienia z pustym ciągiem tekstowym wpisanym ręcznie bądź otrzymanym w wyniku jakiejś formuły. W takiej sytuacji funkcja CZY.TEKST zlicza te komórki. Dodatkowo interesuje nas funkcja LICZ.JEŻELI, w której w argumencie kryteria możemy wpisać operator różne ("<>"). Jeśli w środku operatora różne nie podamy żadnej wartości to Excel zwróci różne od pustych wartości. Zapis takiej formuły wyglądałby następująco:

=LICZ.JEŻELI(B2; "<>")

Taka funkcja nie będzie zliczać wartości tylko w momencie faktycznie pustej komórki. Jeśli w komórce wpiszemy pusty ciąg tekstowy lub będzie on wynikiem działania jakiejś formuły, zostanie on zliczony, tak samo jak wszystkie inne wpisane przez nas wartości (rys. nr 2). Nam chodzi jednak o to, aby większość z tych wartości pominąć, szczególnie puste ciągi tekstowe.

Rys. nr 2 – wartości zwracane przez podane funkcje w różnych przypadkach
Rys. nr 2 – wartości zwracane przez podane funkcje w różnych przypadkach

Leila Gharani pokazała inny zapis funkcji LICZ.JEŻELI, która zlicza liczby zapisane jako tekst, pomija różne rodzaje liczb oraz wartości logiczne PRAWDA i FAŁSZ, nie zlicza wszystkich rodzajów błędów oraz wartości, które są wpisane (https://www.youtube.com/watch?v=cF_yqAU3ybs).

Zapis formuły Leili Gharany powinien wyglądać następująco:

=LICZ.JEŻELI(B2;"><")

Jedyne do czego możemy się „przyczepić” to dwa ostatnie przypadki w danych, gdzie mamy wpisany tekst, ale poprzedzony znakiem hash (#) lub ręcznie wpisany symbol pustego ciągu tekstowego (""). Taką wartość chcielibyśmy mieć zliczoną, bo ewidentnie to jest tekst (rys. nr 3).

Rys. nr 3 – działanie funkcji LICZ.JEŻELI w różnych przypadkach (kolumna G)
Rys. nr 3 – działanie funkcji LICZ.JEŻELI w różnych przypadkach (kolumna G)

Zajmiemy się teraz wyjaśnieniem zapisu formuły Leili Gharani. Chodzi o to że użyte przez nią znaki "><" to nie jest tak naprawdę operator porównania w Excelu. Jest to operator większe niż połączony ze znakiem mniejsze niż. Można to również zapisać jako dwa osobne znaki porównania połączone znakiem ampersand (">"&"<").

Przejdźmy na arkusz Znaki w pliku do filmu. Zrobimy tutaj małą operację, której zapis powinien wyglądać następująco: ="b">"A". W wyniku takiej formuły otrzymamy wynik testu logicznego PRAWDA. Wynika to z zasady, że jak porównujemy litery alfabetu w Excelu to litera, która jest później w alfabecie uznawana jest za większą. Właśnie na tej zasadzie opiera się zapis odwróconego znaku różne niż.

W arkuszu Znaki mamy rozpisane wszystkie znaki kodu ASCII od numeru 1 do 255. W kolumnie Posortowane mamy te znaki posortowane (rys. nr 4).

Rys. nr 4 – znaki kodu ASCII
Rys. nr 4 – znaki kodu ASCII

W tych danych mamy całą masę zer. Co istotne, znak hash (#) jest mniejszy od znaku mniejszości, więc funkcja LICZ.JEŻELI zwraca nam dla niego wartość zero (0). Dlatego zapis tekstowy poprzedzony znakiem hash daje nam wartość 0. Dopiero kiedy natrafimy na znak mniejszości w posortowanej liście, to wszystkie znaki poniżej dadzą nam wartość 1 (rys. nr 5).

Rys. nr 5 – znaki kodu ASCII mniejsze od znaku mniejszości
Rys. nr 5 – znaki kodu ASCII mniejsze od znaku mniejszości

Podsumowując działanie formuły polega na sprawdzaniu czy dane w komórce są mniejsze od znaku mniejszości. Aby puste znaki tekstowe oraz znak hash dawał wynik 1, wystarczy zmienić trochę formułę i zamiast znaku mniejszości wpisać znak wykrzyknika (!), który jest nad znakiem hash po posortowaniu znaków kodu ASCII. Zapis formuły powinien wyglądać następująco:

=LICZ.JEŻELI(B2; ">"&"!")

Powyższą formułę kopiujemy w dół kolumny G i otrzymamy zaktualizowane wyniki przedstawione na rys. nr 6.

Rys. nr 6 – zaktualizowane wyniki dla znaku hash i pustego ciągu tekstowego
Rys. nr 6 – zaktualizowane wyniki dla znaku hash i pustego ciągu tekstowego

Jak widać na rys. powyżej zmiana w formule spowodowała, że zapis tekstowy ze znakiem hash na początku oraz wpisany ręcznie pusty ciąg tekstowy został zliczony w formule.

Ważne jest zapoznanie się z tą sztuczką i z czego ona wynika, bo to pozwoli nam prawidłowo z niej korzystać w obliczeniach.


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 — 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