Автоподбор значения в ячейке

[kompilainenn]

Есть некое число в ячейке С1, оно должно быть пропорционально поделено между несколькими ячейками ниже на основании данных из соседней колонки В. Вопрос в том, что из-за округления сумма поделенных пропорционально чисел не сходится с изначальным числом (см.пример во вложении). Так вот, можно ли каким-либо образом подогнать одно из поделенных чисел (скажем в ячейке С62) так, чтобы сумма сошлась (должно быть С1=С64)? Проблема возникает в принципе когда большие суммы (десятки миллионов) и большое количество площадей (столбец B). Есть решение?

[kompilainenn]

возникла дурная идея сделать просто в ячейке С62 формулу =С1-sum(C3:C61), но как-то это неэлегантно...

[rami]

Непонятно в чём проблема? Вроде всё правильно. При правильном округлении (а комп округляет правильно) абсолютная погрешность не должна превышать половины последнего разряда, а сумма погрешностей должна равняться примерно половине предпоследнего разряда. В вашем примере так и есть: максимальная погрешность ±0.005, а сумма погрешностей 0.06
Сделайте три знака после запятой и всё сойдётся.

[kompilainenn]

проблема в том, что должно быть два знака, поскольку это деньги, рубли и копейки

[celler]

А вот с такой хитростью не пойдёт?

[kompilainenn]

А вот с такой хитростью не пойдёт?

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

[celler]

Ну у нас на заправках цена бензина всегда с точностью до третьего знака, например 1,529 евро за литр, но сдачу всегда дают цельными центами
А вот пусть в B3:B62 будет везде 30,  а в C1 пусть будет 10000, тогда все значения будут одинаковы - 166,67, но в итоге набежит лишних 20 копеек. Если округлять в меньшую сторону до 166,66, то будет в итоге не хватать 40 копеек. Если одно из значений уменьшить на 20 копеек, то всё сойдётся, но ведь кто-то недополучит 20 копеек. По моему, единственный способ сделать как можно более равномерно - высчитать суммы при округлении в меньшую сторону и в большую сторону и по их соотношению с исходной суммой, округлить соответствующее количество результатов в меньшую сторону, а остальные - в большую. Тогда наибольшая разница в идеальном случае между всеми результатами будет одна копейка.

[celler]

Вот попробовал сделать пример реализации вышеописанного.

[rami]

поскольку это деньги, рубли и копейки

kompilainenn, если у вас ещё остались деньги, несите сюда, я знаю как честно поделить

[kompilainenn]

2 rami: опишите словами механизм подгона, я в макрос не лезу, потому что ни фига там не пойму

[rami]

Это пользовательская функция массива. Первый аргумент ячейка с числом, которое надо поделить, второй аргумент диапазон ячеек с пропорциями раздела. Функцию надо объявить как функцию массива, тогда она сама растягивается на нужное количество ячеек.
Механизм округления:
Банковское округление (англ. banker's rounding) — округление для этого случая происходит к ближайшему чётному, то есть 2,5 → 2, 3,5 → 4.
Округление «до ближайшего чётного» исходит из предположения, что при большом числе округляемых значений, имеющих 0,5 в округляемом остатке, в среднем половина из них окажется слева, а половина — справа от ближайшего чётного, таким образом, ошибки округления взаимно погасятся. Строго говоря, предположение это верно лишь тогда, когда набор округляемых чисел обладает свойствами случайного ряда, что обычно верно в бухгалтерских приложениях, где речь идёт о ценах, суммах на счетах и так далее. Если же предположение будет нарушено, то и округление «до чётного» может приводить к систематическим ошибкам. Для таких случаев лучше работают два следующих метода.(Подробности в Вики "Округление")
Макрос рассчитывает пропорции в рублях($,€), умножает  на 100 (копейки, центы), округляет и снова возвращает в рубли (делит на 100.

[kompilainenn]

н-да, только вчера читал где-то спор про нужность математики программисту. ладно, всем спасибо, будем считать, что вопрос решен

[rami]

А зачем программисту математика? Компьютер всегда под рукой, он и посчитает . Дворнику математика нужнее: нашёл копеечку, надо посчитать.
А как результаты "полевых испытаний" ?

[celler]

kompilainenn, но может и другим пригодится.
Я вчера не успел написать объяснения к формуле в файле к предыдущему моему посту. Там высчитывается два варианта суммы, какая у Вас стоит в ячейке C64 - одна при условии, что во всех ячейках C3:C62 произведено округление в меньшую сторону, а вторая - что все они округлены в большую сторону. По этим данным высчитывается их пропорция, которая указывает, сколько данных нужно округлить в меньшую сторону и сколько - в большую. А затем просто данное количество ячеек, начиная с первой, округляется в одну сторону, а остальные в другую. В результате каждое значение отличается от идеального не более чем на одну сотую, или копейку, а их сумма тоже должна отличаться от исходного значения не более чем на одну сотую. Единственно, на что следует обратить внимание, что я для определения номера строки в массиве использовал определение её номера в таблице, от которого отнимал двойку, поскольку первая строка находится в третьей строчке. Поэтому если эти формулы переносить куда-нибудь выше или ниже, то нужно эту двойку исправить на другое число или сделать это по другому.