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

Обновлено: 21.11.2024

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

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

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

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

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

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

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

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

Вычисление промежуточного итога с помощью табличных данных

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

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

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

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

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

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

Шаг 2. В ячейке C3 введите приведенную ниже формулу:

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

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

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

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

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

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

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

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

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

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

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

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

Вычисление промежуточного итога в таблице Excel

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

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

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

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

Ниже приведена формула, которая это сделает:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ниже приведена формула, которая сделает это для столбцов принтера:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<р>1. Выберите ячейку B9 и введите простую функцию СУММ.

<р>2. Выберите ячейку C2 и введите функцию СУММ, показанную ниже.

Объяснение: первая ячейка (B$2) в ссылке диапазона является смешанной ссылкой. Мы исправили ссылку на строку 2, добавив символ $ перед номером строки. Вторая ячейка (B2) в ссылке диапазона является обычной относительной ссылкой.

<р>3. Выберите ячейку C2, нажмите в правом нижнем углу ячейки C2 и перетащите ее вниз к ячейке C7.

Объяснение: когда мы перетаскиваем формулу вниз, смешанная ссылка (B$2) остается прежней, а относительная ссылка (B2) меняется на B3, B4, B5 и т. д.

<р>4. Например, взгляните на формулу в ячейке C3.

<р>5. Например, взгляните на формулу в ячейке C4.

<р>6. На шаге 2 введите функцию ЕСЛИ, показанную ниже (и перетащите ее вниз в ячейку C7), чтобы отображать кумулятивную сумму только в том случае, если данные были введены.

Объяснение: если ячейка B2 не пуста (<> означает не равно), функция ЕСЛИ в ячейке C2 отображает кумулятивную сумму, в противном случае она отображает пустую строку.

Научитесь создавать кумулятивные суммы в Microsoft Excel с помощью этих пошаговых инструкций и снимков экрана.

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

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

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

Что такое промежуточный итог?

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

Как создать промежуточный итог в Excel

<р>1. Начните с =СУММ. Нажмите на ячейку, с которой вы хотите, чтобы ваша промежуточная сумма начиналась. Затем выберите функцию СУММ в этой ячейке. Но вместо выделения ячеек в круглых скобках (путем перетаскивания курсора по ячейкам, которые вы хотите включить в уравнение), как если бы вы добавляли столбец чисел, вам нужно создать так называемую «абсолютную ссылку», за которой следует «относительная ссылка». Не волнуйтесь; это не так сложно, как кажется. Следующий шаг описывает, как это сделать.

Как можно использовать промежуточный итог?

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

Кассовые операции.

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

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

Таблицы результатов игр.

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

Позиции продаж

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

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

Расчеты с начала года

Одно из самых популярных применений промежуточных итогов — расчеты с начала года. Расчет с начала года (YTD) используется для записи определенной функции или деятельности (обычно финансовой) с определенной даты до конца года.

Например, вы можете увидеть массив расчетов за текущий год в своих платежных квитанциях. Это пример промежуточного итога, потому что он отслеживает различные сделанные платежи и собранные налоги, чтобы дать вам окончательную сумму в конце каждого года. Эти окончательные итоги затем переносятся в формы W-2 и используются для целей налогообложения. Расчеты за текущий год также используются для расчета дохода от аренды, финансового положения бизнеса или доходности акций.

Итоговые запасы

Балансовые отчеты.

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

Банковский баланс

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

Расход бензина

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

Подсчет калорий

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

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

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

В этом посте мы рассмотрим несколько способов расчета промежуточного итога для ваших ежедневных данных. Мы узнаем, как использовать формулы на листах, сводные таблицы, Power Pivot с DAX и Power query.

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

Получите файл со всеми примерами.

Подсчет итогов с помощью простой формулы

Основную формулу промежуточного итога можно создать с помощью оператора +.

Однако для выполнения этой задачи нам потребуется использовать две разные формулы.

=C3 будет первой формулой и будет только в первой строке промежуточной суммы.
=C4+D3 будет во второй строке и может быть скопировано в оставшиеся строки для промежуточного итога.

Что происходит с промежуточным итогом, когда мы вставляем или удаляем строки в наших данных?

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

Подсчет итогов с помощью формулы SUM

Мы можем избежать неудобного использования двух разных формул в нашем столбце промежуточных сумм, используя функцию СУММ вместо оператора +. Когда функция СУММ встречает текстовую ячейку, она обрабатывает ее так же, как если бы она содержала 0.

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

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

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

Промежуточные итоги с частично фиксированным диапазоном

Еще один вариант с функцией СУММ – ссылаться только на столбец "Продажи" и использовать ссылку на частично фиксированный диапазон.

Если мы используем следующую формулу =СУММ($C$3:C3) , мы можем скопировать и вставить ее вниз по диапазону. Он не будет ссылаться ни на какие заголовки столбцов, а указанный диапазон будет увеличиваться с каждой строкой.

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

Промежуточные итоги с относительным именованным диапазоном

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

Это трюк, который включает временное переключение стиля ссылок Excel с A1 на R1C1. Затем определение именованного диапазона с использованием нотации R1C1. Затем переключите базовый стиль обратно на A1.

