Как посчитать пустые и незаполненные ячейки в Google Sheets

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

• Using COUNTBLANK
• Using COUNTIF and COUNTIFS
• Using SUMPRODUCT
• Counting Empty Cells

Когда вы анализируете данные в таблице, подсчёт пустых или «пустых на вид» ячеек помогает сфокусироваться на проблемных местах и очистке данных. Функции COUNTBLANK, COUNTIF, COUNTIFS и SUMPRODUCT — базовый набор для таких задач в Google Sheets.

Важно понимать разницу между «пустой на вид» и «технически пустой» ячейкой.

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

Важно: многие функции (COUNTBLANK, COUNTIF с критерием “”) будут считать ячейки с пустой строкой как пустые. Если нужна точность по строго пустым ячейкам, применяйте метод с ROWS/COLUMNS + COUNTIF.

Использование COUNTBLANK

COUNTBLANK быстро считает ячейки, которые Google Sheets рассматривает как пустые на вид. Это самый простой и прямой способ подсчёта «пустых» ячеек, если вас устраивает, что пустая строка “” будет включена в подсчёт.

Ключевые моменты:

• Числа и текст не учитываются (включая 0).
• Ячейки с пустой строкой “” будут считаться пустыми.

Пример формулы. В пустой ячейке введите:

=COUNTBLANK(range)

Замените range на вашу область. Например:

=COUNTBLANK(A:C)

Пример объяснения: если в диапазоне A3:H24 четыре ячейки визуально пустые, COUNTBLANK вернёт 4.

Использование COUNTIF и COUNTIFS

COUNTIF считает ячейки, соответствующие критерию. Для подсчёта пустых ячеек используйте пустую строку “” как критерий.

Пример простого выражения:

=COUNTIF(range,"")

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

=COUNTIFS(range,"")

COUNTIF/COUNTIFS поведут себя так же, как COUNTBLANK в части учёта пустых строк.

Использование SUMPRODUCT

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

=SUMPRODUCT(--(range=""))

SUMPRODUCT полезен, если вы хотите объединить несколько условий (например, пустая ячейка в сочетании с другим критерием по соседнему столбцу).

Как найти действительно пустые ячейки

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

Пошаговая методика:

1. Найдите общее количество ячеек в диапазоне:

=ROWS(range)*COLUMNS(range)

2. Посчитайте ячейки с числовыми значениями:

=COUNTIF(range,">=0")

Этот критерий (“>=0”) подходит для чисел и нуля. Учтите, что отрицательные числа тоже будут учитываться, поскольку “”>=0”” даст логическое значение только для чисел; при необходимости уточните критерий.

3. Посчитайте ячейки с текстом или пустой строкой:

=COUNTIF(range,"*")

Звёздочка означает любое количество символов, поэтому ячейки с текстом и пустыми строками тоже попадают сюда.

4. Сложите результаты COUNTIF для чисел и текста и вычтите их из общего количества ячеек:

=Общее_число - SUM(числа_и_текст)

Пример в ячейках таблицы: если B8 содержит общее число ячеек, B9 — число чисел, B10 — число текстовых/пустых строк, то формула будет:

=B8-SUM(B9:B10)

В примере диапазона A2:E5 из 20 ячеек оказалось 19 занятых (число/текст/пустая строка) и только одна полностью пустая ячейка.

Важно: если в диапазоне есть логические значения TRUE/FALSE или ошибки, их надо учитывать отдельно. COUNTIF с “>=0” не посчитает TRUE/FALSE; их можно обработать через COUNTIF(range,TRUE) или через SUMPRODUCT с ISBLANK/ISTEXT/ISNUMBER.

Расширенные приёмы и альтернативы

• Подсчёт ячеек с формулами, которые возвращают “”:
  • Используйте массивную формулу с LEN и IFERROR, например, чтобы отличать формулы, можно проверять FORMULATEXT (если требуется показать текст формулы), но FORMULATEXT возвращает текст формулы, а не значение.
• Подсчёт пустых ячеек в динамических диапазонах:
  • Используйте именованные диапазоны или функции OFFSET/INDEX для создания динамических областей.
• Исключение пробелов: ячейка может содержать пробелы и выглядеть пустой. Применяйте TRIM или проверку LEN(TRIM(cell))=0 для таких случаев.
• Обработка ошибок: используйте IFERROR при операций с диапазонами, где возможны ошибки.

Когда эти методы не подходят

• Если в ячейке есть невидимые символы (неразрывные пробелы, табы), COUNTBLANK и COUNTIF(“*”) могут давать неожиданные результаты. В таких случаях применяйте TRIM и CLEAN.
• Если нужно определить, была ли ячейка явно очищена пользователем (а не установлена формула), стандартными формулами этого не проверить — потребуется скрипт Apps Script или audit log.
• Для больших диапазонов и частых пересчётов SUMPRODUCT и массивные вычисления могут замедлять файл. В таких случаях лучше кэшировать результаты или использовать скрипт.

Практические примеры и шаблоны

Мини-методология для задач с очисткой данных:

1. Проинвентаризировать диапазон и понять, какие типы данных ожидаются (текст, числа, даты).
2. Запустить проверки: COUNTIF(range,””) для визуальных пустых, COUNTIF(range,”>=0”) для чисел, COUNTIF(range,”*”) для текста.
3. Очистить невидимые символы: создать вспомогательный столбец с =TRIM(CLEAN(A2)).
4. Пересчитать по очищенному диапазону.
5. Если требуется, заменить пустые строки на NA() или другое значение для дальнейшей обработки.

Шаблон формул для одноразового отчёта о состоянии диапазона (пример):

• Общее количество: =ROWS(A2:E5)*COLUMNS(A2:E5)
• Количество чисел: =COUNTIF(A2:E5,">=0")
• Количество текстов и пустых строк: =COUNTIF(A2:E5,"*")
• Действительно пустые: =ROWS(A2:E5)*COLUMNS(A2:E5)-SUM(COUNTIF(A2:E5,">=0"),COUNTIF(A2:E5,"*"))

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

• Скрипт/отчёт должен корректно считать «пустые на вид» ячейки (“”), числа и текст отдельно.
• Для диапазона, где есть пробелы, должен быть шаг очистки TRIM/CLEAN, и результаты пересчитаны.
• Производительность: расчёт не должен вызывать заметной задержки при работе с общедоступными таблицами размером до нескольких тысяч строк. Для больших наборов используйте Apps Script.

Набор тестов для проверки формул

1. Пустая ячейка без формулы должна быть учтена как технически пустая.
2. Ячейка со значением “” (формула возвращает “”) должна считаться пустой для COUNTBLANK/COUNTIF.
3. Ячейка со строкой из пробелов должна считаться текстовой до применения TRIM.
4. Ячейка с числом 0 должна учитываться функцией COUNTIF(range,”>=0”).
5. Ячейка с ошибкой (#N/A, #DIV/0!) должна обрабатываться отдельно.

Роль-ориентированные чек-листы

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

  • Проверить, какие типы данных ожидаются.
  • Выполнить COUNTBLANK и COUNTIF для быстрой оценки качества.
  • Очистить пробелы и несимволы, затем пересчитать.

• Для инженера данных:

  • Если диапазон большой, подготовить Apps Script для массовой проверки.
  • Обеспечить, чтобы формулы не вызывали циклических ссылок.
  • Логировать изменения при автоматической очистке.

• Для QA:

  • Прогнать тесты из раздела “Набор тестов”.
  • Проверить пограничные случаи и значения NULL/NaN.

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

• “Внешний вид ≠ содержимое”: всегда думайте, что видимая пустота может скрывать формулу или невидимые символы.
• “Сначала посчитай, затем чисти”: сначала оцените масштаб проблемы с помощью COUNTBLANK/COUNTIF, затем применяйте очистку и повторный подсчёт.
• “Кэшируй тяжёлые вычисления”: большие SUMPRODUCT или массивные операторы лучше выполнять один раз и сохранять результат в вспомогательной ячейке.

Глоссарий (1 строка определения)

• Пустая ячейка: ячейка без значения и без формулы.
• Пустая строка “”: результат формулы или запись, которая представляет собой строку длины 0.
• Визуально пустая: ячейка выглядит пустой, но содержит формулу или невидимые символы.

Итог

COUNTBLANK, COUNTIF/COUNTIFS и SUMPRODUCT предоставляют быстрые способы оценить «пустые» ячейки в Google Sheets. Если вам нужно найти именно технически пустые ячейки, используйте метод с ROWS*COLUMNS и вычитанием числовых и текстовых значений. Для надёжной очистки данных добавляйте шаги TRIM и CLEAN, а при работе с большими объёмами рассматривайте автоматизацию через Apps Script.

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

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

• COUNTBLANK и COUNTIF(range,””) считают ячейки с пустой строкой как пустые.
• Для поиска строго пустых ячеек применяйте ROWS*COLUMNS минус COUNTIF чисел и текстов.
• Очистка пробелов и невидимых символов обязательна для корректного подсчёта.

Примечание: если вам нужна автоматическая проверка состояния столбцов или скрипт для регулярной очистки, могу подготовить пример Apps Script по запросу.