Този урок демонстрира как да използватеИзклел COUNTIF и COUNTIFS Funcтиони в Excel за преброяване на данни, които отговарят на определени критерии.
Преглед на функцията COUNTIF
Можете да използвате функцията COUNTIF в Excel, за да броите клетки, които съдържат определена стойност, да броите клетки, които са по -големи или равни на стойност и т.н.
(Забележете как се появяват входовете на формулата)
Синтаксис и аргументи на функцията COUNTIF:
= COUNTIF (диапазон, критерии)
диапазон - Обхватът на клетките за преброяване.
критерии - Критериите, които контролират кои клетки трябва да бъдат преброени.
Какво представлява функцията COUNTIF?
Функцията COUNTIF е една от по -старите функции, използвани в електронни таблици. С прости думи, той е чудесен за сканиране на диапазон и да ви каже колко от клетките отговарят на това условие. Ще разгледаме как функцията работи с текст, числа и дати; както и някои други ситуации, които могат да възникнат.
Основен пример
Нека започнем, като разгледаме този списък с произволни елементи. Имаме някои числа, празни клетки и някои текстови низове.
Ако искате да знаете колко елемента точно отговарят на критериите, можете да посочите какво искате да търсите като втори аргумент. Пример за тази формула може да изглежда така
= COUNTIF (A2: A9, "Apple")
Тази формула би върнала числото 3, тъй като в нашия диапазон има 3 клетки, които отговарят на тези критерии. Като алтернатива можем да използваме препратка към клетка вместо твърдо кодиране на стойност. Ако написахме „Apple“ в клетка G2, бихме могли да променим формулата на
= COUNTIF (A2: A9, G2)
Когато се занимавате с число, е важно да правите разлика между числа и числа, които се съхраняват като текст. По принцип не поставяте кавички около числа, когато пишете формули. Така че, за да напишете формула, която проверява за числото 5, бихте написали
= COUNTIF (A2: A9, 5)
И накрая, бихме могли да проверим и за празни клетки, като използваме низ с нулева дължина. Бихме написали тази формула като
= COUNTIF (A2: A9, "")
Забележка: Тази формула ще брои както клетките, които са наистина празни, така и тези, които са празни в резултат на формула, като функция IF.
Частични съвпадения
Функцията COUNTIF поддържа използването на заместващи знаци „*“ или „?“ В критериите. Нека да разгледаме този списък с вкусни хлебни изделия:
За да намерим всички елементи, които започват с Apple, бихме могли да напишем „Apple*“. Така че, за да получим отговор 3, нашата формула в D2 е
= COUNTIF (A2: A5, "Apple*")
Забележка: Функцията COUNTIF не е чувствителна към регистъра, така че можете да напишете и „ябълка*“, ако искате.
Обратно към нашите печени изделия, може би бихме искали също да разберем колко пайове имаме в нашия списък. Можем да открием това, като поставим заместващия знак в началото на нашия термин за търсене и напишем
= COUNTIF (A2: A5, "*пай")
Тази формула дава резултат от 2.
Можем също да използваме заместващи символи, за да проверим за клетки с текст. Нека се върнем към първоначалния ни списък с данни.
За да преброим броя клетки, които имат поне някакъв текст, като по този начин не броим числа или празна клетка, можем да напишем
= COUNTIF (A2: A9, "*")
Можете да видите, че нашата формула правилно връща резултат от 4.
Оператори за сравнение в COUNTIF
Когато пишехме критериите досега, ние предполагахме, че нашият оператор за сравнение е „=“. Всъщност можехме да напишем това:
= COUNTIF (A2: A9, "= Apple")
Това обаче е допълнителен знак за изписване, така че обикновено се пропуска. Това обаче означава, че можете да използвате другите оператори, като по -голямо от, по -малко или не равно на. Нека да разгледаме този списък на записаните възрасти:
Ако искаме да знаем колко деца са на поне 5 години, можем да напишем сравнение „по -голямо или равно на“ по следния начин:
= COUNTIF (A2: A8, "> = 5")
Забележка: Операторът за сравнение винаги се дава като текстов низ и следователно трябва да е вътре в кавички.
По същия начин можете също да проверите за елементи, които са по -малки от дадена стойност. Ако трябва да разберем колко са по -малко от 8, можем да изпишем
= COUNTIF (A2: A8, "<8")
Това ни дава желания резултат от 5. Сега нека си представим, че всички 6-годишни деца излизат на излет. Колко деца ще останат? Можем да разберем това, като използваме сравнение „не е равно на“ по следния начин:
= COUNTIF (A2: A8, "6")
Сега можем бързо да видим, че имаме 6 деца, които не са на 6 години.
В тези примери за сравнение досега трудно кодирахме стойностите, които искахме. Можете също да използвате препратка към клетка. Номерът е, че трябва да обедините оператора за сравнение с препратката към клетката. Да кажем, че поставяме числото 7 в клетка C2 и искаме формулата ни в D2 да покаже колко деца са на по -малко от 7 години.
Нашата формула в D2 трябва да изглежда така:
= COUNTIF (A2: A8, "<" & C2)
Забележка: Обърнете специално внимание, когато пишете тези формули, дали трябва да поставите елемент в кавички или извън него. Операторите винаги са вътре в кавички, препратките към клетки винаги са извън кавички. Числата са отвън, ако правите точно съвпадение, но отвътре, ако правите оператор за сравнение.
Работа с дати
Видяхме как можете да дадете текст или число като критерий, но какво ще кажете, когато трябва да работим с дати? Ето бърз примерен списък, с който можем да работим:
За да преброим колко дати са след 4 май, трябва да бъдем предпазливи. Компютрите съхраняват датите като числа, така че трябва да се уверим, че компютърът използва правилния номер. Ако написахме тази формула, щяхме ли да получим правилния резултат?
= COUNTIF (A2: A9, "
Отговорът е „възможно“. Тъй като пропуснахме годината от нашите критерии, компютърът ще приеме, че имаме предвид текущата година. Ако всички дати, с които работим, са за текущата година, тогава ще получим верния отговор. Ако има някои дати, които са в бъдеще, ще получим грешен отговор. Също така, след като започне следващата година, тази формула ще върне различен резултат. Като такъв, този синтаксис вероятно трябва да се избягва.
Тъй като може да е трудно да напишете правилно датите във формула, най -добрата практика е да напишете датата, която искате да използвате в клетка, и след това можете да използвате тази препратка към клетката във вашата формула COUNTIF. Така че, нека запишем датата 7-май-2020 в клетка C2 и след това можем да поставим формулата си в C4.
Формулата в C4 е
= COUNTIF (A2: A9, "<" & C2)
Сега знаем, че резултатът от 7 е правилен и отговорът няма да се промени неочаквано, ако отворим тази електронна таблица по някое време в бъдеще.
Преди да напуснем този раздел, обичайно е да използвате функцията TODAY, когато работите с дати. Можем да го използваме точно както бихме направили клетка. Например, можем да променим предишната формула така:
= COUNTIF (A2: A9, "<" & TODAY ())
Сега нашата формула все още ще се актуализира с напредването на реалното време и ще имаме брой елементи, които са по -малко от днес.
Множество критерии и COUNTIFS
Първоначалната функция COUNTIF се подобри през 2007 г., когато излезе COUNTIFS. Синтаксисът между двете е много подобен, като последният ви позволява да дадете допълнителни диапазони и критерии. Можете лесно да използвате COUNTIFS във всяка ситуация, в която COUNTIF съществува. Добра идея е да знаете, че и двете функции съществуват.
Нека разгледаме тази таблица с данни:
За да разберете колко хора са на нива на заплащане 1 до 2, можете да напишете обобщение на COUNTIF функции по следния начин:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Тази формула ще работи, тъй като намирате всичко, което е над 1, но след това изваждате броя на записите, които са извън граничната точка. Като алтернатива можете да използвате COUNTIFS по следния начин:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Последното е по -интуитивно за четене, така че може да искате да използвате този маршрут. Освен това COUNTIFS е по -мощен, когато трябва да вземете предвид няколко колони. Да речем, че искаме да знаем колко хора са в Управлението и на Нивото на заплащане 1. Не можете да направите това само с COUNTIF; ще трябва да напишете
= COUNTIFS (A2: A7, "Управление", B2: B7, 1)
Тази формула ще ви даде правилния резултат от 2. Преди да напуснем този раздел, нека разгледаме логика на тип Or. Ами ако искаме да разберем колко хора са в мениджмънта или? Трябва да добавите няколко COUNTIFS заедно, но има два начина да направите това. По -простият начин е да го напишете така:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Management")
Можете също така да използвате масив и да напишете тази формула на масив:
= SUM (COUNTIF (A2: A7, {"HR", "Management"}))
Забележка: Формулите на масива трябва да бъдат потвърдени с помощта на „Ctrl+Shift+Enter“, а не само „Enter“.
Как ще работи тази формула е, че ще види, че сте дали масив като вход. По този начин той ще изчисли резултата до две различни функции COUNTIF и ще ги съхрани в масив. След това функцията SUM ще добави всички резултати в нашия масив, за да направи един изход. Така нашата формула ще бъде оценена така:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Пребройте уникалните стойности
Сега, когато видяхме как да използваме масив с функцията COUNTIF, можем да направим тази стъпка по -далеч, за да ни помогне да преброим колко уникални стойности са в диапазон. Първо, нека да разгледаме отново нашия списък с отдели.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Можем да видим, че има 6 клетки на стойност данни, но има само 3 различни елемента. За да работи математиката, ще трябва всеки елемент да е на стойност 1/N, където N е броят пъти, когато даден елемент се повтаря. Например, ако всеки HR струваше само 1/2, тогава, когато ги добавите, ще получите брой 1 за 1 уникална стойност.
Обратно към нашия COUNTIF, който е предназначен да разбере колко пъти даден артикул се появява в диапазон. В D2 ще напишем формулата на масива
= SUM (1/COUNTIF (A2: A7, A2: A7))
Как ще работи тази формула, е за всяка клетка в диапазона A2: A7, тя ще провери колко пъти се появява. С нашата извадка това ще създаде масив от
{2, 2, 3, 3, 3, 1}
След това превръщаме всички тези числа в дроби, като правим известно деление. Сега нашият масив изглежда така
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Когато съберем всичко това, получаваме желания резултат от 3.
Countif с две или множество условия - Функцията Countifs
Досега сме работили само с функцията COUNTIF. Функцията COUNTIF може да обработва само един критерий наведнъж. За да COUNTIF с множество критерии трябва да използвате функцията COUNTIFS. COUNTIFS се държи точно като COUNTIF. Просто добавяте допълнителни критерии. Нека да разгледаме примера по -долу.
= COUNTIFS (B2: B7, "= 130")
COUNTIF & COUNTIFS в Google Таблици
Функцията COUNTIF & COUNTIFS работи абсолютно същото в Google Sheets като в Excel: