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

Логотип Google на пляже

Знание точного количества заполненных ячеек в таблице часто кажется тривиальным, но это важно при анализе объёмных данных в Google Sheets. В этом материале мы подробно разберём, когда и как применять COUNTA и COUNTIF, какие у них ограничения и какие альтернативы подойдут для чистки данных.

Зачем считать непустые ячейки

Коротко — это помогает понять покрытие данных: сколько строк содержит значения, где есть «дырки», и какова фактическая полнота таблицы перед расчётами или экспортом. Это особенно полезно перед сводами, визуализацией или импортом в BI.

Важно: «пустая» ячейка и «ячейка, визуально пустая» — не всегда одно и то же. Пробелы, табуляции, невидимые символы и префикс-апострофы могут заставить привычные формулы возвращать неожиданные результаты.

Основные способы подсчёта непустых ячеек

Ниже — два распространённых подхода и когда их применять.

1) COUNTA — простая и быстрая

Синтаксис:

=COUNTA(val1, val2, ...)

Описание аргументов:

val1 — первый диапазон или значение; обязателен.
val2 — дополнительный диапазон или значение; необязателен (до val30, всего можно передать до 30 аргументов).

Особенности:

COUNTA считает все непустые ячейки в указанных диапазонах.
Она учитывает нулевые строки, пробелы и строки, содержащие только невидимые символы.

Пример использования — возьмём столбец, где 6 ячеек содержат видимые значения, а 4 — пустые по визуальному восприятию. Выполняем:

Выделите ячейку, в которую хотите поместить результат.
Введите формулу: =COUNTA(A2:A11).
Нажмите Enter.

Подсчёт через COUNTA: пример с данными в столбце

Важно: если в «пустых» ячейках есть пробелы или невидимые символы, COUNTA всё равно посчитает их как непустые.

Когда COUNTA даёт неверный результат

COUNTA подойдёт, если ваши данные «чистые» (нет лишних пробелов, невидимых символов, случайных апострофов или служебных символов). Но в реальных таблицах часто встречаются ошибки ввода:

случайно нажали пробел;
вставили данные из внешнего источника с невидимыми символами;
использовали апостроф перед числом (иногда апостроф не видим, но влияет на содержимое).

В таких случаях COUNTA завышает число заполненных ячеек.

2) COUNTIF — с шаблонами и масками

Синтаксис:

=COUNTIF(range, criteria)

Описание аргументов:

range — диапазон для проверки.
criteria — условие или шаблон.

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

? — соответствует одному любому символу.
- — соответствует любому количеству символов (включая буквы и цифры).
Чтобы искать фактический символ * или ?, предваряйте его ~.

Пример: чтобы посчитать ячейки, которые содержат любую строку (то есть не пустые с текстом), используйте критерий “*”:

Выберите ячейку для результата.
Введите: =COUNTIF(A2:A11, "*").
Нажмите Enter.

COUNTIF с шаблоном

Почему это полезно: COUNTIF с “*” отбирает ячейки по наличию строки. В некоторых случаях это даёт более реалистичный подсчёт, когда в столбце есть пробелы или невидимые символы, но не все типы «мусора» фильтруются одинаково — см. секцию об ограничениях ниже.

Практический пример: шаг за шагом

Сценарий: в диапазоне A2:A11 6 ячеек содержат значения; 2 ячейки содержат только пробел или апостроф; 1 ячейка полностью пустая.

COUNTA(A2:A11) вернёт 9 (считает и пробелы/апострофы как значения).
COUNTIF(A2:A11, “*”) в большинстве случаев вернёт 7 (игнорирует строго пустую ячейку) — но поведение зависимo от типа невидимых символов.

Проверка формулы:

Введите в ячейку: =COUNTA(A2:A11) — посмотрите результат.
В другой ячейке введите: =COUNTIF(A2:A11, "*") — сравните.
Для более строгой очистки используйте одну из следующих формул (см. раздел “Альтернативные подходы”).

Альтернативные подходы (когда стандартные формулы не подходят)

Если данные загрязнены пробелами, невидимыми символами или символами-разделителями, стандартные COUNTA и COUNTIF могут не дать точного результата. Вот рабочие альтернативы.

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

=SUMPRODUCT(--(LEN(TRIM(A2:A11))>0))

Описание: TRIM удаляет ведущие и завершающие пробелы (и дубль-пробелы между словами), LEN считает длину результата, а SUMPRODUCT с приведением логики через – суммирует истины.

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

=SUMPRODUCT(--(REGEXMATCH(A2:A11, "\S")))

Описание: REGEXMATCH с шаблоном \S ищет любой непробельный символ. Формула вернёт количество ячеек, где присутствует хотя бы один непробельный символ.

Использование ARRAYFORMULA для динамического подсчёта на массиве (удобно в «живых» листах):

=SUM(ARRAYFORMULA(LEN(TRIM(A2:A11))>0))

Совет: если в ваших данных могут появляться невидимые управляющие символы, предварительно примените CLEAN: TRIM(CLEAN(...)).