В стиле ссылки R1C1 ссылки на ячейки определяются тем, насколько далеко они находятся от ячейки, использующей ссылку. Например, в этой формуле =R[-2]C[3] относится к ячейке на 2 вверх и на 3 справа от ячейки.

Мы можем использовать эту относительную ссылку, чтобы создать именованный диапазон, который всегда находится на одну ячейку выше ссылающейся ячейки с помощью формулы =R[-1]C .

Чтобы изменить стиль ссылки, перейдите на вкладку "Файл" и выберите "Параметры". Перейдите в раздел «Формула» в меню «Параметры Excel», установите флажок «Стиль ссылок R1C1» и нажмите кнопку «ОК».

Теперь мы можем добавить наш именованный диапазон. Перейдите на вкладку "Формула" на ленте Excel и выберите команду "Определить имя".

Вставьте имя, например «Выше», в качестве имени диапазона. Добавьте формулу =R[-1]C в поле Относится к вводу и нажмите кнопку OK.

Теперь мы можем переключить Excel обратно на стиль ссылки по умолчанию. Перейдите на вкладку «Файл» > «Параметры», раздел «Формула» > снимите флажок «Стиль ссылок R1C1» > затем нажмите кнопку «ОК».

Теперь мы можем использовать формулу =СУММ([@Продажи],выше) в нашем столбце промежуточных сумм.

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

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

Подсчет итогов с помощью сводной таблицы

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

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

Добавьте поле «Дата» в область «Строки» сводной таблицы, затем добавьте поле «Продажи» в область «Значения» сводной таблицы. Теперь добавьте еще один экземпляр поля "Продажи" в область "Строки".

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

Щелкните правой кнопкой мыши любое значение в поле "Сумма продаж2" и выберите "Показать значение как", затем выберите "Промежуточный итог".

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

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

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

Щелкните правой кнопкой мыши в любом месте сводной таблицы и выберите в меню "Обновить".

Подсчет итогов с помощью показателей Power Pivot и DAX

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

Выберите ячейку внутри данных, перейдите на вкладку «Вставка» и выберите команду «Сводная таблица».

Когда вы перейдете в меню «Создать сводную таблицу», установите флажок «Добавить эти данные в модель данных», чтобы добавить данные в модель данных и включить их для использования с Power Pivot.

Поместите поле "Дата" в область "Строки", а поле "Продажи" - в область "Значения" сводной таблицы.

С Power Pivot нам нужно будет создать любые дополнительные вычисления, которые мы хотим, используя язык DAX. Щелкните правой кнопкой мыши имя таблицы в окне «Поля сводной таблицы», затем выберите «Добавить меру», чтобы создать новый расчет. Обратите внимание, что это доступно только для модели данных.

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

В окне меры нам нужно добавить имя меры. В этом случае мы можем назвать новый показатель Промежуточный итог.
Нам также нужно добавить указанную выше формулу в поле "Формула".
Самое интересное в Power Pivot — возможность присваивать показателю числовой формат. Мы можем выбрать формат валюты для нашей меры. Всякий раз, когда мы используем эту меру в сводной таблице, формат будет применяться автоматически.

Нажмите кнопку OK, и новая мера будет создана.

В окне "Поля сводной таблицы" появится новое поле. Слева у него есть небольшой значок fx, обозначающий, что это показатель, а не обычное поле данных.

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

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

Подсчет итогов с помощью Power Query

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

Сначала нам нужно импортировать таблицу в power query. Выберите таблицу данных, перейдите на вкладку «Данные» и выберите параметр «Из таблицы/диапазона». Откроется редактор мощных запросов.

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

Нажмите на переключатель фильтра в заголовке столбца даты и выберите «Сортировать по возрастанию» из вариантов.

Нам нужно добавить индексный столбец. Это будет использоваться в расчете промежуточной суммы позже. Перейдите на вкладку "Добавить столбец" и нажмите маленькую стрелку рядом со столбцом индекса, чтобы вставить индекс, начинающийся с 1, в первую строку.

Нам нужно добавить новый столбец в наш запрос для расчета промежуточной суммы. Перейдите на вкладку "Добавить столбец" и выберите команду "Пользовательский столбец".

Мы можем назвать столбец "Промежуточный итог" и добавить следующую формулу.

Функция List.Range создает список значений из столбца "Продажи", начиная с 1-й строки (0-й элемент), который охватывает несколько строк на основе значения в индексном столбце.

Затем функция List.Sum складывает этот список значений, который является нашей промежуточной суммой.

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

У нас есть промежуточный итог, и мы закончили работу с редактором запросов. Мы можем закрыть запрос и загрузить результаты в новый рабочий лист. Перейдите на вкладку "Главная" редактора запросов и нажмите кнопку "Закрыть и загрузить".

Что происходит с промежуточным итогом, когда мы добавляем или удаляем строки из наших исходных данных? Нам нужно будет обновить таблицу вывода запроса мощности, чтобы обновить промежуточный итог с учетом изменений. Щелкните правой кнопкой мыши в любом месте таблицы и выберите «Обновить», чтобы обновить таблицу.

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

Выводы

Существует множество различных вариантов расчета промежуточных итогов в Excel.

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

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

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

Читайте также: