Zliczanie pustych komórek w Excelu funkcją LICZ.PUSTE

W tym artykule dowiesz się jak podliczyć puste komórki na arkuszu kalkulacyjnym, korzystając z funkcji LICZ.NIEPUSTE. Artykuł ten powstał na bazie darmowej lekcji z mojego kursu enter link description hereFunkcje Excel dla zaawansowanych. Możecie obejrzeć lekcję wideo lub przeczytać jej zapis poniżej. Jeśli chcecie podążać za wskazówkami krok po kroku, zapraszam do pobrania tego samego arkusza kalkulacyjnego, na którym pokazuję tę technikę na filmie. Znajduje się on po prawej stronie pod linkiem Załącznik.

Instrukcja video

Instrukcja tekstowa

W tym arkuszu mamy kolumnę D, z nagłówkiem MANDATY, która zawiera dane dotyczące liczby mandatow zdobytych – jeśli można tak powiedzieć – przez każdego z kierowców w naszej umownej firmie. Z jakichś względów chcemy policzyć ilu kierowców w firmie jeździ bezpiecznie, czyli ilu kierowców nie zarobiło jeszcze żadnego mandatu. Innymi słowy, chcemy wiedzieć, ile komórek w kolumnie D nie zawiera danych, w ten sposób dowiemy się ilu kierowców nie ma mandatów. Jest na to odpowiednia funkcja w Excelu i nazywa się LICZ PUSTE.

Skorzystajmy z tej funkcji na przykład w tej komórce, wpisujemy LICZ.

Z listy podpowiedzi wybieramy LICZ PUSTE, klikamy dwukrotnie aby wstawić funkcję do komórki. Naszym argumentem jest kolumna D, bo to w niej chcemy podliczyć puste komórki.

Wciskam ENTER i mamy wynik… tylko, że coś z nim nie gra. Liczba pustych komórek jest za wysoka, gdy zjadę niżej widzimy, że mamy raptem tylko kilkudziesięciu kierowców. Więc skąd ta liczba? Jako argument podaliśmy CAŁĄ KOLUMNĘ D, więc funkcja przeszukuje całą kolumnę aż do końca arkusza, nie tylko pierwszych 50 komórek, które dotyczą naszych kierowców.

Szybki sposób podliczenia pustych komórek

Jakimś rozwiązaniem tego problemu byłoby zamiast podania całej kolumny jako argumentu, zaznaczenie jedynie tych komórek, które chcemy, aby zostały przeszukane przez tę funkcję. Zaznaczam te komórki, wciskam ENTER i mamy wynik 17 – dużo bardziej realistyczny. Wiec w tym obszarze który zaznaczyłem jest 17 pustych komórek, mamy wiec w firmie 17 ostrożnych kierowców bez mandatów.

Ale to rozwiązanie, czyli zaznaczenie komórek które maja zostać przeszukane przez tę funkcję, mimo że teraz sprawdza się doskonale, ma swoje wady, które mogą wyjść na jaw w przyszłości. Mianowicie, co jeśli w firmie przybędzie kierowców? Lista się rozszerzy, dojdą nowi kierowcy, bedą zdobywać mandaty lub nie, ale wtedy nasza funkcja będzie nieaktualna, ponieważ nie będzie brała do obliczeń tych nowych komórek, dotyczących nowych kierowców.

Sposób dłuższy, ale pewniejszy – „na tabelę”

Lepszym rozwiązaniem jest zamiana tych danych na tabelę. Tabele, oprócz tego, że mają liczne walory estetyczne, posiadają też szereg funkcji. Jedna z nich – automatyczne dodawanie wierszy i kolumn – okaże się pomocna w naszym przypadku. Zatem na zakładce NARZĘDZIA GLÓWNE klikamy na przycisk FORMATUJ JAKO TABELĘ:

Wybieramy jeden z szablonów graficznych, który najbardziej się nam podoba. Możemy zaznaczyć obszar danych który ma być sformatowany jako tabela, ale widzimy, że Excel doskonale sobie poradził sam – zaznaczony jest nasz obszar danych, wiec klikamy OK.

Teraz nasze dane są TABELĄ. Tutaj wyświetlona jest jej domyślna nazwa: Tabela1. Możemy ją zmienić jeśli chcemy, ale ta jest wg mnie OK. Mamy także nazwy automatyczne dla każdej z naszych kolumn, Kolumna 1 ,Kolumna 2, Kolumna 3. Wiec napiszmy nasza funkcje od nowa. LICZ. PUSTE, teraz podajemy argument jako zakres do przeszukania. Zrobimy to trochę inaczej niż poprzednio, ponieważ zacznę wpisywać nazwę mojej tabeli, Tabela 1. Po wpisaniu pierwszych liter T, A, Excel wyświetla mi podpowiedź w dymku, wybieram więc TABELA1. Następnie interesuje nas kolumna MANDATY w tej tabeli, widzimy na górze, że ma ona nazwę KOLUMNA 3. Aby odwołać się do tej kolumny, otwieramy kwadratowy nawias, i w nim podajemy nazwę KOLUMNA 3 – znów możemy wybrać ją z listy. Teraz zamykamy kwadratowy nawias i wciskamy ENTER.

Formuła zadziałała, jej wynik to również 17. I teraz w odróżnieniu od naszej pierwszej formuły, będzie działać również gdy dodamy nowego kierowcę do naszej firmy. Załóżmy, ze z czasem zatrudnimy kolejnego kierowcę, wpiszemy go na koniec listy, teraz wpiszę byle co zamiast imienia i nazwiska. Tu wstawię jakieś przykładowe dane, 10 kursów i ponad 2 tysiące kilometrów. I komórkę w kolumnie mandaty zostawimy pustą, nie ma żadnych mandatów. Wciskamy Enter i sprawdzamy jak zareagowały nasze funkcje. Widzimy, że funkcja, którą utworzyliśmy na tabeli automatycznie się zaktualizowała do 18, podczas gdy nasza pierwotna funkcja dalej wyświetla wartość 17. Jest to już oczywiście wynik nieprawidłowy. To dlatego, że nadal bierze ona pod uwagę tylko ten zakres danych, który jej podaliśmy jako argument funkcji, czyli od wiersza 3 do 50, podczas gdy nasza lista kierowców zajmuje już jeden wiersz więcej. I tak się składa, ze komórka w kolumnie z mandatami dla tego wiersza jest pusta. Natomiast w drugim przypadku naszym argumentem jest kolumna 3 w tabeli 1, nieważne ile komórek się w niej znajduje, i czy dodamy czy usuniemy je w przyszłości.