- Wprowadzenie do funkcji LICZ.JEŻELI i LICZ.JEŻELI w programie Excel
- Korzystanie z kryteriów LICZBA w funkcjach programu Excel LICZ.JEŻELI
- Korzystanie z kryteriów TEKSTOWYCH w funkcjach programu Excel
- Korzystanie z kryteriów DATE w funkcjach programu Excel LICZ.JEŻELI i LICZ.JEŻELI
- Używanie ZNAKÓW WIELDCARD w kryteriach w funkcjach LICZ.JEŻELI i LICZ.JEŻELI
Excel ma wiele funkcji, w których użytkownik musi określić jedno lub wiele kryteriów, aby uzyskać wynik. Na przykład, jeśli chcesz zliczyć komórki na podstawie wielu kryteriów, możesz użyć funkcji LICZ.JEŻELI lub LICZ.JEŻELI w programie Excel.
W tym samouczku omówiono różne sposoby używania jednego lub wielu kryteriów w funkcji LICZ.JEŻELI i LICZ.WARUNKI w programie Excel.
Chociaż w tym samouczku skupię się głównie na funkcjach LICZ.JEŻELI i LICZ.JEŻELI, wszystkie te przykłady mogą być również używane w innych funkcjach programu Excel, które przyjmują wiele kryteriów jako dane wejściowe (takie jak SUMA.JEŻELI, SUMA.JEŻELI, ŚREDNIAJEŻELI i ŚREDNIAJEŻELI).
Wprowadzenie do funkcji LICZ.JEŻELI i LICZ.JEŻELI w programie Excel
Najpierw zajmijmy się używaniem funkcji LICZ.JEŻELI i LICZ.WARUNKI w programie Excel.
Funkcja LICZ.JEŻELI programu Excel (przyjmuje pojedyncze kryteria)
Funkcja Excel LICZ.JEŻELI najlepiej sprawdza się w sytuacjach, gdy chcesz policzyć komórki na podstawie jednego kryterium. Jeśli chcesz liczyć na podstawie wielu kryteriów, użyj funkcji LICZ.WARUNKI.
Składnia
=LICZ.JEŻELI(zakres; kryteria)
Argumenty wejściowe
- zasięg - zakres komórek, które chcesz policzyć.
- kryteria - kryteria, które muszą być ocenione względem zakresu komórek, aby komórka została zliczona.
Funkcja LICZ.WARUNKI programu Excel (przyjmuje wiele kryteriów)
Funkcja LICZ.WARUNKI programu Excel najlepiej sprawdza się w sytuacjach, gdy chcesz policzyć komórki na podstawie wielu kryteriów.
Składnia
=LICZ.WARUNKI(zakres_kryteriów1, kryteria1, [zakres_kryteriów2, kryteria2]… )
Argumenty wejściowe
- kryteria_zakres1 – zakres komórek, dla których chcesz ocenić według kryterium1.
- kryteria1 – kryteria, które chcesz ocenić dla zakresu kryteria_zakres1, aby określić, które komórki mają zostać zliczone.
- [zakres_kryteriów2] – zakres komórek, dla których chcesz ocenić według kryteriów2.
- [kryteria2] - kryteria, które chcesz ocenić dla zakresu kryteria_zakres2, aby określić, które komórki mają zostać zliczone.
Przyjrzyjmy się teraz kilku przykładom użycia wielu kryteriów w funkcjach LICZ.JEŻELI w programie Excel.
Korzystanie z kryteriów LICZBA w funkcjach programu Excel LICZ.JEŻELI
#1 Policz komórki, gdy kryteria są RÓWNE wartości
Aby uzyskać liczbę komórek, w których argument kryteria jest równy określonej wartości, możesz bezpośrednio wprowadzić kryteria lub użyć odwołania do komórki zawierającej kryteria.
Poniżej znajduje się przykład, w którym liczymy komórki zawierające liczbę 9 (co oznacza, że argument kryteria jest równy 9). Oto wzór:
=LICZ.JEŻELI($ B$2:$B$11;D3)
W powyższym przykładzie (na zdjęciu) kryteria znajdują się w komórce D3. Możesz również wprowadzić kryteria bezpośrednio do formuły. Na przykład możesz również użyć:
=LICZ.JEŻELI(2 zł: 11,9 zł)
#2 Policz komórki, gdy kryterium jest WIĘKSZE NIŻ wartość
Aby uzyskać liczbę komórek o wartości większej niż określona, używamy operatora „większy niż” („>”). Możemy użyć go bezpośrednio w formule lub użyć odwołania do komórki, które zawiera kryteria.
Ilekroć używamy operatora w kryteriach w Excelu, musimy umieścić go w podwójnych cudzysłowach. Na przykład, jeśli kryterium jest większe niż 10, musimy wpisać „>10” jako kryterium (patrz zdjęcie poniżej):
Oto wzór:
=LICZ.JEŻELI($B$2:$B$11",">10")
Możesz również mieć kryteria w komórce i użyć odwołania do komórki jako kryteriów. W takim przypadku NIE musisz umieszczać kryteriów w podwójnych cudzysłowach:
=LICZ.JEŻELI($ B$2:$B$11;D3)
Może również zaistnieć przypadek, w którym chcesz, aby kryteria znajdowały się w komórce, ale nie chcesz tego z operatorem. Na przykład możesz chcieć, aby komórka D3 miała numer 10, a nie >10.
W takim przypadku musisz utworzyć argument kryteriów, który jest kombinacją operatora i odwołania do komórki (patrz zdjęcie poniżej):
=LICZ.JEŻELI(2 zł: 11 zł”>”&D3)
UWAGA: Po połączeniu operatora i odwołania do komórki operator jest zawsze w cudzysłowie. Operator i odwołanie do komórki są połączone znakiem ampersand (&).
#3 Policz komórki, gdy kryterium jest MNIEJSZE NIŻ wartość
Aby uzyskać liczbę komórek o wartości mniejszej niż określona wartość, używamy operatora less than („<”). Możemy użyć go bezpośrednio w formule lub użyć odwołania do komórki, które zawiera kryteria.
Ilekroć używamy operatora w kryteriach w Excelu, musimy umieścić go w podwójnych cudzysłowach. Na przykład, jeśli kryterium jest, że liczba powinna być mniejsza niż 5, to jako kryterium musimy wpisać „<5” (patrz zdjęcie poniżej):
=LICZ.JEŻELI($B$2:$B$11”<5″)
Możesz również mieć kryteria w komórce i użyć odwołania do komórki jako kryteriów. W takim przypadku NIE musisz umieszczać kryteriów w podwójnych cudzysłowach (patrz zdjęcie poniżej):
=LICZ.JEŻELI($ B$2:$B$11;D3)
Może się również zdarzyć, że chcesz, aby kryteria znajdowały się w komórce, ale nie chcesz ich z operatorem. Na przykład możesz chcieć, aby komórka D3 miała numer 5, a nie <5.
W takim przypadku musisz utworzyć argument kryteria będący kombinacją operatora i odwołania do komórki:
=LICZ.JEŻELI($B$2:$B$11"<"&D3)
UWAGA: Po połączeniu operatora i odwołania do komórki operator jest zawsze w cudzysłowie. Operator i odwołanie do komórki są połączone znakiem ampersand (&).
#4 Policz komórki z wieloma kryteriami - między dwiema wartościami
Aby uzyskać liczbę wartości między dwiema wartościami, musimy użyć wielu kryteriów w funkcji LICZ.JEŻELI.
Oto dwie metody wykonania tego:
METODA 1: Korzystanie z funkcji LICZ.WARUNKI
Funkcja LICZ.WARUNKI może obsługiwać wiele kryteriów jako argumenty i zlicza komórki tylko wtedy, gdy wszystkie kryteria mają wartość PRAWDA. Aby policzyć komórki o wartościach między dwiema określonymi wartościami (powiedzmy 5 i 10), możemy użyć następującej funkcji LICZ.WARUNKI:
=LICZ.WARUNKI($B$2:$B$11",">5",$B$2:$B$11"<10")
UWAGA: Powyższa formuła nie liczy komórek zawierających 5 lub 10. Jeśli chcesz uwzględnić te komórki, użyj operatorów większy niż równy (>=) i mniejszy niż równy (<=). Oto wzór:
=LICZ.WARUNKI($B$2:$B$11",">=5",$B$2:$B$11"<=10")
Możesz również mieć te kryteria w komórkach i użyć odwołania do komórki jako kryteriów. W takim przypadku NIE musisz umieszczać kryteriów w podwójnych cudzysłowach (patrz zdjęcie poniżej):
Możesz także użyć kombinacji odwołań do komórek i operatorów (gdzie operator jest wprowadzany bezpośrednio w formule). Po połączeniu operatora i odwołania do komórki operator jest zawsze w cudzysłowie. Operator i odwołanie do komórki są połączone znakiem ampersand (&).
METODA 2: Korzystanie z dwóch funkcji LICZ.JEŻELI
Jeśli masz wiele kryteriów, możesz użyć funkcji LICZ.JEŻELI lub utworzyć kombinację funkcji LICZ.JEŻELI. Poniższa formuła również zrobiłaby to samo:
=LICZ.JEŻELI($B$2:$B$11",">5")-LICZ.JEŻELI($B$2:$B$11",">10")
W powyższym wzorze najpierw znajdujemy liczbę komórek, które mają wartość większą niż 5 i odejmujemy liczbę komórek o wartości większej niż 10. To dałoby nam wynik 5 (co jest liczbą komórek, które mają wartości większe niż 5 i mniejsze niż równe 10).
Jeśli chcesz, aby formuła zawierała zarówno 5, jak i 10, użyj zamiast tego następującej formuły:
=LICZ.JEŻELI($B$2:$B$11”>=5″)-LICZ.JEŻELI($B$2:$B$11”>10″)
Jeśli chcesz, aby formuła wykluczała zarówno „5”, jak i „10” z liczenia, użyj następującej formuły:
=LICZ.JEŻELI($2$:$B11$”>=5″)-LICZ.JEŻELI($B$2:$B$11”>10″)-LICZ.JEŻELI($B$2:$B11,10)
Możesz mieć te kryteria w komórkach i używać odwołań do komórek lub możesz użyć kombinacji operatorów i odwołań do komórek.
Korzystanie z kryteriów TEKSTOWYCH w funkcjach programu Excel
#1 Policz komórki, gdy kryteria są RÓWNE określonemu tekstowi
Aby policzyć komórki, które zawierają dokładne dopasowanie do określonego tekstu, możemy po prostu użyć tego tekstu jako kryterium. Na przykład w zestawie danych (pokazanym poniżej na zdjęciu), jeśli chcę policzyć wszystkie komórki z imieniem Joe, mogę użyć poniższej formuły:
=LICZ.JEŻELI(2 zł: 11 zł;Jan”)
Ponieważ jest to ciąg tekstowy, muszę umieścić kryteria tekstowe w podwójnych cudzysłowach.
Możesz również umieścić kryteria w komórce, a następnie użyć tego odwołania do komórki (jak pokazano poniżej):
=LICZ.JEŻELI($B$2:$B$11;E3)
UWAGA: Możesz otrzymać błędne wyniki, jeśli w kryteriach lub zakresie kryteriów znajdują się spacje wiodące/końcowe. Upewnij się, że wyczyściłeś dane przed użyciem tych formuł.
#2 Policz komórki, gdy kryteria NIE są RÓWNE określonemu tekstowi
Podobnie do tego, co widzieliśmy w powyższym przykładzie, możesz również policzyć komórki, które nie zawierają określonego tekstu. Aby to zrobić, musimy użyć operatora nierównego ().
Załóżmy, że chcesz policzyć wszystkie komórki, które nie zawierają imienia JOE, oto formuła, która to zrobi:
=LICZ.JEŻELI(2 zł: 11 zł;Jan”)
Możesz również mieć kryteria w komórce i użyć odwołania do komórki jako kryteriów. W takim przypadku NIE musisz umieszczać kryteriów w podwójnych cudzysłowach (patrz zdjęcie poniżej):
=LICZ.JEŻELI($B$2:$B$11;E3)
Może również zaistnieć przypadek, w którym chcesz, aby kryteria znajdowały się w komórce, ale nie chcesz tego z operatorem. Na przykład możesz chcieć, aby komórka D3 miała imię Joe, a nie Joe.
W takim przypadku musisz utworzyć argument kryteriów, który jest kombinacją operatora i odwołania do komórki (patrz zdjęcie poniżej):
=LICZ.JEŻELI(2 zł: 11 zł””&E3)
Po połączeniu operatora i odwołania do komórki operator jest zawsze w cudzysłowie. Operator i odwołanie do komórki są połączone znakiem ampersand (&).
Korzystanie z kryteriów DATE w funkcjach programu Excel LICZ.JEŻELI i LICZ.JEŻELI
Excel przechowuje datę i godzinę jako liczby. Więc możemy go używać w ten sam sposób, w jaki używamy liczb.
#1 Policz komórki, gdy kryteria są RÓWNE do określonej daty
Aby uzyskać liczbę komórek zawierających określoną datę, użyjemy operatora równości (=) wraz z datą.
Do korzystania z daty polecam skorzystać z funkcji DATE, która eliminuje możliwość błędu w wartości daty. Na przykład, jeśli chcę użyć daty 1 września 2015, mogę użyć funkcji DATA, jak pokazano poniżej:
=DATA(2015;9,1)
Ta formuła zwróciłaby tę samą datę pomimo różnic regionalnych. Na przykład 01-09-2015 to 1 września 2015 r. zgodnie ze składnią daty w USA, a 09 stycznia 2015 r. zgodnie ze składnią daty w Wielkiej Brytanii. Jednak ta formuła zawsze zwraca 1 września 2105.
Oto wzór na zliczanie liczby komórek zawierających datę 02-09-2015:
=LICZ.JEŻELI($A$2:$A$11;DATA(2015,9,2))
#2 Policz komórki, gdy kryteria są PRZED lub PO określonej dacie
Aby zliczyć komórki zawierające datę przed lub po określonej dacie, możemy użyć operatorów mniej niż/większy niż.
Na przykład, jeśli chcę policzyć wszystkie komórki zawierające datę przypadającą po 2 września 2015 r., mogę użyć formuły:
=LICZ.JEŻELI($A$2:$A$11",">"&DATA(2015,9,2))
Podobnie możesz również policzyć liczbę komórek przed określoną datą. Jeśli chcesz uwzględnić datę w liczeniu, użyj operatora „równe” wraz z operatorem „większe niż/mniejsze niż”.
Możesz także użyć odwołania do komórki zawierającego datę. W takim przypadku należy połączyć operator (w cudzysłowie) z datą za pomocą znaku ampersand (&).
Zobacz przykład poniżej:
=LICZ.JEŻELI(2$A$:$A$11",">"&F3)
#3 Policz komórki z wieloma kryteriami - między dwiema datami
Aby uzyskać liczbę wartości między dwiema wartościami, musimy użyć wielu kryteriów w funkcji LICZ.JEŻELI.
Możemy to zrobić za pomocą dwóch metod - jednej pojedynczej funkcji LICZ.JEŻELI lub dwóch funkcji LICZ.JEŻELI.
METODA 1: Korzystanie z funkcji LICZ.WARUNKI
Funkcja LICZ.WARUNKI może przyjmować wiele kryteriów jako argumenty i zlicza komórki tylko wtedy, gdy wszystkie kryteria mają wartość PRAWDA. Aby policzyć komórki z wartościami między dwiema określonymi datami (powiedzmy 2 września i 7 września), możemy użyć następującej funkcji LICZ.WARUNKI:
=LICZ.WARUNKI($A$2:$A$11",">"&DATA(2015,9,2);$A$2:$A$11"<"&DATA(2015,9,7))
Powyższa formuła nie liczy komórek zawierających określone daty. Jeśli chcesz uwzględnić również te daty, użyj operatorów większe niż równe (>=) i mniejsze niż równe (<=). Oto wzór:
=LICZ.WARUNKI($A$2:$A$11",">="&DATA(2015,9,2);$A$2:$A$11"<="&DATA(2015,9,7))
Możesz również umieścić daty w komórce i użyć odwołania do komórki jako kryterium. W takim przypadku nie możesz mieć operatora z datą w komórkach. Musisz ręcznie dodać operatory w formule (w podwójnych cudzysłowach) i dodać odwołanie do komórki za pomocą znaku & (&). Zobacz zdjęcie poniżej:
=LICZ.WARUNKI($A$2:$A$11",">"&F3;$A$2:$A$11","<"&G3)
METODA 2: Korzystanie z funkcji LICZ.JEŻELI
Jeśli masz wiele kryteriów, możesz użyć jednej funkcji LICZ.JEŻELI lub utworzyć kombinację dwóch funkcji LICZ.JEŻELI. Poniższa formuła również sprawdzi się:
=LICZ.JEŻELI(2$A$2$A$11",">"&DATA(2015,9,2))-LICZ.JEŻELI($A$2:$A$11",">"&DATA(2015,9,7))
W powyższym wzorze najpierw znajdujemy liczbę komórek, które mają datę po 2 września i odejmujemy liczbę komórek z datami po 7 września. To dałoby nam wynik 7 (co jest liczbą komórek, które mają daty po 2 września i do 7 września).
Jeśli nie chcesz, aby formuła liczyła zarówno 2 września, jak i 7 września, użyj zamiast tego następującej formuły:
=LICZ.JEŻELI($A$2:$A$11",">="&DATA(2015,9,2))-LICZ.JEŻELI($A$2:$A$11",">"&DATA(2015,9,7))
Jeśli chcesz wykluczyć obie daty z liczenia, użyj następującej formuły:
=LICZ.JEŻELI($A$2:$A$11",">"&DATA(2015,9,2))-LICZ.JEŻELI($A$2:$A$11",">"&DATA(2015,9,7)-LICZ.JEŻELI ($A 2 USD: 11 USD, DATA (2015, 9, 7)))
Ponadto możesz mieć daty kryteriów w komórkach i używać odwołań do komórek (wraz z operatorami w podwójnych cudzysłowach połączonych znakiem ampersand).
Używanie ZNAKÓW WIELDCARD w kryteriach w funkcjach LICZ.JEŻELI i LICZ.JEŻELI
W programie Excel są trzy znaki wieloznaczne:
- * (gwiazdka) - Reprezentuje dowolną liczbę znaków. Na przykład ex* może oznaczać excel, excels, example, expert itp.
- ? (znak zapytania) - Reprezentuje jedną postać. Na przykład Tr?mp może oznaczać Trump lub Tramp.
- ~ (tylda) - Służy do identyfikacji znaku wieloznacznego (~, *, ?) w tekście.
Możesz użyć funkcji LICZ.JEŻELI ze znakami wieloznacznymi, aby zliczyć komórki, gdy inna wbudowana funkcja zliczania nie powiedzie się. Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej:
Weźmy teraz różne przykłady:
#1 Policz komórki zawierające tekst
Aby policzyć komórki zawierające tekst, możemy użyć znaku wieloznacznego * (gwiazdka). Ponieważ gwiazdka reprezentuje dowolną liczbę znaków, policzy wszystkie komórki zawierające dowolny tekst. Oto wzór:
=LICZ.WARUNKI(2 C$: 11 C$”*”)
Uwagi: Powyższa formuła ignoruje komórki zawierające liczby, puste komórki i wartości logiczne, ale zlicza komórki zawierające apostrof (i dlatego są puste) lub komórki zawierające pusty ciąg (=””), które mogły zostać zwrócone jako część formuły.
Oto szczegółowy samouczek dotyczący obsługi przypadków, w których występuje pusty ciąg lub apostrof.
Oto szczegółowy samouczek dotyczący obsługi przypadków, w których występują puste ciągi lub apostrofy.
Poniżej znajduje się film wyjaśniający różne scenariusze liczenia komórek z tekstem.
#2 Policz niepuste komórki
Jeśli myślisz o użyciu funkcji ILE.NIEPUSTYCH, pomyśl jeszcze raz.
Spróbuj, a może cię zawieść. ILE.NIEPUSTYCH policzy również komórkę zawierającą pusty ciąg (często zwracany przez formuły jako =”” lub gdy ludzie wprowadzą tylko apostrof w komórce). Komórki zawierające puste ciągi wyglądają na puste, ale nie są, a zatem są zliczane przez funkcję ILE.NIEPUSTYCH.
ILE.NIEPUSTYCH policzy również komórkę zawierającą pusty ciąg (często zwracany przez formuły jako =”” lub gdy ludzie wprowadzą tylko apostrof w komórce). Komórki zawierające puste ciągi wyglądają na puste, ale nie są, a zatem są zliczane przez funkcję ILE.NIEPUSTYCH.
Więc jeśli użyjesz formuły = ILE.LICZB (A1:A11), zwróci 11, podczas gdy powinna zwrócić 10.
Oto poprawka:
=LICZ.JEŻELI($A$1:$A$11"?*")+LICZBA($A$1$A$11)+SUMPRODUCT(-ISLOGICZNE($A$1:$A$11))
Zrozummy tę formułę, rozkładając ją:
- LICZ.JEŻELI($N$8:$N$18"?*") - ta część formuły zwraca 5. Obejmuje to każdą komórkę zawierającą znak tekstowy. A ? reprezentuje jeden znak, a * reprezentuje dowolną liczbę znaków. Dlatego kombinacja ?* w kryteriach zmusza program Excel do zliczania komórek, które zawierają co najmniej jeden znak tekstowy.
- COUNT ($ A $ 1: $ A $ 11) - Zlicza wszystkie komórki zawierające liczby. W powyższym przykładzie zwraca 3.
- SUMPRODUCT(-ISLOGICAL($A$1:$A$11) - Zlicza wszystkie komórki zawierające wartości logiczne. W powyższym przykładzie zwraca 2.
#3 Policz komórki zawierające określony tekst
Załóżmy, że chcemy policzyć wszystkie komórki, w których nazwa przedstawiciela handlowego zaczyna się od J. Można to łatwo osiągnąć, używając symbolu wieloznacznego w funkcji LICZ.JEŻELI. Oto wzór:
=LICZ.WARUNKI($C$2:$C$11;J*")
Kryteria J* określają, że tekst w komórce powinien zaczynać się od J i może zawierać dowolną liczbę znaków.
Jeśli chcesz policzyć komórki zawierające alfabet w dowolnym miejscu tekstu, otocz je gwiazdką po obu stronach. Na przykład, jeśli chcesz policzyć komórki zawierające w sobie alfabet „a”, użyj *a* jako kryterium.
Ten artykuł jest niezwykle długi w porównaniu z innymi moimi artykułami. Mam nadzieję, że ci się podobało. Daj mi znać swoje przemyślenia, zostawiając komentarz.
Przydatne mogą być również następujące samouczki programu Excel:
- Policz liczbę słów w Excelu.
- Policz komórki na podstawie koloru tła w programie Excel.
- Jak zsumować kolumnę w Excelu (5 naprawdę łatwych sposobów)
