Как использовать COUNTIF и COUNTIFS в Google Таблицах

Что такое функции COUNTIF и COUNTIFS в Google Таблицах?

COUNTIF и COUNTIFS — базовые функции подсчёта по условию. Они берут диапазон ячеек и возвращают количество ячеек, удовлетворяющих критерию. Разница простая:

• COUNTIF принимает один диапазон и одно условие. Используйте, когда нужно одно правило.
• COUNTIFS принимает несколько пар «диапазон — условие» и возвращает количество строк (или соответствующих позиций), где все пары условий истинны одновременно.

Определение в одну строку:

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

Синтаксис

COUNTIF:

=COUNTIF(range, criterion)

COUNTIFS:

=COUNTIFS(range1, criterion1, range2, criterion2, ...)

Важно: пары диапазонов в COUNTIFS должны соответствовать по размеру (одинаковое количество строк/столбцов), иначе функция вернёт ошибку.

Поддерживаемые операторы и шаблоны

COUNTIF и COUNTIFS принимают логические операторы и шаблоны (wildcards):

• , <, =, >=, <= — для чисел и дат.

• ? — заменяет один любой символ. Пример: R?d совпадёт с Rod или Red.
• • — заменяет любое количество символов (включая ноль). Пример: R*d совпадёт с Rod, Road, Roasted.
• ~ — экранирует следующий символ, чтобы он не трактовался как шаблон. Пример: R~?d ищет строку “R?d”.

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

=COUNTIF(B2:B7, ">=" & E1)

Если E1 содержит 20, критерий станет “>=20”.

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

Пример использования COUNTIF

Задача: посчитать, сколько волонтёров старше или равны 20 годам.

Таблица (фрагмент):

Формула в ячейке F4:

=COUNTIF(B2:B7, ">=20")

Как это работает:

1. Функция просматривает каждую ячейку диапазона B2:B7.
2. Проверяет условие “>=20”.
3. Возвращает число совпадений (в примере — 3).

Другой пример — подсчёт точных текстовых совпадений:

=COUNTIF(A1:A10, "Red")

Замечание: COUNTIF с текстом чувствителен к точному соответствию; если нужно нечувствительное сравнение, используйте массивные формулы с LOWER/UPPER.

Когда COUNTIF не подходит

COUNTIF не умеет одновременно проверять два разных поля. Пример: «возраст >=20 И рост >175» — COUNTIF не справится, потому что он принимает только один диапазон и одно условие. В таком случае используйте COUNTIFS.

Пример использования COUNTIFS

Задача: посчитать волонтёров, у которых возраст >=20 и рост >175 см.

Формула в ячейке F5:

=COUNTIFS(B2:B7, ">=20", C2:C7, ">175")

Пояснение:

1. COUNTIFS сначала проверяет в каждой строке условие для B2:B7 (возраст >=20).
2. Для тех же позиций проверяет соответствующее условие в C2:C7 (рост >175).
3. Считает только те позиции, где оба условия истинны одновременно.

Важно: COUNTIFS интерпретирует каждую пару (rangeN, criterionN) как соотносимую по позиции. Это значит, что если диапазоны не одинаковы по размеру, функция выдаст ошибку.

Полезные приёмы и альтернативы

1. Логическое ИЛИ для нескольких условий (например, цвет = Red или Blue):
   • Используйте сумму COUNTIF:

=COUNTIF(A1:A10, "Red") + COUNTIF(A1:A10, "Blue")

• Или массивную форму с SUMPRODUCT:

=SUMPRODUCT((A1:A10="Red")+(A1:A10="Blue"))

2. когда диапазоны не одинаковы по размеру, или нужна гибкая фильтрация — используйте FILTER и COUNTA:

=COUNTA(FILTER(A2:A100, B2:B100>=20, C2:C100>175))

3. SUMPRODUCT — мощная альтернатива, особенно для многомерных условий без строгих требований к одинаковым диапазонам:

=SUMPRODUCT((B2:B7>=20)*(C2:C7>175))

4. QUERY — удобна, когда нужны сложные группировки и агрегаты. Пример:

=QUERY(A1:C, "select count(A) where B >= 20 and C > 175 label count(A) ''")

5. Для нечувствительности к регистру объединяйте LOWER/UPPER с COUNTIF через массивы или SUMPRODUCT.

Типичные ошибки и как их исправить

• Ошибка #VALUE! из-за несовпадающих размеров диапазонов в COUNTIFS. Решение: приведите диапазоны к одному размеру.
• Неправильный результат при сравнении с датой. Решение: используйте DATE(year,month,day) или убедитесь, что формат ячейки — дата.
• Шаблон не срабатывает, если в строке есть невидимые пробелы. Решение: TRIM перед сравнением.
• Ожидание OR вместо AND. COUNTIFS делает AND. Для OR используйте сумму COUNTIF или SUMPRODUCT.

Набор быстрых примеров (cheat sheet)

• Количество пустых ячеек в диапазоне:

=COUNTIF(A1:A100, "")

• Количество непустых ячеек:

=COUNTA(A1:A100)

• Количество ячеек, начинающихся с “2022”:

=COUNTIF(A1:A100, "2022*")

• Количество с условием, использующим ссылку:

=COUNTIF(B2:B100, ">" & D1)

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

=COUNTIFS(B2:B100, ">=18", C2:C100, "<200")

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

• Думайте о COUNTIFS как о фильтре, который применяют последовательно по колонкам: первая пара сужает набор, вторая — сужает дальше по тем же позициям.
• COUNTIF — это одноусловный фильтр; COUNTIFS — набор фильтров, применяемых одновременно.
• Если нужно объединить разные поля как OR — суммируйте результаты, если как AND — используйте COUNTIFS или SUMPRODUCT.

Когда стоит выбрать альтернативу

• Большая гибкость и сложные вычисления: SUMPRODUCT.
• Нужен список значений, а не просто число: FILTER.
• Комбинация с агрегатами или группировкой: QUERY.

Тестовые сценарии и критерии приёмки

1. Простая проверка COUNTIF:

   • Ввод: B2:B4 = {10, 20, 30}, формула COUNTIF(B2:B4, “>=20”).
   • Ожидание: 2.

2. COUNTIFS с двумя диапазонами одинакового размера:

   • B2:B4 = {20, 25, 30}, C2:C4 = {170, 180, 176}, формула COUNTIFS(B2:B4, “>=20”, C2:C4, “>175”).
   • Ожидание: 2 (25/180 и 30/176).

3. Несоответствие размеров диапазонов:

   • B2:B4 и C2:C5 — ожидание: ошибка; поведение: #VALUE!.

4. Проверка шаблонов:

   • A1:A4 = {“Red”,”Road”,”R?d”,”R?d”}, COUNTIF(A1:A4, “R*d”) — ожидание: 2 (Red, Road), COUNTIF(A1:A4, “R~?d”) — ожидание: 2 (“R?d” строки).

Чек-лист для аналитика

• Убедитесь, что диапазоны одинаковой длины в COUNTIFS.
• Проверьте локаль при работе с датами и десятичными разделителями.
• Обрежьте лишние пробелы с помощью TRIM при сравнении текста.
• Используйте CONCATENATION (“&”) для динамических критериев.

Примеры использования в рабочих сценариях

• Отчёт по KPI: посчитать количество заказов с датой >= начала месяца и суммой > X с помощью COUNTIFS или QUERY.
• Кадровый учёт: посчитать сотрудников старше 30 лет в департаментах A и B (для OR — суммируйте COUNTIFS/COUNTIF по отделам).
• Качество данных: найти строки, где обязательное поле пустое: COUNTIF(range, “”).

Короткий глоссарий (1 строка)

• Диапазон: набор ячеек, который функция просматривает.
• Критерий: выражение или шаблон, которому должна соответствовать ячейка.
• Wildcard: шаблонный символ (*, ?) для текстовых совпадений.

Примеры сложных комбинаций

1. Комбинация AND и OR (подсчитать записи, где (A=Red И B=Yes) ИЛИ (A=Blue И B=Maybe)):

=SUM(
  COUNTIFS(A2:A100, "Red", B2:B100, "Yes"),
  COUNTIFS(A2:A100, "Blue", B2:B100, "Maybe")
)

2. Подсчёт по диапазону с динамическим порогом и экранированием:

=COUNTIFS(B2:B100, ">=" & F1, C2:C100, "*" & F2 & "*")

Короткая методика для внедрения формул в отчёты

1. Определите, какие поля должны проверяться и логическое отношение между ними (AND/OR).
2. Если только AND — используйте COUNTIFS; если OR — разбейте на несколько COUNTIF/COUNTIFS и суммируйте.
3. Протестируйте на небольшом наборе данных.
4. Проверьте граничные случаи (пустые ячейки, формат дат, пробелы).
5. Автоматизируйте через named ranges или таблицы, чтобы формулы были понятны и поддерживаемы.

Заключение

COUNTIF и COUNTIFS — простые и мощные инструменты для подсчёта значений по условиям в Google Таблицах. COUNTIF хорош для одного условия. COUNTIFS — для набора условий, которые должны быть верны одновременно. Если нужно OR-логическое поведение или более гибкая логика, используйте сочетания COUNTIF, SUMPRODUCT, FILTER или QUERY. Протестируйте формулы на реальных данных и следите за соответствием диапазонов.

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

Ключевые ресурсы: официальная справка Google Sheets по функциям COUNTIF/COUNTIFS и практика с FILTER/SUMPRODUCT для альтернатив.