Функция COUNTIF в Excel

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 символов.
  • Функция вернет ошибку #ЗНАЧ при ссылке на другую закрытую книгу.
Автор А. Днепров

Обучаю основам работы с офисными программами.

Функция COUNTIF в Excel
3.2 Рисование линий