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