Как да използвате множество условия в Excel COUNTIF и COUNTIFS

Excel има много функции, при които потребителят трябва да посочи едно или повече условия, за да получи резултата.Например, ако искате да преброите клетки въз основа на множество критерии, можете да използвате функциите COUNTIF или COUNTIFS в Excel.

Този урок обхваща различни начини за използване на единичен или множество критерии във функциите COUNTIF и COUNTIFS на Excel.

Макар че ще се съсредоточа основно върху функциите COUNTIF и COUNTIFS в този урок, всички тези примери могат да се използват и с други функции на Excel, които приемат множество критерии като вход (като SUMIF, SUMIFS, AVERAGEIF и AVERAGEIFS).

Въведение във функциите COUNTIF и COUNTIFS на Excel

Нека първо разберем как да използваме функциите COUNTIF и COUNTIFS в Excel.

Excel функция COUNTIF (с един критерий)

Функцията COUNTIF на Excel работи най-добре, когато искате да преброите клетки въз основа на един критерий.Ако искате да броите въз основа на множество критерии, използвайте функцията COUNTIFS.

синтаксис

=COUNTIF(диапазон, условие)

Входни параметри

• Обхват -  Обхватът от клетки, който искате да изчислите.
• състояние -Критерии, които трябва да бъдат оценени спрямо обхвата от клетки, които трябва да бъдат преброени.

Excel функция COUNTIFS (с множество критерии)

Функцията COUNTIFS на Excel работи най-добре, когато искате да преброите клетки въз основа на множество критерии.

синтаксис

=COUNTIFS(критерии_диапазон1,критерии1,[диапазон_критерии2,критерии2]…)

Входни параметри

• критерии_диапазон1 – Обхватът от клетки, който искате да оцените спрямо критерий 1.
• критерии1 – Критерият, който искате да оцените, е criterion_range1, за да определите кои клетки да оцените.
• [диапазон_критерии2] – Обхватът от клетки, който искате да оцените спрямо критерий 2.
• [критерии2] – Критерият, който искате да оцените, е criterion_range2, за да определите кои клетки да се броят.

Сега нека разгледаме някои примери за използване на множество критерии във функцията COUNTIF на Excel.

Използвайте условието NUMBER във функцията COUNTIF на Excel

#1 Пребройте клетките, когато критерият е равен на стойност

За да получите броя на клетките, където параметърът на условието е равен на посочената стойност, можете да въведете условието директно или да използвате препратка към клетка, която съдържа условието.

По-долу е даден пример, в който броим клетки, които съдържат числото 9 (което означава, че параметърът на условието е равен на 9).Ето формулата:

=COUNTIF($B$2:$B$11,D3)

В примера по-горе (на снимката) критерият е в клетка D3.Можете също така да въвеждате условия директно във формулите.Например, можете също да използвате:

=COUNTIF($B$2:$B$11,9)

#2 Пребройте клетките, когато условието е по-голямо от стойността

За да получим броя на клетките със стойност, по-голяма от определена стойност, използваме оператора по-голямо от (">").Можем да го използваме директно във формули или можем да използваме препратки към клетки с условия.

Всеки път, когато използваме оператор в условие в Excel, трябва да го поставим в двойни кавички.Например, ако условието е по-голямо от 10, тогава трябва да въведете ">10" като условие (вижте изображението по-долу):

Ето формулата:

=COUNTIF($B$2:$B$11,”>10″)

Можете също да включите условия в клетките и да използвате препратки към клетки като условия.В този случай не е необходимо да поставяте условието в двойни кавички:

=COUNTIF($B$2:$B$11,D3)

Това може да се случи и когато искате условието да е в клетка, но не искате да го използвате с оператор.Например, може да искате числото в клетка D3 да бъде 10 вместо >10.

В този случай трябва да създадете условен параметър, който е комбинация от оператор и препратка към клетка (вижте изображението по-долу):

=COUNTIF($B$2:$B$11,”>”&D3)

