COUNTIF и COUNTIFS в Excel: как считать по условиям

Что такое COUNTIF и COUNTIFS

COUNTIF и COUNTIFS — это встроенные функции Excel для подсчёта ячеек, которые соответствуют заданным условиям. Они избавляют от ручного подсчёта и работают с логическими операторами и подстановочными символами (wildcards).

• COUNTIF: считает в одном диапазоне по одному критерию.
• COUNTIFS: считает, когда нужно несколько критериев, применённых к одному или нескольким диапазонам.

Короткие определения:

• COUNTIF(range, criteria) — считает ячейки в range, удовлетворяющие criteria.
• COUNTIFS(range1, criteria1, range2, criteria2, …) — одновременно применяет пары диапазон/критерий.

Поддерживаемые логические операторы

• < меньше чем

• больше чем

• <= меньше или равно

• = больше или равно

• = равно
• <> не равно

Поддерживаемые подстановочные символы (wildcards)

• • — любое количество любых символов
• ? — любой одиночный символ
• ~ — экранирует следующий символ (чтобы трактовать * или ? как обычные символы)

Синтаксис в примерах

Простейшие формулы (в кодовых блоках сохранена точная запись):

=COUNTIF(A3:A11, "Красный")

=COUNTIFS(A3:A11, "Красный", B3:B11, "Большой")

Изображения в статье показывают тот же пример: список яблок с колонками «Цвет» и «Размер».

Простой пример: COUNTIF (пошагово)

Исходная задача: в списке яблок в колонке «Цвет» подсчитать, сколько «Красных» яблок.

Шаги:

1. Выделите ячейку, где будет результат (в примере — E3).
2. В строке формул введите формулу:

=COUNTIF(A3:A11, "Красный")

3. Нажмите Enter — Excel вернёт количество строк в диапазоне A3:A11, где значение равно «Красный».

Результат на листе:

Важно: текстовые критерии нужно брать в кавычки. Для числовых сравнений можно писать условия как строку, например “>=10”.

COUNTIFS: пересечение нескольких условий

COUNTIFS удобен, когда нужно считать строки, удовлетворяющие одновременно нескольким условиям (логическое “И“).

Задача: посчитать «Большие» яблоки с цветом «Красный».

Шаги:

1. Выберите ячейку для результата (в примере — E3).
2. Введите формулу:

=COUNTIFS(A3:A11, "Красный", B3:B11, "Большой")

3. Нажмите Enter — результат покажет количество строк, где одновременно выполнены оба условия.

Ключевая идея: пары диапазон/критерий связаны по позиции. COUNTIFS проверяет, какие строки удовлетворяют всем парам одновременно (пересечение).

Частые сценарии и приёмы

1. Подсчёт по «ИЛИ» (например, красные ИЛИ большие). COUNTIFS возвращает пересечение, поэтому для логического ИЛИ используйте сумму COUNTIF’ов или другие приёмы:

=SUM(COUNTIF(A3:A11, "Красный"), COUNTIF(B3:B11, "Большой"))

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

2. SUMPRODUCT как универсальный инструмент (без массивов в старых версиях Excel):

=SUMPRODUCT((A3:A11="Красный") + (B3:B11="Большой") > 0)

Это посчитает строки, где выполнено хотя бы одно условие.

3. Excel 365 / Excel 2021: FILTER + COUNTA или функции массива дают гибкие решения:

=COUNTA(FILTER(A3:A11, (A3:A11="Красный") + (B3:B11="Большой")))

4. Использование подстановочных символов:

• =COUNTIF(A:A, “яблоко“) — найдёт все ячейки, где встречается “яблоко” как часть текста.
• =COUNTIF(A:A, “?at”) — любые трисимвольные слова с окончанием “at”.
• =COUNTIF(A:A, “~“) — ищет буквальный символ ““.

5. Сравнения с числами:

• =COUNTIF(B2:B100, “>=100”) — считает значения >= 100.

Пошаговая методология: как выбрать формулу

1. Определите, нужно ли логическое И (условия одновременно) или ИЛИ (одно из условий).
2. Если только одно условие и один диапазон — COUNTIF.
3. Если несколько условий, применимых к строкам (пара диапазон/критерий) — COUNTIFS.
4. Если нужно ИЛИ между разными столбцами — SUM + COUNTIF, SUMPRODUCT или FILTER/UNIQUE (в 365).
5. Если необходимо игнорировать пустые/ошибочные значения — комбинируйте с IFERROR, LEN, TRIM или ISBLANK.

Мини-чеатшит:

Примеры «когда не работает» и обходные пути

Когда COUNTIFS не даёт ожидаемый результат:

• Разные длины диапазонов: все диапазоны в COUNTIFS должны быть одинакового размера; иначе функция вернёт ошибку.
• Нужен логический ИЛИ: COUNTIFS считает пересечение, поэтому для ИЛИ используйте другие методы.
• Регистр важен: COUNTIF/COUNTIFS не различают регистр. Если нужен учёт регистра, используйте массив с EXACT.

Пример проблемы с диапазонами:

• Нельзя использовать =COUNTIFS(A2:A10, “x”, B2:B8, “y”) — диапазоны разной длины.

Проверки качества и тесты (Критерии приёмки)

