Промежуточный итог (также называемый совокупная сумма) довольно часто используется во многих ситуациях. Это показатель, который сообщает вам, какова сумма полученных значений.
Например, если у вас есть данные о продажах за месяц, промежуточный итог покажет вам, сколько продаж было сделано до определённого дня с первого дня месяца. Есть и другие ситуации, где часто используется промежуточная сумма: расчёт остатка денежных средств в банковских выписках / бухгалтерской книге, подсчёт калорий в плане питания и т. д.
В Microsoft Excel есть несколько различных способов расчёта промежуточных итогов. Выбранный метод также будет зависеть от того, как структурированы ваши данные. Например, если у вас простые табличные данные, можно использовать простую формулу СУММ, но если у вас таблица Excel, лучше использовать структурированные ссылки. Можно также использовать Power Query.
Способ 1: Оператор сложения (табличные данные)
Если у вас есть табличные данные (не преобразованные в таблицу Excel), можно использовать несколько простых формул. Предположим, у вас есть данные о продажах по датам, и вы хотите рассчитать промежуточную сумму в столбце C.
В ячейке C2 (первая ячейка промежуточной суммы) введите: = B2 — это просто возьмёт значение продаж из B2
В ячейке C3 введите: = C2 + B3
Примените формулу ко всему столбцу — используйте маркер заполнения или скопируйте C3 во все оставшиеся ячейки (ссылки настроятся автоматически)
Способ 2: СУММ с частично заблокированной ссылкой
Формула для промежуточной суммы в столбце C:
= СУММ($B$2:B2)
Как работает эта формула:
- $B$2 — абсолютная ссылка, при копировании формулы вниз она не изменится
- B2 (вторая часть) — относительная ссылка, при копировании вниз она скорректируется (станет B3, B4 и т. д.)
Способ 3: Структурированные ссылки в таблице Excel
При работе с табличными данными рекомендуется преобразовать их в таблицу Excel. Это упрощает управление данными и облегчает использование Power Query и Power Pivot. Работа с таблицами даёт преимущества: структурированные ссылки и автоматическую корректировку при добавлении/удалении данных.
Формула для промежуточной суммы в столбце C:
= SUM(SalesData[[#Headers],[Sale]]:[@Sale])
Формула может показаться длинной, но её не нужно писать самостоятельно — это структурированные ссылки, эффективный способ Excel ссылаться на определённые точки данных в таблице. Например, SalesData[[#Headers],[Sale]] относится к заголовку Sale в таблице SalesData, а [@Sale] — к значению в ячейке той же строки в столбце Sale.
Шаги для создания этой формулы:
В ячейке C2 введите =СУММ(
Выберите ячейку B1 (заголовок столбца со стоимостью продажи) — Excel автоматически вставит структурированную ссылку
Добавьте : (двоеточие)
Выберите ячейку B2 — снова автоматическая структурированная ссылка
Закройте скобку и нажмите Enter
Способ 4: Power Query
Power Query — отличный инструмент для подключения к базам данных, извлечения данных из нескольких источников и их преобразования перед помещением в Excel. Если вы уже работаете с Power Query, эффективнее добавлять промежуточные итоги прямо в редакторе Power Query.
В Power Query нет встроенной функции для добавления промежуточных итогов, но это можно сделать с помощью формулы.
Шаги:
Выберите любую ячейку в таблице Excel
Нажмите на «Данные»
На вкладке «Получить и преобразовать» щёлкните «Из таблицы/диапазона» — откроется редактор Power Query
[Необязательно] Если столбец «Дата» не отсортирован, щёлкните значок фильтра и выберите «Сортировать по возрастанию»
Щёлкните вкладку «Добавить столбец» в редакторе Power Query
В группе «Общие» щёлкните раскрывающееся меню «Столбец индекса» (маленькая стрелка рядом с иконкой)
Нажмите «От 1» — добавится столбец индекса, начинающийся с единицы и увеличивающийся на 1
Щёлкните «Пользовательский столбец» (тоже на вкладке «Добавить столбец»)
В диалоговом окне введите имя нового столбца, например «Текущий итог»
В поле формулы введите: List.Sum(List.Range(#"Добавленный индекс"[Продажа], 0, [Индекс]))
Убедитесь, что внизу написано «Синтаксических ошибок не обнаружено»
Щёлкните ОК — добавится новый столбец промежуточной суммы
Удалите столбец индекса
Перейдите на вкладку «Файл» → «Закрыть и загрузить»
Эти шаги вставят в книгу новый лист с таблицей с промежуточными итогами.
Как это работает?
Сначала в редакторе Power Query мы вставляем столбец индекса, начиная с единицы и увеличивая его на единицу по мере продвижения вниз по ячейкам — это нужно для расчёта промежуточной суммы в следующем столбце.
Затем мы вставляем настраиваемый столбец с формулой List.Sum(List.Range(#"Добавленный индекс"[Продажа], 0, [Индекс])) — это формула, которая даёт сумму диапазона. Диапазон указывается функцией List.Range, которая выдаёт указанный диапазон в столбце продажи, изменяясь в зависимости от значения индекса. Для первой записи диапазон будет только первой ценой продажи; по мере продвижения вниз диапазон расширяется.
Способ 5: Промежуточная сумма по критериям (СУММЕСЛИ)
До сих пор мы вычисляли промежуточную сумму для всех значений в столбце. Но бывают случаи, когда нужна промежуточная сумма для определённых записей — например, отдельно для принтеров и сканеров в двух разных столбцах.
Формула для столбца «Принтер»:
= СУММЕСЛИ($C$2:C2, $D$1, $B$2:B2)
Формула для столбца «Сканер»:
= СУММЕСЛИ($C$2:C2, $E$1, $B$2:B2)
Формула СУММЕСЛИ принимает три аргумента:
| Аргумент | Описание |
|---|---|
| диапазон | диапазон критериев, который будет проверяться на соответствие |
| критерии | значение, которое проверяется; если оно совпадает — значения из третьего аргумента суммируются |
| [диапазон_суммы] | диапазон, из которого будут добавлены значения при совпадении критерия |
Промежуточная сумма в сводных таблицах
Если нужно добавить промежуточные итоги в результат сводной таблицы, это легко сделать со встроенной функциональностью.
Перетащите поле «Продажа» в область «Значения» второй раз — добавится ещё один столбец со значениями продаж
Нажмите на опцию «Sum of Sale2» в области «Значение»
Нажмите «Параметры поля значений»
В диалоговом окне измените настраиваемое имя на «Промежуточные итоги»
Перейдите на вкладку «Показать значение как»
Выберите «Текущая сумма в»
Убедитесь, что в параметрах базового поля выбрана «Дата»
Нажмите ОК
Эти шаги превратят второй столбец продаж в столбец «Промежуточная сумма».
Итак, это некоторые способы расчёта промежуточной суммы в Excel. Если у вас данные в табличном формате, используйте простые формулы, а если у вас таблица Excel — формулы со структурированными ссылками. Также рассмотрены способы расчёта промежуточной суммы с помощью Power Query и сводных таблиц.
Овладейте всеми формулами Excel
Пройдите полный курс и научитесь эффективно работать с данными