Программисты, объясните., Формулы в экселе. Опции

[AlexandEr]

Вопрос простой. Имеется ячейка в экселе, в которой записана формула =69468,82+3262,85 (в сумме будет 72 731,67). Имеется другая ячейка, в которой записано число в чистом виде 72 731,67. Имеется ячейка в которой записано =ЕСЛИ(Ч(U5513)-Ч(C5513)=0;"";Ч(U5513)-Ч(C5513)). Т.е. если разность первых двух равна 0, то записывать пустую ячейку, если нет, то значение разности.
В результате в ячейке (формат стоит числовой с 2 знаками после запятой) появлеятся значение 0,00. Если увеличить количество знаков после запятой, то увидим истинное значение -0,000000000014551915228366900000. Почему это происходит? Т.е. догадываюсь, что связано это с представлением числа и особенностями (неточностями) операций с плавающей запятой. Но хочется полную ясность иметь.

P.S. подсказывать мне, как получить вместо 0,00 пустую ячейку, не надо.

[firimar]

для всех 3х ячеек задан числовой формат с 2 цифрами после запятой?
мб при выполнении операций он преобразует такие числа в формат с плавающей запятой?

[AlexandEr]

Да, такой формат у всех трех ячеек. Но откуда все равно берутся эти неточности? Сложение ведь не может быть реализовано через какое-нибудь умножение или преобразование.

И те значения в ячейках заданы числами явно. Это не результат каких-нибудь вычислений.

[firimar]

Но откуда все равно берутся эти неточности? Сложение ведь не может быть реализовано через какое-нибудь умножение или преобразование.

Зато в памяти числа представлены в двоичной системе. А конечная дробь в десятичной системе может стать бесконечной при записи в двоичной, т.е. в память запишется на самом деле не то число, которое задали, а близкое к нему. Сейчас разрядная сетка для чисел с плавающей точкой ограничена 16ю десятичными разрядами. У тебя в вычитании максимальный порядок числа равен 5. При вычитании оба числа приводятся к этому порядку, поэтому возможна погрешность в 11м знаке после запятой (т.к. 16-5=11)

так что сравнивать такие числа с помощью отношения равенства на самом деле плохая идея :)

З.Ы. "конечная" в том смысле, что в ее записи начиная с некоторой позиции после запятой стоят одни нули

[AlexandEr]

А значащих разрядов не 15 случаем? Это раз. Почему ошибка представления возникает именно в последнем разряде? И почему тогда разность не 0,000000000010000000000000000000, а 0,00000000001455191522836690000? И зачем в формате предусмотрено 30 знаков после запятой?

[firimar]

А значащих разрядов не 15 случаем? Это раз. Почему ошибка представления возникает именно в последнем разряде? И почему тогда разность не 0,000000000010000000000000000000, а 0,00000000001455191522836690000. и зачем в формате предусмотрено 30 знаков после запятой?

значащих 16
не совсем понятно что ты имеешь в виду под "последним разрядом", при записи числа в память отбрасываются не влезающие хвостовые двоичные разряды
с чего ты решил, что в формате предусмотрено 30 знаков после запятой? там предусмотрено 16 десятичных разрядов на мантиссу плюс сколько-то на порядок числа

[firimar]

И почему тогда разность не 0,000000000010000000000000000000, а 0,00000000001455191522836690000?

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

[Lizard2k]

Вычитание и сложение не должно проявляться в таких погрешностях! Так что ХЗ...

[firimar]

Вычитание и сложение не должно проявляться в таких погрешностях! Так что ХЗ...

сами операции тут не при чём, дело в представлении чисел в памяти

[AlexandEr]

А значащих разрядов не 15 случаем? Это раз. Почему ошибка представления возникает именно в последнем разряде? И почему тогда разность не 0,000000000010000000000000000000, а 0,00000000001455191522836690000. и зачем в формате предусмотрено 30 знаков после запятой?

значащих 16
не совсем понятно что ты имеешь в виду под "последним разрядом", при записи числа в память отбрасываются не влезающие хвостовые двоичные разряды
с чего ты решил, что в формате предусмотрено 30 знаков после запятой? там предусмотрено 16 десятичных разрядов на мантиссу плюс сколько-то на порядок числа

А знак куда записывается? И, чтобы было понятно, ты сможешь по действиям расписать, как все это происходит? Я еще как-то готов понять ошибку в 1 в последнем разряде.
Про 30 знаков - это ограничение эксэла. Вот я и не понимаю, зачем 30 знаков, если только 16 значащих.

[firimar]

А значащих разрядов не 15 случаем? Это раз. Почему ошибка представления возникает именно в последнем разряде? И почему тогда разность не 0,000000000010000000000000000000, а 0,00000000001455191522836690000. и зачем в формате предусмотрено 30 знаков после запятой?

