Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

14 Декабрь 2017, 01:50 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Часто задаваемые вопросы по LibreOffice и Apache OpenOffice.org
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Автоподбор значения в ячейке  (Прочитано 3727 раз)
0 Пользователей и 1 Гость смотрят эту тему.
kompilainenn
Ветеран
*****
Offline Offline

Сообщений: 2 127



« Стартовое сообщение: 9 Июль 2014, 15:32 »

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

Записан

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
kompilainenn
Ветеран
*****
Offline Offline

Сообщений: 2 127



« Ответ #1: 9 Июль 2014, 15:35 »

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

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
rami
Гуру
*******
Offline Offline

Пол: Мужской
Сообщений: 2 314


MacBook Pro, LibreOffice и Apache OpenOffice


« Ответ #2: 9 Июль 2014, 16:38 »

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

kompilainenn
Ветеран
*****
Offline Offline

Сообщений: 2 127



« Ответ #3: 9 Июль 2014, 17:12 »

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

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
celler
Постоялец
***
Offline Offline

Сообщений: 175


« Ответ #4: 9 Июль 2014, 18:27 »

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

Записан
kompilainenn
Ветеран
*****
Offline Offline

Сообщений: 2 127



« Ответ #5: 9 Июль 2014, 19:18 »

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

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
celler
Постоялец
***
Offline Offline

Сообщений: 175


« Ответ #6: 9 Июль 2014, 19:56 »

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

Сообщений: 175


« Ответ #7: 9 Июль 2014, 21:18 »

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

« Последнее редактирование: 9 Июль 2014, 22:21 от celler » Записан
rami
Гуру
*******
Offline Offline

Пол: Мужской
Сообщений: 2 314


MacBook Pro, LibreOffice и Apache OpenOffice


« Ответ #8: 10 Июль 2014, 13:59 »

поскольку это деньги, рубли и копейки
kompilainenn, если у вас ещё остались деньги, несите сюда, я знаю как честно поделить Смеющийся

* Функция честного округления.ods (79.12 Кб - загружено 8 раз.)
Записан

kompilainenn
Ветеран
*****
Offline Offline

Сообщений: 2 127



« Ответ #9: 10 Июль 2014, 14:49 »

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

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
rami
Гуру
*******
Offline Offline

Пол: Мужской
Сообщений: 2 314


MacBook Pro, LibreOffice и Apache OpenOffice


« Ответ #10: 10 Июль 2014, 15:25 »

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

kompilainenn
Ветеран
*****
Offline Offline

Сообщений: 2 127



« Ответ #11: 10 Июль 2014, 17:01 »

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

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
rami
Гуру
*******
Offline Offline

Пол: Мужской
Сообщений: 2 314


MacBook Pro, LibreOffice и Apache OpenOffice


« Ответ #12: 10 Июль 2014, 17:14 »

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

celler
Постоялец
***
Offline Offline

Сообщений: 175


« Ответ #13: 10 Июль 2014, 17:17 »

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

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!