Забележка: Когато комбинирате оператори и препратки към клетки, операторите винаги са затворени в двойни кавички.Операторите и препратките към клетките са свързани с амперсанд (&).

#3 Пребройте клетките, когато критерият е по-малък от стойност

За да получим броя на клетките със стойност, по-малка от определена стойност, използваме оператора по-малко от ("<").Можем да го използваме директно във формули или можем да използваме препратки към клетки с условия.

Всеки път, когато използваме оператор в условие в Excel, трябва да го поставим в двойни кавички.Например, ако критерият е, че количеството трябва да бъде по-малко от 5, тогава трябва да въведете "<5" като критерий (вижте изображението по-долу):

=COUNTIF($B$2:$B$11,”<5”)

Можете също да включите условия в клетките и да използвате препратки към клетки като условия.В този случай не е необходимо да поставяте стандарта в двойни кавички (вижте изображението по-долу):

=COUNTIF($B$2:$B$11,D3)

Също така може да искате условието да е в клетката, но не и при оператора.Например, може да искате клетка D3 да има числото 5 вместо <5.

В този случай трябва да създадете условен параметър, който е комбинация от оператор и препратка към клетка:

=COUNTIF($B$2:$B$11,"<"&D3)

Забележка: Когато комбинирате оператори и препратки към клетки, операторите винаги са затворени в двойни кавички.Операторите и препратките към клетките са свързани с амперсанд (&).

#4 Пребройте клетки с множество критерии – между две стойности

За да получим броя на стойностите между две стойности, трябва да използваме множество условия във функцията COUNTIF.

Ето два начина да направите това:

Метод XNUMX: Използвайте функцията COUNTIFS

Функцията COUNTIFS може да приема множество условия като аргументи и да брои клетки само ако всички условия са TRUE.За да преброим клетки със стойности между две посочени стойности, като 5 и 10, можем да използваме следната функция COUNTIFS:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Забележка: Горната формула не отчита клетки, които съдържат 5 или 10.Ако искате да включите тези клетки, използвайте операторите по-голямо или равно на (>=) и по-малко или равно на (<=).Ето формулата:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Можете също да включите тези условия в клетките и да използвате препратки към клетки като условия.В този случай не е необходимо да поставяте стандарта в двойни кавички (вижте изображението по-долу):

Можете също да използвате комбинация от препратки към клетки и оператори (въвеждане на оператори директно във формули).Когато се комбинират оператори и препратки към клетки, операторите винаги са затворени в двойни кавички.Операторите и препратките към клетките са свързани с амперсанд (&).

Метод 2: Използвайте две функции COUNTIF

Ако имате няколко условия, можете да използвате COUNTIFS или да създадете комбинация от функции COUNTIF.Следната формула ще направи същото:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

В горната формула първо намираме броя на клетките със стойност по-голяма от 5 и след това изваждаме броя на клетките със стойност, по-голяма от 10.Това ще ни даде резултат 5 (т.е. със стойности, по-големи от 5 и по-малки или равни на 10).

Ако искате формулата да съдържа и 5, и 10, вместо това използвайте следната формула:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Ако искате формулата да изключи "5" и "10" от броенето, използвайте следната формула:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Можете да включите тези условия в клетки и да използвате препратки към клетки или можете да използвате комбинация от оператори и препратки към клетки.

Използвайте условия ТЕКСТ във функциите на Excel

#1 Пребройте клетките, когато условието е равно на посочения текст

За да преброим клетки, които съдържат точно съвпадение с определен текст, можем просто да използваме този текст като условие.Например, в набор от данни (показан по-долу), ако искам да преброя всички клетки с име Joe, мога да използвам следната формула:

=COUNTIF($B$2:$B$11,"Джо")

Тъй като това е текстов низ, трябва да сложа текстовото условие в двойни кавички.

Можете също да включите условие в клетка и след това да използвате тази препратка към клетка (както е показано по-долу):

=COUNTIF($B$2:$B$11,E3)

