Каждый аналитик сталкивался с проблемой: накладываешь фильтр в Excel, пишешь стандартную формулу =СУММ(), а она упорно считает даже те строки, которые скрыты. Чтобы Excel учитывал только видимые ячейки, нужно использовать специальную функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (в английской версии — SUBTOTAL). Этот инструмент незаменим при работе с динамическими отчетами.
Видеоинструкция
Пошаговая инструкция: как настроить формулу для видимых строк
Шаг 1. Выберите ячейку для результата
Кликните на ячейку под вашей таблицей или на отдельном листе, где должен отображаться итог.
Шаг 2. Введите функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Начните ввод формулы. Синтаксис выглядит следующим образом:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; интервал)Например, для подсчета суммы по столбцу A (с ячейки A2 по A100) введите:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; A2:A100)Где 109 — это код функции СУММ, который игнорирует скрытые строки.
Шаг 3. Примените фильтр
Выделите шапку таблицы, нажмите комбинацию клавиш Ctrl + Shift + L для включения фильтра. Отфильтруйте данные — сумма автоматически пересчитается только по видимым строкам.
Кстати, эта же функция часто применяется для создания динамических списков. Подробнее об этом читайте в статье Автонумерация в Excel при добавлении строк.
Дополнительно: Таблица кодов функций
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнять 11 различных операций. Коды от 1 до 11 учитывают скрытые вручную строки, а коды от 101 до 111 — полностью их игнорируют (что нам и нужно):
101— СРЗНАЧ (среднее значение)102— СЧЁТ (количество чисел)103— СЧЁТЗ (количество заполненных ячеек)109— СУММ (сумма)
Если вам нужно скопировать только отфильтрованные данные, выделите диапазон и нажмите Alt + ; (выделить только видимые ячейки), затем Ctrl + C.
Частые ошибки / Устранение неполадок
Ошибка 1: Формула все равно считает скрытые строки.
Убедитесь, что вы использовали код функции из диапазона 100+ (например, 109 вместо 9). Код 9 игнорирует только строки, скрытые фильтром, но учитывает строки, скрытые вручную (через контекстное меню ‘Скрыть’). Код 109 игнорирует любые скрытые строки.
Ошибка 2: Ошибка #ЗНАЧ! или #ИМЯ?
Проверьте раскладку и написание. В русскоязычной версии Excel пишется ПРОМЕЖУТОЧНЫЕ.ИТОГИ, в англоязычной — SUBTOTAL. Разделителем аргументов должна быть точка с запятой ; (для русской локали) или запятая , (для английской).
После настройки формул и фильтров отчет часто отправляют на печать. Если у вас возникли проблемы с выводом документа, изучите руководство: Почему не работает ПЕЧАТЬ в Excel с ASCII: Решение.
А если вы оформляете отчет графиками или логотипами компании и элементы смещаются, вам поможет статья: Почему картинка вставляется поверх текста: Решение проблемы.
Часто задаваемые вопросы
Какая функция в Excel считает только видимые ячейки?
Для этого используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) с кодами функций от 101 до 111.
В чем разница между кодами 9 и 109 в ПРОМЕЖУТОЧНЫЕ.ИТОГИ?
Код 9 скрывает только строки, отфильтрованные фильтром. Код 109 скрывает и отфильтрованные строки, и строки, скрытые вручную.
