Распределение суммы по базе

08.11.15

Разработка - Математика и алгоритмы

Или еще одна попытка изобрести велосипед )))

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

Где-то в библии в адрес древних греков...

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

Итак, классика! 

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

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

 Сумма  Количество   Распределенная сумма
100  1  16,(6) * 1 = 16,67
200  2  16,(6) * 2 = 33,33
300  3  16,(6) * 3 =  50
 итого: 600   итого: 6  итого: 100, к = 100/6 = 16,(6) 

Здесь базой является количество, сумма базы = 6, распределяемая сумма = 100. Коэффициент = распределяемая сумма / сумма базы = 100 / 6 = 16,(6) ("Шесть в скобках" - это то, как нас учили записывать периодичские дроби. Если кого-то учили иначе - проьба иметь это ввиду). Далее в каждой строке я округляю результат до копеек.

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

Давайте рассмотрим случай, когда тот парень был к нам не так благосклонен, а именно - давайте распределим 10 на 3:

Сумма  Количество   Распределенная сумма
100  1  3,(3) * 1 = 3,33
200  1  3,(3) * 1 = 3,33 
250  1  3,(3) * 1 = 3,33 - добавим разницу 0,01 = 3,34
 итого: 550   итого: 3  итого: 10? нет! 9,99 + 0,01 = 10, к = 10/3 = 3,(3) 

 

В итоге у нас не хватило одной копейки. Для того, чтобы решить эту проблему, необходимо учесть остаточек в конце. У нас распределенная сумма получилась равна 9,99, а сумма, которую нужно распределить - 10. Разницу, обычно, добавляют к последней строке. Т.е. в последней строке у нас будет 3,34, "чтобы не нарушать отчетности" (с).

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

 № п/п    Сумма    Количество   Распределенная сумма
1 100  1  0,(3) * 1 = 0,33
2 200  1  0,(3) * 1 = 0,33 
3 250  1  0,(3) * 1 = 0,33 - добавим разницу 0,01 = 3,34
... ...  ... ...
29 200 1  0,(3) * 1 = 0,33 
30 100 1  0,(3) * 1 = 0,33 
     итого: 30  итого: 10? нет! 9,90! 

В последней строке в итоге будет сумма 0,33 + 0,10 = 0,43. Если мы распределяем какие-нибудь ксвенные затраты на количество выпуска, то для каждой статьи затрат может набраться весьма большое отклонение, которое все целиком упадет на последнюю строчку. Таким образом продукт, выпущенный нами в последнюю очередь, вберет в свою себестоимость все те отклонения и станет "золотым" )))

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

 

Новое решение!

Давным-давно, кажется в позапрошлую работу, меня попросили создать обработку, которая бы перекраивала контуры полей, перераспределяя на их новую площадь какие-то старые остатки на счетах учета затрат на дату распределения. Там как раз сумма распределялась между новыми площадями пропорционально новому метражу. Звучит пространно, но примите на веру (как древние греки), что это относится к обсуждаемой нами задаче распределения суммы по базе. И тогда я как раз "родил" (ага, прям как Авраам Исаака) алгоритм распределения, после которого нет остатка. Странно, но тогдашний мой руководитель так и не понял суть алгоритма, хотя после теста сказал, что все работает и оставил как есть. Западные программисты в таких случаях просто стараются не использовать подобные алгоритмы, так что честь и хвала программистам российским, которые используют и то, в чем не понимают )))

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

 № п/п    Сумма    Количество   Распределенная сумма   Плавающий коэффициент   По-новому распределенная сумма 
1 100  1  0,(3) * 1 = 0,33  10/30 = 0,(3)  0,33
2 200  1  0,(3) * 1 = 0,33  9,67/29 = 0,333448...   0,33 
3 250  1  0,(3) * 1 = 0,33  9,34/28 = 0,333571...   0,33
... ...  ... ...    
29 200 1  0,(3) * 1 = 0,33   0,67/2 = 0,34   0,34
30 100 1  0,(3) * 1 = 0,33   0,33/1 = 0,33   0,33 
      итого: 30  итого: 9,90    итого: 10

Таким образом у нас больше нет остатка!

Через практическое мессианство! Или перейдем на ты к практике.

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

Процедура РаспределитьСуммуПропорциональноБазе(Таблица, ИмяКолонкиБазы, ИмяКолонкиДляРаспределения, Сумма)

  СуммаБазы = Таблица.Итог(ИмяКолонкиБазы);

  Для каждого СтрокаТаблицы ИЗ Таблица Цикл

    К = Сумма / СуммаБазы;

    СуммаКРаспределению = Окр(СтрокаТаблицы[ИмяКолонкиБазы] * К, 2);

    СтрокаТаблицы[ИмяКолонкиДляРаспределения] = СтрокаТаблицы[ИмяКолонкиДляРаспределения] + СуммаКРаспределению;

    Сумма = Сумма - СуммаКРаспределению;

    СуммаБазы = СуммаБазы - СтрокаТаблицы[ИмяКолонкиБазы]

  КонецЦикла

КонецПроцедуры

 

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

В качестве постскриптума...

Этот алгоритм был навеян мне целочисленным алгоритмом построения линии, т.к. в нем Х распределяется на У (или наоборот - при оптимизации вообще пишут два варианта, учитывая, какое смещение больше - по Х или по У).

распределение

См. также

Метод Дугласа-Пойкера для эффективного хранения метрик

Математика и алгоритмы Платформа 1C v8.2 Конфигурации 1cv8 Россия Абонемент ($m)

На написание данной работы меня вдохновила работа @glassman «Переход на ClickHouse для анализа метрик». Автор анализирует большой объем данных, много миллионов строк, и убедительно доказывает, что ClickHouse справляется лучше PostgreSQL. Я же покажу как можно сократить объем данных в 49.9 раз при этом: 1. Сохранить значения локальных экстремумов 2. Отклонения от реальных значений имеют наперед заданную допустимую погрешность.

1 стартмани

30.01.2024    1757    stopa85    12    

33

Алгоритм симплекс-метода для решения задачи раскроя

Математика и алгоритмы Бесплатно (free)

Разработка алгоритма, построенного на модели симплекс-метода, для нахождения оптимального раскроя.

19.10.2023    4431    user1959478    50    

34

Регулярные выражения на 1С

Математика и алгоритмы Инструментарий разработчика Платформа 1С v8.3 Мобильная платформа Россия Абонемент ($m)

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

1 стартмани

09.06.2023    7471    4    SpaceOfMyHead    17    

56

Модель распределения суммы по базе

Математика и алгоритмы Платформа 1С v8.3 Россия Абонемент ($m)

Обычно под распределением понимают определение сумм пропорционально коэффициентам. Предлагаю включить сюда также распределение по порядку (FIFO, LIFO) и повысить уровень размерности до 2-х. 1-ое означает, что распределение может быть не только пропорциональным, но и по порядку, а 2-ое - это вариант реализации матричного распределения: по строкам и столбцам. Возможно вас заинтересует также необычное решение этой задачи через создание DSL на базе реализации текучего интерфейса

1 стартмани

21.03.2022    7856    7    kalyaka    11    

44

Изменения формата файлов конфигурации (CF) в 8.3.16

Математика и алгоритмы Платформа 1С v8.3 Бесплатно (free)

Дополнение по формату файлов конфигурации (*.cf) в версии 8.3.16.

16.12.2021    4449    fishca    13    

36

Интересная задача на Yandex cup 2021

Математика и алгоритмы Бесплатно (free)

Мое решение задачи на Yandex cup 2021 (frontend). Лабиринт. JavaScript.

12.10.2021    8846    John_d    73    

46

Механизм анализа данных. Кластеризация.

