gifts2017

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

Опубликовал Sergey Andreev (starik-2005) в раздел Программирование - Практика программирования

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

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

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

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

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

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

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

 Сумма  Количество   Распределенная сумма
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);

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

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

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

  КонецЦикла

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

 

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

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

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

См. также

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

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

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

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

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

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

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

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

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

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