COUNTBLANK в Excel — как посчитать пустые ячейки

Что делает COUNTBLANK и когда её использовать

COUNTBLANK принимает один аргумент — диапазон ячеек — и возвращает количество ячеек, которые Excel считает пустыми. Простая формула выглядит так:

=COUNTBLANK(range)

Определение “пустой ячейки” в Excel при использовании COUNTBLANK:

• Ячейки без значения считаются пустыми.
• Ячейки с формулой, возвращающей пустую строку (“”), считаются пустыми и будут подсчитаны.
• Ячейки, содержащие только пробелы (“ “), НЕ считаются пустыми — пробел это символ.
• Ячейки, которые выглядят пустыми из‑за пользовательского форматирования (например, формат отображает 0 как пустое поле), НЕ считаются пустыми.
• Скрытые ячейки и ячейки в отфильтрованных строках по умолчанию учитываются COUNTBLANK.

Важно: COUNTBLANK проверяет видимое содержание ячеек не по форматированию, а по наличию/отсутствию значения.

Пример: подсчёт участников, прошедших только один круг

В этом примере у нас есть таблица с результатами благотворительного забега: времена участников за первый и второй круги. Цель — узнать, сколько участников выполнили только один круг, то есть не имеют значения во втором столбце.

Шаги:

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

=COUNTBLANK(C2:C11)

3. Нажмите Enter. Формула просматривает ячейки C2:C11 и возвращает число пустых ячеек — число участников, не прошедших второй круг.

Примечание: в примере диапазон C2:C11 выбран, потому что во второй колонке содержатся времена второго круга.

Альтернативные подходы (когда COUNTBLANK не подходит)

1. COUNTIF для гибких условий

=COUNTIF(C2:C11, "=")

COUNTIF с критерием “=” ведёт себя похоже на COUNTBLANK, но позволяет комбинировать условия или использовать шаблоны.

2. SUMPRODUCT + LEN — чтобы игнорировать строки с пробелами

=SUMPRODUCT(--(LEN(TRIM(C2:C11))=0))

Этот приём сначала убирает пробелы TRIM, затем считает ячейки нулевой длины. Полезно, когда в данных могут быть нечётные пробелы.

3. Использование FILTER/ROWS (в Excel 365/2021)

=ROWS(FILTER(C2:C11, C2:C11=""))

Фильтрация возвращает только пустые ячейки, затем ROWS считает их количество.

4. ISBLANK в сочетании с массивами

=SUM(--ISBLANK(C2:C11))

При вводе как формулы массива (или в динамических массивах Excel) это даёт тот же результат, что и COUNTBLANK.

Когда COUNTBLANK даёт неожиданный результат (контрпримеры)

• Формула возвращает “” (пустая строка) — COUNTBLANK посчитает такую ячейку как пустую. Это может ввести в заблуждение, если вы считаете, что ячейка содержит формулу, а значит — не пуста.
• Ячейка содержит пробелы или невидимые символы (неразрывный пробел, символ табуляции) — COUNTBLANK НЕ посчитает её пустой. В таких случаях используйте TRIM или CLEAN.
• Кастомное форматирование скрывает 0 или текст — визуально ячейка пуста, но COUNTBLANK увидит значение и не посчитает её.
• Мерж‑ячейка: при подсчёте диапазона, содержащего смешанные объединённые ячейки, поведение зависит от структуры — лучше избегать объединённых ячеек для аналитики.

Методика аудита отсутствующих значений (мини‑SOP)

1. Быстрая проверка пробелов: добавьте вспомогательный столбец с формулой =LEN(C2) и посмотрите длины.
2. Удалите невидимые символы: =TRIM(C2) и =CLEAN(TRIM(C2)).
3. Найдите формулы, возвращающие “”: =GET.CELL(48, INDIRECT(“rc”,FALSE)) — редко используемая техника; лучше просмотреть формулы явно.
4. Преобразуйте значения в текст/числа, если необходимо, и повторно примените COUNTBLANK.
5. Документируйте правило: что считать пустым в вашем отчёте (пустая ячейка, “”, пробелы?).

Чек‑лист ролей при работе с пустыми ячейками

• Аналитик данных:

  • Убедиться, что определено, какие значения считать отсутствующими.
  • Использовать SUMPRODUCT+TRIM для удаления ложных непустот.
  • Задокументировать формулы и диапазоны.

• Тот, кто подготавливает данные (ETL/оператор):

  • Очистить исходные данные от пробелов и невидимых символов.
  • Убедиться, что формулы, возвращающие “”, действительно нужны.

• QA / ревьювер:

  • Проверить граничные случаи (мердж‑ячейки, форматирование).
  • Сравнить результаты COUNTBLANK с альтернативными методами (LEN/TRIM).

Набор полезных формул (cheat sheet)

• Простая формула:

=COUNTBLANK(A1:A100)

• С учётом удаления пробелов:

=SUMPRODUCT(--(LEN(TRIM(A1:A100))=0))

• Подсчёт только видимых пустых после фильтрации (использует SUBTOTAL + вспомогательный столбец):

1. В столбце помощи B: =IF(A2=””,1,0)
2. Затем: =SUBTOTAL(9, B2:B100)

• Проверка наличия формулы, возвращающей “”:

=IF(AND(ISFORMULA(A2),A2=""),"пустая формула","")

Совместимость и практические советы

• COUNTBLANK доступна во всех современных версиях Excel (включая Excel для Windows, Mac и Excel Online).
• В Excel для Microsoft 365 можно комбинировать COUNTBLANK с динамическими массивами и FILTER для более гибких расчётов.
• Для больших диапазонов SUMPRODUCT и массивные вычисления могут работать медленнее; по возможности используйте компактные диапазоны.

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

Считает ли COUNTBLANK ячейки, скрытые фильтром?

Да — COUNTBLANK учитывает все ячейки в диапазоне, включая скрытые фильтром. Чтобы считать только видимые ячейки, используйте вспомогательный столбец и SUBTOTAL.

COUNTBLANK считает формулы, возвращающие пустую строку “”?

Да — такие ячейки считаются пустыми и будут включены в счёт.

COUNTBLANK считает ячейки с пробелом как пустые?

Нет — ячейка, содержащая один или несколько пробелов, НЕ считается пустой. Чтобы удалить пробелы, применяйте TRIM.

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

• COUNTBLANK — быстрый способ узнать, сколько ячеек в диапазоне Excel считает пустыми.
• Она считает пустые значения и формулы, возвращающие “”, но не учитывает пробелы и визуально пустое форматирование.
• Для точного подсчёта в реальных данных комбинируйте COUNTBLANK с TRIM, SUMPRODUCT или FILTER.

Важно: всегда заранее определяйте, что для вашей аналитики считается «отсутствием значения», и документируйте это правило.