Автозаполнение от 70 до 80 с шагом 0,1

[DikoSt]

Здравствуйте.
Столкнулся с такой непоняткой:
Хочу заполнить ячейки от 70 до 80 с шагом 0,1, для этого в первой ячейке пишу 70, под ней 70,1 выделяю обе ячейки, тяну в низ и получаю 70, 70,1 70,2 70,3 70,4 70,499999999 70,59999999, там где 71 - 70,999999999. При этом например если взять 60-ть, то всё нормально, с 5, 1, и 40 ещё пробовал всё нормально, пробовал на новом листе, в новом документе результат тот же. при заполнении от 70-ти при 70,5 происходит переход на 70,4999999999 и продолжается до упора.
Визуально в таблице отображается 70,5, а в строке ввода 70,4999999999 и результаты вычисления соответственно с кучей знаков после зяпятой.

Version: 7.0.3.1
Build ID: 00(Build:1)
CPU threads: 4; OS: Linux 5.8; UI render: default; VCL: gtk3
Locale: ru-RU (ru_RU.UTF-8); ИП: ru-RU
Ubuntu package version: 1:7.0.3-0ubuntu0.20.10.1
Calc: threaded

Как бы с этим побороться ?

[economist]

В ячейке A2 пишем =A1+0,1 и протягиваем

[sokol92]

С этим не надо бороться.
Дробные числа, знаменатель которых отличен от степени двойки, не могут быть точно представлены в формате double, который использует Calc (так же как и Excel). К таким числам, безусловно относятся и 70,1 , 70,2 и т.д. Поэтому для отображения дробных чисел следует использовать не общий формат (в котором Calc стремится отобразить как можно больше значащих цифр), а числовой формат с разумным числом знаков после запятой.

Проведите эксперимент (хоть в Excel, хоть в Calc).
Запишите в ячейку A1 70, в A2 формулу =A1+0,1 (как советует коллега).
Запишите в ячейку B1 70, в B2 формулу =$B$1+(СТРОКА(B2)-1)/10
Запишите в ячейку C2 формулу =A2=B2 (должна быть ИСТИНА в Excel, 1 в Calc).

Теперь выделяем ячейки A2:C2 и тянем вниз (строк на 200). С какого-то момента в столбце C появится ЛОЖЬ (0), хотя визуально числа одинаковы.

Также для сравнения дробных чисел не следует использовать знак равенства, а следует использовать конструкцию типа

ABS(A1-B1)<EPS

где EPS - достаточно малое число, подходящее для данного случая. Например, для бухгалтерских расчетов, где суммы задаются с 2 значами после запятой, можно использовать в качестве EPS 0,001.

[eeigor]

Тогда уж
=ROUND(Ссылка+0,1;1)

Off topic
Надо учитывать ошибки округления в формулах/расчётах.
Насколько я знаю, некоторые функции округления (в Python, напр.) округляют не по правилам округления, а вверх/вниз в зависимости от четности/нечетности последней цифры, чтобы уменьшить величину общей ошибки (когда набегают копейки).

[DikoSt]

С этим не надо бороться.
Дробные числа, знаменатель которых отличен от степени двойки, не могут быть точно представлены в формате double, который использует Calc (так же как и Excel). К таким числам, безусловно относятся и 70,1 , 70,2 и т.д. Поэтому для отображения дробных чисел следует использовать не общий формат (в котором Calc стремится отобразить как можно больше значащих цифр), а числовой формат с разумным числом знаков после запятой.

ООО, как раз то что хотел понять. Откуда ноги растут.
Хотя с другой стороны, а чем принципиально отличается, 70,1 и 60,1 ? Вот здесь у меня скорее ворпос, т.е. если проводить автозаполнение начиная от 70, то эта "проблема" имеет место быть, а если скажет от 50 или 60ти, то всё проходит без нареканий. Возможно в представлении и есть определённые трудности, я пока не проверял, но при заполнении от 60-ть визуально всё хорошо выглядит, кравильно как надо, т.е. прибавляется по 0,1 и всё ОК. А вот если начинать от 70-ти, то первые значения нормально, вплоть до 70,4, а далее уже идут 70,499999999. (При заполнении от 60ти, всё четко вплоть до 80ти смотрел 60,5, 60,6 60,7 и т.д.)

