СЧЁТЕСЛИ

Раздел функций	Статистические
Название на английском	COUNTIF
Волатильность	Не волатильная
Похожие функции	СЧЁТ, СЧЁТЗ, СУММЕСЛИ, СЧЁТЕСЛИМН

Что делает эта функция?

Функция СЧЁТЕСЛИ в Excel используется для подсчета количества ячеек в диапазоне, которые удовлетворяют определенному критерию. СЧЁТЕСЛИ очень полезна, когда вам нужно узнать, сколько раз встречается в наборе данных определенное значение. Этим она может значительно упростить процесс обработки больших наборов данных в Excel.

Синтаксис функции

=СЧЁТЕСЛИ(диапазон; критерий)

диапазон: Это диапазон ячеек, который вы хотите проверить.
критерий: Это условие, которое должны удовлетворять ячейки в диапазоне. Критерий может быть числом, текстовым выражением, ячейкой со значением или формулой.

Критерии и операторы функции СЧЁТЕСЛИ

Для функции СЧЁТЕСЛИ в Excel в качестве критериев могут выступать следующие объекты:

• Напрямую заданные значения в различных форматах:
  • Числа: Прямое указание числа, например, 10 или 3.14.
  • Текстовые строки: Любые текстовые значения, заключенные в кавычки, например, “Слово” или “Текст”.
  • Логические значения: ИСТИНА или ЛОЖЬ.
  • Даты: Могут использоваться даты в формате, который Excel распознает как дату, например, “2024-01-01” или DATE(2024, 1, 1).
  • Проценты, например, “25%”, “>50%”
• Ячейки: Ссылка на ячейку, содержащую критерий. Например, если критерий находится в ячейке A1, то можно указать A1 как критерий.
• Диапазоны ячеек (тогда функция возвращает массив, равный диапазону, указываемому в качестве критерия).
• Формулы: Критерий может быть задан формулой, результат которой используется для сравнения с ячейками диапазона.

Важно учесть, что критерий должен быть задан в соответствии с типом данных в анализируемом диапазоне, чтобы функция СЧЁТЕСЛИ работала корректно.

Операторы функции СЧЁТЕСЛИ

Операторы условно делятся на числовые и текстовые. Нужно учитывать, что СЧЁТЕСЛИ:

• может использовать числовые операторы сравнения для текста (например, Б > А, а ББ > БА)
• но при этом не умеет использовать текстовые операторы для чисел (например, 200 входит в 2002 как текст, но если формат значения не текстовый, будет возвращаться 0)

Во втором случае вам может пригодиться функция ТЕКСТ для преобразования чисел в текстовый формат.

Числовые операторы

1. Больше (>): Подсчитывает ячейки, содержащие значения больше указанного.
2. Меньше (<): Подсчитывает ячейки, содержащие значения меньше указанного.
3. Больше или равно (>=): Подсчитывает ячейки, содержащие значения больше или равные указанному.
4. Меньше или равно (<=): Подсчитывает ячейки, содержащие значения меньше или равные указанному.
5. Равно (=): Подсчитывает ячейки, строго равные указанному значению. Обычно оператор “=” опускается, так как он подразумевается по умолчанию.
6. Не равно (<>): Подсчитывает ячейки, которые не равны указанному значению.

Эти операторы используются в сочетании с числовым значением или ссылкой на ячейку в качестве критерия для подсчета количества ячеек, удовлетворяющих определенному условию.

Текстовые операторы

Функция СЧЁТЕСЛИ также поддерживает и текстовые операторы (они же подстановочные символы). Они позволяют осуществлять подсчёт ячеек по шаблону (в профессиональном сленге также присутствует выражение “по маске“). Текстовые операторы работают и с числовыми значениями. Всего таковых два:

• Знак вопроса (?): Используется в критериях для замены одного любого символа. Например, критерий “а?” будет соответствовать любому двухбуквенному тексту, который начинается с “а”.
• Звездочка (*): Используется для замены любой последовательности символов, включая их отсутствие. Например, критерий “а*” будет соответствовать любому тексту, который начинается на “а” или равен “а“.

“?*” или “*?” в качестве критерия позволит посчитать ячейки, содержащие текстовые символы в диапазоне, т.к. текстовый критерий не сработает на числовых и логических значениях.

Примеры использования функции

Давайте рассмотрим подробно формулы, указанные вверху страницы. Перед нами каталог товаров интернет-магазина электроники с указанием их категории и ценами. Наша задача определить количество позиций, удовлетворяющих некоторым числовым или текстовым критериям.

Примеры с текстовыми критериями

Допустим, нам нужно посчитать товары с вхождением определенного бренда в их названии. Тогда нам потребуется использовать следующие формулы:

Сами формулы находятся в ячейках C14 и C15. Для удобства копирования, вот они:

=СЧЁТЕСЛИ(A2:A11;"*Samsung*")

=СЧЁТЕСЛИ(A2:A11;"*"&B15&"*")

Отличие второй формулы от первой в том, что её можно больше не редактировать, а изменять значение ячейки, от которой она зависит. Еще удобнее, если во влияющей ячейке будет выпадающий список брендов. Звёздочки в формуле позволяют искать текст по вхождению, а не полному совпадению.

При поиске по полному совпадению ячейки, например, по категории товара, операторы не требуются. По умолчанию поиск осуществляется именно по нему.

Соответствующие формулы:

=СЧЁТЕСЛИ(B2:B11;"Аксессуары")

=СЧЁТЕСЛИ(B2:B11;B17)

Здесь вторая формула отличается от первой аналогично предыдущему примеру.

Примеры формул с числовыми критериями

Когда нужно посчитать ячейки, удовлетворяющие некоторым числовым критериям, главная особенность в том, что, несмотря на то, что они числовые, указываются они внутри кавычек, как строковый параметр.

Ниже текст соответствующих формул для удобства копирования.

1. =СЧЁТЕСЛИ(C2:C11;">3000")

2. =СЧЁТЕСЛИ(C2:C11;">"&B19)

3. =СУММ(--(ABS(D2:D11/C2:C11-1)>20%))

Обратите внимание на третью формулу. Это формула массива, и она используется здесь по той причине, что функция СЧЁТЕСЛИ не работает с массивами. Т.е. нельзя разделить значения двух диапазонов друг на друга и сравнить их с целевым значением скидки в массиве с её помощью.

Абсолютное значение скидки (его возвращает функция ABS) сравнивается с целевым значением 20%, и массив, состоящий из логических значений ИСТИНА и ЛОЖЬ, превращается в нули и единицы методом двойного отрицания. Далее функция СУММ возвращает сумму единиц.

4. =СЧЁТЕСЛИ(E2:E11;"*99")

Четвертая формула также имеет свою особенность. Если бы мы применили её на числовых ячейках в столбце D, она вернула бы 0. Поэтому нам потребовался дополнительный столбец, в котором функция ТЕКСТ перевела числа в текстовый формат.

Ещё примеры

Хотите больше примеров использования функции? Ниже список статей на этом сайте, где функция СЧЁТЕСЛИ используется как вспомогательная в сочетании с другими:

Посчитать слова в ячейках Excel

Посчитать ячейки с цифрами

N-gram анализ в Excel

Поиск повторяющихся значений в диапазоне