Как считать пустые ячейки в Google Sheets с помощью COUNTBLANK

COUNTBLANK — простая функция Google Sheets, которая возвращает число абсолютно пустых ячеек в указанном диапазоне. Она не считает ячейки с формулами, возвращающими пустую строку, с пользовательским форматированием или со значением “” как пустые. Используйте COUNTBLANK для быстрой проверки пропусков данных, а для более гибких сценариев сочетайте её с COUNTA, IF, FILTER или SUMPRODUCT.

Введение

Google Sheets — это удобный инструмент для анализа и учёта данных в таблицах. Часто нужно быстро узнать, сколько ячеек в диапазоне пустые. Для этого существует встроенная функция COUNTBLANK. В этой статье мы подробно разберём, как она работает, какие есть подводные камни и как применять её в одиночных и составных формулах.

Что делает COUNTBLANK

COUNTBLANK просматривает указанный диапазон и возвращает количество ячеек, которые полностью пусты. Синтаксис прост:

=COUNTBLANK(range)

Где range — это один диапазон ячеек (например, A1:A10 или B2:E20).

Определение “пустой” ячейки здесь строгое: функция учитывает только ячейки, в которых нет ни значения, ни формулы, ни невидимого текста. Ниже — короткий список решений, какие случаи считаются пустыми, а какие — нет.

Что считается пустой ячейкой

• Ячейка, в которой нет ничего — считается пустой.

Что не считается пустой ячейкой

• Ячейки с формулой, которая возвращает пустую строку, например =”” или =IF(…, “”, “value”). Такие ячейки не считаются пустыми.
• Ячейки с видимым пустым видом из‑за пользовательского числового формата (например формат «;»), но содержащие число или текст — не пустые.
• Ячейки с пробелами или невидимыми символами — не считаются пустыми.
• Ячейки, содержащие ошибку (#N/A, #REF! и т.д.), не считаются пустыми.

Important: COUNTBLANK смотрит на внутреннее содержание ячейки, а не на визуальное отображение.

Простая демонстрация: считать пустые ячейки в одном столбце

Предположим, у вас есть список из 10 добровольцев и столбец с оценками. Некоторые участники не пришли, и в их ячейках нет значений. Чтобы посчитать число пропусков, выполните шаги:

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

=COUNTBLANK(B2:B11)

3. Нажмите Enter. В ячейке E1 появится количество пустых ячеек в диапазоне B2:B11.

Составной пример: решаем задачу с помощью COUNTBLANK и других функций

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

Если более половины добровольцев пришли на экзамен, назначаем второй экзамен. Как написать формулу, которая выдаст текстовое сообщение с решением?

Функции, которые понадобятся:

=COUNTBLANK(range)          // число пустых ячеек
=COUNTA(range)              // число непустых ячеек
=IF(logical, value_if_true, value_if_false)

Шаги:

1. Выделите ячейку для результата, например E5.
2. Введите формулу:

=IF(COUNTA(B2:B11)>COUNTBLANK(B2:B11), "Второй экзамен назначается", "Второй экзамен не назначается")

3. Нажмите Enter.

Логика формулы: если количество непустых ячеек (принявших участие) больше количества пустых — возвращаем строку о назначении второго экзамена, иначе — о его отмене.

Замечание: если вы используете локализованные листы (с запятой и точкой с запятой как разделителями аргументов), возможно, придётся заменить запятые на точки с запятой: =IF(COUNTA(B2:B11)>COUNTBLANK(B2:B11);”Второй…”;”Не назначается”).

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

Ниже перечислены удобные приёмы и альтернативы, когда простого COUNTBLANK недостаточно.

1) Посчитать пустые ячейки по нескольким диапазонам

COUNTBLANK принимает только один диапазон. Для нескольких диапазонов используйте сумму:

=COUNTBLANK(A2:A50) + COUNTBLANK(C2:C50)

Или используйте SUMPRODUCT с массивом:

=SUMPRODUCT(--(LEN(TRIM(TO_TEXT({A2:A50;C2:C50})))=0))

2) Исключить ячейки с формулами, которые возвращают “” (пустая строка)

COUNTBLANK не считает такие ячейки пустыми, поэтому чтобы посчитать ячейки, которые визуально пустые (включая “”), используйте LEN и TO_TEXT:

=SUMPRODUCT(--(LEN(TRIM(TO_TEXT(A2:A50)))=0))

Этот приём превращает каждое значение в текст и считает длину строки. Если длина равна нулю — ячейка визуально пустая.

3) Посчитать только видимые (отфильтрованные) ячейки

COUNTBLANK учитывает все ячейки диапазона, включая скрытые. Чтобы считать только видимые после фильтра, используйте SUBTOTAL совместно с FILTER и массивами или Apps Script. Один из рабочих вариантов:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2,ROW(A2:A50)-ROW(A2),0))*--(A2:A50=""))

Где SUBTOTAL(103, …) возвращает 1 для видимой строки и 0 для скрытой.

4) Подсчитать пустые ячейки с учётом условий (COUNTIFS)

COUNTBLANK не поддерживает критерии. Чтобы комбинировать условия, используйте COUNTIFS сравнения с пустой строкой:

=COUNTIFS(A2:A50, "")

Этот приём работает, но важно помнить, что COUNTIFS с критерием “” не считает ячейки с формулами, возвращающими “” как пустые — в этом случае они будут считаться соответствующими критерию “”. Поведение может отличаться от COUNTBLANK, поэтому тестируйте на ваших данных.

5) Общая альтернатива: COUNTA и арифметика

Если вам нужно узнать, сколько заполнено и сколько пусто в диапазоне фиксированного размера, можно использовать COUNTA и вычислить разницу:

=ROWS(A2:A11) - COUNTA(A2:A11)

Это вернёт количество ячеек, которые не заполнены, исходя из общей длины диапазона.

Когда COUNTBLANK даёт неожиданные результаты

• Ячейка содержит невидимые символы (пробелы, неразрывный пробел). Визуально пустая, но не пустая для COUNTBLANK.
• Формула явно возвращает “” — COUNTBLANK не считает такую ячейку пустой.
• В ячейке находится логическая FALSE, ноль или пустой массив — они не считаются пустыми.

Решение: применяйте TRIM/TO_TEXT/LEN или используйте регулярные проверки через ARRAYFORMULA, чтобы нормализовать данные перед подсчётом.

Практическая шпаргалка по формулам (cheat sheet)

Мини‑методология: аудит пустых значений в проекте данных

1. Определите, что для вашего проекта значит “пусто” (NULL, пустая строка, пробелы, ошибка).
2. Нормализуйте данные: примените TRIM, замените специальные символы, выполните VALUE/TO_TEXT при необходимости.
3. Примените COUNTBLANK или SUMPRODUCT/LEN, в зависимости от определения пустоты.
4. Задокументируйте логику в отдельной ячейке или заметке для повторного использования.
5. Автоматизируйте проверку с помощью условного форматирования или скрипта, если данные приходят регулярно.

Чек‑листы для ролей

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

• Определил правило пустоты для набора данных
• Привёл данные к единому формату (TRIM/TO_TEXT)
• Использовал COUNTBLANK/COUNTIFS и сохранил формулы шаблоном

Менеджер продукта

• Проверил, где пропуски влияют на метрику
• Попросил аналитиков настроить проверки качества данных

HR / Администратор

• Проверил пропуски в обязательных полях формы
• Настроил валидацию в форме для уменьшения пустых полей

Критерии приёмки

1. Формула корректно считает пустые ячейки в тестовом диапазоне из 10 строк, где 3 ячейки полностью пусты.
2. Формула учитывает случаи с пробелами и формулами, возвращающими “” в соответствии с принятым определением пустоты.
3. Результат совпадает с ручной проверкой на репрезентативной выборке.

Примеры тестовых сценариев

• Тест 1: Диапазон 1:10, пустые ячейки: A3, A7, A9 — ожидаемый результат =3.
• Тест 2: Диапазон содержит формулы =IF(B2>0, “x”, “”) — при визуально пустых ячейках COUNTBLANK вернёт 0 для таких строк, тестируем альтернативную формулу с LEN.
• Тест 3: Диапазон с фильтрами — скрытые строки не должны учитываться при подсчёте видимых пустых.

Модель принятия решений: простая схема

flowchart TD
  A[Начало: есть диапазон] --> B{Нужно учитывать визуально пустые ''''?}
  B -- Да --> C[Использовать SUMPRODUCT+LEN/TO_TEXT]
  B -- Нет --> D{Нужно учитывать скрытые строки?}
  D -- Да --> E[Использовать SUBTOTAL+SUMPRODUCT]
  D -- Нет --> F[Использовать COUNTBLANK]
  C --> G[Результат]
  E --> G
  F --> G
  G --> H[Дальнейшие действия: IF/COUNTA/отчёт]

Полезные советы и эвристики

• Для быстрой диагностики пробелов используйте формулу =LEN(A2). Если она >0, в ячейке что‑то есть.
• Применяйте TRIM при импорте данных из форм — часто приходят невидимые пробелы.
• Документируйте в комментариях листа, как именно считается пустая ячейка, чтобы коллеги не путались.

Сопровождение и миграция

Если вы переносите таблицу в Excel или из Excel в Google Sheets, проверьте локализацию разделителей аргументов и функции (в некоторых локализациях необходимы точки с запятой). Также убедитесь, что формулы, возвращающие “”, ведут себя одинаково в целевой системе.

Итог

COUNTBLANK — простой и быстрый инструмент для подсчёта абсолютно пустых ячеек. В реальных рабочих сценариях чаще требуется более тонкая логика: учитывать визуально пустые строки, фильтры или условия. Для таких задач используйте комбинации SUMPRODUCT, LEN, TO_TEXT, SUBTOTAL и COUNTIFS. Всегда определяйте, что в вашем проекте считается пустотой, и документируйте это правило.

Краткое резюме и действия

• COUNTBLANK хорош для быстрого подсчёта полностью пустых ячеек.
• Для визуально пустых ячеек применяйте LEN(TRIM(TO_TEXT(…))).
• Для учёта видимости строк используйте SUBTOTAL + SUMPRODUCT.
• Документируйте выбор метода и делайте тесты на контрольной выборке.

Notes: Проверяйте формулы на небольших образцах данных перед массовым применением.