5 простых способов подсчета промежуточной суммы в Excel (совокупная сумма)

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

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

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

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

Выбранный вами метод также будет зависеть от того, как структурированы ваши данные.

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

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

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

Расчет промежуточной суммы с табличными данными

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

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

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

Ниже приведены шаги для этого

Шаг 1 — В ячейке C2, которая является первой ячейкой, в которой вы хотите получить промежуточную сумму, введите
= B2
Это просто получит те же значения продаж в ячейке B2.

Шаг 2 — В ячейке C3 введите следующую формулу:
= C2 + B3

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

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

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

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

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

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

Использование 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.

Ниже приведена формула, которая сделает это:
= SUM (SalesData [[# заголовков], [Sale]]: [@ Sale])

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

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

А [@Sale] относится к значению в ячейке в той же строке в столбце 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, во всем столбце.
8. Щелкните значок «Пользовательский столбец» (который также находится на вкладке «Добавить столбец»).
9. В открывшемся диалоговом окне настраиваемого столбца введите имя нового столбца. в этом примере я буду использовать название «Текущий итог».
10. В поле Формула настраиваемого столбца введите следующую формулу: List.Sum (List.Range (# «Добавленный индекс» [Продажа], 0, [Индекс]))
11. Убедитесь, что в нижней части диалогового окна установлен флажок «Синтаксических ошибок не обнаружено».
12. Щелкните ОК. Это добавит новый столбец промежуточной суммы
13. Удалить столбец индекса
14. Перейдите на вкладку «Файл» и затем нажмите «Закрыть и загрузить».

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

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

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

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

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

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

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

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

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

Затем мы вставляем настраиваемый столбец и используем следующую формулу
List.Sum (List.Range (# "Добавленный индекс" [Продажа], 0, [Индекс]))
Это формула List.Sum, которая даст вам сумму диапазона, указанного в ней.

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

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

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

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

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

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

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

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

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

Ниже приведена формула, которая сделает это для столбцов «Принтер»:
= СУММЕСЛИ ($ C $ 2: C2; $ D $ 1; $ B $ 2: B2)

Аналогичным образом, чтобы рассчитать промежуточную сумму для сканеров, используйте следующую формулу:
= СУММЕСЛИ ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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