Сравнение: COUNTA vs COUNTIF vs SUMPRODUCT(TRIM/LEN)

COUNTA — самый простой, считает все непустые ячейки, включая пробелы и невидимые символы.
COUNTIF(range, “*”) — удобен и часто даёт более точный результат для обычного текста; игнорирует полностью пустые ячейки, но может считать ячейки, содержащие только пробелы, как пустые или нет в зависимости от их реального содержимого.
SUMPRODUCT + LEN(TRIM(…)) — надёжнее всего для игнорирования ведущих/завершающих пробелов и пустых строк, подходит для чистки данных перед анализом.

Типичные ошибки и когда методы не сработают

Ожидаете, что COUNTA удалит пробелы. Не удалит.
COUNTIF(“*”) может быть обманут невидимыми символами или форматом данных (например, если значение — формула, возвращающая пустую строку).
TRIM не удаляет все типы управляющих символов. Для этого применяют CLEAN.
REGEXMATCH с неправильным шаблоном даст ошибочные совпадения — проверяйте шаблоны на тестовых примерах.

Шпаргалка (cheat sheet)

Простая проверка непустых: =COUNTA(A2:A11)
Игнорировать пустые строки: =COUNTIF(A2:A11, "*")
Игнорировать пробелы спереди/сзади: =SUMPRODUCT(--(LEN(TRIM(A2:A11))>0))
Игнорировать любые пробельные символы: =SUMPRODUCT(--(REGEXMATCH(A2:A11, "\\S")))

Примечание: в формуле JSON-строки и экранирование выглядят иначе; в самом листе используйте формулы как указано.

Чек-листы по ролям — что проверить перед отчётом

Аналитик:

Очистить поля от ведущих/завершающих пробелов с TRIM.
Проверить на невидимые символы и применить CLEAN.
Сравнить результаты COUNTA и SUMPRODUCT(LEN(TRIM(…))).

Администратор данных:

Настроить проверку ввода (Data validation) для критичных колонок.
Проводить периодическую очистку данных (скрипты Apps Script или импорты с предобработкой).

Бизнес-пользователь:

Перед сводами преосмотреть «пустые» строки визуально и по формуле TRIM.
Документировать правила: что считать пустым в контексте отчёта.

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

Все подсчёты по колонке совпадают с ручной выборочной проверкой не менее чем в 10 случайных строк.
Формулы не дают ошибок при появлении пустых ячеек, формул и чисел.
При использовании SUMPRODUCT/TRIM счёт исключает строки, содержащие только пробелы.
Документирован используемый метод и объяснено, почему он выбран.

Мини‑методология для внедрения в проект

Оцените источник данных — как часто появляются пробелы/управляющие символы.
Выберите формулу по критериям: простота (COUNTA) vs устойчивость к мусору (SUMPRODUCT+TRIM).
Напишите тестовые случаи и проверьте на выборке.
Автоматизируйте очистку (скрипт, Power Query/ETL) при необходимости.

Быстрый план отката/инцидентный сценарий

Если отчёт показывает неожиданное снижение числа заполненных ячеек — сначала сравните COUNTA и SUMPRODUCT(LEN(TRIM(…))).
Если расхождение появилось после импорта — верните предыдущую версию листа (Файл → История версий).

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

COUNTA — функция, считающая все непустые значения в диапазоне.
COUNTIF — функция для подсчёта по критерию (шаблону).
TRIM — удаляет ведущие и завершающие пробелы.
CLEAN — удаляет непечатные/управляющие символы.
REGEXMATCH — проверяет соответствие регулярному выражению.

Маленькая памятка: когда использовать что

Быстрая проверка заполненности — COUNTA.
Учёт только «настощие» текстовые значения — COUNTIF с “*”.
Самая строгая проверка на отсутствие пробелов — SUMPRODUCT + LEN(TRIM(…)).

Небольшая диаграмма решения (Mermaid)

flowchart TD
  A[Начать: надо посчитать непустые ячейки?] --> B{Данные «чистые» от пробелов/символов?}
  B -- Да --> C[Использовать COUNTA]
  B -- Нет --> D{Нужна ли точность против пробелов/символов?}
  D -- Нет --> E[COUNTIF'range, '*'']
  D -- Да --> F[SUMPRODUCT'--'LEN'TRIM'range''>0'']
  C --> Z[Готово]
  E --> Z
  F --> Z

Заключение

COUNTA и COUNTIF — простые и удобные инструменты для подсчёта непустых ячеек в Google Sheets. Однако в реальном рабочем потоке данные часто «грязные»: пробелы, невидимые символы и префикс-апострофы меняют поведение формул. Для надёжного подсчёта применяйте TRIM/CLEAN вместе с SUMPRODUCT или регулярные выражения, тестируйте формулы на выборке и документируйте выбранный подход.

Важно: всегда проверяйте результаты на контрольной выборке перед массовым применением — это сэкономит время и убережёт от неверных выводов.

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