Funkcja Excel LICZ.JEŻELI pozwala zliczyć liczbę komórek spełniających określone kryteria lub warunki w podanym zakresie.
Funkcja LICZ.JEŻELI jest jedną z funkcji statystycznych w programie Excel, która jest kombinacją funkcji ILE.LICZB i JEŻELI lub funkcji ILE.NIEPUSTYCH. W przypadku użycia we wzorze funkcja zlicza komórki, które spełniają określone kryteria lub warunki w tym samym lub wielu zakresach. Funkcja LICZ.JEŻELI pomaga zliczać komórki zawierające tekst, liczby lub daty spełniające określone kryteria.
Możesz zliczać komórki za pomocą funkcji LICZ.JEŻELI lub LICZ.JEŻELI w programie Excel. Różnica między funkcjami LICZ.JEŻELI i LICZ.WARUNKI polega na tym, że funkcja LICZ.JEŻELI służy do zliczania komórek spełniających jedno kryterium w jednym zakresie, podczas gdy LICZ.JEŻELI zlicza komórki spełniające wiele warunków w tym samym lub wielu zakresach.
W tym artykule dowiesz się, jak korzystać z dwóch funkcji LICZ.JEŻELI i LICZ.JEŻELI w programie Excel.
Funkcja LICZ.JEŻELI Excel
Funkcja LICZ.JEŻELI umożliwia zliczanie danych na podstawie określonego kryterium lub warunku. Warunek używany w funkcji działa z operatorami logicznymi (, , =, >=, <=) i symbolami wieloznacznymi (*, ?) w celu częściowego dopasowania.
Składnia funkcji LICZ.JEŻELI
Struktura funkcji LICZ.JEŻELI to:
=LICZ.JEŻELI(zakres;kryteria)Parametry:
zakres– Zakres komórek do zliczenia.kryteria– Warunek określa, które komórki powinny być uwzględnione w liczniku w określonym zakresie. Kryteria mogą być wartością liczbową, tekstem, odwołaniem do adresu komórki lub równaniem.
Używanie funkcji LICZ.JEŻELI do zliczania wartości liczbowych
Jak omówiliśmy powyżej, kryteria (drugi argument) w funkcji LICZ.JEŻELI definiują warunek, który mówi funkcji, które komórki mają zliczyć.
Ta funkcja pomaga policzyć liczbę komórek z wartościami, które spełniają warunki logiczne, takie jak równa, większa niż, mniejsza lub nie równa określonej wartości itp.
W poniższym przykładzie formuła zlicza komórki zawierające wartość równą 5 (kryteria). Możesz bezpośrednio wstawić „5” do formuły lub użyć odwołania do adresu komórki, która ma wartość (komórka D2 w poniższym przykładzie).
=LICZ.JEŻELI(B2:B11;D2)
Powyższa formuła zlicza liczbę komórek w zakresie komórek (B2:B11), które zawierają wartość równą wartości w komórce D2.
Poniższa formuła zlicza komórki o wartości mniejszej niż 5.
=LICZ.JEŻELI(B2:B11;"<5")Operator mniej niż (<) informuje formułę, aby zliczała komórki o wartości mniejszej niż „5” w zakresie B2:B11. Za każdym razem, gdy używasz operatora w warunku, pamiętaj o umieszczeniu go w podwójnym cudzysłowie („”).
Czasami, gdy chcesz policzyć komórki, sprawdzając je według kryterium (wartości) w komórce. W takich przypadkach utwórz kryterium, łącząc operator i odwołanie do komórki. Gdy to zrobisz, musisz ująć operator porównania w podwójne cudzysłowy („”), a następnie umieścić znak ampersand (&) między operatorem porównania a odwołaniem do komórki.
=LICZ.JEŻELI(B2:B11;">="&D2)
Poniższy rysunek przedstawia kilka przykładowych formuł i ich wynik.
Używanie funkcji LICZ.JEŻELI do zliczania wartości tekstowych
Aby zliczyć komórki zawierające określone ciągi tekstowe, użyj tego ciągu tekstowego jako argumentu kryteriów lub komórki zawierającej ciąg tekstowy. Na przykład w poniższej tabeli, jeśli chcemy policzyć wszystkie komórki w zakresie (B21:D27) z wartością tekstową w komórce B21 (sam), możemy użyć następującej formuły:
=LICZ.JEŻELI(B21:D27;B21)
Jak omówiliśmy wcześniej, możemy albo użyć tekstu „sam” bezpośrednio w formule, albo użyć odwołania do komórki zawierającej kryteria (B21). Ciąg tekstowy powinien być zawsze ujęty w cudzysłów („”), gdy jest używany w formule w programie Excel.
=LICZ.JEŻELI(B21:D27;"sam")
Aby policzyć komórki, które nie zawierają określonego tekstu, użyj poniższej formuły:
=LICZ.JEŻELI(B21:D27;""&B21)Pamiętaj, aby dołączyć „nie równe” "" operator w cudzysłowie.
Jeśli używasz tekstu „sam” bezpośrednio w formule, musisz umieścić razem operator „” i ciąg tekstowy („Sam”) w cudzysłowie.
=LICZ.JEŻELI(B21:D27;"sam") 
Używanie symboli wieloznacznych w funkcji LICZ.JEŻELI programu Excel (dopasowanie częściowe)
Możesz użyć formuły LICZ.JEŻELI ze znakami wieloznacznymi, aby zliczyć komórki zawierające określone słowo, frazę lub litery. W funkcji LICZ.JEŻELI w programie Excel można użyć trzech symboli wieloznacznych:
*(gwiazdka) – służy do zliczania komórek z dowolną liczbą początkowych i końcowych znaków/liter. (np. St* może oznaczać Stark, Stork, Stacks itp.?(znak zapytania) – służy do wyszukiwania komórek z dowolnym pojedynczym znakiem. (np. St?rk może oznaczać Stark lub Stork.~(tylda) – służy do wyszukiwania i liczenia komórek zawierających znak zapytania lub gwiazdkę (~, *, ?) w tekście.
Zliczanie komórek zaczynających się lub kończących się na określonych znakach
Aby policzyć komórki, które zaczynają się lub kończą określonym tekstem z dowolną liczbą innych znaków w komórce, użyj symbolu wieloznacznego gwiazdki (*) w drugim argumencie funkcji LICZ.JEŻELI.
Użyj tej przykładowej formuły:
=LICZ.JEŻELI(A1:A10;"A*") – zliczanie komórek zaczynających się na „A”.
=LICZ.JEŻELI(A19:A28;"*er") – aby policzyć liczbę komórek, które kończą się znakami „er”.
=LICZ.JEŻELI(A2:A12;"*QLD*") – do zliczania komórek zawierających tekst „QLD” w dowolnym miejscu ciągu tekstowego.
A ? reprezentuje dokładnie jeden znak, użyj tego symbolu wieloznacznego w funkcji LICZ.JEŻELI poniżej, aby policzyć liczbę komórek zawierających dokładnie +1 znak, gdzie „?' jest używany.
=LICZ.JEŻELI(A1:A10;"Pary")
Zliczanie pustych i niepustych komórek za pomocą funkcji LICZ.JEŻELI
Formuła LICZ.JEŻELI jest również pomocna przy zliczaniu liczby pustych lub niepustych komórek w danym zakresie.
Policz niepuste komórki
Jeśli chcesz policzyć tylko komórki zawierające wartości „tekstowe”, użyj poniższej formuły. Ta formuła traktuje komórki z datami i liczbami jako puste komórki i nie uwzględnia ich w zliczaniu.
=LICZ.JEŻELI(A1:B12;"*")Dzika karta * dopasowuje tylko wartości tekstowe i zwraca liczbę wszystkich wartości tekstowych w danym zakresie.
Jeśli chcesz policzyć wszystkie niepuste komórki w danym zakresie, wypróbuj tę formułę:
=LICZ.JEŻELI(A1:B12;"")
Policz puste komórki
Jeśli chcesz policzyć puste komórki w określonym zakresie, użyj funkcji LICZ.JEŻELI z * znak wieloznaczny i operator w argumencie kryteria do zliczania pustych komórek.
Ta formuła zlicza komórki, które nie zawierają żadnych wartości tekstowych:
=LICZ.JEŻELI(A1:B12;""&"*")Odkąd * symbol wieloznaczny pasuje do dowolnej wartości tekstowej, powyższa formuła zliczy wszystkie komórki nie równe *. Liczy również komórki z datami i liczbami jako puste.
Aby policzyć wszystkie puste pola (wszystkie typy wartości):
=LICZ.JEŻELI(A1:B12;"")Ta funkcja zlicza tylko puste komórki w zakresie.
Używanie funkcji LICZ.JEŻELI do liczenia dat
Możesz zliczyć komórki z datami (tak samo jak w przypadku kryteriów liczbowych), które spełniają warunek logiczny lub określoną datę lub datę w komórce odniesienia.
Aby policzyć komórki zawierające podaną datę (05-05-2020), użyjemy tej formuły:
=LICZ.JEŻELI(B2:B10;"05-05-2020")
Możesz również określić datę w różnych formatach jako kryteria w funkcji LICZ.JEŻELI, tak jak pokazano poniżej:
Jeśli chcesz zliczyć komórki zawierające daty przed określoną datą lub po niej, użyj operatorów mniej niż (przed) lub większych niż (po) wraz z określoną datą lub odwołaniem do komórki.
=LICZ.JEŻELI(B2:B10;">=05/05/2020")
Możesz również użyć odwołania do komórki zawierającej datę, łącząc je z operatorem (w cudzysłowie).
Aby policzyć liczbę komórek w zakresie A2:A14 z datą przed datą w E3, użyj poniższej formuły, gdzie operator większy niż (<) oznacza przed datą w E3.
=LICZ.JEŻELI(A2:A14;"<"&E3)
Kilka przykładowych formuł i ich wynik:
Data zliczania na podstawie aktualnej daty
Możesz połączyć funkcję LICZ.JEŻELI z określonymi funkcjami daty programu Excel, np. DZIŚ (), aby zliczyć komórki z bieżącą datą.
=LICZ.JEŻELI(A2:A14;">"&DZIŚ())Ta funkcja zlicza wszystkie daty od dzisiaj w zakresie (A2:A14).
Policz daty między określonym zakresem dat
Jeśli chcesz policzyć wszystkie daty między dwiema datami, musisz użyć w formule dwóch kryteriów.
Możemy to zrobić za pomocą dwóch metod: funkcji LICZ.JEŻELI i LICZ.WARUNKI.
Korzystanie z funkcji LICZ.JEŻELI Excel
Aby zliczyć wszystkie daty między dwiema określonymi datami, musisz użyć dwóch funkcji LICZ.JEŻELI.
Aby policzyć daty między ’09-02-2020′ a ’20-08-2021′, użyj tego wzoru:
=LICZ.JEŻELI(A2:A14;">09-02-2020")-LICZ.JEŻELI(A2:A14;">20-08-2021")Ta formuła najpierw wyszukuje liczbę komórek, które mają datę po 2 lutego i odejmuje liczbę komórek z datami po 20 sierpnia. Teraz otrzymujemy nie. komórek, których daty przypadają po 2 lutego i przed 20 sierpnia (liczba to 9).
Jeśli nie chcesz, aby formuła liczyła zarówno 2 lutego, jak i 20 sierpnia, użyj tej formuły:
=LICZ.JEŻELI(A2:A14;">09.02.2020")-LICZ.JEŻELI(A2:A14;">=20-08-2021")Po prostu zamień „>” operator na „>=” w drugim kryterium.
Korzystanie z funkcji LICZ.WARUNKI programu Excel
Funkcja LICZ.JEŻELI obsługuje również wiele kryteriów iw przeciwieństwie do funkcji LICZ.JEŻELI zlicza komórki dopiero po spełnieniu wszystkich warunków. Jeśli chcesz policzyć komórki ze wszystkimi datami między dwiema określonymi datami, wprowadź tę formułę:
=LICZ.WARUNKI(A2:A14;">"&A11;A2:A14;"<"&A10)
Jeśli chcesz uwzględnić w liczeniu również określone daty, użyj operatorów „>=” i „<=”. Tutaj idź z tą formułą:
=LICZ.WARUNKI(A2:A14;">=09-02-2020;A2:A14;"<=20-08-2021")W tym przykładzie użyliśmy daty bezpośrednio w kryteriach zamiast odwołania do komórki.
Jak obsługiwać LICZ.JEŻELI i LICZ.JEŻELI z wieloma kryteriami w programie Excel
Funkcja LICZ.JEŻELI służy głównie do zliczania komórek z jednym kryterium (warunkiem) w jednym zakresie. Jednak nadal możesz użyć funkcji LICZ.JEŻELI, aby zliczyć komórki spełniające wiele warunków w tym samym zakresie. Jednak funkcja LICZ.WARUNKI może służyć do zliczania komórek spełniających wiele warunków w tym samym lub różnych zakresach.
Jak liczyć liczby w zakresie
Możesz zliczać komórki zawierające liczby między dwiema określonymi liczbami za pomocą dwóch funkcji: LICZ.JEŻELI i LICZ.JEŻELI.
LICZ.JEŻELI do zliczania liczb między dwiema liczbami
Jednym z typowych zastosowań funkcji LICZ.JEŻELI z wieloma kryteriami jest zliczanie liczb między dwiema określonymi liczbami, np. aby zliczyć liczby większe niż 10, ale mniejsze niż 50. Aby zliczyć liczby w zakresie, połącz dwie lub więcej funkcji LICZ.JEŻELI w jedną formułę. Pozwól, że pokażemy Ci jak.
Załóżmy, że chcesz policzyć komórki z zakresu B2:B9, gdzie wartość jest większa niż 10 i mniejsza niż 21 (nie licząc 10 i 21), zastosuj następującą formułę:
=LICZ.JEŻELI(B2:B14;">10")-LICZ.JEŻELI(B2:B14;">=21")Różnicę między dwiema liczbami można znaleźć, odejmując jedną formułę od drugiej. Pierwsza formuła zlicza liczby większe niż 10 (czyli 7), druga formuła zwraca liczbę liczb większych lub równych 21 (czyli 4), a wynik drugiej formuły jest odejmowany od pierwszej formuły (7 -4), aby uzyskać liczbę liczb między dwiema liczbami (3).
Jeśli chcesz policzyć komórki, których liczba jest większa niż 10 i mniejsza niż 21 w zakresie B2:B14, w tym liczby 10 i 21, użyj tej formuły:
=LICZ.JEŻELI(B2:B14;">=10")-LICZ.JEŻELI(B2:B14;">21")
COUNTIFS do liczenia liczb między 2 liczbami
Aby zliczyć liczby od 10 do 21 (z wyjątkiem 10 i 21) zawarte w komórkach od B2 do B9, użyj następującej formuły:
=LICZ.WARUNKI(B2:B14;">10";B2:B14;"<21")
Aby uwzględnić w liczbie 10 i 21, po prostu użyj w formułach operatorów „większe lub równe” (>=) zamiast „większe niż” i „mniejsze lub równe” (<=) zamiast „mniejsze niż” .
LICZ.IFS do zliczania komórek z wieloma kryteriami (kryteriami ORAZ)
Funkcja LICZ.JEŻELI jest odpowiednikiem funkcji LICZ.JEŻELI w liczbie mnogiej, która zlicza komórki na podstawie co najmniej dwóch kryteriów w tym samym lub wielu zakresach. Jest to znane jako „logika AND”, ponieważ funkcja służy do zliczania komórek tylko wtedy, gdy wszystkie podane warunki są PRAWDZIWE.
Na przykład chcemy dowiedzieć się, ile razy (liczba komórek) ten chleb (wartość w kolumnie A) został sprzedany mniej niż 5 (wartość w kolumnie C).
Możemy użyć tej formuły:
=LICZ.WARUNKI(A2:A14;"Chleb";C2:C14;"<5")
LICZ.JEŻELI do zliczania komórek z wieloma kryteriami (kryteria OR)
Jeśli chcesz policzyć liczbę komórek spełniających wiele kryteriów w tym samym zakresie, połącz ze sobą dwie lub więcej funkcji LICZ.JEŻELI. Na przykład, jeśli chcesz dowiedzieć się, ile razy „Chleb” lub „Ser” powtarza się w określonym zakresie (A2:A14), użyj poniższego wzoru:
=LICZ.JEŻELI(A2:A14;"Chleb")+LICZ.JEŻELI(A2:A14;"Ser")Ta formuła zlicza komórki, dla których co najmniej jeden z warunków ma wartość PRAWDA. Dlatego nazywa się to „logiką OR”.
Jeśli chcesz ocenić więcej niż jedno kryterium w każdej z funkcji, lepiej użyć opcji LICZ.JEŻELI zamiast LICZ.JEŻELI. W poniższym przykładzie chcemy uzyskać liczbę statusów „Zamówiono” i „Dostarczono” dla „Chleba”, więc użyjemy tej formuły:
=LICZ.WARUNKI(A2:A14;"Chleb";C2:C14;"Zamówione")+LICZ.WARUNKI(A2:A14;"Chleb";C2:C14;"Dostarczone")
Mamy nadzieję, że ten łatwy, ale raczej długi samouczek da ci pewne pojęcie o tym, jak używać funkcji LICZ.JEŻELI i LICZ.JEŻELI w programie Excel.