Excel: zliczanie wierszy według warunku — proste formuły i przykłady

Gdy pracuję z tabelą, często chcę szybko sprawdzić, ile wierszy spełnia określony warunek. Zamiast przeglądać dane i ręcznie liczyć pasujące pozycje, korzystam z funkcji LICZ.JEŻELI. Sprawdza się zarówno w małych zestawieniach, jak i arkuszach z tysiącami rekordów.

W dużym skrócie:

Funkcja LICZ.JEŻELI zlicza komórki spełniające jeden warunek. Wybierz kolumnę reprezentującą każdy rekord i sprawdź, czy zakres obejmuje właściwe wiersze.
Kryterium może być tekstem, liczbą lub porównaniem, np. „Gdańsk”, 100 albo „>100”. Możesz też odwołać się do komórki, by zmieniać kryterium bez edytowania formuły.
Symbole wieloznaczne ułatwiają dopasowywanie tekstu: gwiazdka (*) zastępuje dowolny ciąg znaków, a znak zapytania (?) — jeden znak. Funkcja nie rozróżnia wielkości liter.
Do zliczania rekordów spełniających kilka warunków jednocześnie służy LICZ.WARUNKI. Wszystkie użyte zakresy muszą mieć takie same wymiary, a kryteria są łączone spójnikiem „i”.
ILE.NIEPUSTYCH zlicza komórki zawierające dane, ILE.LICZB — wartości liczbowe, a LICZ.PUSTE — puste komórki. Wybierz funkcję odpowiednią do rodzaju kontroli.
Aby uniknąć zawyżonego wyniku, przy zliczaniu wierszy użyj kolumny, w której każdy rekord ma jeden wpis, np. identyfikatora.

Składnia tej funkcji jest prosta: =LICZ.JEŻELI(zakres; kryteria). W argumencie zakres wskazuję komórki do sprawdzenia, a w argumencie kryteria określam warunek. Na przykład formuła =LICZ.JEŻELI(B2:B101;"Gdańsk") zlicza wystąpienia nazwy „Gdańsk” w komórkach od B2 do B101. Jeśli miasto pojawia się w nich 3 razy, formuła zwraca 3.

Kryterium może być tekstem, liczbą lub warunkiem porównującym wartość. Tekst i operatory porównania ujmuję w cudzysłów, na przykład "Gdańsk", ">100" lub "<>Gotowe". Liczbę mogę natomiast podać bez cudzysłowu: =LICZ.JEŻELI(C2:C101;100). Ponadto wielkość liter nie ma znaczenia, więc „GDAŃSK” i „Gdańsk” funkcja traktuje tak samo.

Gwiazdka i znak zapytania szybko wychwycą różne warianty zapisów

Gdy wpisy w danych różnią się od siebie, mogę użyć symboli wieloznacznych. Gwiazdka * zastępuje dowolny ciąg znaków, natomiast znak zapytania ? — dokładnie jeden znak. Formuła =LICZ.JEŻELI(B2:B101;"Gda*") zliczy teksty zaczynające się od „Gda”, a kryterium "K?t" dopasuje między innymi „Kot” i „Kąt”. Symbole te pomagają znaleźć różne warianty zapisu, ale nie poprawiają ani nie ujednolicają danych — określają jedynie, które wpisy uwzględnić.

Ponieważ funkcja sprawdza komórki w podanym zakresie, przy zliczaniu wierszy zwykle wybieram kolumnę z interesującą mnie informacją, na przykład statusem, miastem lub nazwiskiem. Jeśli chcę policzyć pozycje oznaczone jako „Gotowe”, wpisuję =LICZ.JEŻELI(B2:B101;"Gotowe"). Warto też sprawdzić, czy zakres obejmuje właściwe wiersze: pominięcie ostatniego rekordu lub uwzględnienie nagłówka może zmienić wynik.

Warunek mogę oprzeć na wartości z innej komórki. Jeśli E1 zawiera nazwę miasta, formuła =LICZ.JEŻELI(B2:B101;E1) zliczy pasujące wpisy. Z kolei gdy w F1 znajduje się kryterium liczbowe, zapis =LICZ.JEŻELI(C2:C101;">"&F1) pozwoli policzyć wartości większe od podanej liczby. Dzięki temu zmienię kryterium bez modyfikowania formuły.

LICZ.JEŻELI sprawdza tylko jeden warunek naraz. Jeśli chcę policzyć rekordy spełniające jednocześnie kilka kryteriów, na przykład dotyczące wybranego miasta i określonego statusu, używam funkcji LICZ.WARUNKI. Do innych zadań przydadzą się także funkcje ILE.NIEPUSTYCH, która zlicza komórki zawierające tekst, liczby i inne informacje, ILE.LICZB, która uwzględnia wartości liczbowe, oraz LICZ.PUSTE, która zlicza puste komórki. Dobierając funkcję do zadania, szybciej kontroluję dane i ograniczam ręczne liczenie.

