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

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

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

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

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

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

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

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

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

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

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

Источник

Нарастающий итог в 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. Как можно заметить, даже без оконных функций эта задача вполне решаема, причём механику решения нельзя назвать сложной.

Источник

Как верно заполнить декларацию по налогу на прибыль нарастающим итогом?

Что такое нарастающий итог при расчете налога на прибыль?

Налоговой базой по налогу на прибыль является денежное выражение прибыли организации (п. 1 ст. 274 НК РФ).

При определении налоговой базы облагаемая прибыль определяется нарастающим итогом с начала налогового периода (п. 7 ст. 274 НК РФ). Исходя из базы за год исчисляется налог.

Авансы по итогам отчетных периодов исчисляются исходя из прибыли, рассчитанной нарастающим итогом с начала налогового периода до окончания отчетного периода (п. 2 ст. 286 НК РФ). Нарастающий итог означает, что прибыль отчетного квартала определяется исходя из доходов и расходов, полученных/понесенных с начала года до отчетной даты. То есть она фактически включает в себя и прибыль/убыток прошлого отчетного периода, и прибыль/убыток текущего.

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

Про нарастающий итог в другом отчете читайте в материале «6-НДФЛ заполняется нарастающим итогом с начала года».

Пример расчета налоговой базы

Поясним сказанное на примере.

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

За I квартал ее доходы составили 900 тыс. рублей, а расходы — 750 тыс. рублей.

За II квартал: доходы — 600 тыс., расходы 800 тыс. рублей соответственно.

За III квартал: 1 млн и 700 тыс. рублей.

За IV квартал — 700 тыс. и 800 тыс.

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

I квартал

Полугодие

9 месяцев

Доходы, тыс.

(900 + 600 + 1000 + 700)

Расходы, тыс.

(750 + 800 + 700 + 800)

Финрезультат, тыс.

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

Что в декларации?

Декларация по налогу на прибыль также составляется нарастающим итогом с начала года (п. 2.1 Порядка заполнения, утв. приказом ФНС России от 23.09.2019 № ММВ-7-3/475@).

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

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

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

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

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

Итоги

Исчисление налога нарастающим итогом означает, что расчет нужно вести исходя из доходов и расходов, полученных (осуществленных) с 1 января по отчетную дату. По этому же принципу заполняется налоговая декларация. Нарастает и сумма налога к уплате, т. к. при ее определении учитываются платежи предыдущих отчетных периодов.

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Нарастающий итог по таблице значений

Входящая сумма Х = 100.
строки ТЗ

Сделано перебором входящей ТЗ, можно было и запросом.

Скачать файлы

Специальные предложения

А что если идет речь не о кол-стве, а о сумме? Т.е., надо списать заданную сумму, вллоть до копеек из какого то документа (перечня номенклатуры). Не важно что списать, но нужно списать целое число номенклатуры (кство) на заданную сумму до копеек.

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

документ, в нем таб часть

отбери мне товара с кством в сумме, твоим перебором точно на 55,30, вот мне нужно отобрать из тч только на 22,9(могу подсказать, это 1 и 3 строки по 1шт в сумме), ну а если это победишь, то отбери потом товара на 54,3 и это прикинь, тоже в тч с этих 4 скудных строк выплывает.

ты думаешь и вправду чуваки так заморачивались бы http://infostart.ru/public/437890/ если бы твой код проканал?!

(6) А при чём тут публикация http://infostart.ru/public/437890/ и твой пример? Я обсуждал конкретно эту публикацию. Твой пример данная публикация тоже не решит, т.к. там как минимум нет возможности вбивать сумму и количество.
Если проанализировать все примеры на скриншотах и пример в тексте публикации, то можно увидеть, что все они достаточно лёгкие и решаются моим кодом (написанным за несколько минут без конфигуратора).

рекомендую тс и оптимизаторам

Данная задача решаем МОЙ вопрос
она меня удовлетворяет на все 100%

Обновление 18.05.17 19:13

См. также

Навигатор по конфигурации базы 1С 8.3 Промо

Универсальная внешняя обработка для просмотра метаданных конфигураций баз 1С 8.3. Отображает свойства и реквизиты объектов конфигурации, их количество, основные права доступа и т.д. Отображаемые характеристики объектов: свойства, реквизиты, стандартные рекизиты, реквизиты табличных частей, предопределенные данные, регистраторы для регистров, движения для документов, команды, чужие команды, подписки на события, подсистемы. Отображает структуру хранения объектов базы данных, для регистров доступен сервис «Управление итогами». Платформа 8.3, управляемые формы. Версия 1.1.0.87 от 02.12.2021

