Вычислить промежуточные итоги (кумулятивные суммы) в Excel — 5 простых способов

промежуточный итог (также известный каксовокупная сумма) очень часто используется во многих случаях.Это метрика, которая сообщает вам, какова сумма значений на данный момент.

Например, если у вас есть ежемесячные данные о продажах, промежуточный итог покажет вам, сколько продаж было совершено с первого дня месяца до определенной даты.

Есть и другие ситуации, когда часто используются итоги, например, расчет остатков денежных средств в банковских выписках/бухгалтерских книгах, расчет калорий в планах питания и т. д.

В Microsoft Excel существует несколько различных способов расчета промежуточных сумм.

Выбор метода также зависит от структуры данных.

Например, если у вас есть простые табличные данные, вы можете использовать простую формулу СУММ, но если у вас есть таблица Excel, лучше использовать структурированные ссылки.Вы также можете использовать Power Query для этого.

В этом уроке я расскажу обо всехПодсчет нарастающей суммы в Excelразные методы.

Итак, приступим!

Рассчитать промежуточные итоги, используя табличные данные

Если у вас есть табличные данные (то есть таблицы в Excel, которые не преобразуются в таблицы Excel), вы можете использовать несколько простых формул для расчета промежуточных сумм.

Используйте оператор сложения

Предположим, у вас есть данные о продажах по дате, и вы хотите在 в столбце СрасчетОбщая сумма.

Вот шаги, чтобы сделать это.

шаг 1 – В ячейку C2, первую ячейку, для которой вы хотите запустить итог, введите

= B2

Это просто получает то же значение продаж в ячейке B2.

шаг 2– В ячейку C3 введите следующую формулу:

= С2 + В3

шаг 3– Применить формулу ко всему столбцу.Вы можете использовать маркер заполнения, чтобы выбрать и перетащить его, или просто скопировать и вставить ячейку C3 во все оставшиеся ячейки (это автоматически скорректирует ссылку и даст правильный результат).

Это даст вам результат, показанный ниже.

Это очень простой метод, который хорошо работает в большинстве случаев.

Логика проста — каждая ячейка берет значение из нее (которое представляет собой совокупную сумму за предыдущий день) и добавляет значение из соседней ячейки (которое является значением продаж за текущий день).

Есть только один недостаток — если вы удалите любую существующую строку в этом наборе данных, все ячейки ниже вернут ошибку ссылки (#ССЫЛКА!)

Если в вашем наборе данных есть такая возможность, используйте следующий метод, используя формулу СУММ.

Используйте SUM с частично заблокированными ссылками на ячейки

Предположим, у вас есть данные о продажах по дате, и вы хотите рассчитать промежуточный итог в столбце C.

Ниже приведена формула СУММ, которая даст вам промежуточный итог.

= СУММ ($ B $ 2: B2)

Позвольте мне объяснить, как работает эта формула.

В приведенной выше формуле СУММ я использовал ссылку для сложения как $B$2:B2.

• $B$2 — это абсолютная ссылка, что означает, что эта ссылка не изменится, когда я скопирую ту же формулу в ячейку ниже.Поэтому, когда вы скопируете формулу в ячейку ниже, формула изменится на СУММ($B$2:B3)
• B2 — это вторая часть ссылки, это относительная ссылка, что означает, что когда я копирую формулу вниз или вправо, это корректируется.Поэтому при копировании формулы в ячейку ниже значение становится B3

Преимущество этого подхода заключается в том, что если вы удалите какие-либо строки в наборе данных, эта формула подстроится и по-прежнему будет давать правильный промежуточный итог.

Рассчитать промежуточный итог в листе Excel

При работе с табличными данными в Excel лучше всего преобразовать их в таблицу Excel.Это упрощает управление данными, а также упрощает использование таких инструментов, как Power Query и Power Pivot.

Использование таблицы Excel дает несколько преимуществ, таких как структурированные ссылки (которые позволяют очень легко ссылаться на данные в таблице и использовать их в формулах) и автоматическая корректировка ссылок при добавлении или удалении данных из таблицы.

Хотя вы все еще можете использовать приведенную выше формулу, которую я показал вам на листе Excel, позвольте мне показать вам несколько лучших способов сделать это.

Предположим, у вас есть таблица Excel, подобная приведенной ниже, и вы хотите рассчитать промежуточный итог в столбце C.

Вот формула для этого:

=СУММ(Данные о продажах[[#Заголовки],[Продажа]]:[@Продажа])

Приведенная выше формула может показаться немного длинной, но вам не обязательно писать ее самостоятельно.То, что вы видите в формуле суммирования, называется структурированной ссылкой, и это эффективный способ для Excel ссылаться на определенные точки данных в таблице Excel.

Например, SalesData[[#Headers],[Sale]] относится к заголовку Sales в таблице SalesData (SalesData — это имя таблицы Excel, которое я дал при создании таблицы)

Принимая во внимание, что [@Sale] относится к значению в ячейке в той же строке столбца «Продажа».

Я просто объясняю это здесь для вашего понимания, но даже если вы ничего не знаете о структурированных ссылках, вы все равно можете легко создать эту формулу.

Вот шаги, чтобы сделать это:

1. В ячейке C2 введите =СУММ(
2. Выберите ячейку B1, которая является заголовком столбца со стоимостью продажи.Вы можете использовать мышь или использовать клавиши со стрелками.Вы заметите, что Excel автоматически вводит структурированную ссылку на ячейку.
3. Добавить: (двоеточие)
4. Выберите ячейку B2.Excel снова автоматически вставит структурированные ссылки на ячейки
5. Закройте скобку и нажмите Enter

Вы также заметите, что вам не нужно копировать формулу во весь столбец, лист Excel сделает это за вас автоматически.

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

Хотя мы включили заголовки столбцов в формулу, имейте в виду, что формула игнорирует текст заголовка и учитывает только данные в столбце.

Рассчитать промежуточные итоги с помощью Power Query

Power Query — отличный инструмент для подключения к базам данных, извлечения данных из нескольких источников и преобразования их перед помещением в Excel.

Если вы уже используете Power Query, эффективнее добавлять промежуточные итоги при преобразовании данных в самом редакторе Power Query (вместо того, чтобы сначала получать данные в Excel, а затем добавлять промежуточные итоги любым из описанных выше способов).

Хотя в Power Query нет встроенных функций для добавления промежуточных сумм (хотелось бы, чтобы они были), вы все равно можете сделать это с помощью простой формулы.

Предположим, у вас есть таблица Excel, которая выглядит так, и вы хотите добавить промежуточные итоги к этим данным:

Вот шаги, чтобы сделать это:

1. Выберите любую ячейку в таблице Excel
2. нажмите данные
3. На вкладке «Получить и преобразовать» щелкните значок «Из таблицы/диапазона».Это откроет таблицу в редакторе Power Query.
4. [Необязательно] Если ваш столбец дат еще не отсортирован, щелкните значок фильтра в столбце дат, затем нажмите «Сортировать по возрастанию».
5. Перейдите на вкладку «Добавить столбец» в редакторе Power Query.
6. В группе «Общие» щелкните раскрывающееся меню «Столбец индекса» (не щелкайте значок «Столбец индекса», а нажимайте маленькую черную наклонную стрелку рядом с ним, чтобы открыть дополнительные параметры).
7. Нажмите «От 1».Это добавит новый индексный столбец, который будет начинаться с 1 и вводить числа, увеличивающиеся на 1 по всему столбцу.
8. Щелкните значок «Пользовательские столбцы» (также на вкладке «Добавить столбцы»).
9. В открывшемся диалоговом окне «Пользовательские столбцы» введите имя нового столбца.В этом примере я буду использовать имя «Нарастающая сумма».
10. В поле Формула пользовательского столбца введите следующую формулу:Список.Сумма(Список.Диапазон(#"Добавленный индекс"[Продажа],0,[Индекс]))
11. Убедитесь, что в нижней части диалогового окна установлен флажок «Синтаксические ошибки не обнаружены».
12. Щелкните ОК.Это добавит новый столбец промежуточных сумм.
13. удалить столбец индекса
14. Перейдите на вкладку «Файл», затем нажмите «Закрыть и загрузить».

Вышеуказанные шаги вставят в вашу книгу новый рабочий лист с таблицей с промежуточными итогами.

Теперь, если вы думаете, что это слишком много шагов по сравнению с предыдущим подходом с простыми формулами, вы правы.

Если у вас уже есть набор данных и все, что вам нужно сделать, это добавить промежуточные итоги, лучше не использовать Power Query.

Использование Power Query имеет смысл в ситуациях, когда вам нужно извлечь данные из базы данных или объединить данные из нескольких разных книг, а также добавить к ним промежуточные итоги в процессе.

Кроме того, как только вы автоматизируете это с помощью Power Query, в следующий раз, когда ваш набор данных изменится, вам не нужно делать это снова, вы можете просто обновить запрос, и он даст вам результаты на основе нового набора данных.

Как это работает?

Теперь позвольте мне быстро объяснить, что происходит с этим подходом.

Первое, что мы делаем в редакторе Power Query, — вставляем столбец индекса, который начинается с 1 и увеличивается по мере продвижения вниз по ячейке.

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

Затем мы вставляем пользовательский столбец и используем следующую формулу

Список.Сумма(Список.Диапазон(#"Добавленный индекс"[Продажа],0,[Индекс]))

Это формула List.Sum, которая даст вам сумму указанного в ней диапазона.

Диапазон указывается с помощью функции List.Range.

Функция List.Range принимает в качестве выходных данных указанный диапазон в столбце продажи, и диапазон варьируется в зависимости от значения индекса.Например, для первой записи диапазон — это первая стоимость продажи.Этот диапазон расширяется по мере продвижения вниз по ячейке.

Итак, для первой ячейки.List.Sum даст вам только сумму первой стоимости продажи, для второй ячейки она даст вам сумму первых двух значений продажи и так далее.

Хотя этот подход работает хорошо, он может стать очень медленным для больших наборов данных (тысячи строк).

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

До сих пор мы видели примеры расчета промежуточной суммы всех значений в столбце.

Но в некоторых случаях может потребоваться рассчитать промежуточный итог для конкретной записи.

Например, ниже у меня есть набор данных, в котором я хочу подсчитать общее количество принтеров и сканеров в двух разных столбцах.

Это можно сделать с помощью формулы СУММЕСЛИ, которая вычисляет промежуточный итог, обеспечивая при этом соблюдение заданных условий.

Вот формула для столбца принтера:

=SUMIF($C$2:C2,$D$1,$B$2:B2)

Аналогичным образом, чтобы рассчитать промежуточную сумму сканера, используйте следующую формулу:

=SUMIF($C$2:C2,$E$1,$B$2:B2)

В приведенной выше формуле я использовал СУММЕСЛИ, который даст мне сумму в диапазоне при соблюдении определенных условий.

Формула принимает три параметра:

1. ассортимент: это диапазон условий, которые будут проверяться на соответствие указанному условию.
2. Критерии: это критерий, который будет проверен только в том случае, если этот критерий выполнен, тогда будет добавлено значение в третьем параметре, диапазон суммы
3. [сумма_диапазон]: это диапазон суммы значений, добавляемых при выполнении условия

Кроме того, вассортиментИдиапазон_суммыпараметр, я заблокировал вторую часть ссылки, чтобы по мере продвижения вниз по ячейке диапазон продолжал расширяться.Это позволяет нам учитывать и добавлять значения только до этого диапазона (отсюда промежуточный итог).

В этой формуле я использую столбцы заголовков (принтеры и сканеры) в качестве критериев.Вы также можете жестко закодировать стандарт, если заголовки столбцов не совпадают со стандартным текстом.

Если вам нужно проверить несколько условий, вы можете использовать формулу СУММЕСЛИМН.

Промежуточные итоги в сводной таблице

Если вы хотите добавить промежуточный итог к результатам сводной таблицы, вы можете легко сделать это, используя встроенные функции сводных таблиц.

Предположим, у вас есть сводная таблица, подобная приведенной ниже, с датами в одном столбце и значениями продаж в другом.

Вот шаги, чтобы добавить дополнительный столбец, который будет показывать совокупные продажи по дате:

1. Перетащите поле «Продажа» в область «Значение».
2. Это добавит еще один столбец со значением продаж.
3. Щелкните параметр «Общий объем продаж 2» в области «Значение».
4. Нажмите на параметр «Настройки поля значения».
5. В диалоговом окне «Параметры поля значения» измените «Пользовательское имя» на «Промежуточный итог».
6. Перейдите на вкладку «Отображать значение как».
7. В раскрывающемся меню «Отображать значение как» выберите параметр «Нарастающая сумма в».
8. В параметрах основных полей убедитесь, что выбрана дата.
9. Нажмите ОК

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

Итак, вот некоторые из методов, которые вы можете использовать для расчета промежуточных сумм в Excel.Если у вас есть табличные данные, вы можете использовать простые формулы, а если у вас есть таблицы Excel, вы можете использовать формулы, использующие структурированные ссылки.

Я также рассказал, как рассчитать промежуточные итоги с помощью Power Query и сводных таблиц.

Я надеюсь, что вы нашли этот урок полезным.