Расчет средних по периодам в запросе - это элементарно!

15.10.11

Задачи пользователя - Анализ учета

Распространено мнение, что расчет средних по периодам в запросе - это сложно. В статье приводится  прием, позволяющий опровергнуть это заблуждение. Приводится описание метода, его обоснование и ограничения использования. Как примеры прилагаются  два практичных отчета на СКД: для расчета среднедневных остатков товаров и для расчета среднедневной задолжности контрагентов. Отчеты созданы на платформе 8.1 для конфигурации УТ10.3 .

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

Наименование Файл Версия Размер
СредниеОстатки.erf
.erf 6,08Kb
130
.erf 6,08Kb 130 Скачать
СреднийДолг.erf
.erf 6,07Kb
48
.erf 6,07Kb 48 Скачать

 

Легче всего объяснить предлагаемый прием на таком простом примере и конкретных числах: Пусть требуется посчитать среднее число рабов на плантации сотрудников некоторой компании за год. На начало года имеем  10 сотрудников. Предположив, что состав будет стабильным, оценим годовой бюджет рабочего времени компании в 3650 (10х365) человеко-дней. Если 13 мая в пятницу один из сотрудников уволится, то бюджет уменьшится на 232 дня (это число оставшихся дней года). А когда 17 июня на работу выйдет новенькая сотрудница, бюджет увеличится на 197 дней. Приём на работу 1-го сентября одного сотрудника увеличит бюджет на 121 дней, а увольнение 3-х сотрудников 30 сентября - уменьшит его на 376 (3х92) человеко-дней. В итоге получим бюджет 3650 - 232 + 197 + 121 - 376 = 3360. Разделив его на 365, получим в среднем примерно 9,2 работающих сотрудника. Заметьте, что при этом нам не пришлось учитывать ни интервалы работы сотрудников, ни интервалы между событиями!

То же самое можно описать формулой:

Формула расчета

И привести как пример реализации в запросе:

ВЫБРАТЬ
    
Номенклатура,
    
СУММА(ВЫБОР КОГДА Период &НачалоПериода ТОГДА КоличествоКонечныйОстаток ИНАЧЕ КоличествоОборот КОНЕЦ * (РАЗНОСТЬДАТ(Период&КонецПериодаДЕНЬ) + 1))
    / (
РАЗНОСТЬДАТ(&НачалоПериода&КонецПериодаДЕНЬ) + 1) КАК СреднийЗапас
ИЗ
    
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода&КонецПериодаДень, , )
СГРУППИРОВАТЬ ПО
    
Номенклатура

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

Ограничивает применение метода два обстоятельства:

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

2) Случай, когда расчет среднего производится "по рабочим дням" или похожими способами. При этом не просто (но возможно!) определить число оставшихся периодов.

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

 

См. также

Анализ расхождений выручки НДС и Налога на прибыль в декларациях (БП 3.0 ПРОФ и КОРП, КА 2, ЕRP)

Анализ учета Закрытие периода Платформа 1С v8.3 Бухгалтерский учет 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Комплексная автоматизация 2.х Россия Бухгалтерский учет Налоговый учет Налог на прибыль НДС Платные (руб)

Каждый бухгалтер не раз сталкивался с требованием от налоговой инспекции пояснить расхождения в показателях декларации по Налогу на прибыль («Доходы от реализации» + «Внереализационные доходы») и налоговой базой по НДС за год. Являются ли ошибкой подобные расхождения? Как пояснить налоговой их причину? Отчет «Анализ расхождений выручки НДС и Налога на прибыль в декларациях» поможет найти все расхождения.

7200 руб.

21.10.2017    83324    245    167    

247

Дополнительный анализ расходов УСН в 1С:Бухгалтерии 3.0

Учет доходов и расходов Анализ учета Платформа 1С v8.3 Бухгалтерский учет 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Налоговый учет УСН Платные (руб)

Мы добавили девять новых проверок в Экспресс-проверку учета. Цель таких проверок — узнать, есть ли потенциальные проблемы в учёте УСН.

7200 руб.

27.09.2021    13401    24    11    

34

Экспресс проверка управленческого учёта для УНФ 1.6/3.0

Анализ учета Платформа 1С v8.3 1С:Управление нашей фирмой 1.6 1С:Управление нашей фирмой 3.0 Россия Управленческий учет Платные (руб)

Расширение автоматически анализирует учёт 1С:УНФ, помогает находить и исправлять ошибки, а также проверять те места, которые хорошо бы проверить. Разработка для тех, кто хочет построить управленческий финансовый учет в УНФ.

12000 руб.

28.07.2022    8517    5    25    

21

Отчет "ABC-анализ" для 1С УТ 11, КА, ERP

Анализ учета Анализ продаж Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

ABC-анализ — метод, позволяющий классифицировать ресурсы фирмы по степени их важности. Отличный отчет для анализа выручки и продаж! Может применяться в сфере деятельности любого предприятия. В его основе лежит принцип Парето — 20% усилий обеспечивают 80% результата.

6000 руб.

26.06.2022    11166    16    6    

18

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

Загрузка и выгрузка в Excel Регламентированный учет и отчетность Анализ учета БДР, БДДС Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

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

12000 руб.

08.02.2021    19317    12    66    

26

Сверка остатков товаров БУХ3 - УТ11

Анализ учета Платформа 1С v8.3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 Россия Бухгалтерский учет Платные (руб)

Обработка позволяет сравнивать количественные остатки номенклатуры между Бухгалтерия 3.0 и УТ 11.5

3600 руб.

06.02.2023    4033    6    1    

11

Сверка документов между 1С:УНФ и 1С: Бухгалтерией 8

Обмен между базами 1C Анализ учета Платформа 1С v8.3 Конфигурации 1cv8 Россия Платные (руб)

"Сверка документов" используется для проверки расхождений по документам в 1С:УНФ и 1С: Бухгалтерия 8.

7920 руб.

19.04.2022    9639    6    8    

8

Сверка данных УНФ - БУХ по документам и по долгам

Взаиморасчеты Анализ учета Платформа 1С v8.3 1С:Управление нашей фирмой 1.6 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Платные (руб)

Обработка позволяет сравнивать по документам и сверять по долгам покупателей и поставщиков между базами УНФ 1.6, УНФ 3.0 и Бухгалтерия 3.0,

6000 руб.

24.08.2022    6267    3    0    

5
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Арчибальд 2706 30.09.11 10:27 Сейчас в теме
Ну, что тут сказать... Остальное вы видели. ©
wildwilduser; +1 Ответить
2. cool.vlad4 2 30.09.11 10:47 Сейчас в теме
(0) Безусловно молодец, но только можешь объяснить выкладку из рисунка, че-то не соображу с утра никак почему
Прикрепленные файлы:
3. ildarovich 7846 30.09.11 11:38 Сейчас в теме
(2) Вот выкладка для числителя
s1 + s2 + s3 + ... + sn = (s0+d1) + (s1+d2) + (s2+d3) + ... + (sn-1 + dn) = 
= (s0 + d1) + (s0 + d1 + d2) + (s0 + d1 + d2 + d3) + ... + (s0 + d1 + d2 + d3 + ... + dn) = 
= s0 * n + d1 * n + d2 * (n-1) + d3 * (n - 2) + dn * 1
Из нее, кстати, следует, что в формуле (не в запросе) была неточность - забыл умножение s0 на n. Сейчас поправлю в статье.
Прикрепленные файлы:
4. cool.vlad4 2 30.09.11 11:57 Сейчас в теме
(3) Ну, вот блин, а я время потратил, бумагу перевел, думаю почему у меня не сходится;-)
5. ildarovich 7846 30.09.11 12:08 Сейчас в теме
(4) Приношу извинения - торопился!
6. cool.vlad4 2 30.09.11 12:09 Сейчас в теме
(5) да, не какие извинения, - молодец, очень ценная статья, а я плюс даже забыл поставить...исправился...
7. alexk-is 6533 30.09.11 16:34 Сейчас в теме
(0) Про быстрые тэта-соединения можно посмотреть здесь http://infostart.ru/public/71130/#Pro_hitrye_zaprosy
Пример 3

Расчет периода по рабочим дням легко производится по производственному календарю. Расчет периода по банковским дням можно посмотреть здесь http://infostart.ru/public/68269/
JohnyDeath; ildarovich; +2 Ответить
8. losara1983 6 30.09.11 17:33 Сейчас в теме
Я как раз недавно возился с этой задачей, жаль не нашел вашего метода)
9. hogik 443 30.09.11 19:41 Сейчас в теме
(0)
Сергей.
Пора открывать цикл публикаций "Мы пишем з..." с единым логотипом. ;-)
Восторгаюсь Вашей головой...
10. maljaev 789 03.10.11 12:32 Сейчас в теме
Формула шокировала :)
Надо будет все свои ТЗ к проектам снабжать аналогичными формулами вместо описания на "общечеловеческом" - для придания пущей важности проекту и уважения заказчика :)
SunShinne; romankoav; igormiro; Светлый ум; kuzyara; Winstoncuk; +6 Ответить
11. bulpi 215 05.10.11 21:15 Сейчас в теме
Объясните тупому (возможно)
Чем плохо посчитать остатки на каждый день, а потом сумму разделить на количество дней?
13. ildarovich 7846 05.10.11 22:14 Сейчас в теме
(11) Дело в том, что посчитать остатки на каждый день в запросе просто не получится - остатки будут выдаваться только на периоды, по которым есть обороты. В запросе с итогами есть возможность "дополнить периодами", а в пакетном запросе - нет. В запросе люди выкручиваются как могут: гораздо более сложными способами. Примеры можно посмотреть:
v8: Полные остатки по периоду во вложенном запросе
v8: Полные остатки по периоду во вложенном запросе по начальному остатку
Отчет «Среднесписочная численность» для ЗУП 2.5.25
wildwilduser; +1 Ответить
14. bulpi 215 06.10.11 00:33 Сейчас в теме
(13)
Спасибо. Про запрос без итогов я не подумал.
12. bulpi 215 05.10.11 21:17 Сейчас в теме
За красоту идеи в любом случае + без разговоров, но все таки ?
15. alexk-is 6533 15.10.11 01:53 Сейчас в теме
Чем раскрашен запрос в публикации? Спрашиваю потому, что вижу это была не Разукрашка.
16. ildarovich 7846 15.10.11 10:20 Сейчас в теме
(15) Не знаю, почему Вы так решили, но я пользовался именно ей.
Как и для раскрашивания запросов в Порождающий запрос. Буду обновлять другие публикации - постараюсь сделать также. Если так удобнее читателям.
Прикрепленные файлы:
17. alexk-is 6533 15.10.11 11:39 Сейчас в теме
(16) Как почему? Конечно же по HTML-коду. Структура HTML-кода Разукрашки очень специфична, т.к. содержит большое количество различных способов оптимизации. Её очень легко узнать на тексте достаточно большого объема именно по наличию оптимизации.

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

Как минимум один уровень преобразования получается при вставке HTML-кода в публикацию на Infostart. Пусть незначительно, но код становится больше.
18. sommid 12.01.12 16:32 Сейчас в теме
19. Antony_2009 29.03.12 14:26 Сейчас в теме
Спасибо за публикацию! Скажите, пожалуйста, а возможно данный метод применить для платформы 1С 7.7?
20. ildarovich 7846 13.04.12 17:55 Сейчас в теме
(19) Думаю, да. Постараюсь в ближайшие дни дополнить статью данным примером.
Antony_2009; +1 Ответить
21. ildarovich 7846 22.04.12 12:34 Сейчас в теме
(19)(20) После более тщательного изучения данного вопроса
а возможно данный метод применить для платформы 1С 7.7?

выяснилось следующее:
1. В 7.7 нет возможности обработки данных в самом запросе, чтобы на выходе запроса сразу получить хронологическое среднее. Идеология 7.7 такова, что запрос является в основном средством получения данных, без их обработки, которая делается в коде.
2. При задании периода "День" (например), результат запроса будет содержать столько строк, сколько дней в периоде и покажет остатки на каждый день. То есть проблемы запросов восьмерки "получения остатков на дни периода, в которых не было движений", в 7.7 нет. Среднее получается элементарной обработкой полученной в запросе таблицы значений: делением итога колонки на число строк.
3. В то же время, есть ситуация, в которых имеет смысл применить прием, описанный в статье:
Если в анализируемом периоде движений заданной номенклатуры существенно меньше числа подпериодов.
Только в этом случае выгоднее использовать группировку "Документ" и метод, рассмотренный в статье.
Так, что с примером для 77 сейчас торопиться не буду.
22. Antony_2009 22.04.12 23:02 Сейчас в теме
Спасибо большое за ответ! Вы правы по пункту 3. Например при расчете средней дебеторской задолженности (не каждый день происходят движения), а на получение остатков на каждый день ооочень много времени уходит в запросе. Чтож, буду пробовать по документам.
23. Модератор раздела 16.06.12 10:04 Сейчас в теме
У меня периодически возникают подобные задачи.
например, http://www.forum.mista.ru/topic.php?id=615738
Есть задача:
есть документ накладная, есть торговый представитель, через которого выполнена заявка и выписана эта накладная,
нужно через СКД получить итоговую таблицу за месяц или несколько месяцев, в которой
строки - ТП (Торговые представители)
колонки - Дни (от 1 до 30/31 - последний день месяца)
ресурс - количество Накладных в день
В итогах за месяц нужно получить среднее число накладных за день по каждому ТП.
Проблема именно с этим последним итогом, не могу придумать, как его подсчитать в СКД.
какие есть предложения?
==
я же количество документов за день также в СКД считаю.
Мне нужно посчитать среднее число по уже вычисленному полю через агрегатную функцию
т.е. что-то типа Среднее(Количество(Различные Регистратор))
СКД не дает сделать этого в чистом виде :(

хочется универсального решения для СКД
периодически возникают подобные задачи, когда нужно посчитать два раза агрегатные функции - типа Среднее(Количество(...)) или Сумма(Количество(...))
==
Что можете подсказать?
24. Модератор раздела 16.06.12 10:08 Сейчас в теме
+(23) За день количество документов получить не проблема, проблема в СКД получить среднее число документов в день в итоге за месяц
например, пусть итоговый период состоит из 2-х дней
и
1 число - 2 документа
2 число - 4 документа
в итоге за период должно быть 3 документа.
как это сделать средствами СКД ?
25. dusha0020 1103 06.06.13 00:38 Сейчас в теме
Да уж. Действительно элементарно, Ватсон! Я бился над проблемой 3 часа, пока не нагуглил статью. Спасибо!
26. truba 07.06.13 17:24 Сейчас в теме
Спешу заметить про 3е ограничение метода. Если в данном примере надобно свернуть номенклатуру по объединяющему реквизиту. К примеру номенклатура1 и номенклатура2 по сути для анализирующего манагера ничем не отличаются, а метод даст движения (и средние) в разрезе каждой номенклатуры. В то время как среднее суммы не равно сумме среднего.
Путано написал, понимаю, но если вы с подобной задачей сталкивались то наверняка меня поняли.
27. dpagon 7 25.09.13 01:40 Сейчас в теме
Спасибо за публикацию! Мне была поставлена аналогичная задача. Применил Ваш метод. Вроде бы есть одна неточность- приведу на примере:

День 1 2 3 Расчет средней
Остатки
1 договор 1000 1000 1000 1000=1000*3(колво дней с остатками)/3(колво дней за период)
2 договор 500 500 0 !!!500=500*3(колво дней с остатками-верно 2 дня)/3(колво дней за период)
3 договор 0 300 300 200=300*2(колво дней с остатками)/3(колво дней за период)

Думаю это из-за того, что и КоличествоКонечныйОстаток, и КоличествоОборот умножаем на РАЗНОСТЬДАТ(Период, &КонецПериода, ДЕНЬ) + 1)

Попробывал
СУММА(ВЫБОР КОГДА Период = &НачалоПериода
ТОГДА КоличествоКонечныйОстаток*(РАЗНОСТЬДАТ(&НачалоПериода, Период, ДЕНЬ) + 1)
ИНАЧЕ КоличествоОборот * (РАЗНОСТЬДАТ(Период, &КонецПериода, ДЕНЬ) + 1))
конец / (РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ) + 1)


Но так вообще не то...
28. Longinoff 50 29.09.13 19:40 Сейчас в теме
29. ildarovich 7846 29.03.14 20:38 Сейчас в теме
Для случая, когда используется метод трапеции, то есть формула расчета среднего учитывает крайние значения с коэффициентом 1/2, данный метод следует слегка изменить. Как это сделать, описано в комментарии http://forum.infostart.ru/forum26/topic45819/message495419/#message495419.
30. zmit 15.06.16 10:11 Сейчас в теме
ildarovich умница. Сколько огромных запросов-расчетов я видел, а это в 3 строки
SunShinne; +1 Ответить
31. yalex9 104 30.11.17 11:42 Сейчас в теме
32. SunShinne 633 28.05.18 12:33 Сейчас в теме
33. user598883_galiev.uralsk 26.05.20 16:45 Сейчас в теме
Если s1 = s0+d1, то получается, что s0 это начальный остаток на первом периоде, d1 оборот, а s1 конечный остаток на первом периоде. Получается по формуле запрос должен быть:

ВЫБРАТЬ
    Номенклатура,
    СУММА(ВЫБОР КОГДА Период = &НачалоПериода ТОГДА КоличествоНачальныйОстаток ИНАЧЕ КоличествоОборот КОНЕЦ * (РАЗНОСТЬДАТ(Период, &КонецПериода, ДЕНЬ) + 1))
    / (РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ) + 1) КАК СреднийЗапас
ИЗ
    РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , )
СГРУППИРОВАТЬ ПО
    Номенклатура


То есть выбираем начальный остаток, а не конечный.
34. tenaxxx 07.08.20 07:37 Сейчас в теме
(33) вы в чем то правы. автору нагляднее было бы последний результат где s0+сумма(1,n) записать в виде s0+d1+сумма(2,n)= s1+сумма(2,n), что по факту и реализовано в запросе
Оставьте свое сообщение