Забележка: Може да получите грешни резултати, ако има водещи/завършващи интервали в условието или диапазона на условието.Уверете се, че сте почистили данните, преди да използвате тези формули.

#2 Пребройте клетките, когато условието не е равно на посочения текст

Подобно на това, което видяхме в примера по-горе, можете също да преброите клетки, които не съдържат определен текст.За да направим това, трябва да използваме оператора не е равно (<>).

Ако приемем, че искате да преброите всички клетки, които не съдържат името JOE, ето формула, която може да направи това:

=COUNTIF($B$2:$B$11,”<>Джо”)

Можете също да включите условия в клетките и да използвате препратки към клетки като условия.В този случай не е необходимо да поставяте стандарта в двойни кавички (вижте изображението по-долу):

=COUNTIF($B$2:$B$11,E3)

Това може да се случи и когато искате условието да е в клетка, но не искате да го използвате с оператор.Например, може да искате клетка D3 да има името Джо вместо <>Джо.

В този случай трябва да създадете условен параметър, който е комбинация от оператор и препратка към клетка (вижте изображението по-долу):

=COUNTIF($B$2:$B$11,”<>”&E3)

Когато се комбинират оператори и препратки към клетки, операторите винаги са затворени в двойни кавички.Операторите и препратките към клетките са свързани с амперсанд (&).

Използване на DATE условия в Excel функции COUNTIF и COUNTIFS

Excel съхранява датите и часовете като числа.Така че можем да го използваме като число.

#1 Пребройте клетките, когато условието е равно на определена дата

За да получим броя на клетките, които съдържат определена дата, ще използваме оператора за равенство (=) заедно с датата.

За да работите с дати, препоръчвам да използвате функцията DATE, тъй като тя елиминира всяка вероятност стойността на датата да е грешна.Така че, например, ако искам да използвам датата 2015 септември 9 г., мога да използвам функцията DATE по следния начин:

=ДАТА(2015,9,1)

Въпреки регионалните различия, тази формула ще върне същата дата.Например 01-09-2015 ще бъде 2015 септември 9 г. според синтаксиса на датата в САЩ и 1 януари 2015 г. при синтаксиса на датата в Обединеното кралство.Тази формула обаче винаги ще се връща на 2105 септември 9 г.

Ето формулата за преброяване на броя на клетките, съдържащи датата 02-09-2015:

=COUNTIF($A$2:$A$11,ДАТА(2015,9,2))

#2 Пребройте клетките, когато условието е преди или след определена дата

За да преброим клетки, които съдържат дати преди или след определена дата, можем да използваме оператори по-малко от/повече от.

Например, ако искам да преброя всички клетки, съдържащи дати след 2015 септември 9 г., мога да използвам следната формула:

=COUNTIF($A$2:$A$11,”>”&ДАТА(2015,9,2))

По същия начин можете също да преброите броя на клетките преди определена дата.Ако искате да включите дати в броя, използвайте оператора „равно“ заедно с операторите „по-голямо от/по-малко от“.

Можете също да използвате препратки към клетки, които съдържат дати.В този случай трябва да използвате амперсанд (&), за да комбинирате оператора (в двойни кавички) с датата.

Вижте примера по-долу:

=COUNTIF($A$2:$A$11,”>”&F3)

#3 Пребройте клетки с множество критерии – между две дати

За да получим броя на стойностите между две стойности, трябва да използваме множество условия във функцията COUNTIF.

Можем да направим това по два начина - една функция COUNTIFS или две функции COUNTIF.

Метод XNUMX: Използвайте функцията COUNTIFS

Функцията COUNTIFS може да приема множество условия като аргументи и да брои клетки само ако всички условия са TRUE.За да преброим клетки със стойности между две посочени дати, като 9 септември и 2 септември, можем да използваме следната функция COUNTIFS:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

Горната формула не отчита клетките, които съдържат посочената дата.Ако искате да включите и тези дати, използвайте операторите по-голямо или равно на (>=) и по-малко или равно на (<=).Ето формулата:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Можете също да включите дати в клетките и да използвате препратки към клетки като критерии.В този случай не можете да накарате оператора да включи датата в клетката.Трябва ръчно да добавите оператори към формулата (оградени в двойни кавички) и да използвате амперсанд (&), за да добавите препратки към клетки.Виж отдолу:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

Метод 2: Използвайте функцията COUNTIF

Ако имате няколко условия, можете да използвате функция COUNTIFS или да създадете комбинация от две функции COUNTIF.Следната формула също ще свърши работа:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

В горната формула първо намираме броя на клетките, чиято дата е след 9 септември, и след това изваждаме броя на клетките, чиято дата е след 2 септември.Това ще даде резултат 9 (тоест броят на клетките с дати след 7 септември и на или преди 7 септември).

Ако не искате формулата да изчислява както 9 септември, така и 2 септември, използвайте следната формула вместо това:

=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,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

Освен това можете да задавате стандартни дати в клетки и да използвате препратки към клетки (и оператори в двойни кавички с амперсанди).

Използване на заместващи знаци в условията на функциите COUNTIF и COUNTIFS

Има три заместващи знака в Excel:

1. *(звездичка) - представлява произволен брой знаци.Например ex* може да означава excel, excels, пример, експерт и т.н.
2. ? (въпросителен знак) - Представлява един символ.Например, Trömp може да означава Тръмп или Tramp.
3. ~ (тилда) - Използва се за идентифициране на заместващи знаци (~, *, ?) в текста.

Можете да използвате функцията COUNTIF с заместващи знаци, за да преброите клетките, когато други вградени функции за броене се провалят.Например, да предположим, че имате набор от данни, който изглежда така:

Сега нека вземем няколко примера:

#1 Пребройте клетки, които съдържат текст

За да преброим клетките с текст в тях, можем да използваме заместващия знак * (звездичка).Тъй като звездичките представляват произволен брой знаци, той ще преброи всички клетки, които съдържат текст.Ето формулата:

=COUNTIFS($C$2:$C$11,"*")

Забележка: Горната формула игнорира клетки, които съдържат числа, празни клетки и логически стойности, но брои клетки, които съдържат апострофи (и поради това изглеждат празни) или които може да са били включени като част от формулата.

Ето подробен урок за работа с случаи с празни низове или апострофи.

#2 Пребройте непразни клетки

Ако обмисляте да използвате функцията COUNTA, помислете отново.

Опитайте, може да ви разочарова.COUNTA също така ще брои клетки, които съдържат празни низове (обикновено се връщат от формули като ="", или когато хората просто въвеждат апострофи в клетките).Клетките, които съдържат празни низове, изглеждат празни, но не са, така че се броят от функцията COUNTA.

COUNTA също така ще брои клетки, които съдържат празни низове (обикновено се връщат от формули като ="", или когато хората просто въвеждат апострофи в клетките).Клетките, които съдържат празни низове, изглеждат празни, но не са, така че се броят от функцията COUNTA.

Така че, ако използвате формулата = COUNTA (A1:A11), той връща 11, когато трябва да върне 10.

Ето поправката:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Нека разберем тази формула, като я разбием:

#3 Пребройте клетките, съдържащи конкретен текст

Да предположим, че искаме да преброим всички клетки, където името на търговския представител започва с J.Това може лесно да се постигне чрез използване на заместващи знаци във функцията COUNTIF.Ето формулата:

=COUNTIFS($C$2:$C$11,"J*") 

Условието J* указва, че текстът в клетката трябва да започва с J и може да съдържа произволен брой знаци.

Ако искате да преброите клетки, които съдържат букви навсякъде в текста, заобиколете ги със звездичка.Например, ако искате да преброите клетки, които съдържат буквата "a", използвайте *a* като условие.

В сравнение с другите ми статии, тази е необичайно дълга.Надявам се да ви хареса.Кажете ми какво мислите, като оставите коментар.