Математика и алгоритмы Анализ учета Платформа 1С v8.3 Анализ и прогнозирование Бесплатно (free)

Подробный разбор, с примером использования, встроенного механизма кластеризации 1С.

31.08.2021    7814    dusha0020    8    

70
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. ildarovich 7850 08.11.15 23:02 Сейчас в теме
Это не новый метод. Много раз видел его в типовых. Кажется, даже чаще, чем "метод последней строки". Вот в этой статье есть подробное исследование на тему сравнения методов: Честное распределение суммы по таблице значений (3.1.4.1) . Приведенный здесь метод - это "Вариант 3". Метод удобный, но не самый точный. Для простоты понимания метод можно назвать рекурсивным. Рекурсия, правда, неявная. Фактически, обработав очередную строку, ту же самую процедуру применяют к оставшейся части суммы и базы.
Также стоит обратить внимание на статью: Пропорциональное распределение в запросе . Решение в запросе любопытно тем, что при попытке уйти от метода "последней строки" мы попадаем на задачу вычисления "нарастающего итога". При большом количестве строк в базе распределения время запроса многократно возрастает.
А вообще вопрос интересный: копеечный, но допускающий множество разнообразных решений.
Somebody1; Rega; Irwin; vv2; cleaner_it; Патриот; zqzq; serge_focus; dnikolaev; starik-2005; Armando; awa; +12 Ответить
3. starik-2005 3033 09.11.15 10:45 Сейчас в теме
(1) ildarovich, а кто ж спорит-то?
5. starik-2005 3033 09.11.15 14:05 Сейчас в теме
(1) ildarovich, кстати, я зашел в указанную статью и описанный метод предлагает дополнительное упорядочивание по базе. В принципе разумный подход.
2. Di_Denis 32 09.11.15 04:16 Сейчас в теме
Я вот так делал при распределении
СтрокаСМаксимальнойОшибкой=Неопределено;
	
	МаксимальнаяОшибка=0;
	
	К=СуммаОплаты/СуммаБазы;
	
	Для каждого Данные Из Выборка Цикл		
		
		СуммаБезОкругления=Данные.База*К;
		
		СуммаСокруглением=Окр(СуммаБезОкругления, 2);
		
		Ошибка=СуммаСокруглением-СуммаБезОкругления;
		
		Ошибка=?(Ошибка<0, -Ошибка, Ошибка);
		
		Если Ошибка>МаксимальнаяОшибка Тогда
			
			СтрокаСМаксимальнойОшибкой=Данные;
			
			МаксимальнаяОшибка=Ошибка;
			
		КонецЕсли;
		
		Данные.Сумма=СуммаСокруглением;
		
	КонецЦикла;
	
	Разница=СуммаОплаты-Выборка.Итог("Сумма");
	
	Если Разница<>0 Тогда
		
		СтрокаСМаксимальнойОшибкой.Сумма=СтрокаСМаксимальнойОшибкой.Сумма+Разница;
		
	КонецЕсли;
Показать
4. starik-2005 3033 09.11.15 10:46 Сейчас в теме
(2) Di_Denis, тоже вариант, просто вычислений больше.
6. Chrizt 264 11.11.15 12:46 Сейчас в теме
рекомендую курс для ИТ-менеджеров в открытом университете, который там сейчас находится в открытом доступе.

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

Процедура РаспределитьСуммуПропорциональноБазе(Таблица, ИмяКолонкиБазы, ИмяКолонкиДляРаспределения, Сумма)

Я несколько язапутался:
  • "ИмяКолонкиБазы" - я так понял, в Вашем случае, это количество, верно?
  • "ИмяКолонкиДляРаспределения" - а это что?
  • "Сумма" - а это сумма, которую необходимо распределить, я правильно понял?

навеян мне целочисленным алгоритмом построения линии

