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

Автор kompilainenn, 9 июля 2014, 16:32

0 Пользователи и 1 гость просматривают эту тему.

kompilainenn

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

kompilainenn

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

rami

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

kompilainenn

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

celler


kompilainenn

Цитата: celler от  9 июля 2014, 18:27А вот с такой хитростью не пойдёт?
знаете, как-то считать с тремя знаками после запятой, а показывать только два - это как-то не по-бухгалтерски, все же это деньги...неужели нету способа автоподбора? видимо придется-таки по-тупому просто в последнюю ячейку ставить разницу, так хоть видно будет откуда что=(
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

celler

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

celler

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

rami

Цитата: kompilainenn от  9 июля 2014, 18:12
поскольку это деньги, рубли и копейки
kompilainenn, если у вас ещё остались деньги, несите сюда, я знаю как честно поделить ;D

kompilainenn

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

rami

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

kompilainenn

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

rami

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

celler

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