Liczenie komórek według warunków to umiejętność pozornie prosta, ale szybko staje się kluczowa, gdy pracujemy na rzeczywistych tabelach: sprzedażach, listach klientów, terminach płatności czy inwentaryzacjach. Funkcje NB.SI i NB.SI.ENS oferują bezpośrednie rozwiązanie: pierwsza dla jednego kryterium, druga dla wielu. Tutaj wyjaśniam ich składnię, konkretne przypadki użycia, typowe pułapki — oraz podaję formuły do kopiowania, aby zaoszczędzić czas.
Somaire
W skrócie
🧭 NB.SI liczy komórki w zakresie, które spełniają jedno kryterium (np. „>100”, „Jean”, „2025-01-01”). Przydatne do szybkich sprawdzeń i prostych tabel.
🔗 NB.SI.ENS akceptuje wiele zakresów/kryteriów i wymaga, aby wszystkie zakresy miały ten sam rozmiar: idealne do łączenia statusu + regionu + okresu.
⚠️ Uwaga na formaty: daty, liczby przechowywane jako tekst i ukryte spacje często powodują błędy. Wystarczy kilka prostych poprawek.
💡 Alternatywnie, SUMPRODUCT i tablice dynamiczne pozwalają na bardziej zaawansowane liczenia, gdy NB.SI.ENS osiąga swoje ograniczenia.
Podstawowa różnica i składnia
NB.SI: jedno kryterium, jeden zakres
NB.SI (COUNTIF po angielsku) odpowiada na pytanie: „ile komórek w tym zakresie spełnia to kryterium?” Jego składnia jest prosta: =NB.SI(zakres; kryterium). Kryterium może być dokładnym tekstem („Czerwony”), operatorem logicznym połączonym z liczbą („>=100”) lub zawierać symbole wieloznaczne (* i ?), aby dopasować częściowo.
NB.SI.ENS: wiele kryteriów
NB.SI.ENS (COUNTIFS) rozszerza zasadę na wiele par zakres/kryterium: =NB.SI.ENS(zakres_kryterium1; kryterium1; zakres_kryterium2; kryterium2; …). Każdy zakres musi mieć tę samą liczbę wierszy/kolumn. Funkcja zwraca liczbę wierszy, w których wszystkie kryteria są jednocześnie spełnione.
Podsumowująca tabela
| Funkcja | Zastosowanie | Przykład | Praktyczna uwaga |
|---|---|---|---|
| NB.SI | 1 kryterium na 1 zakres | =NB.SI(B2:B100; „Sprzedane”) | Szybsze do pojedynczej weryfikacji |
| NB.SI.ENS | Wiele kryteriów na niezależnych zakresach | =NB.SI.ENS(B2:B100; „Sprzedane”; C2:C100; „Francja”) | Zakresy muszą mieć ten sam rozmiar |
| SUMPRODUCT | Alternatywa dla dowolnej logiki | =SOMMEPROD((B2:B100=”Sprzedane”)*(C2:C100=”Francja”)) | Bardziej elastyczne, ale czasem wolniejsze |
Przykłady praktyczne i przydatne warianty
1) Liczenie z użyciem symboli wieloznacznych (częściowe wyszukiwanie)
Jeśli chcesz policzyć klientów, których nazwisko zaczyna się na „Dup”, użyj:
=NB.SI(A2:A500; „Dup*”)
Symbol wieloznaczny * zastępuje dowolny ciąg znaków; ? zastępuje jeden znak. NB.SI nie rozróżnia wielkości liter: „dupont” i „Dupont” będą liczone tak samo.
2) Kryteria liczbowe i daty
Aby policzyć kwoty większe niż 1000:
=NB.SI(D2:D1000; „>1000”)
Dla daty po 1 stycznia 2024:
=NB.SI(E2:E100; „>2024-01-01”)
Jeśli Excel nie interpretuje poprawnie daty, umieść ją w funkcji DATA lub użyj komórki odniesienia:
=NB.SI(E2:E100; „>” & F1)
gdzie F1 zawiera datę.
3) Kilka kryteriów: przykład handlowy
Liczba zatwierdzonych zamówień we Francji dla handlowca „Martin”:
=LICZ.WARUNKI(StatusRange; „Zatwierdzona”; CountryRange; „Francja”; SalesRepRange; „Martin”)
Upewnij się, że StatusRange, CountryRange i SalesRepRange obejmują dokładnie te same wiersze (np. B2:B500, C2:C500, D2:D500).
4) Zaawansowany przypadek: łączenie części tekstu i liczb
Szukasz wierszy, w których produkt zawiera „USB” i ilość jest większa niż 10:
=LICZ.WARUNKI(ProductRange; „*USB*”; QuantityRange; „>10”)
Pierwszy warunek używa symbolu wieloznacznego, drugi operatora numerycznego. To połączenie jest często używane do filtrowania sprzedaży według typu produktu i progu ilości.
Częste pułapki i poprawki
- Nieprawidłowy format: daty i liczby przechowywane jako tekst nie są poprawnie zliczane. Rozwiązanie: użyj WARTOŚĆ, DATA.WARTOŚĆ lub wymuś format przez specjalne wklejanie.
- Niewidoczne spacje: spacja na końcu uniemożliwia dokładne dopasowanie. Wyczyść za pomocą USUŃ.ZBĘDNE.ODSTĘPY() lub ZNAJDŹ/ZAMIEŃ.
- Różne rozmiary zakresów: LICZ.WARUNKI zwróci błąd, jeśli zakresy nie mają tych samych wymiarów.
- Kryteria dynamiczne: łącz kryterium za pomocą & (ampersand) aby porównać z komórką:
=LICZ.JEŻELI(A2:A100; „>” & G1)
- Wydajność: bardzo duże zakresy z wieloma kryteriami mogą spowolnić działanie; SUMA.ILOCZYNÓW może być jeszcze wolniejsza w zależności od kontekstu.
Alternatywy i zaawansowane wskazówki
SUMA.ILOCZYNÓW pozwala tworzyć dowolne logiki, np. złożone OR lub ważenia. Aby policzyć wiersze, w których przynajmniej jedno z warunków jest prawdziwe, nie można użyć bezpośrednio LICZ.WARUNKI; można połączyć LICZ.JEŻELI lub użyć SUMA.ILOCZYNÓW:
=SUMA.ILOCZYNÓW(((A2:A100=”X”) + (B2:B100=”Y”))>0)
Ta formuła sumuje warunki logiczne i zwraca 1, jeśli przynajmniej jeden jest prawdziwy. Jest bardziej rozbudowana, ale bardzo potężna do jednorazowych analiz.
Praktyczna wskazówka: po imporcie danych
Po zaimportowaniu tabeli — na przykład pliku CSV — natychmiast sprawdź:
- formaty kolumn jedna po drugiej (tekst vs liczba vs data),
- puste lub podwójne komórki na końcu zakresu,
- separator dziesiętny zmieniający się w zależności od regionu (przecinek vs kropka).
Te kontrole zapobiegają większości fałszywych negatywów podczas używania LICZ.JEŻELI i LICZ.WARUNKI. Po ustandaryzowaniu formatów twoje formuły stają się odporne i wielokrotnego użytku.
Przykłady gotowe do skopiowania
- Liczba zwróconych produktów:
=LICZ.JEŻELI(StatusRange; „Zwrócony”)
- Klienci VIP w 2024:
=LICZ.WARUNKI(ClientTypeRange; „VIP”; OrderDateRange; „>=2024-01-01”)
- Sprzedaż Franco + kwota > 5000:
=LICZ.WARUNKI(CountryRange; „Francja”; AmountRange; „>5000”)
FAQ
Dlaczego LICZ.WARUNKI zwraca #WARTOŚĆ! ?
Najczęstszą przyczyną jest to, że zakresy nie mają tego samego rozmiaru. Sprawdź, czy każdy zakres zaczyna się i kończy na tych samych wierszach/kolumnach. Inną przyczyną może być źle sformułowane kryterium (np. „=” samo) lub niezgodny typ (data jako tekst).
Jak traktować wielkie i małe litery?
LICZ.JEŻELI i LICZ.WARUNKI są niewrażliwe na wielkość liter. Jeśli potrzebujesz porównania uwzględniającego wielkość liter, musisz użyć formuł tablicowych lub SUMA.ILOCZYNÓW łącząc EXACT() z N().
Czy można używać wyrażeń regularnych?
Wbudowany Excel nie oferuje wyrażeń regularnych w funkcji LICZ.JEŻELI. Dla złożonych wzorców użyj Power Query lub VBA, albo zaawansowanych funkcji w Office 365 (LET, FILTER) połączonych z logiką warunkową.
Jak debugować liczenie, które wydaje się błędne?
Postępuj krok po kroku: sprawdź widoczny zakres (sortowanie/filtr), oczyść spacje i formaty, przetestuj prosty warunek, a następnie dodawaj kolejne stopniowo. Użyj formatowania warunkowego, aby zobaczyć, co Excel uznaje za prawdziwe.
