Страницы: 1
RSS
Пропорциональное распределение числа на группу чисел, Плывут цифры после запятой
 
Здравствуйте Уважаемые форумчане.

Помогите пожалуйста, с решением вопроса. Поиском по форуму искал. Есть близкие темы, но для моего примера сопоставить не смог.

Имеем общий вес брутто 5000,000 кг. Его нужно распределить пропорционально ячейкам нетто в столбце "А" с 3 знаками после запятой,
Нужно поучить распределение которое в сумме будет равно точно исходному брутто т.е. 5000,000 кг. и эти значения можно было бы скопировать в новую колонку или другую программу.
Через формат ячейки округление до 3-х знаков не подходит, т.к. при вставке значения в новый столбец или Word при суммировании не выходим на итоговое.
Пример во вложении.

Спасибо.
 
Как вариант распределить "недостающее" по первым позициям списка. По 1 грамму, до тех пор пока не получится ровно. Но решение с доп.столбцом.
 
evgen032 Спасибо.

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

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

Может как то программно можно при помощи VBA?
 
Цитата
aSeSja написал:
Тогда уже лучше распределять в позиции с большими значениями
Используйте сортировку
Цитата
aSeSja написал:
Итоговая сумма при распределении может быть ка меньше так и больше.
Поправил
 
evgen032, спасибо.

Но такой вариант не подходит. Сортировка и добавление дополнительных ячеек не допустима. Таблицы в которых это нужно делать имеют более 30 столбцов с различными данными. Я просто для примера скопировал один столбец с весом нетто из этой таблицы.

Нужен другой подход. Лучше всего VBA.

Пишу программу и пока зациклился на этапе этого на первый взгляд несложного распределения.
 
Вариант распределения во вложении, прост в реализации, дает определенную погрешность в зависимости от исходных данных
Можно конечно реализовать метод Хейера-Нимейера, чтобы минимизировать погрешность: http://www.excelworld.ru/forum/2-6852-1
Изменено: MCH - 22.10.2019 06:54:49
 
MCH, Огромное спасибо. Это примерно то что я искал. Если погрешность будет, думаю она не составит больше 1-2 грамм.

Найду разницу с исходной суммой и раскидаю её по максимальным значениям.

Осталось реализовать эту формулу на VBA как одну из частей общей программы.
 
Если устроит, то можно такой формулой считать:
Код
=ОКРУГЛ(5000/$B$2*A3;3)+ЕСЛИ(ABS(ОКРУГЛ((5000-СУММПРОИЗВ(ОКРУГЛ(5000*Ч(+A$2:A$204)/B$2;3)))*1000;))>=СТРОКА(E1);ЗНАК(5000-СУММПРОИЗВ(ОКРУГЛ(5000*Ч(+A$2:A$204)/B$2;3)))/1000)
А если использовать такую формулу:
Код
=ОКРУГЛ(5000/$B$2*A3+ЕСЛИ(ABS(ОКРУГЛ((5000-СУММПРОИЗВ(ОКРУГЛ(5000*Ч(+A$2:A$204)/B$2;3)))*1000;))>СУММПРОИЗВ(Ч((A3+СТРОКА(A3)%%%<A$3:A$204+СТРОКА($3:$204)%%%)));ЗНАК(5000-СУММПРОИЗВ(ОКРУГЛ(5000*Ч(+A$2:A$204)/B$2;3)))/1000);3)
то ошибка округления будет корректирваться у самых больших чисел, поэтому относительная погрешность будет ниже.
Формулы неоптимизированные. Возможно, есть более короткое решение.
*Для меньших чисел формула такая:
Код
=ОКРУГЛ(5000/$B$2*A3+ЕСЛИ(ABS(ОКРУГЛ((5000-СУММПРОИЗВ(ОКРУГЛ(5000*Ч(+A$2:A$204)/B$2;3)))*1000;))>=СУММПРОИЗВ(Ч((A3+СТРОКА(A3)%%%>=A$3:A$204+СТРОКА($3:$204)%%%)));ЗНАК(5000-СУММПРОИЗВ(ОКРУГЛ(5000*Ч(+A$2:A$204)/B$2;3)))/1000);3)
Формулы исправил.
Изменено: Светлый - 22.10.2019 19:27:10
 
Цитата
MCH написал:
дает определенную погрешность в зависимости от исходных данных
Немного лучше формула будет выглядеть так:
Код
=ОКРУГЛ(5000*СУММ(A$2:A3)/B$2-СУММ(D$2:D2);3)
Не возникает погрешность вычитания. Убирает цифры после третьего знака.
 
Ребята, вы Гении мысли и Excel!

Светлый, MCH, не подскажете как проще реализовать данную формулу на VBA "=ОКРУГЛ(5000*СУММ(A$2:A3)/B$2-СУММ(D$2:D2);3)"

Это будет часть моего программного кода для общей программы. Через FormulaR1C1 сложно получается.

Спасибо.
 
Справка. Метод из #6 (он же в #9) называется методом скользящего (нарастающего) округления, используется в бухгалтерском учете как минимум со времен Великих географических открытий.
Владимир
Страницы: 1
Наверх