Как сделать промежуточный итог в 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

Аналогичным образом вы можете использовать функцию СУММ Excel, чтобы найти совокупную сумму для вашего банковского баланса. Для этого введите депозиты как положительные числа, а снятие средств как отрицательные числа в каком-либо столбце (столбец C в этом примере). А затем, чтобы показать промежуточный итог, введите следующую формулу в столбце D:

=СУММ($B$C:C2)
Расчет промежуточной суммы для баланса банка

Строго говоря, на приведенном выше снимке экрана показана не совсем кумулятивная сумма, подразумевающая суммирование, а своего рода «нарастающая сумма и нарастающая разница». В любом случае, кого волнует правильное слово, если вы получили желаемый результат, верно? 🙂

На первый взгляд наша формула Excel Cumulative Sum выглядит идеально, но у нее есть один существенный недостаток. Когда вы копируете формулу вниз по столбцу, вы заметите, что совокупные итоги в строках ниже последней ячейки со значением в столбце C показывают одно и то же число:
Совокупные итоги в строках ниже последней ячейки со значением в зависимом столбце показывают одно и то же число.

Чтобы исправить это, мы можем немного улучшить нашу формулу промежуточного итога, встроив ее в функцию ЕСЛИ:

=ЕСЛИ(C2=»»,»»,СУММ($C$2:C2))

Формула предписывает Excel сделать следующее: если ячейка C2 пуста, вернуть пустую строку (пустую ячейку), в противном случае применить формулу кумулятивного итога.

Теперь вы можете скопировать формулу в любое количество ячеек, и ячейки формулы будут выглядеть пустыми, пока вы не введете число в соответствующую строку в столбце C. Как только вы это сделаете, вычисленная совокупная сумма появится рядом с каждая сумма:
Улучшенная формула кумулятивной суммы Excel

Как сделать кумулятивный график в Excel

Как только вы вычислите промежуточный итог с помощью формулы Sum, создание сводной диаграммы в Excel займет несколько минут.

  1. Выберите свои данные, включая столбец «Накопленная сумма», и создайте двухмерную столбчатую диаграмму с кластерами, нажав соответствующую кнопку на Вставлять вкладка, в Графики группа:
    Создайте двухмерную гистограмму с группировкой.
  2. На только что созданной диаграмме нажмите кнопку Совокупная сумма ряд данных (оранжевые столбцы в этом примере) и щелкните правой кнопкой мыши, чтобы выбрать Изменить тип диаграммы ряда… в контекстном меню.
    Выберите Изменить тип диаграммы серии... в контекстном меню.
  3. Если вы используете последнюю версию Excel 2013 или Excel 2016, выберите тип комбинированной диаграммы и щелкните первый значок («Сгруппированный столбец — строка») в верхней части окна. Изменить тип диаграммы диалог:
    В Excel 2013 или Excel 2016 выберите тип комбинированной диаграммы.

    Или можно выделить Пользовательская комбинация значок и выберите тип строки, который вы хотите для серии данных Cumulative Sum (Линия с маркерами в этом примере):

    Выберите тип строки, который вы хотите для серии данных Cumulative Sum.

    В Excel 2010 и более ранних версиях просто выберите нужный тип строки для ряда «Накопленная сумма», который вы выбрали на предыдущем шаге:

    В Excel 2010 и более ранних версиях выберите нужный тип строки для серии «Накопленная сумма».

  4. Нажмите OK и оцените сводную диаграмму Excel:
    Совокупная диаграмма Excel
  5. При желании вы можете щелкнуть правой кнопкой мыши строку «Совокупная сумма» на диаграмме и выбрать «Добавить метки данных» в контекстном меню:
    Добавьте метки данных в строку Cumulative Sum.

В результате ваш совокупный график Excel будет выглядеть примерно так:
Совокупный график Excel с метками данных

Чтобы еще больше украсить сводную диаграмму Excel, вы можете настроить названия диаграммы и осей, изменить легенду диаграммы, выбрать другой стиль и цвет диаграммы и т. д. Подробные инструкции см. в нашем руководстве по диаграммам Excel.

Вот как вы делаете промежуточный итог в Excel. Если вам интересно узнать еще несколько полезных формул, ознакомьтесь с приведенными ниже примерами. Я благодарю вас за чтение и надеюсь увидеть вас снова в ближайшее время!

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *