Нарастающий итог что это
Перейти к содержимому

Нарастающий итог что это

  • автор:

Вопрос: Что значит «нарастающим итогом»? Во многих отчетностях сведения указываются нарастающим итогом, как их рассчитывать? («Современный предприниматель. Индивидуальный подход к бизнесу», N 8, август 2008 г.)

Что значит «нарастающим итогом»? Во многих отчетностях сведения указываются нарастающим итогом, как их рассчитывать?

Это означает, что суммы при расчете налога (взноса) каждый раз берутся, начиная с января. Например, ЕСН с зарплаты сотрудников рассчитывается нарастающим итогом (п. 2 ст. 237 НК РФ). Предположим, выплаты за январь составили 100 руб., за февраль — 150 руб., за март — 200 руб. По итогам января ЕСН определяется со 100 руб., февраля — с 250 руб. (100 + 150), марта — с 450 руб. (100 + 150 + 250). В бюджет перечисляется разница между рассчитанной суммой и уплаченной на момент расчета.

1 августа 2008 г.

«Современный предприниматель. Индивидуальный подход к бизнесу», N 8, август 2008 г.

Актуальная версия заинтересовавшего Вас документа доступна только в коммерческой версии системы ГАРАНТ. Вы можете подать заявку на получение полного доступа к системе бесплатно на 3 дня.

Если вы являетесь пользователем интернет-версии системы ГАРАНТ, вы можете открыть этот документ прямо сейчас или запросить по Горячей линии в системе.

Журнал «Современный предприниматель. Индивидуальный подход к бизнесу»

Учредитель и издатель ООО «Издательский дом «В-Медиа»

Журнал зарегистрирован Министерством Российской Федерации по делам печати, телерадиовещания и средств массовой информации. Свидетельство о регистрации ПИ N ФС77-25680 от 22 сентября 2006 г.

Адрес: 109444, г. Москва, ул. Ташкентская, д. 9

Тел./факс: (495) 646-07-51

По каталогу «Роспечать»: 36654

Нарастающий итог

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

Пример

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

На следующей гистограмме к оси значений было применено выражение «Нарастающий итог», оформленное в виде ярлыка. (Стрелка указывает на полосу, используемую для объяснения того, что вычисляется в приведенном ниже выражении.)

На оси значений используется следующее выражение.

