SUBTOTAL в Google Sheets: как и когда использовать

Коротко о функции

SUBTOTAL — это специализированная функция для подсчёта подитогов в наборах данных. Одной строкой: SUBTOTAL считает только те ячейки, которые нужны, автоматически игнорируя подитоги в указанном диапазоне и (при выборе кодов 100+) — скрытые строки.

Зачем использовать SUBTOTAL вместо SUM?

• SUM рискнёт привести к двойному счёту, если в диапазоне уже есть подитоги. SUBTOTAL игнорирует ссылки на другие подитоги в пределах диапазона.
• SUBTOTAL позволяет выбрать не только сумму, но и среднее, количество, стандартное отклонение и другие статистики через числовой код.
• Есть режимы, которые игнорируют скрытые строки, — удобно при использовании фильтров и динамических дашбордов.

Важно: SUBTOTAL не включает строки, скрытые фильтром, независимо от кода. Коды 100+ используются только для игнорирования скрытых строк, скрытых вручную (через скрытие строк), тогда как фильтрованные строки всегда исключаются.

Синтаксис

=SUBTOTAL(code, range1, …)

Параметры:

• code: числовой код, определяющий, какую функцию выполнить (список ниже).
• range1: первый диапазон ячеек для вычисления подитога. Можно указывать дополнительные диапазоны.

Примеры:

=SUBTOTAL(9, B2:B4)      // сумма в диапазоне B2:B4
=SUBTOTAL(101, A2:A50)   // среднее по A2:A50, игнорируя скрытые строки (код 101 = AVERAGE, скрытые строки игнорируются)

Коды функций для аргумента code

• 1 — AVERAGE (среднее)
• 2 — COUNT (количество чисел)
• 3 — COUNTA (количество непустых)
• 4 — MAX (максимум)
• 5 — MIN (минимум)
• 6 — PRODUCT (произведение)
• 7 — STDEV (оценка стандартного отклонения на выборке)
• 8 — STDEVP (стандартное отклонение по всей популяции)
• 9 — SUM (сумма)
• 10 — VAR (оценка дисперсии на выборке)
• 11 — VARP (дисперсия по популяции)

Если вы добавите 100 к любому из кодов (например, 109), то SUBTOTAL будет игнорировать вручную скрытые строки при вычислении. Учтите, что строки, скрытые фильтром, игнорируются всегда — это поведение неизменно.

Пошаговый пример: подитоги по кварталам

Представим таблицу продаж, где строки с месяцами объединяются в кварталы, и вы подводите итоги по кварталам, а затем общий итог. Используем SUBTOTAL так, чтобы итог не учитывал уже вычисленные квартальные подитоги.

1. На листе есть значения продаж в ячейках B2:B4 — это Q1.
2. В ячейку B5 нужно поместить подитог Q1.
3. Перейдите в ячейку B5 и введите формулу:

=SUBTOTAL(9, B2:B4)

4. Повторите шаги для Q2, Q3 и Q4, подставляя соответствующие диапазоны.
5. Для общей суммы используйте SUBTOTAL по всему диапазону, включающему отдельные месяцы, но тогда SUBTOTAL проигнорирует другие формулы SUBTOTAL внутри этого диапазона (это предотвращает двойной счёт):

=SUBTOTAL(9, B2:B17)

На картинке показан ввод формулы в ячейку B5. SUBTOTAL в ячейках итогов кварталов позволяет итоговой строке работать корректно, даже если в том же диапазоне присутствуют другие SUBTOTAL.

На изображении видно итог в B18, C18 и D18, где итоговые формулы игнорируют значения, полученные через SUBTOTAL в промежуточных строках.

Частые ошибки и когда SUBTOTAL даёт неправильный результат

• Использование SUM для итоговой строки поверх диапазона, в котором уже есть подитоги — приведёт к двойному счёту.
• Ожидание, что код 100+ будет менять поведение относительно фильтра — фильтрованные строки всегда исключены; 100+ влияет на вручную скрытые строки.
• Смешивание абсолютных ссылок и динамических диапазонов без понимания: при копировании формул диапазоны могут смещаться и включать подитоги, которых не должно быть.
• Подитоги, встроенные через другие формулы (например, в скриптах или внешних ссылках), всё равно будут игнорироваться только если они исполнены через SUBTOTAL; обычные формулы SUM будут видны и включены.

