Функцията COUNTIF на Excel ви позволява да преброите броя на клетките, които отговарят на специфични критерии или условия в дадения диапазон.
Функцията COUNTIF е една от статистическите функции в Excel, която е комбинация от функции COUNT и IF или функцията COUNTA. Когато се използва във формула, функцията отчита броя на клетките, които отговарят на конкретни критерии или условия в един и същи или множество диапазони. Функцията COUNTIF помага да се преброят клетки, съдържащи текст, числа или дати, които отговарят на специфични критерии.
Можете да преброите клетки с помощта на функции COUNTIF или COUNTIFS в Excel. Разликата между функциите COUNTIF и COUNTIFS е, че COUNTIF се използва за преброяване на клетки, които отговарят на един критерий в един диапазон, докато COUNTIFS брои клетки, които отговарят на множество условия в един и същи или множество диапазони.
Тази статия ще ви покаже как да използвате двете функции COUNTIF и COUNTIFS в Excel.
Excel функция COUNTIF
Функцията COUNTIF ви позволява да извършвате преброяване на данни въз основа на конкретен критерий или условие. Условието, използвано във функцията, работи с логически оператори (, , =, >=, <=) и заместващи знаци (*, ?) за частично съвпадение.
Синтаксис на функцията COUNTIF
Структурата на функцията COUNTIF е:
=COUNTIF(диапазон,критерии)Параметри:
обхват– Обхватът на клетките за броене.критерии– Условието определя кои клетки трябва да бъдат включени в броя в посочения диапазон. Критериите могат да бъдат числова стойност, текст, препратка към адрес на клетка или уравнение.
Използване на функция COUNTIF за преброяване на числови стойности
Както обсъдихме по-горе, критериите (втори аргумент) във функцията COUNTIF дефинират условието, което казва на функцията кои клетки да брои.
Тази функция ви помага да преброите броя на клетките със стойности, които отговарят на логически условия като равно на, по-голямо от, по-малко или неравно на определена стойност и т.н.
В примера по-долу формулата брои клетки, които съдържат стойност, равна на 5 (критерии). Можете директно да вмъкнете „5“ във формулата или да използвате препратка към адреса на клетката, която има стойността (клетка D2 в примера по-долу).
=БРОЙ,АКО(B2:B11,D2)
Горната формула отчита броя на клетките в диапазона от клетки (B2:B11), които съдържат стойността, равна на стойността в клетка D2.
Следната формула преброява клетките, чиято стойност е по-малка от 5.
=COUNTIF(B2:B11,"<5")Операторът по-малко от (<) казва на формулата да брои клетки със стойност по-малка от „5“ в диапазона B2:B11. Всеки път, когато използвате оператор в състояние, не забравяйте да го поставите в двойни кавички (“”).
Понякога, когато искате да преброите клетките, като ги изследвате спрямо критерий (стойност) в клетка. В такива случаи направете критерий, като присъедините оператор и препратка към клетка. Когато направите това, трябва да поставите оператора за сравнение в двойни кавички (“”) и след това да поставите амперсанд (&) между оператора за сравнение и препратката към клетката.
=COUNTIF(B2:B11,">="&D2)
Снимката по-долу показва няколко примерни формули и техния резултат.
Използване на функция COUNTIF за преброяване на текстови стойности
За да преброите клетки, които съдържат определени текстови низове, използвайте този текстов низ като аргумент за критерии или клетката, която съдържа текстов низ. Например, в таблицата по-долу, ако искаме да преброим всички клетки в диапазона (B21:D27) с текстовата стойност в клетка B21 (sam) в него, можем да използваме следната формула:
=БРОЙ,АКО(B21:D27,B21)
Както обсъдихме по-рано, можем или да използваме текста „sam“ директно във формулата, или да използваме препратка към клетка, която има критериите (B21). Текстов низ винаги трябва да бъде затворен в двойни кавички (“”), когато се използва във формула в excel.
=COUNTIF(B21:D27,"сам")
За да преброите клетки, които не съдържат определен текст, използвайте следната формула:
=COUNTIF(B21:D27,""&B21)Не забравяйте да приложите „не е равно на“ "" оператор в двойни кавички.
Ако използвате текста „sam“ директно във формулата, трябва да затворите оператора „“ и текстовия низ заедно ("сам") в двойни кавички.
=COUNTIF(B21:D27,"сам") 
Използване на заместващи знаци в Excel функция COUNTIF (частично съвпадение)
Можете да използвате формулата COUNTIF със заместващи знаци, за да преброите клетки, които съдържат конкретна дума, фраза или букви. Има три заместващи знака, които можете да използвате във функцията COUNTIF на Excel:
*(звездичка) – Използва се за броене на клетки с произволен брой начални и крайни знаци/букви. (напр. St* може да означава Старк, Щъркел, Стекове и т.н.?(въпросителен знак) – Използва се за намиране на клетки с всеки единичен знак. (напр. St?rk може да означава Старк или Щъркел.~(тилда) – Използва се за намиране и преброяване на броя на клетките, съдържащи въпросителна или звездичка (~, *, ?) в текста.
Преброяване на клетки, започващи или завършващи с определени символи
За да преброите клетките, които започват или завършват с конкретен текст с произволен брой други знаци в клетка, използвайте заместващ знак звездичка (*) във втория аргумент на функцията COUNTIF.
Използвайте тази примерна формула:
=COUNTIF(A1:A10,"A*") – за преброяване на клетки, които започват с „А“.
=COUNTIF(A19:A28,"*er") – за преброяване на броя клетки, които завършват със знаците „er“.
=COUNTIF(A2:A12,"*QLD*") – за преброяване на клетките, които съдържат текст „QLD“ навсякъде в текстовия низ.
А ? представлява точно един знак, използвайте този заместващ знак във функцията COUNTIF по-долу, за да преброите броя на клетките, които съдържат точно +1 знак, където „?' се използва.
=COUNTIF(A1:A10,"Par?s")
Преброяване на празни и непразни клетки с функция COUNTIF
Формулата COUNTIF също е полезна, когато става въпрос за преброяване на броя на празните или непразни клетки в даден диапазон.
Пребройте непразни клетки
Ако искате да преброите само клетки, които съдържат каквито и да е „текстови“ стойности, използвайте формулата по-долу. Тази формула разглежда клетките с дати и числа като празни клетки и няма да ги включва в броя.
=COUNTIF(A1:B12,"*")Заместващият знак * съвпада само с текстовите стойности и връща броя на всички текстови стойности в дадения диапазон.
Ако искате да преброите всички непразни клетки в даден диапазон, опитайте тази формула:
=COUNTIF(A1:B12,"")
Пребройте празните клетки
Ако искате да преброите празни клетки в определен диапазон, използвайте функцията COUNTIF с * заместващ знак и оператор в аргумента критерии за преброяване на празни клетки.
Тази формула брои клетки, които не съдържат никакви текстови стойности:
=COUNTIF(A1:B12,""&"*")От * заместващият знак съвпада с всяка текстова стойност, горната формула ще преброи всички клетки, на които не са равни *. Той също така брои клетки с дати и числа като празни места.
За да преброите всички празни места (всички типове стойности):
=COUNTIF(A1:B12,"")Тази функция отчита само празни клетки в диапазона.
Използване на функция COUNTIF за броене на дати
Можете да преброите клетки с дати (също, както направихте с критериите за число), които отговарят на логическо условие или определената дата или дата в референтната клетка.
За да преброим клетките, които съдържат посочената дата (05-05-2020), ще използваме тази формула:
=COUNTIF(B2:B10,"05-05-2020")
Можете също да посочите дата в различни формати като критерии във функцията COUNTIF, както е показано по-долу:
Ако искате да преброите клетки, които съдържат дати преди или след определена дата, използвайте операторите по-малко от (преди) или по-големи от (след) заедно с конкретната дата или препратка към клетка.
=COUNTIF(B2:B10,">=05/05/2020")
Можете също да използвате препратка към клетка, която съдържа дата, като я комбинирате с оператора (в двойни кавички).
За да преброите броя на клетките в диапазона A2:A14 с дата преди датата в E3, използвайте формулата по-долу, където операторът по-голям от (<) означава преди датата в E3.
=COUNTIF(A2:A14,"<"&E3)
Няколко примерни формули и техния резултат:
Бройте дата въз основа на текущата дата
Можете да комбинирате функцията COUNTIF със специфични функции за дата на Excel, т.е. TODAY(), за да преброите клетките, които имат текуща дата.
=COUNTIF(A2:A14,">"&ДНЕС())Тази функция отчита всички дати от днес в диапазона (A2:A14).
Бройте дати между конкретен период от време
Ако искате да преброите всички дати между две дати, трябва да използвате два критерия във формулата.
Можем да направим това, като използваме два метода: функции COUNTIF и COUNTIFS.
Използване на функцията COUNTIF на Excel
Трябва да използвате две функции COUNTIF, за да преброите всички дати между двете посочени дати.
За да преброите датите между ’09-02-2020′ и ’20-08-2021′, използвайте тази формула:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")Тази формула първо намира броя на клетките, които имат дата след 2 февруари и изважда броя на клетките с дати след 20 август. Сега получаваме номер. от клетки, които имат дати след 2 февруари и на или преди 20 август (броят е 9).
Ако не искате формулата да отчита както 2 февруари, така и 20 август, използвайте тази формула вместо това:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Просто заменете оператора „>“ с „>=“ във втория критерий.
Използване на функцията COUNTIFS на Excel
Функцията COUNTIFS също поддържа множество критерии и за разлика от функцията COUNTIF, тя брои клетките само след като са изпълнени всички условия. Ако искате да преброите клетките с всички дати между две посочени дати, въведете тази формула:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
Ако искате да включите и посочените дати в броя, използвайте операторите „>=“ и „<=“. Ето, продължете с тази формула:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")Използвахме дата директно в критериите вместо препратка към клетка за този пример.
Как да се справяме с COUNTIF и COUNTIFS с множество критерии в Excel
Функцията COUNTIF се използва най-вече за преброяване на клетки с единичен критерий (условие) в един диапазон. Но все пак можете да използвате COUNTIF, за да преброите клетки, които отговарят на множество условия в същия диапазон. Въпреки това, функцията COUNTIFS може да се използва за преброяване на клетки, които отговарят на множество условия в едни и същи или различни диапазони.
Как да броим числа в диапазон
Можете да преброите клетки, съдържащи числа между двете посочени числа, като използвате две функции: COUNTIF и COUNTIFS.
COUNTIF за броене на числа между две числа
Една от често срещаните употреби на функцията COUNTIF с множество критерии е преброяването на числата между две посочени числа, напр. за преброяване на числа, по-големи от 10, но по-малко от 50. За да преброите числа в рамките на диапазон, съединете две или повече функции COUNTIF заедно в една формула. Нека ви покажем как.
Да приемем, че искате да преброите клетки в диапазона B2:B9, където стойността е по-голяма от 10 и по-малка от 21 (без 10 и 21), използвайте тази формула:
=COUNTIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")Разликата между две числа се намира чрез изваждане на една формула от друга. Първата формула отчита числата, по-големи от 10 (което е 7), втората формула връща броя на числата, по-големи или равни на 21 (което е 4), а резултатът от втората формула се изважда от първата формула (7 -4), за да получите броя на числата между две числа (3).
Ако искате да преброите клетки с число по-голямо от 10 и по-малко от 21 в диапазона B2:B14, включително числа 10 и 21, използвайте тази формула:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS за броене на числа между 2 числа
За да преброите числата между 10 и 21 (с изключение на 10 и 21), които се съдържат в клетки B2 до B9, използвайте тази формула:
=COUNTIFS(B2:B14,">10",B2:B14,"<21")
За да включите 10 и 21 в броя, просто използвайте операторите „по-голямо или равно на“ (>=) вместо „по-голямо от“ и „по-малко или равно на“ (<=) вместо операторите „по-малко от“ във формулите .
COUNTIFS за преброяване на клетки с множество критерии (И критерии)
Функцията COUNTIFS е множествена двойка на функцията COUNTIF, която брои клетки въз основа на два или повече критерия в един и същи или множество диапазони. Тя е известна като „И логика“, тъй като функцията е направена за броене на клетки само когато всички дадени условия са ВЯРНИ.
Например, искаме да разберем колко пъти (брой клетки) този хляб (стойност в колона A) е бил продаден под 5 (стойност в колона C).
Можем да използваме тази формула:
=COUNTIFS(A2:A14,"Хляб",C2:C14,"<5")
COUNTIF за броене на клетки с множество критерии (или критерии)
Ако искате да преброите броя на клетките, които отговарят на множество критерии в един и същи диапазон, съединете две или повече функции COUNTIF заедно. Например, ако искате да разберете колко пъти „хляб“ или „сирене“ се повтарят в посочения диапазон (A2:A14), използвайте следната формула:
=COUNTIF(A2:A14,"хляб")+COUNTIF(A2:A14,"сирене")Тази формула брои клетки, за които поне едно от условията е ВЯРНО. Ето защо се нарича „ИЛИ логика“.
Ако искате да оцените повече от един критерий във всяка от функциите, по-добре е да използвате COUNTIFS вместо COUNTIF. В примера по-долу искаме да получим броя на статуса „Поръчано“ и „Доставено“ за „Хляб“, така че ще използваме тази формула:
=COUNTIFS(A2:A14,"Хляб",C2:C14,"Поръчано")+COUNTIFS(A2:A14,"Хляб",C2:C14,"Доставено")
Надяваме се, че този лесен, но по-скоро дълъг урок ще ви даде представа как да използвате функциите COUNTIF и COUNTIF в Excel.