COUNTIF — это функция Excel для подсчета ячеек в диапазоне, соответствующих одному условию. COUNTIF (СЧЁТЕСЛИ) можно использовать для подсчета ячеек, содержащих даты, числа и текст. Критерий, используемый в COUNTIF, поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного совпадения.
Что делает функция COUNTIF в Excel?
Функция COUNTIF в Excel подсчитывает ячейки, соответствующие критериям.
Возвращаемое значение
Число, обозначающее подсчитанные ячейки.
Аргументы
- range — диапазон ячеек для подсчета.
- criteria — критерии, определяющие, какие ячейки следует подсчитывать.
Синтаксис
=COUNTIF(range, criteria)
Примечания по использованию
Функция COUNTIF подсчитывает ячейки в диапазоне, которые соответствуют заданному условию, называемому критериями. СЧЕТЕСЛИ — это распространенная и широко используемая функция в Excel, которую можно использовать для подсчета ячеек, содержащих даты, числа и текст.
Обратите внимание, что COUNTIF может применять только одно условие.
Синтаксис
Общий синтаксис COUNTIF выглядит следующим образом:
=COUNTIF(range,criteria)
Функция СЧЕТЕСЛИ принимает два аргумента: диапазон и критерий. Диапазон — это диапазон ячеек, к которым применяется условие. Критерий — это условие применения, а также любые необходимые логические операторы.
Применение критериев
Функция СЧЕТЕСЛИ поддерживает логические операторы (>,<,<>,<=,>=) и подстановочные знаки (*,?) для частичного сопоставления. Сложная часть использования функции СЧЕТЕСЛИ — это синтаксис, используемый для применения критериев.
COUNTIFS входит в группу из восьми функций , которые разделяют логические критерии на две части: диапазон и критерии. Из-за такой конструкции каждое условие требует отдельного аргумента диапазона и критерия , а операторы в критериях должны быть заключены в двойные кавычки («»). В таблице ниже показаны примеры синтаксиса, необходимого для общих критериев:
| Цель | Критерии |
|---|---|
| Клетки больше 75 | «>75» |
| Ячейки равны 100 | 100 или «100» |
| Ячейки меньше или равны 100 | «<=100» |
| Ячейки, равные «красному» | «красный» |
| Ячейки, не равные «красному» | «<>красный» |
| Ячейки пустые «» | «» |
| Ячейки, которые не пусты | «<>» |
| Ячейки, начинающиеся с «X» | «Икс*» |
| Ячейки меньше A1 | «<«&A1 |
| Клеток меньше, чем сегодня | «<«&СЕГОДНЯ() |
Обратите внимание, что последние два примера включают объединение с символом амперсанда (&). Каждый раз, когда вы используете значение из другой ячейки или используете результат формулы в критериях с логическим оператором, например «<», вам необходимо выполнить объединение. Это связано с тем, что Excel необходимо сначала оценить ссылки на ячейки и формулы, чтобы получить значение, прежде чем это значение можно будет присоединить к оператору.
Базовый пример
На листе, показанном выше, в ячейках G5, G6 и G7 используются следующие формулы:
=COUNTIF(D5:D12,">100") // подсчитайте продажи за 100
=COUNTIF(B5:B12,"jim") // имя графы = "jim"
=COUNTIF(C5:C12,"ca") // состояние подсчета = "ca"
Обратите внимание: COUNTIF не чувствителен к регистру, «CA» и «ca» обрабатываются одинаково.
Двойные кавычки («») в критериях
Обычно текстовые значения необходимо заключать в двойные кавычки («»), а числа — нет. Однако если логический оператор включен в число, число и оператор должны быть заключены в кавычки, как показано во втором примере ниже:
=COUNTIF(A1:A10,100) // количество ячеек равно 100
=COUNTIF(A1:A10,">32") // количество ячеек больше, чем 32
=COUNTIF(A1:A10,"jim") // количество ячеек равно "jim"
Значение из другой ячейки
Значение из другой ячейки можно включить в критерии с помощью конкатенации. В приведенном ниже примере СЧЕТЕСЛИ вернет количество значений в A1:A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше» (текстовый) заключен в кавычки.
=COUNTIF(A1:A10,"<"&B1) // количество ячеек меньше, чем B1
Не равно
Чтобы создать критерий «не равно», используйте оператор «<>» , заключенный в двойные кавычки («»). Например, формула ниже будет подсчитывать ячейки, не равные «красному» в диапазоне A1:A10:
=COUNTIF(A1:A10,"<>red") // not "red"
Пустые ячейки
COUNTIF может подсчитывать пустые или непустые ячейки. В приведенных ниже формулах учитываются пустые, а не пустые ячейки в диапазоне A1:A10:
=COUNTIF(A1:A10,"<>") // not blank
=COUNTIF(A1:A10,"") // blank
Примечание. Имейте в виду, что COUNTIF обрабатывает формулы, возвращающие пустую строку («»), как не пустые.
Даты
Самый простой способ использовать СЧЕТЕСЛИ с датами — это обратиться к действительной дате в другой ячейке с помощью ссылки на ячейку. Например, чтобы подсчитать ячейки в A1:A10, содержащие дату, большую, чем дата в B1, вы можете использовать такую формулу:
=COUNTIF(A1:A10, ">"&B1) // не учитывайте даты, превышающие A1
Обратите внимание, что мы должны объединить оператор с датой в B1. Чтобы использовать более расширенные критерии даты (т. е. все даты в данном месяце или все даты между двумя датами), вам нужно переключиться на функцию СЧЁТЕСЛИ, которая может обрабатывать несколько критериев.
Самый безопасный способ жестко запрограммировать дату в COUNTIF — использовать функцию DATE. Это гарантирует, что Excel поймет дату. Чтобы подсчитать ячейки в A1:A10, содержащие дату меньше 1 апреля 2020 г., вы можете использовать подобную формулу.
=COUNTIF(A1:A10,"<"&DATE(2020,4,1)) // дата меньше, чем 1-Apr-2020
Подстановочные знаки
В критериях можно использовать подстановочные знаки: вопросительный знак (?), звездочку (*) или тильду (~ ). Знак вопроса (?) соответствует любому одному символу, а звездочка (*) соответствует нулю или более символам любого типа. Например, чтобы подсчитать ячейки в A1:A5, содержащие текст «яблоко» где угодно, вы можете использовать такую формулу:
=COUNTIF(A1:A5,"*apple*") // ячейки, содержащие "apple"
Чтобы подсчитать ячейки в формате A1:A5, содержащие любые 3 текстовых символа, вы можете использовать:
=COUNTIF(A1:A5,"???") // ячейки, содержащие любые 3 символа
Тильда (~) — это escape-символ, соответствующий буквальным подстановочным знакам. Например, чтобы посчитать буквальный вопросительный знак (?), звездочку (*) или тильду (~), добавьте тильду перед подстановочным знаком (т. е. ~?, ~*, ~~).
OR
Функция СЧЕТЕСЛИ предназначена для применения только одного условия. Однако для подсчета ячеек, содержащих «это OR то», вы можете использовать константу массива и функцию СУММ следующим образом:
=SUM(COUNTIF(range,{"red","blue"})) // red or blue
Приведенная выше формула будет подсчитывать ячейки в диапазоне , содержащие «красный» или «синий». По сути, СЧЁТЕСЛИ возвращает два значения в массиве (одно для «красного» и одно для «синего»), а функция СУММ возвращает сумму.
Ограничения
Функция СЧЕТЕСЛИ имеет некоторые ограничения, о которых следует знать:
- COUNTIF поддерживает только одно условие.
- Требует фактического диапазона для аргумента диапазона; вы не можете предоставить массив. Это означает, что вы не можете изменять значения в диапазоне перед применением критериев.
- Не учитывает регистр.
Самый распространенный способ обойти вышеуказанные ограничения — использовать функцию SUMPRODUCT. В текущей версии Excel есть еще один вариант — использовать новые функции BYROW и BYCOL .
Примечания
- Текстовые строки в критериях должны быть заключены в двойные кавычки («»), например «apple», «>32», «app*».
- Ссылки на ячейки в критериях не заключаются в кавычки, т.е. «<»&A1.
- Подстановочные знаки? и * могут использоваться в критериях. Знак вопроса соответствует любому символу, а звездочка соответствует любой последовательности символов (ноль или более).
- Чтобы сопоставить буквальный вопросительный знак (?) или звездочку (*), используйте тильду (~), например (~?, ~*).
- COUNTIF в экселе требует диапазона, вы не можете заменить массив.
- СЧЕТЕСЛИ возвращает неправильные результаты при использовании строк длиной более 255 символов.
- Функция вернет ошибку #ЗНАЧ при ссылке на другую закрытую книгу.