Важно: если часть ячеек содержит текстовые значения или ошибки (#DIV/0!, #N/A), они могут влиять на COUNTA/COUNT и другие вычисления. Для сумм используйте числовые диапазоны или функцие-предварительную очистку данных (VALUE, IFERROR).

Альтернативы и когда использовать другие подходы

• Если требуется простая необязательная сумма без игнорирования подитогов, достаточно SUM.
• Для сводных таблиц используйте встроенные средства сводных таблиц Google Sheets — они дают гибкие подытоги и группировки.
• Если нужны сложные условия (например, сумма только по определённому статусу и игнорирование подитогов), применяйте комбинацию FILTER + SUM или SUMIFS, но учитывайте, что эти функции не автоматически игнорируют подитоги в диапазоне.
• Для больших таблиц и автоматизации можно применять Apps Script, который вычисляет подитоги по заданной логике и записывает результаты в отдельные столбцы.

Как правильно выбирать код: простая схема принятия решения

flowchart TD
  A[Нужен итог по диапазону?] --> B{Это среднее, сумма или другое?}
  B -->|Сумма| C[Используй 9]
  B -->|Среднее| D[Используй 1]
  B -->|Кол-во чисел| E[Используй 2]
  B -->|Кол-во непустых| F[Используй 3]
  C --> G{Нужно игнорировать вручную скрытые строки?}
  D --> G
  E --> G
  F --> G
  G -->|Да| H[Добавь 100: 109,101,102,103]
  G -->|Нет| I[Оставь код: 9,1,2,3]
  H --> J[Вставь в формулу]
  I --> J

Эта простая блок-схема поможет выбрать код и понять, нужно ли добавлять 100 к значению.

Практические советы и подсказки (cheat sheet)

• Чаще всего используйте 9 (SUM). Для игнорирования скрытых строк — 109.
• SUBTOTAL игнорирует другие SUBTOTAL в пределах диапазона — это ключевое отличие от SUM.
• При работе с фильтрами помните: фильтрованные строки не учитываются всегда.
• Если копируете формулу вниз или вправо, используйте абсолютные ссылки ($) там, где диапазон должен оставаться фиксированным.
• Для динамических диапазонов применяйте именованные диапазоны или конструкцию OFFSET/INDEX в сочетании с SUBTOTAL.

Краткий набор формул:

• Сумма квартала: =SUBTOTAL(9, B2:B4)
• Сумма с игнорированием вручную скрытых строк: =SUBTOTAL(109, B2:B17)
• Среднее любого диапазона: =SUBTOTAL(1, C2:C100)

Чек-лист перед публикацией отчёта

Аналитик:

• Проверить, чтобы итоговые формулы были SUBTOTAL, а не SUM.
• Убедиться, что диапазоны не включают уже рассчитанные подитоги (или что они вычислены через SUBTOTAL).
• Проверить поведение при скрытии строк вручную и при применении фильтров.

Бухгалтер:

• Пересчитать итог с выборочным скрытием строк (проверить режимы 100+).
• Сверить итог с первичными суммами (без подитогов) в отдельной копии листа.

Менеджер/владелец отчёта:

• Убедиться, что дашборд обновляется корректно при изменении фильтров и списков валидации.

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

• Итоговая строка отражает сумму исходных месяцев и не содержит двойного счёта.
• Фильтры не должны включать значения в итог по умолчанию.
• При ручном скрытии строк итог должен вести себя как ожидается (в зависимости от кода 100+).
• Все промежуточные подитоги — формулы SUBTOTAL.

Примеры расширенного использования

1. Динамический дашборд с выпадающим списком: настройте валидацию данных (Data validation) по списку, а в результате используйте INDIRECT или SWITCH вместе с SUBTOTAL, чтобы подставлять диапазоны для отображения.

2. Сочетание с FILTER: если требуется подсчитать сумму по условию и при этом избежать подитогов, сначала фильтруйте диапазон, затем применяйте SUBTOTAL к результату.

3. Работа с массивами и ссылками на другие листы: SUBTOTAL(9, Sheet2!B2:B50) — корректно считает подитог, игнорируя SUBTOTAL в целевом диапазоне.

Примеры тест-кейсов (Критерии приёма)

• Тест 1: В диапазоне есть 12 месяцев и 4 подитога кварталов. Подитог общей суммы = сумма 12 месяцев, без учета квартальных подитогов.
• Тест 2: Вручную скрыты некоторые строки — при использовании 109 итог должен исключать скрытые строки.
• Тест 3: Применён фильтр по региону — итог автоматически пересчитывается и не считает скрытые фильтром строки.

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

• “SUBTOTAL — это SUM с сознанием контекста”: функция понимает, что в диапазоне уже есть подитоги и не повторяет их.
• “Код +100 = игнорирование вручную скрытых строк”: запомните, что 1→101, 9→109 и т.д.
• “Фильтр всегда побеждает”: фильтрованные строки исключаются независимо от кода.

Часто задаваемые вопросы

Как SUBTOTAL ведёт себя при скрытых строках и при фильтре?

SUBTOTAL всегда исключает фильтрованные строки. Если строки скрыты вручную (через контекстное меню), то поведение зависит от кода: для 1–11 скрытые строки учитываются, для 101–111 — не учитываются.

Можно ли заменить все SUM на SUBTOTAL сразу?

Да, но проверьте логику: если вы замените простые суммы, которые не должны игнорировать скрытые строки, возможно, понадобится корректировка кода (добавление 100 или нет).

SUBTOTAL работает с массивными формулами и сводными таблицами?

SUBTOTAL корректно считает диапазоны с массивными формулами и ссылается на другие листы; сводные таблицы имеют свои собственные подитоги и не всегда требуют SUBTOTAL внутри ячеек.

Заключение

SUBTOTAL — обязательный инструмент для тех, кто строит рабочие таблицы и дашборды. Он предотвращает двойной счёт, позволяет выбирать разные статистики и корректно работает с подитогами внутри таблиц. Освойте базовый набор кодов (особенно 9 и 109) и используйте чек-листы и тест-кейсы из этого руководства, чтобы ваши отчёты были надёжными и предсказуемыми.

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

FAQ:

1. Какой код использовать для суммы, если хочу игнорировать скрытые строки? 109.
2. SUBTOTAL исключает другие SUBTOTAL в диапазоне? Да, всегда игнорирует подитоги, вычисленные через SUBTOTAL.
3. Фильтрованные строки учитываются? Нет, они исключаются всегда.