СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ в Excel считает, сколько ячеек диапазона удовлетворяют условию: =СЧЁТЕСЛИ(A2:A100;"Москва") — ячейки со словом «Москва», =СЧЁТЕСЛИ(C2:C100;">3000") — числа больше 3000, =СЧЁТЕСЛИ(A2:A100;"*Samsung*") — ячейки, где встречается «Samsung». Условие берут в кавычки, а ссылку на ячейку приклеивают амперсандом: =СЧЁТЕСЛИ(C2:C100;">"&E1). Повторы значения считает =СЧЁТЕСЛИ($A$2:$A$100;A2), а несколько условий сразу — СЧЁТЕСЛИМН. На английском функция называется 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

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

Почему СЧЁТЕСЛИ считает неправильно — частые ошибки

• Формула не принимается. В русском Excel аргументы разделяются точкой с запятой, а текстовое условие берётся в кавычки: =СЧЁТЕСЛИ(A2:A10;"Москва").
• Результат 0, хотя значения есть. Лишние пробелы, неразрывные пробелы или числа, сохранённые как текст. Очистите данные СЖПРОБЕЛЫ или приведите к одному типу.
• Условие с ячейкой не работает. Оператор должен быть в кавычках, а ссылка — снаружи: ">"&E1, а не ">E1".
• Нужно учесть регистр. СЧЁТЕСЛИ его не различает — используйте СУММПРОИЗВ(--СОВПАД(диапазон;"текст")).
• Длинный текст не находится. Условие длиннее 255 символов не работает — сравнивайте через СУММПРОИЗВ(--(A2:A10=E1)).

Функция СЧЁТЕСЛИ — частые вопросы

Как работает СЧЁТЕСЛИ в Excel?

Считает ячейки диапазона, которые подходят под условие: =СЧЁТЕСЛИ(A2:A100;"Москва").

Как посчитать ячейки, содержащие слово?

Формулой со звёздочками: =СЧЁТЕСЛИ(A2:A100;"*слово*").

Как посчитать числа больше значения из ячейки?

Формулой =СЧЁТЕСЛИ(A2:A100;">"&E1).

Как посчитать по нескольким условиям?

Функцией СЧЁТЕСЛИМН: =СЧЁТЕСЛИМН(A2:A100;"Москва";B2:B100;">1000").

Как называется СЧЁТЕСЛИ на английском?

COUNTIF, а СЧЁТЕСЛИМН — COUNTIFS.

Итоги: функция СЧЁТЕСЛИ в Excel

Надеюсь, на этой странице вы нашли то, что искали. СЧЁТЕСЛИ — главная функция подсчёта по условию; запомните кавычки вокруг условия и амперсанд для ссылок.