Главная → Как сделать в Excel → Промежуточные итоги

Промежуточные итоги в Excel

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ и команда «Данные → Промежуточные итоги»: чем они отличаются от СУММ, что значат номера 9 и 109 и почему общий итог перестаёт удваиваться.

Коротко

«Промежуточные итоги» в Excel — это две разные вещи с одним названием, и половина непонимания растёт отсюда.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ — формула, которую вы пишете сами. Синтаксис: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; диапазон). Номер выбирает действие: 9 — сумма, 1 — среднее, 3 — количество непустых, 4 — максимум, 5 — минимум.
Команда «Данные → Промежуточные итоги» — кнопка, которая сама разрезает отсортированный список на группы, вставляет между ними строки «Итог» и сворачивает всё в структуру с цифрами 1, 2, 3 слева от таблицы. Внутрь этих строк она подставляет ту же функцию.

Зачем нужна функция, если есть СУММ. Отличий два, и оба решают дело.

Первое: не считает то, что спрятал фильтр. =СУММ(D2:D100) складывает все сто строк независимо от того, что показано на экране. =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D2:D100) складывает только видимые: включили фильтр по одному менеджеру — и в итоге сумма по нему, сняли фильтр — сумма по всем. Число под таблицей всегда соответствует тому, что человек видит.

Второе: не считает сама себя. Промежуточные итоги внутри диапазона она пропускает — сколько бы их там ни было. Поэтому общий итог, накрывающий всю таблицу вместе со строками групп, не удваивается. Обычная СУММ в такой таблице даёт ровно двойную сумму, и это самая частая причина, по которой в поиск приходят с «неправильным итогом».

Про номера 9 и 109. Обе не считают строки, спрятанные фильтром — это общее свойство функции. Разница только в строках, скрытых вручную (правый клик по номеру строки → «Скрыть»): 9 их всё-таки посчитает, 109 — нет. Если руками вы ничего не прячете, между ними нет никакой разницы, и спорить не о чем.

То же самое формулой в Excel

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D2:D100) — сумма только видимых строк: под фильтром меняется вместе с ним

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B2:B100) — сколько строк осталось после фильтра — считает непустые

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;D2:D100) — среднее по видимым строкам; 4 — максимум, 5 — минимум

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;D2:D100) — то же, что 9, но не считает и строки, скрытые вручную

=СУММЕСЛИ($B$2:$B$100;$F2;$D$2:$D$100) — итог по конкретной группе — не зависит от фильтра и не ломается при его снятии

=АГРЕГАТ(9;3;D2:D100) — сумма видимых строк, но ещё и в обход ошибок в диапазоне; в Google Таблицах АГРЕГАТА нет

Пошагово

Функцией — когда нужен один живой итог под таблицей.

Встаньте в ячейку под столбцом с числами, оставив пустую строку между данными и итогом.
Напишите =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D2:D100), где 9 — сумма, а D2:D100 — только строки данных, без самой ячейки итога.
Включите фильтр (Данные → Фильтр) и выберите любое значение. Число под таблицей пересчитается по видимым строкам — это и есть проверка, что формула написана правильно.

Командой — когда нужны итоги по каждой группе.

Отсортируйте таблицу по тому столбцу, по которому будете группировать: Данные → Сортировка. Без сортировки команда сделает новую группу на каждом переходе значения, и одинаковых «итогов» будет столько же, сколько кусков в списке. Это обязательный шаг, а не рекомендация.
Встаньте в любую ячейку таблицы, Данные → Промежуточный итог.
В окне заполните три поля: «При каждом изменении в» — столбец группировки, тот самый, по которому сортировали; «Операция» — Сумма (или Среднее, Количество); «Добавить итоги по» — галочки на числовых столбцах.
Нажмите ОК. Excel вставит строки «Название итог» после каждой группы и «Общий итог» внизу, а слева появятся цифры 1 2 3: 1 — только общий итог, 2 — итоги групп, 3 — всё вместе со строками.
Убрать всё обратно: та же кнопка Данные → Промежуточный итог → «Убрать всё». Вручную удалять вставленные строки не нужно и не стоит.

Где обычно ломается

Кнопка «Промежуточный итог» бледная и не нажимается. Диапазон оформлен как умная таблица — с ними эта команда не работает. Встаньте в таблицу, Конструктор → Преобразовать в диапазон, и кнопка оживёт. Обратная сторона: у умной таблицы есть своя строка итогов, и она сама подставляет ПРОМЕЖУТОЧНЫЕ.ИТОГИ — часто это и есть то, что нужно.
Итогов получилось слишком много. Значит, таблицу не отсортировали перед вызовом команды. Уберите всё, отсортируйте по столбцу группировки и повторите.
Общий итог вдвое больше правды. Внизу стоит =СУММ(D2:D120), а внутри диапазона уже есть строки групповых итогов, и они складываются вместе с данными. Замените на =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D2:D120) — вложенные промежуточные итоги она пропускает по определению.
Итог не меняется при фильтре. Проверьте, что в ячейке именно ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а не СУММ: автосумма подставляет нужную функцию сама, только если фильтр уже включён в момент нажатия.
Скрытые вручную строки всё равно считаются. Номера 1–11 их учитывают, 101–111 — нет. Фильтра это не касается: отфильтрованное не считает ни одна из версий.
Скопировали видимые строки, а вставилось всё. Копирование берёт и скрытые строки тоже. Выделите диапазон и нажмите Alt+; — выделение сожмётся до видимых ячеек, и копироваться будут только они.
В Google Таблицах функция называется так же и работает так же, включая номера 101–111. А вот команды «Промежуточные итоги» в меню там нет — группы делают сводной таблицей.

Частые вопросы

Как в Excel сделать промежуточные итоги?

Отсортируйте таблицу по столбцу, по которому нужны группы, встаньте в любую её ячейку и выберите Данные → Промежуточный итог. В окне укажите столбец группировки, операцию (Сумма) и столбцы, по которым считать. Excel вставит строки итогов после каждой группы и общий итог внизу. Сортировка обязательна: без неё группы разорвутся.

Для чего нужна функция промежуточные итоги?

Чтобы итог считался по тем строкам, которые человек видит на экране. ПРОМЕЖУТОЧНЫЕ.ИТОГИ не складывает строки, спрятанные фильтром, и не складывает промежуточные итоги внутри своего диапазона. Из-за второго свойства общий итог по таблице с групповыми итогами не удваивается — обычная СУММ в такой таблице даёт двойную сумму.

Как посчитать промежуточные итоги в Excel?

Формулой =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D2:D100), где 9 — сумма, а диапазон — только строки данных, без самой ячейки итога. Другие номера: 1 — среднее, 3 — количество непустых, 4 — максимум, 5 — минимум. Номера со 101 по 111 делают то же самое, но дополнительно пропускают строки, скрытые вручную.

Как в Excel подвести итоги?

Три способа под разные задачи. Один живой итог под таблицей — =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D2:D100). Итоги по группам с сворачиванием — Данные → Промежуточный итог после сортировки. Итог по конкретному условию, не зависящий от фильтра, — =СУММЕСЛИ или =СУММЕСЛИМН. Если таблица оформлена через Ctrl+T, у неё есть готовая строка итогов: Конструктор → Строка итогов.

Как в Excel сделать промежуточную сумму?

Если имеется в виду сумма видимых строк — =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D2:D100). Если нарастающий итог по столбцу, где каждая строка складывает всё с начала, — это другая формула: =СУММ($D$2:D2), протянутая вниз. У неё закреплено только начало диапазона, поэтому при протягивании он растёт: $D$2:D2, потом $D$2:D3 и так далее.

Связанные страницы

Почему Excel не считает формулу