Zliczanie wierszy w Excelu według kryteriów

Najpierw dobierz formułę do warunku i danych, a następnie policz pasujące rekordy. Dzięki temu unikniesz błędów wynikających z kryteriów lub źle dobranego zakresu.

Wybierz kolumnę, która reprezentuje każdy wiersz. Aby policzyć rekordy spełniające warunek, wskaż kolumnę, w której każdy kompletny wiersz zawiera wartość, na przykład identyfikator lub nazwę. W ten sposób puste komórki w pozostałych kolumnach nie zmienią wyniku.
Zlicz wiersze spełniające jeden warunek. Skorzystaj z funkcji LICZ.JEŻELI(zakres; kryterium). Na przykład =LICZ.JEŻELI(B2:B100;"Gdańsk") zliczy wystąpienia miasta w kolumnie B. Tekst oraz kryteria z operatorami porównania ujmij w cudzysłów, np. ">100", natomiast samej wartości liczbowej nie musisz tak zapisywać. Wielkość liter nie wpływa na wynik.
Uwzględnij różne warianty zapisu tekstu. Możesz użyć symboli wieloznacznych: * zastępuje dowolny ciąg znaków, a ? — jeden znak. Na przykład kryterium "Gdańsk*" obejmie wpisy zaczynające się od „Gdańsk”. Symbole te dopasują tekst o zmiennej końcówce, ale nie zastąpią brakujących polskich znaków.
Połącz kilka warunków funkcją LICZ.WARUNKI. Podaj pary zakresów i kryteriów, np. =LICZ.WARUNKI(B2:B100;"Gdańsk";C2:C100;">100"). Formuła zliczy wiersze, w których miasto to Gdańsk, a wartość przekracza 100. Każdy kolejny warunek wymaga następnej pary argumentów, a wszystkie sprawdzane zakresy muszą mieć takie same wymiary.
Dobierz funkcję do tego, co uznajesz za dane. ILE.NIEPUSTYCH zlicza komórki zawierające dowolną informację, w tym tekst, liczby i wartości logiczne. Liczy również komórki ze spacją. Z kolei LICZ.PUSTE zlicza puste komórki, a ILE.LICZB — te z wartościami liczbowymi, w tym datami i godzinami. Funkcje te pomagają sprawdzić kompletność danych, ale nie zliczają wierszy według dowolnego kryterium.

Jak używać symboli wieloznacznych do dopasowywania tekstu

Symbole wieloznaczne w Excelu ułatwiają wyszukiwanie i zliczanie tekstu, gdy wpisy różnią się pojedynczymi znakami lub zawierają dodatkowe fragmenty. Oto najważniejsze zasady ich stosowania:

Gwiazdka (*) zastępuje dowolny ciąg znaków, także pusty. Dlatego w kryterium funkcji LICZ.JEŻELI możesz użyć na przykład *Gdańsk*, aby zliczyć teksty zawierające ten fragment.
Znak zapytania (?) zastępuje dokładnie jeden znak. Użyj go, gdy wpisy różnią się pojedynczą literą: kryterium Gda?sk dopasuje teksty z dowolnym znakiem w miejscu znaku zapytania.
Stosuj symbole w kryteriach funkcji LICZ.JEŻELI, aby zliczać komórki pasujące do wzorca. Podaj zakres sprawdzanych komórek i kryterium, a tekstowe kryterium ujmij w cudzysłów. Funkcja nie rozróżnia wielkości liter.
Łącz symbole z fragmentami tekstu, aby dopasować różne warianty wpisów, na przykład nazwy z błędami lub bez polskich znaków. Gwiazdka zastąpi dowolną liczbę znaków, a znak zapytania — tylko jeden.
Dobierz funkcję do liczby warunków: LICZ.JEŻELI sprawdza jedno kryterium, natomiast LICZ.WARUNKI zlicza dane spełniające kilka kryteriów jednocześnie. Pamiętaj, że zakresy używane w LICZ.WARUNKI muszą mieć takie same wymiary.

Sprawdzanie kilku warunków jednocześnie funkcją LICZ.WARUNKI

Gdy chcę szybko sprawdzić, ile rekordów spełnia jednocześnie kilka warunków, używam funkcji LICZ.WARUNKI. Zamiast filtrować tabelę i liczyć wyniki ręcznie, wskazuję kryteria, a Excel zlicza wiersze, które spełniają je wszystkie.

Funkcja ma składnię =LICZ.WARUNKI(zakres_kryteriów₁; kryterium₁; [zakres_kryteriów₂; kryterium₂]; …). Każdemu kryterium przypisuję odpowiedni zakres. Na przykład, aby policzyć zamówienia z Gdańska o wartości powyżej 500 zł, wpisuję: =LICZ.WARUNKI(A₂:A₅₀; "Gdańsk"; B₂:B₅₀; ">500"). Zakres A₂:A₅₀ zawiera miasta, a B₂:B₅₀ — kwoty.

