- Въведение в функциите на Excel COUNTIF и COUNTIFS
- Използване на НОМЕРНИ критерии в Excel COUNTIF функции
- Използване на критерии за ТЕКСТ във функции на Excel
- Използване на критерии DATE във функциите на Excel COUNTIF и COUNTIFS
- Използване на WILDCARD CHARACTERS в критерии във функции COUNTIF & COUNTIFS
Excel има много функции, при които потребителят трябва да посочи един или няколко критерия, за да получи резултата. Например, ако искате да броите клетки въз основа на множество критерии, можете да използвате функциите COUNTIF или COUNTIFS в Excel.
Този урок обхваща различни начини за използване на един или няколко критерия във функциите COUNTIF и COUNTIFS в Excel.
Въпреки че в този урок ще се съсредоточа основно върху функциите COUNTIF и COUNTIFS, всички тези примери могат да се използват и в други функции на Excel, които приемат множество критерии като входни данни (като SUMIF, SUMIFS, AVERAGEIF и AVERAGEIFS).
Въведение в функциите на Excel COUNTIF и COUNTIFS
Нека първо разберем как да използваме функциите COUNTIF и COUNTIFS в Excel.
Функция Excel COUNTIF (приема единични критерии)
Функцията Excel COUNTIF е най -подходяща за ситуации, когато искате да броите клетки въз основа на един критерий. Ако искате да броите въз основа на множество критерии, използвайте функцията COUNTIFS.
Синтаксис
= COUNTIF (диапазон, критерии)
Аргументи за въвеждане
- диапазон - диапазона от клетки, които искате да преброите.
- критерии - критериите, които трябва да бъдат оценени спрямо обхвата на клетките, за да бъде преброена клетката.
Функция Excel COUNTIFS (приема множество критерии)
Функцията Excel COUNTIFS е най -подходяща за ситуации, когато искате да броите клетки въз основа на множество критерии.
Синтаксис
= COUNTIFS (диапазон_критерии1, критерии1, [диапазон_критерии2, критерии2] …)
Аргументи за въвеждане
- диапазон на критериите1 - Обхватът на клетките, за които искате да оцените спрямо критерии1.
- критерии 1 - критериите, които искате да оцените за range_ range1, за да определите кои клетки да се броят.
- [обхват_критерии2] - Обхватът на клетките, за които искате да оцените спрямо критерии2.
- [критерии2] - критериите, които искате да оцените за range_ range2, за да определите кои клетки да се броят.
Сега нека разгледаме някои примери за използване на множество критерии в функциите COUNTIF в Excel.
Използване на НОМЕРНИ критерии в Excel COUNTIF функции
#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.
Ето два метода за това:
МЕТОД 1: Използване на функцията 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 Брой клетки, когато критериите са РЪВНИ на посочен текст
За да преброим клетки, които съдържат точно съвпадение на посочения текст, можем просто да използваме този текст като критерии. Например в набора от данни (показан по -долу на снимката), ако искам да преброя всички клетки с името Джо в него, мога да използвам формулата по -долу:
= 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, тъй като тя премахва всяка възможност за грешка в стойността на датата. Така например, ако искам да използвам датата 1 септември 2015 г., мога да използвам функцията DATE, както е показано по -долу:
= ДАТА (2015,9,1)
Тази формула би върнала същата дата въпреки регионалните различия. Например 01-09-2015 ще бъде 1 септември 2015 г. според синтаксиса на датите в САЩ и 09 януари 2015 г. според синтаксиса за дата във Великобритания. Тази формула обаче винаги ще връща 1 септември 2105 г.
Ето формулата за преброяване на броя клетки, които съдържат датата 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Брой клетки, когато критериите са ПРЕДИ или СЛЕД определена дата
За да броим клетки, които съдържат дата преди или след определена дата, можем да използваме операторите по -малки от/по -големи от.
Например, ако искам да преброя всички клетки, които съдържат дата, която е след 02 септември 2015 г., мога да използвам формулата:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))
По същия начин можете също да преброите броя на клетките преди определена дата. Ако искате да включите дата в броенето, използвайте оператора „равно на“ заедно с оператора „по -голямо от/по -малко от“.
Можете също да използвате препратка към клетка, която съдържа дата. В този случай трябва да комбинирате оператора (в двойни кавички) с датата, като използвате амперсанд (&).
Вижте примера по -долу:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & F3)
#3 Брой клетки с множество критерии - между две дати
За да получим брой стойности между две стойности, трябва да използваме множество критерии във функцията COUNTIF.
Можем да направим това, като използваме два метода - Една единична функция COUNTIFS или две функции COUNTIF.
МЕТОД 1: Използване на функцията COUNTIFS
Функцията COUNTIFS може да приема множество критерии като аргументи и да брои клетките само когато всички критерии са TRUE. За да броим клетки със стойности между две определени дати (да речем 2 септември и 7 септември), можем да използваме следната функция 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))
В горната формула първо откриваме броя на клетките с дата след 2 септември и изваждаме броя на клетките с дати след 7 септември. Това би ни дало резултата като 7 (което е броят на клетките с дати след 2 септември и на или преди 7 септември).
Ако не искате формулата да отчита и 2, и 7 септември, използвайте следната формула:
= 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)))
Също така можете да имате датите на критериите в клетките и да използвате препратките към клетките (заедно с операторите в двойни кавички, съединени с помощта на амперсанд).
Използване на WILDCARD CHARACTERS в критерии във функции COUNTIF & COUNTIFS
В Excel има три заместващи знака:
- * (звездичка) - Той представлява произволен брой знаци. Например, ex* може да означава excel, excels, например, експерт и т.н.
- ? (въпросителен знак) - Той представлява един единствен знак. Например, Tr? Mp може да означава Тръмп или Бродяга.
- ~ (тилда) - Използва се за идентифициране на заместващ знак (~, *,?) В текста.
Можете да използвате функцията 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))
Нека разберем тази формула, като я разбием:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Тази част от формулата връща 5. Това включва всяка клетка, която има текстов символ в нея. А? представлява един знак и * представлява произволен брой знаци. Следователно комбинацията?* В критерия принуждава Excel да брои клетки, които имат поне един текст в него.
- COUNT ($ A $ 1: $ A $ 11) - Това отчита всички клетки, които съдържат числа. В горния пример връща 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - Това отчита всички клетки, които съдържат логически стойности. В горния пример връща 2.
#3 Брой клетки, които съдържат определен текст
Да приемем, че искаме да преброим всички клетки, където името на търговския представител започва с J. Това може лесно да се постигне, като се използва заместващ знак във функцията COUNTIF. Ето формулата:
= COUNTIFS ($ C $ 2: $ C $ 11, "J*")
Критерият J* определя, че текстът в клетка трябва да започва с J и може да съдържа произволен брой знаци.
Ако искате да преброите клетки, които съдържат азбуката, навсякъде в текста, оградете я със звездичка от двете страни. Например, ако искате да броите клетки, които съдържат азбуката „а“ в нея, използвайте * a * като критерии.
Тази статия е необичайно дълга в сравнение с другите ми статии. Надявам се да ви е харесало. Кажете ми вашите мисли, като оставите коментар.
Можете също така да намерите за полезни следните уроци по Excel:
- Пребройте броя думи в Excel.
- Пребройте клетките въз основа на цвета на фона в Excel.
- Как да сумирате колона в Excel (5 наистина лесни начина)
