Excel6 мин·
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) — итоги, которые игнорируют скрытые строки
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ: сумма, среднее, количество с учётом фильтра. Чем отличается от СУММ и когда использовать.
Проблема СУММ при фильтрации
Вы отфильтровали таблицу — показываются только заказы из Москвы. Но СУММ внизу всё ещё считает ВСЕ строки, включая скрытые. Итого не соответствует тому, что видно на экране. ПРОМЕЖУТОЧНЫЕ.ИТОГИ решает эту проблему.
Синтаксис
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; диапазон)
Номера функций:
- 1 (101) — СРЗНАЧ
- 2 (102) — СЧЁТ
- 3 (103) — СЧЁТЗ
- 4 (104) — МАКС
- 5 (105) — МИН
- 9 (109) — СУММ
- 11 (111) — СТАНДОТКЛОН
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;C2:C1000)
💡 Совет: Номера 1-11 игнорируют строки, скрытые фильтром. Номера 101-111 игнорируют ВСЕ скрытые строки (и вручную скрытые тоже).
Сумма видимых строк
Отфильтровали по Москве — ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;...) покажет сумму только по видимым строкам:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;C2:C1000)
Количество видимых строк
Сколько строк показывает фильтр:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;A2:A1000)
Вставка промежуточных итогов автоматически
Данные → Промежуточные итоги:
1. Отсортируйте данные по группирующему столбцу (город, категория)
2. Данные → Промежуточные итоги
3. Группировать по: Город
4. Операция: Сумма
5. Добавить итоги по: Сумма заказа
6. ОК
Excel вставит строки с итогами после каждого города и общий итог внизу. Всё через ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Не считает другие ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Бонус: если в диапазоне есть другие ячейки с ПРОМЕЖУТОЧНЫЕ.ИТОГИ — они не учитываются. Не будет двойного счёта при вложенных итогах.
В Google Sheets
SUBTOTAL работает аналогично:
=SUBTOTAL(109, C2:C1000)
Но в Google Sheets фильтрация через формулы (FILTER/QUERY) предпочтительнее.
Заключение
Замените СУММ на ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;...) внизу любой таблицы с фильтрами — итоги будут всегда корректными. Это одна из тех функций, которые стоит использовать по умолчанию. Fast Formula подберёт правильный номер функции для вашей задачи.
🤖
Не хотите запоминать формулы?
Опишите задачу на русском — AI сгенерирует формулу с правильными ссылками на вашу таблицу. 5 запросов бесплатно.
Попробовать бесплатно →Читайте также
ТОП-20 формул Excel, которые должен знать каждый бухгалтер
Подборка самых нужных формул Excel для бухгалтерии: СУММЕСЛИ, ВПР, ЕСЛИ, СЧЁТЕСЛИ и другие. С примерами и объяснениями на русском.
Как сделать ВПР (VLOOKUP) в Google Таблицах — полное руководство
Подробное руководство по VLOOKUP в Google Sheets: синтаксис, примеры, ошибки, альтернативы (INDEX/MATCH, XLOOKUP). С картинками и объяснениями.