28.10.2018 46233 414 ROL32 72

Универсальный редактор данных (УРД)

27.08.2021 6467 130 Adeptus 51

Подсистема «Показатели объектов»

06.03.2021 7434 6 pila86 16

Внешняя компонента: Android tools

12.01.2021 6369 19 KAV2 13

Удаление и/или копирование сохраненных в 1С настроек (например настроек печати табличных форм) Промо

Иногда нужно удалить сохраненную в 1С «покореженную» настройку или скопировать «удачную» другому пользователю.

01.09.2012 67364 1381 AnryMc 46

FormCodeGenerator Программная доработка форм. Часть 2 (Режим работы «Режим сравнения форм») на примере ERP 2.5

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

21.12.2020 10624 27 huxuxuya 11

Внешний регламент для 1С

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

05.03.2020 9088 9 moolex 12

CF & SQL : конструктор прямых запросов к БД 1С

Программа для чтения и анализа внутренней структуры данных 1С V8.х, Содержит конструктор прямых SQL запросов, позволяет обрабатывать и редактировать тексты SQL-запросов, выполнять прямые запросы к базе данных 1С и обрабатывать полученные результаты.

02.10.2019 31466 300 dmitrydemenew 63

Программа Faster 9.4 позволяет ускорить процесс работы программиста (работает в любом текстовом редакторе). Подсказка при вводе текста на основе ранее введенного текста и настроенных шаблонов. Программа Faster позволяет делится кодом с другими программистами в два клика или передать ссылку через QR Код. Исправление введенных фраз двойным Shift (с помощью speller.yandex). Переводчик текста. Переворачивает текст случайно набранный на другой раскладке. Полезная утилита для тех, кто печатает много однотипного текста, кодирует в среде Windows на разных языках программирования. Через некоторое время работы с программой у вас соберется своя база часто используемых словосочетаний и кусков кода. Настройка любых шорткатов под себя с помощью скриптов. Никаких установок и лицензий, все бесплатно.

24.05.2012 85453 967 moolex 157

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

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

09.09.2019 18432 35 bmk74 8

Работа с релизами 1С и договорами ИТС

Работа с релизами 1С и партнерским кабинетом.

15.08.2019 21980 65 RocKeR_13 37

Вам нравятся запросы в 1С?

Речь не только о том, что простейший запрос с «легальным» оформлением растянется на пол-экрана, речь еще обо всем, что нужно написать «в нагрузку» к тексту запроса. Все эти «Новый Запрос», «УстановитьПараметр» и последующие пляски с обработкой результата. Пора с этим заканчивать!

03.07.2019 28998 7 m-rv 91

Очистка кэша 1С 8 (8.0, 8.1, 8.2, 8.3). Грамотная чистка кэша 1С с сохранением настроек. Промо

04.11.2018 54743 542 Eugen-S 35

Модель объекта

Подсистема позволяет описать модель данных объекта, где описана зависимость между реквизитами, и затем использовать эту модель в разных сценариях работы с объектом. Версия платформы: 8.3.6 и выше. С небольшими доработками будет работать на 8.2.

30.06.2019 17185 1 vadim1980 5

Блин, мы забыли включить регламентные задания…

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

08.04.2019 25583 19 slozhenikin_com 37

Simple WMS Client – это визуальный конструктор мобильного клиента для терминала сбора данных(ТСД) или обычного телефона на Android. Приложение работает в онлайн режиме через интернет или WI-FI, постоянно общаясь с базой посредством http-запросов (вариант для 1С-клиента общается с 1С напрямую как обычный клиент). Можно создавать любые конфигурации мобильного клиента с помощью конструктора и обработчиков на языке 1С (НЕ мобильная платформа). Вся логика приложения и интеграции содержится в обработчиках на стороне 1С. Это очень простой способ создать и развернуть клиентскую часть для WMS системы или для любой другой конфигурации 1С (УТ, УПП, ERP, самописной) с минимумом программирования. Например, можно добавить в учетную систему адресное хранение, учет оборудования и любые другие задачи. Приложение умеет работать не только со штрих-кодами, но и с распознаванием голоса от Google. Это бесплатная и открытая система, не требующая обучения, с возможностью быстро получить результат.

09.01.2019 63397 271 informa1555 239

Установка предопределенных элементов: просмотр, исправление и поиск ошибок (задвоенных и отсутствующих) Промо

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

06.10.2014 171157 2773 ekaruk 170

Легкое и гибкое управление списком доступных баз 1С у пользователей

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

05.12.2018 22354 23 RomikR 9

Источник