значащих 16
не совсем понятно что ты имеешь в виду под "последним разрядом", при записи числа в память отбрасываются не влезающие хвостовые двоичные разряды
с чего ты решил, что в формате предусмотрено 30 знаков после запятой? там предусмотрено 16 десятичных разрядов на мантиссу плюс сколько-то на порядок числа

А знак куда записывается? И, чтобы было понятно, ты сможешь по действиям расписать, как все это происходит? Я еще как-то готов понять ошибку в 1 в последнем разряде.
Про 30 знаков - это ограничение эксэла. Вот я и не понимаю, зачем 30 знаков, если только 16 значащих.

знак в мантиссу
как происходит в моем понимании:
число 69468.82: m=6.946882 p=4
чтобы записать точное значение m необходимо 54 бита
похоже, на самом деле для мантиссы предусмотрена меньшая область, поэтому не влезающие двоичные разряды отбрасывают, в память записывают ближайшее двоичное число, которое влезает, т.е. не точное значение.
про 30 знаков:
например, число 1.2e-29 = 0.000000000000000000000000000012
знаков после запятой 30, а значащих всего 2

[AlexandEr]

Ты же сам написал 1,2е-29. Тогда причем здесь 0,000...012? Представление есть. Больше 16 знаков не получится задать, или эксел с 17-го все отбросит. Так зачем тогда 30?

И если используется не 54 бита, то логично предположить , что ошибка будет только в последнем разряде, и ошибка это не должна превышать половины от следующего меньшего разряда. Ведь так? Соответственно, в разности ошибка может быть ...00, +/-...05, +/-...10.

[firimar]

Ты же сам написал 1,2е-29. Тогда причем здесь 0,000...012? Представление есть. Больше 16 знаков не получится задать, или эксел с 17-го все отбросит. Так зачем тогда 30?

И если используется не 54 бита, то логично предположить , что ошибка будет только в последнем разряде, и ошибка это не должна превышать половины от следующего меньшего разряда. Ведь так? Соответственно, в разности ошибка может быть ...00, +/-...05, +/-...10.

если я правильно понял, в экселе имеется ввиду 30 знаков после запятой в обычной (неэкспоненциальной) записи числа, т.е. 1e-n является в этом смысле числом с n знаками после запятой.

не понял, хочешь сказать, что ошибка должна принимать только какие-то дискретные значения?

[AlexandEr]

А как иначе? Отсекаем последние разряды. Последний из оставшихся округляется. Если округление происходит по математическим законам, а не строго вверх или вниз, то ошибка не более 0,5 первого отсекаемого разряда.

Т.е. не совсем так. Диапазон ошибки ограничен.

[firimar]

А как иначе? Отсекаем последние разряды. Последний из оставшихся округляется. Если округление происходит по математическим законам, а не строго вверх или вниз, то ошибка не более 0,5 первого отсекаемого разряда.

Т.е. не совсем так. Диапазон ошибки ограничен.

Ошибка, конечно, ограничена. Иначе это все нафиг не надо, если она может быть сколь угодно большой.

[AlexandEr]

Но ограничен-то единицей последнего разряда. Откуда в моем случае такое число?

[firimar]

Но ограничен-то единицей последнего разряда. Откуда в моем случае такое число?

в твоем случае ошибка равна 2^(-36)
видимо, это и есть та единица разряда?

[AlexandEr]

Олег, сколько бит надо, чтобы записать в двоичной системе 15-значное (в десятичной системе) число?
Пусть n. Т.е. любое число в экселе будет записано n-разрядным. Так? Может и не так, но если число записано точно, т.е. размерность числа до 15 знаков и отсечения не происходит, то оно и в двоичную систему будет переведено вполне точным числом. Без отсечений. Ведь так? Оба числа в моем примере имеют размерность до 15 знаков.

Или, например, происходит перевод в двоичную систему с отсеканием каких-то разрядов. Но тогда ведь ошибка вылезет, скорее всего либо в последнем разряде, либо в следующем, но маловероятно, что через 11 нулей.

[firimar]

Ты не путай тёплое с мягким :) в смысле числа целые и числа с плавающей точкой. Для 15значного целого надо не более [2^(log(10^16))+1] бит. Для числа с плавающей точкой все сложнее, можно ли как-то там оценить кол-во бит я не знаю.

После нескольких экспериментов родилась следующая мысль. Возьмём для определённости число в полуинтервале [0.1;1), чтоб не думать сколько лидирующих нулей надо убирать. Для него в память записываются двоичные цифры, соответствующие весам разрядов с 2^(-1) по 2^(-53), т.е. цифры с первой по 53ю после запятой. На 54ю и остальные забиваем, так как 2^(-54) уже меньше машинной точности (10^(-16)), т.е. часть числа просто обрезали (если для его представления нужно более 53х двоичных разрядов после запятой).
Ошибка вылезет около того места, где отсекли.

[firimar]

ой, накосячил )
[log(10^16)+1] бит для целого )