Как сделать промежуточный итог в Excel (формула совокупной суммы)
В этом кратком руководстве показано, как обычная формула суммы Excel с разумным использованием абсолютных и относительных ссылок на ячейки может быстро вычислить промежуточный итог на вашем листе.
Промежуточный итог или кумулятивная сумма — это последовательность частичных сумм заданного набора данных. Он используется для отображения суммирования данных по мере их роста со временем (обновляется каждый раз, когда в последовательность добавляется новое число).
Этот метод очень распространен в повседневном использовании, например, для расчета текущего счета в играх, отображения продаж за год или месяц до даты или для расчета вашего банковского баланса после каждого снятия и депозита. В следующих примерах показан самый быстрый способ расчета промежуточного итога в Excel и построения кумулятивного графика.
Как рассчитать промежуточный итог (нарастающую сумму) в Excel
Чтобы вычислить промежуточный итог в Excel, вы можете использовать функцию СУММ в сочетании с разумным использованием абсолютных и относительных ссылок на ячейки.
Например, чтобы вычислить совокупную сумму чисел в столбце B, начиная с ячейки B2, введите следующую формулу в ячейку C2, а затем скопируйте ее в другие ячейки:
=СУММ($B$2:B2)
В формуле промежуточного итога первой ссылкой всегда должна быть абсолютная ссылка со знаком $ ($B$2). Поскольку абсолютная ссылка никогда не меняется, независимо от того, куда перемещается формула, она всегда будет ссылаться на B2. Вторая ссылка без знака $ (B2) является относительной и корректируется в зависимости от относительного положения ячейки, в которую копируется формула. Дополнительные сведения о ссылках на ячейки Excel см. в статье Зачем использовать знак доллара ($) в формулах Excel.
Итак, когда наша формула суммы копируется в ячейку B3, она становится SUM($B$2:B3) и возвращает сумму значений в ячейках от B2 до B3. В ячейке B4 формула превращается в СУММ($B$2:B$) и суммирует числа в ячейках от B2 до B4 и так далее:
Аналогичным образом вы можете использовать функцию СУММ Excel, чтобы найти совокупную сумму для вашего банковского баланса. Для этого введите депозиты как положительные числа, а снятие средств как отрицательные числа в каком-либо столбце (столбец C в этом примере). А затем, чтобы показать промежуточный итог, введите следующую формулу в столбце D:
=СУММ($B$C:C2)
Строго говоря, на приведенном выше снимке экрана показана не совсем кумулятивная сумма, подразумевающая суммирование, а своего рода «нарастающая сумма и нарастающая разница». В любом случае, кого волнует правильное слово, если вы получили желаемый результат, верно? 🙂
На первый взгляд наша формула Excel Cumulative Sum выглядит идеально, но у нее есть один существенный недостаток. Когда вы копируете формулу вниз по столбцу, вы заметите, что совокупные итоги в строках ниже последней ячейки со значением в столбце C показывают одно и то же число:
Чтобы исправить это, мы можем немного улучшить нашу формулу промежуточного итога, встроив ее в функцию ЕСЛИ:
=ЕСЛИ(C2=””,””,СУММ($C$2:C2))
Формула предписывает Excel сделать следующее: если ячейка C2 пуста, вернуть пустую строку (пустую ячейку), в противном случае применить формулу кумулятивного итога.
Теперь вы можете скопировать формулу в любое количество ячеек, и ячейки формулы будут выглядеть пустыми, пока вы не введете число в соответствующую строку в столбце C. Как только вы это сделаете, вычисленная совокупная сумма появится рядом с каждая сумма:
Как сделать кумулятивный график в Excel
Как только вы вычислите промежуточный итог с помощью формулы Sum, создание сводной диаграммы в Excel займет несколько минут.
- Выберите свои данные, включая столбец «Накопленная сумма», и создайте двухмерную столбчатую диаграмму с кластерами, нажав соответствующую кнопку на Вставлять вкладка, в Графики группа:
- На только что созданной диаграмме нажмите кнопку Совокупная сумма ряд данных (оранжевые столбцы в этом примере) и щелкните правой кнопкой мыши, чтобы выбрать Изменить тип диаграммы ряда… в контекстном меню.
- Если вы используете последнюю версию Excel 2013 или Excel 2016, выберите тип комбинированной диаграммы и щелкните первый значок («Сгруппированный столбец — строка») в верхней части окна. Изменить тип диаграммы диалог:
Или можно выделить Пользовательская комбинация значок и выберите тип строки, который вы хотите для серии данных Cumulative Sum (Линия с маркерами в этом примере):
В Excel 2010 и более ранних версиях просто выберите нужный тип строки для ряда «Накопленная сумма», который вы выбрали на предыдущем шаге:
- Нажмите OK и оцените сводную диаграмму Excel:
- При желании вы можете щелкнуть правой кнопкой мыши строку «Совокупная сумма» на диаграмме и выбрать «Добавить метки данных» в контекстном меню:
В результате ваш совокупный график Excel будет выглядеть примерно так:
Чтобы еще больше украсить сводную диаграмму Excel, вы можете настроить названия диаграммы и осей, изменить легенду диаграммы, выбрать другой стиль и цвет диаграммы и т. д. Подробные инструкции см. в нашем руководстве по диаграммам Excel.
Вот как вы делаете промежуточный итог в Excel. Если вам интересно узнать еще несколько полезных формул, ознакомьтесь с приведенными ниже примерами. Я благодарю вас за чтение и надеюсь увидеть вас снова в ближайшее время!
