Функция COUNTIFS в Excel возвращает количество ячеек, которые соответствуют нескольким условиям, называемым критериями. Для определения критериев СЧЁТЕСЛИМН поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~) и может использоваться для подсчета ячеек, содержащих даты, числа и текст.
Что делает функция COUNTIFS в Excel?
Функция COUNTIFS (СЧЁТЕСЛИМН) в Excel подсчитывает ячейки, соответствующие нескольким критериям.
Возвращаемое значение
Количество раз, когда критерии удовлетворяются.
Аргументы
- range1 — первый диапазон для оценки.
- criteria1 — критерии для диапазона1.
- range2 — [необязательный] Второй диапазон для оценки.
- criteria2 — [необязательный] Критерий для использования в диапазоне2.
Синтаксис
=COUNTIFS(range1, criteria1, [range2], [criteria2], …)
Примечания по использованию
Функция COUNTIFS подсчитывает ячейки в диапазоне, которые соответствуют нескольким условиям, называемым критериями . Каждое условие имеет отдельный диапазон и критерии . Для определения критериев COUNTIFS поддерживает различные логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~). Функция СЧЁТЕСЛИМН — широко используемая функция в Excel, но синтаксис, используемый для применения условий, немного сложен, поскольку он необычен для Excel.
Синтаксис
Синтаксис функции COUNTIFS зависит от оцениваемых критериев. Каждое условие имеет отдельный диапазон и критерии . Общий синтаксис для COUNTIFS выглядит следующим образом:
=COUNTIFS(range1,criteria1) // 1 condition
=COUNTIFS(range1,criteria1,range2,criteria2) // 2 conditions
Первые два аргумента, диапазон1 и критерии1 являются обязательными . Диапазон1 — это диапазон, к которому следует применить критерий1 . Диапазон2 — это диапазон, к которому следует применить критерий2 . Дополнительные условия применяются путем предоставления дополнительных аргументов диапазона и критериев : третье условие определяется диапазоном3 и критериями3 , четвертое условие определяется диапазоном4 и критериями4 и так далее. При использовании СЧЁТЕСЛИМН помните следующее:
- Для включения в окончательный подсчет должны быть соблюдены все условия.
- Все диапазоны должны быть одного размера, иначе COUNTIFS вернут ошибку #ЗНАЧ! ошибка.
- Критерии должны включать логические операторы (>,<,<>,<=,>=) по мере необходимости.
- Каждое новое условие требует отдельного диапазона и критериев .
Критерии
Функция COUNTIFS поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного совпадения. Поскольку COUNTIFS находится в группе из восьми функций , которые разбивают логические критерии на две части, синтаксис немного сложен. Каждое условие требует отдельного диапазона и критериев , а операторы необходимо заключать в двойные кавычки («»). В таблице ниже приведены некоторые распространенные примеры:
| Цель | Критерии |
|---|---|
| Ячейки больше 75 | «>75» |
| Ячейки равные 100 | 100 или «100» |
| Ячейки меньше или равные 100 | «<=100» |
| Ячейки, равные «красному» | «красный» |
| Ячейки не равны «красному» | «<>красный» |
| Пустые ячейки «» | «» |
| Непустые ячейки _ | «<>» |
| Ячейки, начинающиеся с «X» | «Икс*» |
| Ячейки меньше A1 | «<«&A1 |
| Ячейки меньше, чем сегодня | «<«&СЕГОДНЯ() |
Обратите внимание, что в последних двух примерах используется конкатенация с символом амперсанда (&). Когда аргумент критерия включает значение из другой ячейки или результат формулы, логические операторы, такие как «<«, должны быть объединены конкатенацией. Это связано с тем, что Excel необходимо сначала оценить ссылки на ячейки и формулы, чтобы получить значение, прежде чем это значение можно будет присоединить к оператору.
Базовый пример
В показанном примере COUNTIFS можно использовать для подсчета записей с использованием двух критериев следующим образом:
=COUNTIFS(C5:C14,"red",D5:D14,"tx") // red and TX
=COUNTIFS(C5:C14,"red",F5:F14,">20") // red and >20
Обратите внимание, что функция не чувствительна к регистру.
Двойные кавычки («») в критериях
Как правило, текстовые значения необходимо заключать в двойные кавычки, а числа — нет. Однако, когда логический оператор включен в число, число и оператор должны быть заключены в кавычки, как показано ниже:
=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"
Примечание: показано одно условие только для простоты. Дополнительные условия должны соответствовать тем же правилам.
Значение из другой ячейки
При использовании значения из другой ячейки в условии ссылка на ячейку должна быть объединена с оператором при использовании. В приведенном ниже примере COUNTIFS подсчитает значения в ячейке A1:A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше » (текст) заключен в кавычки, а ссылка на ячейку — нет:
=COUNTIFS(A1:A10,"<"&B1) // count cells less than B1
Примечание. СЧЁТЕСЛИМН — это одна из нескольких функций, которые разбивают условия на две части: диапазон + критерии. Это вызывает некоторые несоответствия по отношению к другим формулам и функциям.
Не равно
Чтобы создать критерий «не равно», используйте оператор «<>» , заключенный в двойные кавычки («»). Например, приведенная ниже формула будет подсчитывать ячейки, не равные «красному» в диапазоне A1:A10:
=COUNTIFS(A1:A10,"<>red") // not "red"
Пустые ячейки
COUNTIFS может подсчитывать ячейки, которые пусты или не пусты. Приведенные ниже формулы подсчитывают пустые и не пустые ячейки в диапазоне A1: A10:
=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank
Даты
Самый простой способ использовать COUNTIFS с датами — это сослаться на действительную дату в другой ячейке с помощью ссылки на ячейку. Например, для подсчета ячеек в A1:A10, содержащих дату, превышающую дату в B1, можно использовать следующую формулу:
=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1
Обратите внимание, что мы присоединяем оператор «>» к дате в ячейке B1, но и не заключаем в кавычки ссылку на ячейку.
Самый безопасный способ жестко закодировать дату в СЧЁТЕСЛИМН — использовать функцию DATE . Это гарантирует, что Excel поймет дату. Чтобы подсчитать ячейки в A1: A10, содержащие дату менее 1 сентября 2020 г., вы можете использовать:
=COUNTIFS(A1:A10,"<"&DATE(2020,9,1)) // dates less than 1-Sep-2020
Подстановочные знаки
В критериях можно использовать знак вопроса (?), звездочку (*) или тильду (~) . Знак вопроса (?) соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, для подсчета ячеек в A1:A5, содержащих текст «яблоко», можно использовать следующую формулу:
=COUNTIFS(A1:A5,"*apple*") // count cells that contain "apple"
Тильда (~) — это escape-символ, позволяющий находить буквальные подстановочные знаки. Например, чтобы подсчитать буквальный вопросительный знак (?), звездочку (*) или тильду (~), добавьте тильду перед подстановочным знаком (например, ~?, ~*, ~~).
Логика OR
Функция COUNTIFS предназначена для применения нескольких критериев, но условия применяются с логикой AND. Это означает, что если вы попытаетесь подсчитать ячейки, содержащие «красный» или «синий» в одном диапазоне, результат будет равен нулю (0). Однако для подсчета ячеек с помощью логики OR вы можете использовать константу массива и функцию SUM следующим образом:
=SUM(COUNTIFS(range,{"red","blue"})) // red or blue
Приведенная выше формула будет подсчитывать ячейки в диапазоне , содержащем «красный» или «синий». Вкратце, COUNTIFS возвращает два числа в массиве (одно для «красного» и одно для «синего»), а функция SUM возвращает сумму в качестве конечного результата.
Ограничения
Функция COUNTIFS имеет некоторые ограничения, о которых вам следует знать:
- Условия соединяются логическим И. Другими словами, все условия должны быть TRUE , чтобы ячейка была включена в подсчет. Приведенный выше обходной путь можно использовать в простых ситуациях.
- Функция требует фактических диапазонов для всех аргументов диапазона; вы не можете использовать массив. Это означает, что вы не можете изменить значения, которые появляются в аргументе диапазона, до применения критерия.
- COUNTIFS не чувствителен к регистру. Для подсчета значений на основе условия с учетом регистра можно использовать формулу, основанную на функции SUMPRODUCT с EXACT.
Самый распространенный способ обойти вышеуказанные ограничения — использовать функцию SUMPRODUCT. В текущей версии Excel другим вариантом является использование более новых функций BYROW и BYCOL .
Примечания
- Несколько условий применяются с логикой AND, т. е. условие 1 AND условие 2 и т. д.
- Все диапазоны должны быть одного размера. Если вы укажете диапазоны, которые не совпадают, вы получите ошибку #VALUE.
- Нечисловые критерии должны быть заключены в двойные кавычки (например, «<100», «>32», «TX»).
- Подстановочные знаки «?» и «*» можно использовать в критериях. Знак вопроса соответствует любому одному символу, а звездочка соответствует любой последовательности символов.
- Чтобы соответствовать буквальному вопросительному знаку (?) или звездочке (*), используйте тильду (~), т. е. (~?, ~*).
