что такое нарастающий итог в отчете

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

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

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

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

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

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

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

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

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

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

Источник

Дополнительные вычисления в сводных таблицах

Интересный факт: часто встречаю пользователей, которые хорошо владеют инструментом сводных таблиц, но при этом не знают о такой их возможности, как дополнительные вычисления в сводных таблицах. Такие вычисления доступны в Excel 2010–2016, а в Excel 2007 дополнительные вычисления «спрятаны» в параметрах поля и их гораздо меньше.

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

Предположим, нам нужно построить несколько отчетов:

Разберем, как создать такие отчеты с помощью дополнительных вычислений в сводных таблицах.

1. Процентная структура продаж

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

Шаг 1. Постройте сводную таблицу, где в области строк Города и Товары, а в области сумм — Доходы (если вы не знаете, как создать сводную таблицу, посмотрите статью «Как построить сводную таблицу в Excel»).

Шаг 2. Щелкаем правой кнопкой мыши по любому числу в сводной таблице и выбираем раздел:
Дополнительные вычисления → % от общей суммы. В появившемся меню доступно несколько способов вычисления процентов:

а) % от общей суммы – рассчитывается к итоговой сумме, от «угла».

Если переместить данные по Городам в область строк, а Товары в столбцы, мы увидим, что общий процент считается как по строкам, так и по колонкам, и сумма процентов равна 100%.

б) % от суммы по столбцу или по строке.
Если требуется рассчитать структуру продаж, например, только по Городам, выбираем % от суммы по столбцу. Если только по товарам, соответственно – по строке.

в) А если нужно видеть структуру продаж и по товарам, и по городам? Не проблема! Нужно выбрать % от суммы по родительской строке.
Тогда процент рассчитается от суммы группы, а не от общего итога. А сумма процентов внутри группы будет равна 100%.

Шаг 3. Все, конечно замечательно, НО хотелось бы рядом с процентами видеть суммы. И это тоже не проблема! Открою маленький секрет: в область значений сводной таблицы мы можем несколько раз перетащить один и тот столбец. Для этого просто захватываем мышкой нужное поле и несколько раз перетаскиваем его в область сумм.

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

2. Продажи нарастающим итогом

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

Шаг 1. Постройте сводную таблицу. В строки поместите Города, в столбцы — Месяцы.

Шаг 2. Правой кнопкой мыши по любому числу, выберите Дополнительные вычисления → С нарастающим итогом в поле.

Шаг 3. В открывшемся окне выбираем, что нарастание нужно по Месяцам и все готово!
Можно выбрать, относительно какого поля будет идти нарастание – строк и столбцов, городов или месяцев. В нашем случае выбран вариант нарастающего итога по месяцам. Кстати, столбец Общий итог пустой, потому что нарастающий итог рассчитан в декабре.

3. Темпы роста

Настроим отчет, в котором будут темпы роста, рассчитанные в сводной таблице.

Шаг 1. В новую сводную таблицу добавляем в строки Города, в столбцы Месяцы. В область значений – два одинаковых столбца Доходы.
Когда в области Значений появляется более двух полей, в столбцах появляется «виртуальное» поле «∑ Значения», которое определяет размещение данных в сводной таблице – по строкам или столбцам. Переместите «∑ Значения» в область строк.

Шаг 2. Щелкаем правой кнопкой мышки по числам одного из полей сводной таблицы и выбираем Дополнительные вычисления → Приведенное отличие. Указываем Базовое поле «месяцы», элемент – «назад».

Январь будет пустым, потому что перед ним нет других данных. Это место можно занять спарклайнами. Чтобы их добавить, перейдите в меню Вставка → Спарклайны → График.

Источник

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

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

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

… а затем мы суммируем текущее значение и предыдущий итог.

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

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

Но что SQL? Очень долго в нём не было нужного функционала. Необходимый инструмент – оконные функции – впервые появился только стандарте SQL:2003. К этому моменту они уже были в Oracle (версия 8i). А вот реализация в других СУБД задержалась на 5-10 лет: SQL Server 2012, MySQL 8.0.2 (2018 год), MariaDB 10.2.0 (2017 год), PostgreSQL 8.4 (2009 год), DB2 9 для z/OS (2007 год), и даже SQLite 3.25 (2018 год).

1. Оконные функции

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

… но нас интересуют только строки до текущей:

В конечном итоге, нам нужна сумма с этими параметрами:

А полный запрос будет выглядеть так:

В случае нарастающего итога по группам (поле grp ) нам требуется только одна небольшая правка. Теперь мы рассматриваем данные как разделённые на «окна» по признаку группы:

Чтобы учесть это разделение необходимо использовать ключевое слово partition by :

И, соответственно, считать сумму по этим окнам:

Тогда весь запрос преобразуется таким образом:

Производительность оконных функций будет зависеть от специфики вашей СУБД (и её версии!), размеров таблицы, и наличия индексов. Но в большинстве случаев этот метод будет самым эффективным. Тем не менее, оконные функции недоступны в старых версиях СУБД (которые ещё в ходу). Кроме того, их нет в таких СУБД как Microsoft Access и SAP/Sybase ASE. Если необходимо вендоро-независимое решение, следует обратить внимание на альтернативы.

2. Подзапрос

Как было сказано выше, оконные функции были очень поздно введены в основных СУБД. Эта задержка не должна удивлять: в реляционной теории данные не упорядочены. Куда больше духу реляционной теории соответствует решение через подзапрос.

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

Что в коде выглядит так:

Чуть более эффективным будет решение, в котором подзапрос считает итог до текущей даты (но не включая её), а затем суммирует его со значением в строке:

В случае нарастающего итога по нескольким группам нам необходимо использовать коррелированный подзапрос:

Условие g.grp = t2.grp проверяет строки на вхождение в группу (что, в принципе, сходно с работой partition by grp в оконных функциях).

3. Внутреннее соединение

Поскольку подзапросы и джойны взаимозаменяемы, мы легко можем заменить одно на другое. Для этого необходимо использовать Self Join, соединив два экземпляра одной и той же таблицы:

Точно также можно сделать для случая с разными группами grp :

4. Декартово произведение

Раз уж мы заменили подзапрос на join, то почему бы не попробовать декартово произведение? Это решение потребует только минимальных правок:

Или для случая с группами:

Перечисленные решения (подзапрос, inner join, cartesian join) соответсвуют SQL-92 и SQL:1999, а потому будут доступны практически в любой СУБД. Основная проблема всех этих решений в низкой производительности. Это не велика беда, если мы материализуем таблицу с результатом (но ведь всё равно хочется большей скорости!). Дальнейшие методы куда более эффективны (с поправкой на уже указанные специфику конкретных СУБД и их версий, размер таблицы, индексы).

5. Рекурсивный запрос

Один из более специфических подходов – это рекурсивный запрос в common table expression. Для этого нам необходим «якорь» – запрос, возвращающий самую первую строку:

Часть кода, добавляющая один день, не универсальна. Например, это r.dt = dateadd(day, 1, cte.dt) для SQL Server, r.dt = cte.dt + 1 для Oracle, и т.д.

Совместив «якорь» и основной запрос, мы получим окончательный результат:

Решение для случая с группами будет ненамного сложнее:

6. Рекурсивный запрос с функцией row_number()

Итак, для рекурсивного запроса с row_number() нам понадобится два СТЕ. В первом мы только нумеруем строки:

… и если номер строки уже есть в таблице, то можно без него обойтись. В следующем запросе обращаемся уже к cte1 :

А целиком запрос выглядит так:

… или для случая с группами:

7. Оператор CROSS APPLY / LATERAL

Один из самых экзотических способов расчёта нарастающего итога – это использование оператора CROSS APPLY (SQL Server, Oracle) или эквивалентного ему LATERAL (MySQL, PostgreSQL). Эти операторы появились довольно поздно (например, в Oracle только с версии 12c). А в некоторых СУБД (например, MariaDB) их и вовсе нет. Поэтому это решение представляет чисто эстетический интерес.

