Автоматизация расчетов в таблицах без сводных таблиц

Иван Корнев·11.04.2026·⏱4 мин

Чтобы посчитать сумму или среднее значение только по видимым строкам после применения фильтра в Excel, используйте функцию =SUBTOTAL(109; диапазон). Код 109 гарантирует, что скрытые фильтром строки не будут учтены в расчете, в отличие от обычной функции СУММ. Для сложной выборки данных без изменения отображения таблицы применяйте SUMIFS (сумма по условиям) или динамическую функцию FILTER (в новых версиях Excel). Эти инструменты позволяют создавать гибкие отчеты, которые обновляются автоматически при изменении исходных данных.

Почему стандартная сумма не работает с фильтрами

Обычная функция СУММ (SUM) складывает все значения в указанном диапазоне, независимо от того, скрыты строки фильтром или нет. Это создает ошибку в отчетах: вы видите на экране 10 строк, а формула считает данные по 1000 строк.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) создана специально для работы с отфильтрованными списками. Она умеет «видеть» состояние строки: если она скрыта автфильтром, функция её игнорирует.

Ключевое различие кодов функций:

Коды 1–11: Игнорируют только строки, скрытые вручную (правая кнопка мыши → Скрыть), но считают строки, скрытые фильтром.
Коды 101–111: Игнорируют все скрытые строки (и вручную, и фильтром).
Рекомендация: Всегда используйте код 109 для суммы, чтобы результат был корректным при любой фильтрации.

Функция SUBTOTAL: настройка динамических итогов

Эта функция — основа интерактивных дашбордов. Она пересчитывается мгновенно при изменении параметров фильтра.

Синтаксис и основные коды

Формула выглядит так: =SUBTOTAL(код_функции; диапазон)

Наиболее востребованные коды для анализа:

Код	Функция	Описание
109	SUM	Сумма видимых ячеек
101	AVERAGE	Среднее значение видимых ячеек
103	COUNTA	Подсчет непустых видимых ячеек (текст/числа)
102	COUNT	Подсчет видимых ячеек с числами

Практический пример

Допустим, у вас есть таблица продаж (столбец B — суммы). Вы отфильтровали регион «Москва».

В ячейке под таблицей введите: =SUBTOTAL(109; B2:B1000).
Результат покажет сумму продаж только по Москве.
Если вы снимете фильтр или выберете другой город, число изменится автоматически.

Не выделяйте всю колонку (например, B:B) в больших файлах. Это замедляет работу книги. Указывайте конкретный диапазон с запасом, например B2:B5000.

Условное суммирование: SUMIF и SUMIFS

Если вам нужно получить итог по определенному критерию, не скрывая остальные строки, используйте функции семейства СУММЕСЛИ. Они работают независимо от визуальных фильтров экрана.

SUMIF (Одно условие)

Суммирует значения, если они соответствуют одному правилу.

Синтаксис: =SUMIF(диапазон_проверки; условие; диапазон_суммы)
Пример: Посчитать продажи менеджера «Иванов»: =SUMIF(A2:A100; "Иванов"; C2:C100)

SUMIFS (Несколько условий)

Позволяет задать множество критериев (до 127). Порядок аргументов здесь отличается: сначала указывается диапазон для суммирования.

Синтаксис: =SUMIFS(диапазон_суммы; диапазон_1; условие_1; диапазон_2; условие_2)
Пример: Продажи «Иванова» за январь свыше 10 000 руб.: =SUMIFS(C2:C100; A2:A100; "Иванов"; B2:B100; "Январь"; C2:C100; ">10000")

Частая ошибка: Использование текстовых значений в условиях без кавычек. Операторы сравнения (>, <, <>) обязательно должны быть в двойных кавычках вместе с числом: ">10000". Просто число 10000 кавычек не требует.

Продвинутые инструменты: AGGREGATE и динамический массив FILTER

Для сложных задач, где SUBTOTAL недостаточно, существуют более мощные инструменты.

AGGREGATE (АГРЕГАТ)

Функция появилась в Excel 2010 и объединяет возможности SUBTOTAL с игнорированием ошибок.

Зачем нужна: Если в столбце есть ошибки #ДЕЛ/0! или #Н/Д, обычная сумма сломается. AGGREGATE пропустит их.
Пример: Сумма видимых ячеек с игнорированием ошибок и вложенных подытогов: =AGGREGATE(9; 5; B2:B100)
- 9 — функция СУММ.
- 5 — опция игнорировать скрытые строки и ошибки.

FILTER (ФИЛЬТР) — только для Excel 365 / 2021+

Эта функция не просто считает, она выводит список данных, соответствующих условию, в соседние ячейки (динамический массив).

Синтаксис: =FILTER(массив; условие; [если_пусто])
Пример: Выгрузить список всех товаров с продажами больше 50 000: =FILTER(A2:C100; C2:C100 > 50000; "Нет данных") Результат появится автоматически, занимая столько строк, сколько найдено совпадений.

Сравнение методов обработки данных

Выбор инструмента зависит от вашей версии Excel и задачи.

Задача	Лучшая функция	Особенности
Сумма по экрану (с учетом фильтра)	SUBTOTAL (109)	Работает во всех версиях, игнорирует скрытое.
Сумма по условию (без скрытия строк)	SUMIFS	Гибкость условий, не зависит от вида таблицы.
Сумма с игнорированием ошибок	AGGREGATE	Надежнее SUBTOTAL при «грязных» данных.
Выгрузка списка данных	FILTER	Создает новую таблицу динамически (новые версии).

Типичные ошибки и решения

#ЗНАЧ! в SUBTOTAL: Возникает, если в диапазоне суммирования есть текст, а вы используете код суммы.
- Решение: Проверьте данные или используйте код 103 (COUNTA) для подсчета заполненных ячеек.
Результат не меняется при фильтрации:
- Причина: Использован код 9 вместо 109, либо данные скрыты вручную, а не фильтром.
- Решение: Замените формулу на =SUBTOTAL(109; ...) и убедитесь, что используется инструмент «Фильтр» (Ctrl+Shift+L).
Неверный разделитель:
- В русской локализации Excel аргументы разделяются точкой с запятой (;), а не запятой. Формула SUMIF(A1:A10, "Test", B1:B10) выдаст ошибку, правильная: СУММЕСЛИ(A1:A10; "Test"; B1:B10).

FAQ

Можно ли использовать SUBTOTAL внутри другой формулы? Да, но с ограничениями. Например, =IF(SUBTOTAL(109; B2:B10)>1000; "Бонус"; "Нет") сработает корректно. Однако вкладывать SUBTOTAL внутрь массивных формул старого типа не рекомендуется.

Как посчитать количество уникальных значений в отфильтрованном списке? Стандартный SUBTOTAL не умеет считать уникальные значения напрямую. Для этого потребуется комбинация функций или использование сводной таблицы. В новых версиях Excel можно использовать =ROWS(UNIQUE(FILTER(...))).

Почему формула FILTER возвращает ошибку #ПЕРЕНОС? Это значит, что ячейки ниже или правее формулы заняты данными, и функции некуда «разлиться». Освободите место вокруг ячейки с формулой.