В общем, да скорее всего вопрос представления числа. Просто мне случайным образом повезло столкнтуться с 70-тью, а все остальные случайно выбранные числа были не подвержены этой особенности. Сейчас попробовал это проделать с большим количеством чисел от 1 до 70, опишу так, некоторые нормально отображаются, некоторые вот ведут себя выше описанным образом.
С точки зрения вычислений скорее всего проблем не будет, но визуально выглядит это весьма не красиво и загадачно.

[sokol92]

а чем принципиально отличается, 70,1 и 60,1

Ничем. Проведите упомянутый выше эксперимент, заменив 70 на 60. Значение ЛОЖЬ (0 в Calc) все равно появится. Погрешности округления в столбце "A" накапливаются.

Функция Round уменьшает погрешность округления до минимально возможного (в формате double) значения.

Что касается банковского округления (или округления до ближайшего чётного), то оно реализовано в VBA (но не в Star Basic). Функция ROUND (ОКРУГЛ) в формулах Calc и Excel использует математическое округление.

[DikoSt]

Спасибо всем откликнувшимся, вполне емко и доходчиво объяснили напуганному пользователю.

[mikekaganski]

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

[DikoSt]

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

Попробую. Спасибо.

PS: Просто так сложилось, что у меня не получилось выполнить простые мат расчёты, притом не только в calc но и в другой программулине выросли проблемы. И у меня крепкий несходняк результатов получился. Поэтому когда я увидел, что при автозаполнении происходит такая ситуация я прям таки удивился.
PPS: Проверку вычислений проводил в ConsolCalculator, там я ещё не понял как, но получилось что 2-1 =1, а (2-1)=-1, и ещё чуть позже вылезло, что если знаков после запятой 8, то результат 8,6...., а если сократить до 5 знаков, то как положено 86, ...... ( но к данной теме это отношения не имеет).

[kompilainenn]

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

[kompilainenn]

ыыыы, а когда столбец раздвигаешь, то получаешь ОГОНЬ

[mikekaganski]

О! Прелесть. В случае 78, 78.1, ... в результате ситуация ухудшилась начиная с 7.0 по сравнению с версией 6.4. В той версии проблема отображения начиналась с A11, а с версии 7.0 - начиная с A6.

[sokol92]

Господа! Вы находитесь в мире приближенных вычислений - привыкайте к его ландшафтам.  
1/10 - бесконечная периодическая дробь в 2-ичной системе счисления и этот факт нужно принять. В указанных выше примерах мы видим абсолютно приемлемую погрешность. В Excel и др. программах, использующих формат double, всё так же.
Вот еще мнение из более авторитетного (?) источника: Floating-Point Expressions Do Not Compare as Equal

[mikekaganski]

Господа! Вы находитесь в мире приближенных вычислений - привыкайте к его ландшафтам. 
1/10 - бесконечная периодическая дробь в 2-ичной системе счисления и этот факт нужно принять. В указанных выше примерах мы видим абсолютно приемлемую погрешность.

Владимир, Вы не правы. Дело не в периодичности 1/10, а в гораздо большей погрешности, чем погрешность 0.1 в двоичной системе. И эта большая погрешность вылезает из факта, что для вычисления инкремента (в идеале 0.1) производится вычитание двух первых чисел последовательности, забитых пользователем.

Код Выделить Развернуть


double(78.1)      = 78.099999999999994
double(78.1 - 78) =  0.099999999999994316
double(0.1)       =  0.10000000000000001


Погрешность вычисленного инкремента на порядки превышает погрешность "идеального" значения (0.1 в формате IEEE 754 double precision). Естественно - потому что исходные числа на порядки больше этого инкремента. Но вопрос - можно ли улучшить это вычисление? Первый мой коммит (в версии 7.0) явно улучшал результат примерно в 9 раз, а вот второй (в 7.1), как оказалось, ухудшил его в 3 раза (баг 141970). Так что проблема не теоретическая и не бесперспективная.

[sokol92]

Михаил, позвольте с Вами не согласиться. Первое число из Вашего примера имеет относительное отклонение от "точного" результата 6E-15/78.1 и это - правильный порядок.

В эксперименте из #2 для Excel и Calc при каких-то начальных значениях отклонения раньше появляются в Calc, при каких-то в Excel, примерно поровну.