Tekst oraz operatory porównania ujmuję w cudzysłów. Zapis ">500" oznacza wartość większą niż 500, a "<=250" — wartość nie większą niż 250. Próg mogę też pobierać z komórki, na przykład za pomocą kryterium ">"&D₁. Dzięki temu zmieniam próg bez edytowania formuły. Funkcja odróżnia tekst od liczb, ale nie rozróżnia wielkości liter.

Precyzyjne wyniki dzięki zgodnym zakresom i kryteriom

Wszystkie sprawdzane zakresy muszą mieć ten sam rozmiar i układ. Jeśli pierwszy obejmuje A₂:A₅₀, drugi również powinien zawierać 49 wierszy, na przykład B₂:B₅₀. W przeciwnym razie Excel zwróci błąd. Nawet gdy sprawdzam dwa warunki w tej samej kolumnie, osobno podaję zakres dla każdej pary zakresu i kryterium.

Aby policzyć osoby z działu „Sprzedaż”, które zdobyły co najmniej 80 punktów, wpisuję =LICZ.WARUNKI(A₂:A₅₀; "Sprzedaż"; B₂:B₅₀; ">=80"). Z kolei wartości od 200 do 800 zliczam, podając ten sam zakres dwukrotnie: =LICZ.WARUNKI(C₂:C₅₀; ">=200"; C₂:C₅₀; "<=800"). Excel uwzględnia tylko wartości spełniające oba warunki, w tym 200 i 800.

W kryteriach tekstowych mogę stosować symbole wieloznaczne: gwiazdka zastępuje ciąg znaków, a znak zapytania — jeden znak. Na przykład "Gda*" pasuje do tekstów zaczynających się od „Gda”. LICZ.WARUNKI łączy kryteria spójnikiem „i”. Dlatego rekordy pasujące do warunku „Gdańsk” lub „Sopot” zliczam, sumując wyniki dwóch osobnych formuł.

LICZ.WARUNKI zlicza komórki spełniające wskazane kryteria, a nie wszystkie komórki zawierające dane. Do liczenia komórek niepustych służy ILE.NIEPUSTYCH, pustych — LICZ.PUSTE, a zawierających liczby — ILE.LICZB. Jeśli potrzebuję tylko jednego warunku, używam LICZ.JEŻELI; przy co najmniej dwóch kryteriach LICZ.WARUNKI pozwala precyzyjnie zestawić wyniki.

Zliczanie pustych, niepustych i liczbowych komórek w Excelu

Porządkując arkusz, najpierw sprawdzam, ile komórek jest pustych, ile zawiera dowolne dane, a ile — same liczby. Wpisy niepuste zliczam funkcją ILE.NIEPUSTYCH, na przykład =ILE.NIEPUSTYCH(A2:A15). Funkcja uwzględnia tekst, liczby i wartości logiczne, a także komórkę zawierającą pojedynczą spację. Dlatego wpis, który wygląda na pusty, może zwiększyć wynik.

Puste pola zliczam funkcją LICZ.PUSTE, natomiast same wartości liczbowe — funkcją ILE.LICZB. Formuły =LICZ.PUSTE(A2:A15) i =ILE.LICZB(A2:A15) zwrócą odpowiednio liczbę pustych komórek oraz liczb i wartości daty lub czasu. Warto jednak sprawdzić, jak wprowadzono dane, ponieważ komórka z formułą zwracającą pusty tekst może być traktowana inaczej niż faktycznie niewypełniona komórka.

Szybka kontrola tabeli bez ręcznego liczenia

Gdy chcę zliczyć wpisy spełniające dodatkowy warunek, korzystam z funkcji LICZ.JEŻELI. Na przykład =LICZ.JEŻELI(B2:B15;"Gdańsk") policzy komórki z nazwą miasta. Kryteria tekstowe i operatory porównania zapisuję w cudzysłowie, a wielkość liter nie wpływa na wynik. Ponadto znak ? zastępuje jeden znak, a * — dowolny ciąg, co ułatwia zliczanie niejednolitych zapisów.

Przy kilku warunkach stosuję funkcję LICZ.WARUNKI i podaję każdą parę: zakres kryteriów oraz kryterium. Przykładowo =LICZ.WARUNKI(A2:A15;">=10";A2:A15;"<=50") zlicza wartości od 10 do 50. Wszystkie zakresy muszą mieć ten sam rozmiar, czyli obejmować tyle samo wierszy i kolumn. Dzięki tym funkcjom szybko sprawdzam kompletność tabeli, kontroluję przedział wartości i wykrywam braki bez ręcznego liczenia komórek.

Wskazówka: jeśli chcesz zliczyć wiersze, a nie pojedyncze komórki, użyj kolumny, w której każdy wiersz ma jeden wpis, np. identyfikator, i zastosuj do niej LICZ.JEŻELI lub LICZ.WARUNKI. Dzięki temu kilka pasujących komórek w jednym wierszu nie zawyży wyniku.

Źródła: