COUNTIF в Microsoft Excel

Быстрые ссылки

• Что такое функция COUNTIF?

• Как использовать формулу COUNTIF в Microsoft Excel

• Как посчитать несколько значений

• Ограничения функции COUNTIF

В Microsoft Excel

COUNTIF

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

Что такое функция COUNTIF?

COUNTIF

Позволяет подсчитать число ячеек, которые соответствуют заданному критерию — например, сколько раз в списке встречается слово или часть слова. В формуле вы указываете диапазон, где Excel должен искать, и критерий, что именно искать. COUNTIF применим для одиночных условий; для нескольких условий существует COUNTIFS и другие подходы.

Важное: COUNTIF не различает регистр букв и поддерживает подстановочные символы (“*”, “?”).

Как использовать формулу COUNTIF в Microsoft Excel

Для примера используем простую таблицу запаса канцелярских товаров: две колонки — наименование и количество.

В пустой ячейке введите

=COUNTIF

и откройте скобку. Первый аргумент range — это диапазон ячеек, который вы хотите проверить. Второй аргумент criteria — то, что вы хотите посчитать; чаще всего это текст или число. Если критерий — текст, берите его в двойные кавычки.

В нашем примере нужно посчитать, сколько раз в столбце встречается “Pens” — диапазон

G9:G15

. Формула выглядит так:

=COUNTIF(G9:G15,"Pens")

Если вы ищете число, можно передавать его без кавычек, например

=COUNTIF(A1:A10,100)

Для условий с операторами используйте строку с оператором в кавычках, например

=COUNTIF(A1:A10,"<100")

посчитает все числа меньше 100.

Совет: если критерий хранится в ячейке B1, используйте ссылку, например

=COUNTIF(A1:A10,B1)

Как посчитать несколько значений

Если нужно посчитать суммарное количество нескольких отдельных значений (например, “Pens” и “Erasers”), можно сложить два COUNTIF:

=COUNTIF(G9:G15, "Pens")+COUNTIF(G9:G15, "Erasers")

Это работает, но если критериев много, формула становится громоздкой. В таких случаях рассмотрите альтернативы (см. раздел “Альтернативные подходы”).

Ограничения функции COUNTIF

• Ограничение на длину критерия: если критерий — строка длиннее 255 символов, COUNTIF вернёт ошибку. Решение: объединить части строки с помощью CONCATENATE или амперсанда, например:

=COUNTIF(A2:A5,"long string"&"another long string")

• COUNTIF не различает регистр. Критерии “erasers” и “ERASERS” будут совпадать с одними и теми же ячейками.

• Подстановочные символы: “*” соответствует любому набору символов, “?” — одному символу. Пример:

=COUNTIF(A2:A5,"*eraser*")

посчитает все ячейки, содержащие слово “eraser” в любой части текста.

Примечание: COUNTIF работает с одним условием. Для нескольких условий используйте COUNTIFS или другие формулы.

Частые задачи и альтернативные подходы

Когда COUNTIF подходит, а когда лучше использовать другой инструмент:

• Нужен один простой критерий — COUNTIF.
• Нужны несколько критериев одновременно (логика И) — COUNTIFS.
• Нужны сложные комбинации условий, пересечения или исключения (логика И/ИЛИ) — SUMPRODUCT или сочетание функций SUM и –(массивы).
• Работа с динамическими диапазонами и фильтрацией — FILTER + COUNTA или AGGREGATE.

Примеры альтернатив:

• COUNTIFS (несколько условий):

=COUNTIFS(A1:A10,"Pens",B1:B10,">0")

• SUMPRODUCT для И/ИЛИ и сложных условий:

=SUMPRODUCT((A1:A10="Pens")+(A1:A10="Erasers"))

• FILTER + COUNTA (Excel 365 / Excel 2021):

=COUNTA(FILTER(A1:A100, (A1:A100="Pens")+(A1:A100="Erasers")))

• UNIQUE + COUNTIF для подсчёта уникальных значений:

=COUNTA(UNIQUE(A1:A100))

Быстрый чек-лист ролей

Для аналитика:

• Определите диапазон данных.
• Проверьте наличие пробелов и невидимых символов (TRIM, CLEAN).
• Уточните, чувствительна ли логика к регистру (IF/EXACT для чувствительности).

Для менеджера отчётов:

• Укажите критерии в отдельных ячейках для удобства обновления.
• Используйте именованные диапазоны для повторного использования.

Для разработчика шаблонов:

• Предусмотрите обработку ошибок: IFERROR.
• Документируйте формулы и параметры в комментариях.

Мини-методология: как внедрить COUNTIF в отчёт (шаги)

1. Сформулируйте задачу в одном предложении: что и где считать.
2. Выберите диапазон данных и убедитесь, что в нём однотипные данные.
3. Определите критерий(и) — текст, число или выражение.
4. Вставьте COUNTIF или альтернативу и протестируйте на нескольких строках.
5. Добавьте обработку исключений и комментарии.

Шпаргалка — часто используемые шаблоны

• Один критерий (текст):

=COUNTIF(A2:A100,"Товар")

• Один критерий (ссылка на ячейку):

=COUNTIF(A2:A100,B1)

• Числовой критерий:

=COUNTIF(B2:B100,">=10")

• Несколько отдельных значений (складываем COUNTIF):

=COUNTIF(A2:A100,"A") + COUNTIF(A2:A100,"B")

• Несколько условий (AND):

=COUNTIFS(A2:A100,"A", B2:B100,">0")

Ментальные модели и эвристики

• Подумайте о COUNTIF как о фильтре: он отвечает на вопрос «сколько в этом наборе удовлетворяют критерию». Если вопрос сложнее — нужен другой фильтр/агрегатор.

• Если вы добавляете третий критерий — сначала проверьте, не удобнее ли перейти на COUNTIFS.

• Для логики ИЛИ используйте суммирование отдельных COUNTIF или SUMPRODUCT с логическими массивами.

Решение для выбора функции (графическое дерево)

flowchart TD
  A[Нужно посчитать значения?] --> B{Один критерий?}
  B -->|Да| C[COUNTIF]
  B -->|Нет| D{Все условия должны выполняться 'И'?}
  D -->|Да| E[COUNTIFS]
  D -->|Нет| F[SUMPRODUCT или FILTER + COUNTA]

Тестовые случаи и критерии приёмки

• Простое совпадение текста: COUNTIF считает точные вхождения.
• Поиск числа: COUNTIF считает числа корректно без кавычек.
• Операторы: “>”, “<”, “>=” работают внутри строк.
• Длинный критерий (>255 символов): COUNTIF не должен выдавать ошибку, если критерий собран через & или CONCATENATE.
• Подстановочные символы: “*” и “?” должны правильно находить шаблонные совпадения.

Критерии приёмки: формула возвращает ожидаемое значение для каждого теста; в случае ошибки формула должна иметь обработчик (IFERROR) и комментарий.

Глоссарий (в 1 строчку)

• range — диапазон ячеек для проверки.
• criteria — условие, которое должна удовлетворять ячейка.
• COUNTIFS — расширение COUNTIF для нескольких условий.
• SUMPRODUCT — универсальная функция для сложных логических подсчётов.

Дополнительные замечания по безопасности и локализации

• При локализации формул учтите разделитель аргументов: в русской локали Excel иногда использует точку с запятой “;” вместо запятой “,”. Проверяйте синтаксис вашей версии Excel.

• Не храните чувствительные данные в открытом виде в рабочих книгах; при автоматическом подсчёте учитывайте права доступа к файлу.

Краткое резюме

• COUNTIF — быстрый инструмент для одного условия.
• Для нескольких условий используйте COUNTIFS или SUMPRODUCT в зависимости от логики.
• Учтите ограничение в 255 символов для критерия и нечувствительность к регистру.

Примечание: если вы используете Excel 365/2021, функции FILTER, UNIQUE и динамические массивы часто упрощают задачи подсчёта и агрегации.

| | Освоение функций Excel | | Функции | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH · MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | | | Типы | Базовые · Бюджетирование · Ввод данных · Логические · Текстовые · Время и дата | | | Пояснения | Копирование формул · Оценка формул · Поиск функций · Исправление ошибок формул · Функции vs Формулы · Сравнение функций поиска · Фиксация формул · Структурирование формул · Перевод формул |

Важно: перед массовым применением формул протестируйте их на контрольной выборке данных.