Функционально использование CROSS APPLY или LATERAL идентично подзапросу: мы присоединяем к основному запросу результат вычисления:

… что целиком выглядит так:

Похожим будет и решение для случая с группами:

Итого: мы рассмотрели основные платформо-независимые решения. Но остаются решения, специфичные для конкретных СУБД! Поскольку здесь возможно очень много вариантов, остановимся на нескольких наиболее интересных.

8. Оператор MODEL (Oracle)

Оператор MODEL в Oracle даёт одно из самых элегантных решений. В начале статьи мы рассмотрели общую формулу нарастающего итога:

MODEL позволяет реализовать эту формулу буквально один к одному! Для этого мы сначала заполняем поле total значениями текущей строки

Функция cv() здесь отвечает за значение текущей строки. А весь запрос будет выглядеть так:

9. Курсор (SQL Server)

Нарастающий итог – один из немногих случаев, когда курсор в SQL Server не только полезен, но и предпочтителен другим решениям (как минимум до версии 2012, где появились оконные функции).

Реализация через курсор довольно тривиальна. Сначала необходимо создать временную таблицу и заполнить её датами и значениями из основной:

Затем задаём локальные переменные, через которые будет происходить обновление:

После этого обновляем временную таблицу через курсор:

И, наконец, получем нужный результат:

10. Обновление через локальную переменную (SQL Server)

Обновление через локальную переменную в SQL Server основано на недокументированном поведении, поэтому его нельзя считать надёжным. Тем не менее, это едва ли не самое быстрое решение, и этим оно интересно.

Создадим две переменные: одну для нарастающих итогов и табличную переменную:

Сначала заполним @tv данным из основной таблицы

Затем табличную переменную @tv обновим, используя @VarTotal :

… после чего получим окончательный результат:

Резюме: мы рассмотрели топ 10 способов расчёта нарастающего итога в SQL. Как можно заметить, даже без оконных функций эта задача вполне решаема, причём механику решения нельзя назвать сложной.

Источник

6-НДФЛ заполняется нарастающим итогом с начала года

Как заполнять 6-НДФЛ: нарастающим итогом или нет

Расчет по форме 6-НДФЛ уже стал привычным, но всё же при его заполнении могут возникать сомнения, в том числе поиск ответа на вопрос: 6-НДФЛ заполняется нарастающим итогом или нет?

Ответ содержится в приказе ФНС от 15.10.2020 № ЕД-7-11/753@, описывающем алгоритм заполнения 6-НДФЛ и устанавливающем основные требования к его оформлению (приказе от 14.10.2015 № ММВ-7-11/450@ — до 2021 года).

В указанных приказах ответ содержится как в явном, так и в завуалированном виде. Например:

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

Важно! Заполнить и сдать 6-НДФЛ по новой форме с 2021 года вам поможет Готовое решение от КонсультантПлюс. Пробный доступ к системе можно получить бесплатно.

Но все ли строки отчета 6-НДФЛ заполняются нарастающим итогом? Поговорим об этом в следующих разделах.

Какие строки 6-НДФЛ заполняются нарастающим итогом

С 2021 года строки 6-НДФЛ, в которых показатели накапливают значения от периода к периоду в течение календарного года, располагаются во 2-м разделе и отражают:

В форме до 2021 года нарастащим итогом заполняли показатели в 1-м разделе отчета:

На этом перечень «нарастающих» строк в 6-НДФЛ заканчивается — все суммовые строки 1-го раздела НДФЛ-отчета (до 2021 года — 2-го раздела) представляют собой не что иное, как квартальную календарную и суммовую детализацию доходов и НДФЛ. Охват показателей здесь ограничен тремя месяцами отчетного периода, и о нарастающем характере их формирования речь не идет.

Где взять данные для «нарастающих» строк отчета 6-НДФЛ

Чтобы сконцентрировать отчетные показатели о доходах и НДФЛ, налоговому агенту требуется:

О подробностях ведения основного документа, помогающего сформировать 6-НДФЛ без ошибок, см. в материале «Образец заполнения налогового регистра для 6-НДФЛ».