Можно, пожалуйста, ссылку на этот алгоритм? Интересуюсь.
7. starik-2005 3033 11.11.15 12:56 Сейчас в теме
(6) Chrizt, открытый университет "ИНТУИТ". Забыл, что их сейчас как мышей на мельнице...

По поводу колонки - в моем случае именно так, но эта функция универсальная и никто не вправе требовать от Вас распределения именно по количеству, а не по стоимости, например. Главное, чтобы колонка была числового типа.

ИмяКолонкиДляРаспределения - это то, куда мы будем добавлять распределяемую сумму. Она не обязательно будет равна колонке базы.

Сумма - да, именно то, что нужно распределить по базе.

По поводу ссылки - не знаю, существует ли она в природе, но была замечательная книжка, которую я издалека видел классе в 7-м. За пару вечеров тогда, помнится, на основании мельком вычитанных представлений о том, как можно сделать все целочисленными методами, написал на maсro 11 (см в вики, что это) простенький алгоритм. Суть в том, что определяется бОльшее смещение, потом из его половины вычитается меньшее и курсор сдвигается в сторону бОльшено смещения. Если появился флаг знака (вычли так, что стало меньше нуля) - к текущему значению смещения добавляем первоначальное и сдвигаем курсор в сторону меньшего смещения. Ну и каждый раз не забываем ставить точку (включая точку начала линии).
8. gortol 201 13.11.15 12:22 Сейчас в теме
спасибо за алгоритм, я похожим занимался, и на практике приходилось чуть ли не 2м проходом ПРОПОРЦИОНАЛЬНО общему количеству распределять суммы, те не просто первые 3 строчки +1 копейка, а именно если 30 строчек, значит на 10,20,30 строчке +1 копейка, ну как то так...
9. starik-2005 3033 13.11.15 13:01 Сейчас в теме
(8) gortol, да всегда пожалуйста.
10. insurgut 207 16.08.18 13:13 Сейчас в теме
Спасибо за алгоритм, особенно за универсальность процедуры и возможность указания столбцов базы и распределения отдельно :)
11. starik-2005 3033 16.08.18 13:57 Сейчас в теме
(10) да всегда пожалуйста. А по поводу процедуры, то, если я правильно помню, в ней есть некоторая неточность, но пытливому уму оное не проблема;)
12. Gleb_Varlamov 04.10.19 14:59 Сейчас в теме
Сергей, добрый день.

Спасибо за статью, очень интересная. Но как быть, если СуммаКРаспределению = 0 после округления до двух символов после запятой, как в моем случае? Столкнулся на практике с такой ситуацией, что в итоге СуммаКРаспределению равна 0 из-за ситуации, как на скриншоте и распределить не получается.
Прикрепленные файлы:
13. starik-2005 3033 06.10.19 14:34 Сейчас в теме
(12) ну а чем ноль плох? Сумма распределения не уменьшится, а сумма базы уменьшится на столько незначительно, что в этом нет ничего такого. В других строках все встанет на место.
Gleb_Varlamov; +1 Ответить
14. batsy66 61 08.10.19 15:03 Сейчас в теме
(13)
м ноль плох? Сумма распределения не уменьшится, а сумма базы уменьшится на столько незначительно, что в этом нет ничего такого. В других строках все встанет на место.


При распределении незначительного числа, типа 0,01, оно пропадает совсем
15. starik-2005 3033 09.10.19 13:35 Сейчас в теме
(14)
При распределении незначительного числа, типа 0,01, оно пропадает совсем
Точно? Фактически коэффициент для последней строки всегда будет равен единице, т.к. оставшаяся база делится на саму себя. Если у Вас не так, то Вы неверно поняли суть статьи и не смогли на основании сути разработать соответствующую Вашим данным функцию распределения. Код, приведенный в статье, только описывает принцип.
16. batsy66 61 09.10.19 14:07 Сейчас в теме
(15)



Сумма к распределению: 0,01.
Вроде всё правильно посчитал. Уменьшаем с каждым обходом базу и остаток суммы и пересчитываем коэффициент. Либо подскажите, что не так, либо не бросайтесь обвинениями попусту.
Прикрепленные файлы:
Распределени_0,01.xlsx
17. starik-2005 3033 09.10.19 14:59 Сейчас в теме
(16)
подскажите, что не так
Так просто же все. Когда вычитаете из базы распределившуюся сумму - округляйте ее до второго знака. Вот есть у Вас 0,01 как сумма распределения и база 100 000. Первая строка - 50 000, вторая строка 30 000, третья строка 20 000. В итоге распределили на первую строку 0,01 / 100 000 * 50 000 = 0,005. Добавили к ней - получили 50 000,005, но сумма до второго знака у нас, что приводит к тому, что у нас 50 000,01 после округления. Распределенная сумма 0,01, остаток - 0,00. Все. Если же первую сумму распределить 20 000, то 0,01 / 100 000 * 20 000 = 20 000,002, округлили - 20 000,00, вычли из базы, в итоге база 80 000, сумма распределения опять 0,01. Дальше на 30 000, что даст 0,01 / 80 000 * 30 000 = 0,00375, снова округлили - опять ничего не распределили. Дальше база опять уменьшилась и стала 50 000, сумма распределения опять 0,01, сумма в строку последней 50 000, в итоге 0,01 / 50 000 * 50 000 = 0,01, в итоге в последней сроке 50 000,01.

Если же не округлять, то будет 0,01 - 0,005 сумма распределения, 80 000 - сумма базы. дальше 0,005 - 0,001875 сумма распределения, 50 000 - сумма базы.

Вообще, Вы в своей эксельке только сумму полученную округляете. Или ничего не округляйте, или все - иначе ничего не получится.
Прикрепленные файлы:
18. batsy66 61 09.10.19 15:13 Сейчас в теме
(17) Перепроверил расчет, ошибся в таблице. По итогу копейка ушла на первую строку. Округление в итоговой колонке нужно было для имитации поля регистра бухгалтерии (число (15,2)), куда должна сумма упасть
Прикрепленные файлы:
Распределени_0,01.xlsx
19. starik-2005 3033 09.10.19 15:13 Сейчас в теме
(18) прикрепил картинки, как у меня с учетом округления.
20. Adilgeriy 25 08.02.22 02:01 Сейчас в теме
частный случай этого алгоритма если работаем с табличной частью документа и распределять нужно не по всем строкам

ОбщаяСумма = 0;
	Для каждого СтрокаТабличнойЧасти Из Объект.ТаблицаНоменклатуры Цикл
		Если СтрокаТабличнойЧасти.Пометка Тогда
			ОбщаяСумма = ОбщаяСумма + СтрокаТабличнойЧасти[ИмяКолонки];
		КонецЕсли;
	КонецЦикла;

	Если ОбщаяСумма = 0 Тогда
		Сообщить("Общая сумма помеченных строк нулевая! Распределение невозможно.");
		Возврат;
	КонецЕсли;

	// Теперь распределяем
		
	Для каждого СтрокаТабличнойЧасти Из Объект.ТаблицаНоменклатуры Цикл
		
		Если СтрокаТабличнойЧасти.Пометка Тогда
			ЕдиницаРаспределения    = СуммаРаспределенияУменьшаемая / ОбщаяСумма;
			ТекущаяСумма = СтрокаТабличнойЧасти.Сумма;
			Дельта       = Окр(ЕдиницаРаспределения * СтрокаТабличнойЧасти[ИмяКолонки],2);
                        СтрокаТабличнойЧасти.Сумма = ТекущаяСумма + Дельта;
			ОбщаяСумма=ОбщаяСумма-ТекущаяСумма;
			СуммаРаспределенияУменьшаемая= СуммаРаспределенияУменьшаемая-Дельта;
		КонецЕсли;
	КонецЦикла;
Показать
Оставьте свое сообщение