Sum([Sales]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

В первой части выражения указывается, что столбец [Sales] должен быть агрегирован в виде суммы. Результат этой части помещается в столбец [Value] для использования в выражении после THEN. Для каждого узла в визуализации (в данном случае для каждой полосы) последняя часть выражения суммирует текущий узел и все предыдущие узлы.

Например, расчет нарастающего итога за 1-й кв. 2020 г. (см. стрелку на предыдущем изображении):

2350 долларов США = 400 долларов США + 200 долларов США + 500 долларов США + 600 долларов США + 650 долларов США (сумма полос в прямоугольнике ниже).

Селектор столбцов в развернутом виде

В установленном клиенте развернутый селектор столбцов включает элементы управления, упрощающие изменение различных параметров в ярлыке выражения:

Отображаемое имя При желании можно указать другое отображаемое имя вместо «Нарастающий итог ([имя столбца])» по умолчанию.
Ось Укажите ось, по которой будет производиться вычисление узлов.

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

Нарастающий итог в Excel

Нарастающие итоги формулой в Excel

Начнём, для разогрева, с наиболее простого варианта — формул. Если мы имеем на входе небольшую отсортированную по дате таблицу, то для расчёта нарастающего итога в отдельном столбце нам потребуется элементарная формула:
Основная фишка тут в хитром закреплении диапазона внутри функции СУММ — ссылка на начало диапазона делается абсолютной (со знаками доллара), а на конец — относительной (без долларов). Соответственно, при копировании формулы вниз на весь столбец мы получаем растягивающийся диапазон, сумму которого и считаем. Минусы такого подхода очевидны:

  • Таблица обязательно должна быть отсортирована по дате.
  • При добавлении новых строк с данными формулу придётся допротягивать вручную.

Способ 2. Сводная таблица

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

Исходные данные для сводной

Конвертируем нашу исходную таблицу в «умную» (динамическую) сочетанием клавиш Ctrl + T или командой Главная — Форматировать как таблицу (Home — Format as Table) , а затем строим по ней сводную таблицу командой Вставка — Сводная таблица (Insert — Pivot Table) . В область строк в сводной закидываем дату, а в область значений — число проданных товаров:

Строим сводную

Обратите внимание, что если у вас не совсем старая версия Excel, то даты автоматически сгруппируются по годам, кварталам и месяцам. Если вам нужна другая группировка (или она совсем не нужна), то это можно исправить, щёлкнув правой кнопкой мыши по любой дате и выбрав команды Группировать / Разгруппировать (Group / Ungroup) .

Если хочется видеть одновременно и получившиеся итоги по периодам и нарастающий итог в отдельном столбце, то имеет смысл закинуть в область значений поле Продано ещё раз, чтобы получить дубликат поля — в нём мы и включим отображение нарастающих итогов. Для этого щёлкните правой кнопкой мыши по полю и выберите команду Дополнительные вычисления — Нарастающий итог (Show Values as — Running Totals) :

Переключаем поле в режим нарастающих итогов

Там же можно выбрать и вариант нарастающих итогов в процентах, а в следующем окне нужно выбрать поле, по которому пойдет накопление — в нашем случае это поле даты:

Нарастающий итог в шт и % в сводной таблице

Плюсы такого подхода:

  • Быстро считается большой объем данных.
  • Не нужно вводить вручную никаких формул.
  • При изменении в исходных данных достаточно обновить сводную правой кнопкой мыши или командой Данные — Обновить всё (Data — Refresh All).

Способ 3. Запрос Power Query

Загрузим нашу «умную» таблицу с исходными данными в редактор запросов Power Query, используя команду Данные — Из таблицы / диапазона (Data — From Table/Range) . В последних версиях Excel её, кстати, переименовали — теперь она называется С листа (From Sheet) :

Загружаем данные в Power Query

Затем выполним следующие шаги:

1 . Отсортируем таблицу по возрастанию по столбцу даты командой Сортировать по возрастанию в выпадающем списке фильтра в шапке таблицы.

2 . Чуть позже для подсчета нарастающего итога нам потребуется вспомогательный столбец с порядковым номером строки. Добавим его командой Добавление столбца — Столбец индекса — От 1 (Add column — Index column — From 1) .

3 . Также для подсчёта нарастающего итога нам потребуется ссылка на столбец Продано, где лежат наши суммируемые данные. В Power Query столбцы ещё называются списками (list) и чтобы получить ссылку на него, щёлкнем правой кнопкой мыши по заголовку столбца и выберем команду Детализация (Show detail) . В строке формул появится нужное нам выражение, состоящее из имени предыдущего шага #»Добавлен индекс», откуда мы берём таблицу и имени столбца [Продано] из этой таблицы в квадратных скобках:

Ссылка на нужный столбец-список в Power Query

Копируем это выражение в буфер для дальнейшего использования.

4 . Удаляем ненужный более последний шаг Продано и добавляем вместо него вычисляемый столбец для подсчета нарастающего итога командой Добавление столбца — Настраиваемый столбец (Add column — Custom column) . Нужная нам формула будет выглядеть так:

Добавляем вычисляемый столбец для нарастающего итога

Здесь функция List.Range берёт исходный список (столбец [Продано]) и извлекает из него элементы, начиная с первого (в формуле это 0, т.к. нумерация в Power Query начинается с нуля). Количество извлекаемых элементов — это номер строки, который мы берём из столбца [Индекс]. Таким образом, эта функция для первой строки возвращает только одну первую ячейку столбца Продано. Для второй строки — уже первых две ячейки, для третьей — первых три и т.д.

Ну, а затем функция List.Sum суммирует извлечённые значения и мы получаем в каждой строке сумму всех предыдущих элементов, т.е. нарастающий итог:

Готовый нарастающий итог в Power Query

Осталось удалить ненужный нам больше столбец Индекс и выгрузить результаты обратно в Excel командой Главная — Закрыть и загрузить (Home — Close&Load to).

Форсаж

В принципе, на этом можно было бы и остановиться, но есть небольшая ложка дёгтя — созданный нами запрос работает со скоростью черепахи. Для примера, на моем не самом слабом ПК таблица всего в 2000 строк обрабатывается 17 секунд. А если данных будет больше?

Для ускорения можно использовать буферизацию при помощи специальной функции List.Buffer, которая загружает заданный ей в качестве аргумента список (list) в оперативную память, что сильно ускоряет обращение к нему в дальнейшем. В нашем случае имеет смысл буферизовать список #»Добавлен индекс»[Продано], к которому Power Query вынужден обращаться при расчёте нарастающего итога в каждой строке нашей 2000-строчной таблицы.

Для этого в редакторе Power Query на Главной вкладке жмём кнопку Расширенный редактор (Home — Advanced Editor), чтобы открыть исходный код нашего запроса на встроенном в Power Query языке М:

Исходный код запроса

А затем добавляем туда строку с переменной MyList, значение которой возвращает функция буферизации и подменяем на следующем шаге обращение к списку на эту переменную:

Буферизованный запрос

После внесения таких изменений наш запрос станет существенно резвее и справится с 2000-строчной таблицей всего за 0.3 секунды!

Другое дело, правда? 🙂

Ссылки по теме

  • Диаграмма Парето (80/20) и как построить её в Excel
  • Поиск ключевых слов в тексте и буферизация запроса в Power Query

Нарастающие итоги в Power Pivot и Power BI

У нас накопились ответы на вопросы о накопительных итогах (даже ответы про накопительные итоги – накапливаются =) Такое впечатление, что с задачей рассчитать нарастающие или накопительные итоги сталкивается практически каждый слушатель наших курсов. И вопрос стоит даже не в том, какую формулу использовать.

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

Немного о нарастающих итогах

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

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

В Power Pivot и Power BI для расчета нарастающих итогов есть специальные формулы.

DAX-формулы для расчета нарастающих итогов

1. Нарастающие итоги с начала года считаются с помощью формул TOTALYTD или DATESYTD .

YTD = TOTALYTD ( [факт] , ‘ Календарь ‘ [Date] )
или
YTD = CALCULATE ( [факт] , DATESYTD ( ‘ Календарь ‘ [Date] ) )

Нарастающий итог DAX

2. Нарастающий итог с начала квартала – формулы TOTALQTD или DATESQTD .

QTD = TOTALQTD ( [факт] , ‘ Календарь ‘ [Date] )
или
QTD = CALCULATE ( [факт] , DATESQTD ( ‘ Календарь ‘ [Date] ) )

Нарастающие итоги DAX

3. C начала месяца – формулы TOTALMTD или DATESMTD .

MTD = TOTALMTD ( [факт] , ‘ Календарь ‘ [Date] )
или
MTD = CALCULATE ( [факт] , DATESMTD ( ‘ Календарь ‘ [Date] ) )

Нарастающий итог по дням

4. Нарастающий итог без привязки к периодам.

При расчете нарастающего итога без привязки к периодам показатели будут суммироваться с самого начала проекта – с его первой даты, а в начале нового периода не «сбросятся».

Нарастающий итог с начала проекта =
CALCULATE (
[факт] ,
FILTER (
ALL ( ‘ Календарь ‘ [Date] ),
‘ Календарь ‘ [Date] )
)

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

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