Если вам нужен образец 6-НДФЛ за 3 квартал/9 месяцев 2021 года, воспользуйтесь образцом от экспертов КонсультантПлюс. Это можно сделать бесплатно, получив пробный онлайн доступ к системе.

Пример заполнения строк отчета 6-НДФЛ нарастающим итогом

Рассмотрим на примере, как заполнить «нарастающие» строки 2-го раздела отчета 6-НДФЛ в 2021 году.

Персонифицированный НДФЛ-учет по состоянию на 30.06.2021 (организованный ООО «Форсаж» в специальных регистрах) содержит информацию за полугодие:

Итого выплаченные доходы за полугодие составили 21 938 000 руб. (21 860 000 руб. + 60 000 руб. + 18 000 руб.).

Кроме того, для формирования отчета требуются данные:

В 6-НДФЛ за полугодие отражено:

Пошаговая инструкция по заполнению 6-НДФЛ, с описанием нюансов оформления каждой строки отчета, доступна в КонсультантПлюс. Получите бесплатный доступ к К+ и переходите в Готовое решение, чтобы узнать все подробности данной процедуры.

Особенности строк 160 (ранее 070) и 170 (ранее 080) отчета 6-НДФЛ

Строки 160 (ранее 070) и 170 (ранее 080) тоже относятся к разряду «нарастающих» в отчете 6-НДФЛ. Для рассмотренного примера их заполнение необходимо производить с учетом следующего:

Подробнее о нюансах формирования строки 160 (ранее 070) расскажет этот материал.

Итоги

Отчет 6-НДФЛ является комбинированным: часть его строк заполняются нарастающим итогом, а остальные данные приводятся только за последние 3 месяца без привязки к общему итогу.

Источник

Что означает «нарастающим итогом»?

Как считать прибыль, налоги и т.п. нарастающим итогом?

Понятие «нарастающий итог» подразумевает расчёт промежуточных итогов по истечению каждого промежуточного периода.

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

1) Сумма за 1 квартал.

2) Сумма за 1 квартал + сумма за 2 квартал = Сумма за полугодие.

3) Сумма за полугодие + сумма за 3 квартал = Сумма за 9 месяцев.

4) Сумма за 9 месяцев + сумма за 4 квартал = Сумма за год.

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

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

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

Теперь рассмотрим, как выполнить суммирование нарастающим итогом в программе Эксель (Excel).

Вот исходные данные:

В ячейку C2 нужно внести формулу:

Теперь с помощью маркера заполнения копируем эту формулу в нижние ячейки (C3, C4 и C5).

В результате мы получим итоги за год и промежуточные итоги:

Такое понятие как нарастающий итог используется в бухгалтерии.

Нагляднее и быстрее всего объяснять что такое нарастающий итог на примере. Допустим у нашей организации показатели продаж за 7 месяцев отражены вот в такой табличке:

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

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

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

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

Многие документы в бухгалтерской и финансовой отчетности составляются нарастающим итогом. Это означает, что данные по какому-то показателю как бы накапливаются от периода ( например, отчетного, налогового ) к периоду: к сумме текущего периода добавляются суммы предшествующих периодов.

Нарастающим итогом это будет выглядеть следующим образом:

2 квартал ( полугодие )- 210000 рублей ( ФОТ 1 квартала + ФОТ 2 квартала ),

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

Этот термин часто употребляется экономистами, бухгалтерами.

Считать этот показатель лучше в Экселе, ниже в ответе приведу пример:

Теперь рассмотрим как считать нарастающий итог, например в 4 квартале:

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

Думаю, что после этого примера понятно, как считать нарастающий итог.

Сам тоже недавно познакомился с понятием «нарастающий итог»

Когда пришлось сдавать отчет по объемам потребленной воды из недр.

Рассмотрим нарастающий итог на примере объема продаж за определённый срок.

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

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

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

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

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

К примеру, надо подсчитать зарплату за 12 месяцев, значит нарастающим итогом будет сумма зарплаты за 12 месяцев.

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

Вот еще один расчет:

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

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

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

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

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

Источник