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 policzyć komórki na podstawie wielu kryteriów, możesz użyć funkcji COUNTIF lub COUNTIFS w programie Excel.
Ten samouczek obejmuje różne sposoby korzystania z jednego lub wielu kryteriów w funkcji COUNTIF i COUNTIFS w Excelu.,
podczas gdy w tym samouczku skupię się głównie na funkcjach COUNTIF i COUNTIFS, 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 SUMIF, SUMIFS, AVERAGEIF i AVERAGEIFS).
Ten samouczek obejmuje:
Wprowadzenie do funkcji COUNTIF i COUNTIFS Excela
najpierw opanujmy korzystanie z funkcji COUNTIF i COUNTIFS w Excelu.,
funkcja Excel COUNTIF (przyjmuje pojedyncze kryteria)
funkcja Excel COUNTIF najlepiej nadaje się do sytuacji, w których chcesz policzyć komórki na podstawie jednego kryterium. Jeśli chcesz liczyć na podstawie wielu kryteriów, użyj funkcji COUNTIFS.
składnia
=COUNTIF(zakres, kryteria)
argumenty wejściowe
- range – zakres komórek, które chcesz policzyć.
- kryteria-kryteria, które muszą być oceniane w stosunku do zakresu komórek, aby komórka miała być policzona.,
funkcja liczników Excel (przyjmuje wiele kryteriów)
funkcja liczników Excel najlepiej nadaje się do sytuacji, w których chcesz liczyć komórki na podstawie wielu kryteriów.
składnia
=COUNTIFS(criteria_range1, criteria1,…)
Input Arguments
- criteria_range1 – zakres komórek, dla których chcesz obliczyć criteria1.
- criteria1 – kryteria, które chcesz ocenić dla criteria_range1, aby określić, które komórki policzyć.
- – zakres komórek, dla których chcesz ocenić kryteria.,
- – kryteria, które chcesz ocenić dla criteria_range2, aby określić, które komórki policzyć.
przyjrzyjmy się teraz przykładom użycia wielu kryteriów w funkcjach COUNTIF w Excelu.
Korzystanie z kryteriów liczbowych w programie Excel COUNTIF Functions
#1 licz komórki gdy kryteria są równe wartości
aby uzyskać liczbę komórek, w których argument kryteriów jest równy określonej wartości, możesz bezpośrednio wprowadzić kryteria lub użyć referencji komórki, która zawiera kryteria.,
Poniżej znajduje się przykład, w którym liczymy komórki zawierające liczbę 9 (co oznacza, że argument kryteriów jest równy 9). Oto formuła:
=COUNTIF($B$2:$B$11,D3)
w powyższym przykładzie (w pic) 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ć:
=COUNTIF ($B$2:$B$11,9)
#2 Count Cells gdy kryteria są większe niż wartość
aby uzyskać liczbę komórek o wartości większej niż określona wartość, używamy operatora greater than („>”). Możemy użyć go bezpośrednio w formule lub użyć referencji komórki, która ma kryteria.
za każdym razem, gdy używamy operatora w kryteriach w Excelu, musimy umieścić go w podwójnych cudzysłowach., Na przykład, jeśli kryteria są większe niż 10, to musimy wprowadzić „>10” jako kryteria (patrz pic poniżej):
oto formuła:
=COUNTIF($B$2:$B$11,”>10″)
Możesz również mieć kryteria w komórce i używać referencji komórki jako kryteriów. W tym przypadku nie musisz umieszczać kryteriów w podwójnych cudzysłowach:
=COUNTIF($B$2:$B$11,D3)
może być również przypadek, gdy 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 criteria, który jest kombinacją operatora i odwołania do komórki (patrz rysunek poniżej):
=COUNTIF($B$2:$B$11,”>”&D3)
uwaga: po połączeniu operatora i odwołania do komórki, operator jest zawsze w podwójnych cudzysłowach. Operator i odniesienie do komórki są połączone przez ampersand (&).,
#3 Count Cells gdy kryteria są 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ć referencji komórki, która ma kryteria.
za każdym razem, gdy używamy operatora w kryteriach w Excelu, musimy umieścić go w podwójnych cudzysłowach., Na przykład, jeśli kryterium jest to, że liczba powinna być mniejsza niż 5, to musimy wprowadzić „<5” jako kryteria (patrz rysunek poniżej):
=COUNTIF($B$2:$B$11,”<5″)
Możesz również mieć kryteria w komórce i używać referencji komórki jako kryteriów., W tym przypadku nie musisz umieszczać kryteriów w podwójnych cudzysłowach (patrz rysunek poniżej):
=COUNTIF($B$2:$B$11,D3)
również może wystąpić przypadek, gdy chcesz, aby kryteria były w komórce, ale nie chcesz tego z operatorem. Na przykład komórka D3 może mieć numer 5, a nie < 5.,
w takim przypadku musisz utworzyć argument criteria, który jest kombinacją operatora i odwołania do komórki:
=COUNTIF($B$2:$B$11,”<„&D3)
uwaga: po połączeniu operatora i odwołania do komórki, operator jest zawsze w podwójnych cudzysłowach. Operator i odniesienie do komórki są połączone przez ampersand (&).,
#4 zliczanie komórek z wieloma kryteriami – między dwiema wartościami
aby uzyskać zliczanie wartości między dwiema wartościami, musimy użyć wielu kryteriów w funkcji COUNTIF.
oto dwie metody:
metoda 1: Używanie funkcji COUNTIFS
funkcja COUNTIFS może obsługiwać wiele kryteriów jako argumenty i Zlicza komórki tylko wtedy, gdy wszystkie kryteria są prawdziwe., Aby policzyć komórki o wartościach pomiędzy dwoma podanymi wartościami (powiedzmy 5 i 10), możemy użyć następującej funkcji COUNTIFS:
=COUNTIFS($B$2:$b$11,”>5″,$B$2:$b$11,”<10″)
Uwaga: powyższy wzór nie liczy komórek zawierających 5 lub 10. Jeśli chcesz dołączyć te komórki, użyj operatorów większych niż równe (>=) I mniejszych niż równe (<=)., Oto formuła:
=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$b$11,”<=10″)
Możesz również mieć te kryteria w komórkach i używać referencji komórki jako kryteriów. W takim przypadku nie musisz umieszczać kryteriów w podwójnych cudzysłowach (patrz rysunek poniżej):
Możesz również użyć kombinacji odwołań do komórek i operatorów (gdzie operator jest wprowadzany bezpośrednio w formule). Gdy połączysz operator i odniesienie do komórki, operator jest zawsze w podwójnych cudzysłowach., Operator i odniesienie do komórki są połączone przez ampersand (&).
Metoda 2: Korzystanie z dwóch funkcji COUNTIF
Jeśli masz wiele kryteriów, możesz użyć funkcji COUNTIF lub utworzyć kombinację funkcji COUNTIF., Poniższa formuła również zrobi to samo:
=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($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 daje nam wynik jako 5 (co jest liczbą komórek, które mają wartości więcej niż 5 i mniej niż równe 10).,3c219a21b4″>
Jeśli chcesz, aby formuła zawierała zarówno 5, jak i 10, użyj następującej formuły:
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$b$11,”>10″)
Jeśli chcesz, aby formuła wykluczyła zarówno '5′, jak i ’10′ z zliczania, użyj następującej formuły:
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”> = > 10″)-COUNTIF($B$2:$B$11,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 Excela
#1 licz komórki gdy kryteria są równe podanemu tekstowi
aby policzyć komórki, które zawierają dokładne dopasowanie podanego tekstu, możemy po prostu użyć tego tekstu jako kryteriów. Na przykład, w zbiorze danych (pokazanym poniżej na zdjęciu), jeśli chcę policzyć wszystkie komórki z nazwą Joe, mogę użyć poniższej formuły:
=COUNTIF($B$2:$B$11,”Joe”)
ponieważ jest to ciąg tekstowy, muszę umieścić kryteria tekstu w podwójnych cudzysłowach.,
Możesz również mieć kryteria w komórce,a następnie użyć referencji tej komórki (jak pokazano poniżej):
=COUNTIF($B$2:$B$11, E3)
uwaga: możesz się pomylić wyniki, jeśli w zakresie kryteriów lub kryteriów znajdują się spacje wiodące/końcowe. Upewnij się, że wyczyścisz dane przed użyciem tych formuł.
#2 Policz komórki gdy kryteria nie są równe podanemu tekstowi
podobne do tego, co widzieliśmy w powyższym przykładzie, możesz również policzyć komórki, które nie zawierają podanego tekstu., Aby to zrobić, musimy użyć operatora not equal to (<>).
Załóżmy, że chcesz policzyć wszystkie komórki, które nie zawierają nazwy JOE, oto formuła, która to zrobi:
=COUNTIF($B$2:$B$11,”<>Joe”)
Możesz również mieć kryteria w komórce i używać referencji komórki jako kryteriów., W tym przypadku nie musisz umieszczać kryteriów w podwójnych cudzysłowach (patrz rysunek poniżej):
=COUNTIF($B$2:$B$11,E3)
może być również przypadek, gdy 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 nazwę Joe, a nie <> Joe.,
w takim przypadku musisz utworzyć argument criteria, który jest kombinacją operatora i odwołania do komórki (patrz rysunek poniżej):
=COUNTIF($B$2:$B$11,”<>”&E3)
gdy łączysz operator i odniesienie do komórki, operator jest zawsze w podwójnych cudzysłowach. Operator i odniesienie do komórki są połączone przez ampersand (&).
Korzystanie z kryteriów daty w funkcjach Excel COUNTIF i COUNTIFS
Excel przechowuje datę i czas jako liczby., Więc możemy używać tego samego sposobu, w jaki używamy liczb.
#1 Policz komórki gdy kryteria są równe podanej dacie
aby uzyskać liczbę komórek, które zawierają podaną datę, użyjemy operatora equal to (=) wraz z datą.
aby użyć daty, polecam korzystanie z funkcji DATE, ponieważ pozbywa się możliwości błędu w wartości daty. Na przykład, jeśli chcę użyć daty 1 września 2015, mogę użyć funkcji daty, Jak pokazano poniżej:
=Data(2015,9,1)
ta formuła zwróci tę samą datę pomimo różnic regionalnych., Na przykład 01-09-2015 to 1 września 2015 według składni daty w USA i 09 stycznia 2015 według składni daty w Wielkiej Brytanii. Jednak ta formuła zawsze wracała 1 września 2105 roku.
oto formuła do liczenia liczby komórek,które zawierają datę 02-09-2015:
=COUNTIF($a$2:$A$11, Data(2015,9,2))
#2 Policz komórki, gdy kryteria są przed lub po określonej dacie
policz komórki, które zawierają datę przed lub po określonej dacie, możemy użyć operatorów mniej niż/więcej niż.,
na przykład, jeśli chcę policzyć wszystkie komórki, które zawierają datę po 02 września 2015 r., mogę użyć formuły:
=COUNTIF($a$2:$A$11,”>”&Data(2015,9,2))
podobnie, możesz również policzyć liczbę komórek przed określoną datą. Jeśli chcesz dołączyć datę do zliczania, użyj operatora „equal to” wraz z operatorem „greater than / less than”.
Możesz również użyć odwołania do komórki, które zawiera datę., W tym przypadku należy połączyć operator (w podwójnych cudzysłowach) z datą za pomocą ampersand (&).
zobacz przykład poniżej:
=COUNTIF($a$2:$A$11,”>”&F3)
#3 Liczba komórek z wieloma kryteriami – pomiędzy dwoma datami
aby uzyskać zliczanie wartości pomiędzy dwoma wartościami, musimy użyć wielu kryteriów w funkcji COUNTIF.
możemy to zrobić za pomocą dwóch metod – jednej pojedynczej funkcji COUNTIF lub dwóch funkcji COUNTIF.,
metoda 1: Korzystanie z funkcji COUNTIFS
funkcja COUNTIFS może przyjmować wiele kryteriów jako argumenty i Zlicza komórki tylko wtedy, gdy wszystkie kryteria są prawdziwe. Aby zliczyć komórki o wartościach pomiędzy dwoma określonymi datami (powiedzmy 2 września i 7 września), możemy użyć następującej funkcji COUNTIFS:
=COUNTIFS($a$2:$A$11,”>”&DATE(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 również podać te daty, użyj operatorów greater than equal to (>=) I less than equal to (<=). Oto formuła:
=COUNTIFS($a$2:$A$11,”>=”&Data(2015,9,2),$a$2:$a$11,”<=”&date(2015,9,7))
Możesz również mieć daty w komórce i używać odniesienia do komórki jako kryteriów. 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ą ampersand (&). Zobacz zdjęcie poniżej:
=”$A$2:$A$11,”>”&F3,$A$2:$a$11,”<„&G3)
Metoda 2: Korzystanie z funkcji COUNTIF
Jeśli masz wiele kryteriów, możesz użyć jednej funkcji COUNTIF lub utworzyć kombinację dwóch funkcji COUNTIF., Poniższy wzór zrobiłby również sztuczkę:
=COUNTIF($a$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($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 daje nam wynik jako 7 (co jest liczbą komórek, które mają daty po 2 września i na lub przed 7 września).,
Jeśli chcesz wykluczyć obie daty Z liczenia, użyj następującej formuły:
=COUNTIF($a$2:$a$11,”>”&date(2015,9,2))-COUNTIF($a$2:$a$11,”>”&date(2015,9,7)-COUNTIF($a$2:$a$11, Date(2015,9,7)))
Możesz również mieć daty kryteriów w komórkach i używać odniesień do komórek (wraz z operatorami w podwójnych cudzysłowach połączonych za pomocą Ampersand).,
używanie znaków wieloznacznych w kryteriach w COUNTIF & funkcje COUNTIFS
w programie Excel są trzy znaki wieloznaczne:
Możesz użyć funkcji COUNTIF ze znakami wieloznacznymi do liczenia komórek, gdy inna wbudowana funkcja count zawiedzie. Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej:
teraz weźmy różne przykłady:
#1 Policz komórki zawierające tekst
aby policzyć komórki z tekstem, możemy użyć znaku wieloznacznego * (gwiazdka)., Ponieważ gwiazdka reprezentuje dowolną liczbę znaków, policzy wszystkie komórki, które mają w sobie dowolny tekst. Oto formuła:
=COUNTIFS($C$2:$C$11,”*”)
Uwaga: powyższa formuła ignoruje komórki, które zawierają liczby, puste komórki i wartości logiczne, ale policzy komórki zawierające apostrof (a zatem wydają się puste) lub komórki, które zawierają pusty łańcuch ( = „” ), który mógł zostać zwrócony jako część formuły.
oto szczegółowy samouczek obsługi przypadków, w których występuje pusty łańcuch lub apostrof.,
oto szczegółowy samouczek dotyczący obsługi przypadków, w których występują puste ciągi znaków lub apostrofy.
Poniżej znajduje się film wyjaśniający różne scenariusze zliczania komórek z tekstem.
#2 Policz niepuste komórki
Jeśli myślisz o użyciu funkcji COUNTA, pomyśl jeszcze raz.
spróbuj, a może Ci się nie udać. COUNTA będzie również liczyć komórkę, która zawiera pusty ciąg znaków (często zwracany przez formuły jako = „” lub gdy ludzie wprowadzają tylko apostrof w komórce). Komórki, które zawierają puste ciągi znaków, wyglądają na puste, ale nie są, a zatem liczone przez funkcję COUNTA.,
COUNTA będzie również liczyć komórkę, która zawiera pusty ciąg znaków (często zwracany przez formuły jako =”” lub gdy ludzie wprowadzają tylko apostrof w komórce). Komórki, które zawierają puste ciągi znaków, wyglądają na puste, ale nie są, a zatem liczone przez funkcję COUNTA.
więc jeśli użyjesz formuły =COUNTA(A1:A11), zwróci ona 11, podczas gdy powinna zwrócić 10.
oto poprawka:
=COUNTIF($A$1:$A$11,”?*”) + COUNT ($a$1:$A$11)+SUMPRODUCT (–ISL ($A$1:$A$11))
zrozummy tę formułę dzieląc ją:
- COUNTIF ($N$8:$N$18,”?,* „)- Ta część wzoru zwraca 5. Dotyczy to każdej komórki, która ma w sobie znak tekstowy. A ? reprezentuje jeden znak, a * reprezentuje dowolną liczbę znaków. Stąd ta kombinacja ?* w kryteriach wymusza excel zliczanie komórek, które mają w sobie 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
powiedzmy, że chcemy policzyć wszystkie komórki, w których nazwa przedstawiciela handlowego zaczyna się od J. można to łatwo osiągnąć za pomocą znaku wieloznacznego w funkcji COUNTIF. Oto formuła:
=COUNTIFS($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, zaznacz go gwiazdką po obu stronach., Na przykład, jeśli chcesz policzyć komórki zawierające alfabet „a”, użyj *a* jako kryteriów.