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