1. Для простых тестовых данных убедитесь, что ручной подсчёт совпадает с результатом формулы.
2. Граничные случаи: пустые ячейки, пробелы, разные регистры, скрытые символы.
3. Тест на пересчет: добавьте строку, удовлетворяющую только одному условию — убедитесь, что при логическом И результат не изменится.
4. Диапазоны одинаковой длины — проверьте до применения COUNTIFS.

Примеры тестовых данных и ожидаемый результат:

• Набор: [“Красный”,”Зелёный”,”Красный”], COUNTIF(range, “Красный”) → 2
• Набор размеров: [“Большой”, “Маленький”], COUNTIFS(цвет, “Красный”, размер, “Большой”) → соответствует пересечению

Альтернативные подходы и когда их применять

• Сводная таблица (Pivot Table): подходит для быстрой агрегации и анализа, не требует написания формул.
• SUMPRODUCT: мощный инструмент для логики И/ИЛИ и масок без явных массивов.
• FILTER + COUNTA (Excel 365): более читаемый путь при работе с динамическими массивами.
• Power Query: преобразование и подсчёт больших или плохо структурированных наборов данных.

Подсказки по производительности

• COUNTIF/COUNTIFS быстры на обычных данных. Однако при десятках тысяч строк и сложных формулах SUMPRODUCT и массивы могут быть медленнее.
• Для больших наборов данных рассмотрите Power Query или сводные таблицы.

Совместимость и примечания по версиям

• COUNTIF доступен в Excel давно (включая старые версии).
• COUNTIFS появился в Excel 2007 и присутствует во всех последующих версиях.
• Excel 365/2021 добавляют динамические массивы и FILTER, которые упрощают многие сценарии.

Шаблоны/шпаргалки (несколько готовых примеров)

Подсчитать все непустые в столбце A:

=COUNTIF(A:A, "<>")

Подсчитать по диапазону дат (включительно):

=COUNTIFS(DateRange, ">=" & DATE(2023,1,1), DateRange, "<=" & DATE(2023,12,31))

Подсчитать уникальные значения, соответствующие условию (Excel 365):

=COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="Красный")))

Подсчитать, если хотя бы одно условие верно (логическое ИЛИ) при помощи SUMPRODUCT:

=SUMPRODUCT((A2:A100="Красный") + (B2:B100="Большой") > 0)

Роли и чек‑листы

Аналитик:

• Проверить одинаковую длину диапазонов.
• Убедиться в корректности пробелов и регистра (TRIM/UPPER при необходимости).
• Тестировать на крайних значениях.

Владелец данных:

• Поддерживать конвенции ввода (например, «Красный», а не «красный»).
• Документировать приемлемые значения.

Менеджер отчёта:

• Проверять сводные показатели на соответствие бизнес-правилам.
• Просить автоматические тесты (Unit tests для шаблонов отчётов).

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

• “COUNTIF — это фильтр + счётчик”: представьте, что Excel сначала фильтрует строки по критерию, а затем считает.
• “COUNTIFS — несколько фильтров, применённых последовательно”: каждая пара диапазон/критерий сужает набор.
• “ИЛИ = сумма” — подсчёт “ИЛИ“ часто реализуется через суммирование отдельных COUNTIF и корректировку пересечений.

Частые ошибки и как их избежать

• Пробелы в конце: используйте TRIM.
• Неправильный регистр: функция нечувствительна к регистру; если нужен учёт — используйте EXACT в массивах.
• Разный тип данных: числа записаны как текст — используйте VALUE или приведение типов.
• Разные длины диапазонов — проверяйте до применения COUNTIFS.

Пример плейбука: исправление неверного результата

1. Проверить, совпадают ли диапазоны по размеру.
2. Проверить, нет ли лишних пробелов (TRIM).
3. Проверить типы данных (TEXT vs NUMBER).
4. Протестировать формулу на небольшом отрезке данных вручную.

Диаграмма выбора (Mermaid)

flowchart TD
  A[Нужно ли одно условие?] -->|Да| B[Используйте COUNTIF]
  A -->|Нет| C[Несколько условий]
  C --> D{Условия должны быть одновременно}
  D -->|Да| E[COUNTIFS]
  D -->|Нет| F[SUM'COUNTIF...' или SUMPRODUCT или FILTER]
  E --> G[Проверьте одинаковую длину диапазонов]

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

COUNTIF и COUNTIFS — базовые приёмы для подсчёта по условиям в Excel. COUNTIF подходит для простых задач, COUNTIFS — для пересечений условий. Для логики ИЛИ и более сложной фильтрации используйте SUMPRODUCT, комбинации SUM+COUNTIF, FILTER или Power Query в зависимости от версии Excel и объёма данных.

Впечатление от изучения этих функций: небольшой объём знаний даёт большую экономию времени при повседневной работе с таблицами.

Часто задаваемые вопросы

Как посчитать по нескольким значениями в одном столбце (например, “Красный” или “Зелёный”)?

Можно суммировать несколько COUNTIF:

=SUM(COUNTIF(A3:A11, {"Красный","Зелёный"}))

(В старых версиях это может потребовать подтверждения как формулы массива.)

Можно ли учитывать регистр в подсчёте?

COUNTIF/COUNTIFS нечувствительны к регистру. Для учёта регистра используйте массив с функцией EXACT, например:

=SUM(--(EXACT(A3:A11, "Красный")))

Как избежать двойного счёта при суммировании COUNTIF для ИЛИ?

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

Авторская заметка: если хотите, я могу подготовить готовый файл Excel с примерами формул и тестовыми данными, либо альтернативный вариант с использованием Power Query.