Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Потеря точности некоторых чисел
 
#1
05.11.2020 13:02:10
Добрый день!
Заметил необычное поведение Excel при вводе цифр в диапазоне 33000-65000 со значениями "0,098" или "0,848" после запятой. При вводе они превращаются в 33000,0979999999 и 33000,8479999999. Проблема возникает на двух компьютерах в двух независимых фирмах. У меня стоит Excel 2013.
На данный момент вышел из этой ситуации, применив текстовый формат к ячейкам.
Есть идеи, отчего возникает такая ошибка округления и как ее устранить?
Изменено: FearDog - 05.11.2020 13:02:45
 
 
 
#2
05.11.2020 13:11:28
Это точно при ручном вводе?
Стандарт IEEE 754, параграф 9
 
 
 
#3
05.11.2020 13:11:35
Наберите в поиске по сайту "Приближенные вычисления".
Владимир
 
 
 
#4
05.11.2020 13:17:08
Посмотрите статьи на тему "точности операций над числами с плавающей точкой/запятой", например:
https://docs.microsoft.com/ru-ru/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu...
https://habr.com/ru/post/322984/

Эта тема неоднократно поднималась и на этом форуме - поищите в архиве, если интересно:
Если Вам требуются результаты с точностью до двух (или подставьте конкретное число) знаков после запятой, то используйте функции ОКРУГЛ() и пр.
Если же Вас смущают исключительно некорректные представления числе на экране/на бумаге, то можно обойтись просто корректировкой форматов  
 
 
 
#5
05.11.2020 13:33:35
Цитата
vikttur написал:
Это точно при ручном вводе?
А вы попробуйте)
Цитата
vikttur написал:
Стандарт IEEE 754 , параграф 9
Интересный документ, почитаю. Сам программирую и давно отказался от форматов single и double в выражениях, где потери копеек недопустимы.
Цитата
sokol92 написал:
Наберите в поиске по сайту "Приближенные вычисления".
Про вычисления с ошибкой я давно знаю. Знаю также что в память числа с плавающей точкой изначально записываются с погрешностью. Но тут, вроде как, введено вручную, и такой фокус)
 
 
 
#6
05.11.2020 13:37:55
FearDog, здравствуйте!
У меня Excel 2016 и проблема подтверждается. Спасибо за баг - при вводе такого не видел ещё…
Изменено: Jack Famous - 05.11.2020 13:38:01
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#7
05.11.2020 13:53:44
Цитата
IKor написал:
Если же Вас смущают исключительно некорректные представления числе на экране/на бумаге, то можно обойтись просто корректировкой форматов  
Да, я просто перевел ячейки в текстовый формат и все ок. Работаю с Excel уже лет 15 и такой проблемы при вводе не встречал, только при вычислениях.
На экране и печати все ок, всех устраивает, видно только в редакторе формул. Старому другу директор стал задавать вопросы, типа "Зачем ты всякую ерунду в ячейках пишешь? Откуда эти цифры?" и т.д.. Он обратился ко мне и я неплохо так озадачился =)
Подумал, вдруг это настройками правится, но не нашел подходящих, решил здесь спросить.  
Изменено: FearDog - 05.11.2020 13:54:57
 
 
 
#8
05.11.2020 14:18:18
Цитата
Jack Famous написал:
Спасибо за баг - при вводе такого не видел ещё
Здесь нет никакого бага. Расхождение в 16-й значащей цифре не является багом.

Вы хотите увидеть в ячейке 33000,098. Это число не может быть точно представлено в формате double. Вместо этого Вам Excel показывает 33000,0979999999
Разница между указанными двумя значениями вполне соответствует точности представления чисел в формате double.
Если хотите видеть числа в "человеческом" виде, используйте "числовой" формат.
Изменено: sokol92 - 05.11.2020 14:26:07
Владимир
 
 
 
#9
05.11.2020 14:25:51
sokol92, приветствую, Владимр!
Мы уже с вами обсуждали этот момент и сошлись на том, что вы судите с технико-юридической точки зрения, согласно которой как раз  "расхождение в 16-й значащей цифре не является багом", а я сужу с пользовательско-практической точки зрения, которая подсказывает, что, если я ввожу число 33000,098, то именно его я хочу увидеть в ячейке, а не "33000,0979999999"  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#10
05.11.2020 14:28:04
Цитата
Jack Famous написал:
33000,098, то именно его я хочу увидеть в ячейке, а не "33000,0979999999"
ну воспринимайте разницу , как отчисления в Microsoft.  :D
По вопросам из тем форума, личку не читаю.
 
 
 
#11
05.11.2020 14:29:22
Что при вводе, что при вычислениях Стандарт IEEE 754 един.
Цитата
FearDog написал:
Сам программирую и давно отказался от форматов single и double в выражениях, где потери копеек недопустимы.
есть пример вычислений, где 15 разрядов после запятой не хватает для правильного расчета копеек (2х разрядов)? т.е. погрешность при вычислении в 13 разрядов.
Даже если вы считаете милиарды, 5-6 знаков после запятой (10-9 на целое число) вполне достаточно для расчета копеек (или я заблуждаюсь)?
Цитата
Jack Famous написал:
что, если я ввожу число 33000,098, то именно его я хочу увидеть в ячейке, а не "33000,0979999999"  
Привет, поставь формат ячейки с 2 знаками после запятой и будет тебе желаемое )
Изменено: bedvit - 05.11.2020 14:34:20
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#12
05.11.2020 14:30:58
Тут проблема в том, что "баг" - это недокументированная ошибка программы, которую разработчики пропустили. А тут указанные погрешности в числах с плавающей запятой прекрасно и подробно документированы...
Так что с точки зрения пользователя ничего не остается как смириться, т.к. фиг чего с этим сделаешь.
Цитата
Jack Famous написал:
при вводе такого не видел ещё
это тянется еще с 2003 Excel - помню подобную проблему еще там. Таких чисел вообще много. Например вот 37869,598 или вот 57719,473
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#13
05.11.2020 14:40:37
В период гиперинфляции в РФ при расчете балансовой (первоначальной) стоимости основных средств на крупных предприятиях формат double давал погрешность в копейках. Правда, можно было вести учет стоимости ОС в целых рублях.
Владимир
 
 
 
#14
05.11.2020 14:40:57
Цитата
bedvit: Что при вводе, что при вычислениях Стандарт IEEE 754 един
привет)) ну как сказать: смотри, я же могу ввести в ячейку число "0,1" и оно будет выглядеть, как "0,1". Но, если я напишу =10,8-10,7, то результат будет с хвостом. То есть, отобразить "0,1" Excel может, но в данном случае, при вычислении получается не свовсем он.
С числами же типа "33000,098" другая история - Excel не может даже просто отобразить их (не как результат вычисления)
Цитата
bedvit: поставь формат ячейки
думаешь, я не знаю этого "волшебства"?)))
Цитата
Дмитрий(The_Prist) Щербаков: "баг" - это недокументированная ошибка программы, которую разработчики пропустили
привет, Дим)) согласен - не баг это, а задокументированная "слабость"  :D
Изменено: Jack Famous - 05.11.2020 14:41:57
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#15
05.11.2020 15:02:07
Цитата
sokol92 написал:
формат double давал погрешность в копейках.
Владимир, и каким форматом пользовались? Decimal?
Цитата
Jack Famous написал:
число "0,1" и оно будет выглядеть, как "0,1". Но, если я напишу =10,8-10,7, то результат будет с хвостом. То есть, отобразить "0,1" Excel может, но в данном случае, при вычислении получается не свовсем он.С числами же типа "33000,098" другая история
Алексей, это все одна и та же история. Просто нужно внимательно прочитать формат Стандарт IEEE и понять, почему так происходит.
Комп работает с двоичным представлением данных/числа/чего угодно. Поэтому некоторые десятичные значения привычные для нас, в двоичном представлении это бесконечная дробь. С бесконечностями работать не умеет, да и памяти столько нет, и т.к.в double всего 64 бита имеем погрешность расчета. Так же как и например 1/3=0,333333(3) в десятичной системе, вы же округляете при расчете?
Цитата
Jack Famous написал:
я не знаю этого "волшебства"?)))
это не волшебство, а решение твоего вопроса (чем не устраивает?)
Изменено: bedvit - 05.11.2020 15:06:05
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#16
05.11.2020 15:10:10
Дело как раз в том, что задача программиста спрятать эти "документированные" проблемы подальше от пользовательских глаз))) Почему Excel это не учли, непонятно. с 0,1 же все ок =)
Цитата
Jack Famous написал:
смотри, я же могу ввести в ячейку число "0,1" и оно будет выглядеть, как "0,1". Но, если я напишу =10,8-10,7, то результат будет с хвостом. То есть, отобразить "0,1" Excel может, но в данном случае, при вычислении получается не совсем он. С числами же типа "33000,098" другая история - Excel не может даже просто отобразить их (не как результат вычисления)
Было много ситуаций в написании и макросов, и программ, когда я сначала пытался доказать, что что-либо невозможно, а потом брал и делал, учитывая все подводные камни =)
Цитата
FearDog написал:
"Зачем ты всякую ерунду в ячейках пишешь? Откуда эти цифры?" и т.д.
Порой сложно Директору объяснить, "зачем и почему", ему нужно "чтобы красиво было" =)

Или отправляешь такой смету контрагенту, а он: "Чего-то вы тут мухлюете, округляете как-то странно, обмануть нас хотите!?!?"

Да и разговоры про потерю точности, бла бла бла... - все фигня. Можно спокойно написать класс, который будет корректно выполнять операции с точностью свыше 100 знаков после запятой (конечно с небольшими потерями производительности, но на дворе не 89 год). Тут MS видимо поленились, при том что прослеживается определенная зависимость=)
Изменено: FearDog - 05.11.2020 15:17:45
 
 
 
#17
05.11.2020 15:14:56
Цитата
bedvit написал:
и каким форматом пользовались
Oracle и другие "большие" базы имеют для чисел собственный формат (тип NUMBER Oracle обеспечивает 33 "точных" десятичных цифры). Если кроме double иных поддерживаемых форматов в базах не было - считали в целых рублях. Кроме того, для минимизации ошибок округления при суммировании больших массивов данных умножали предварительно суммы в копейках на 100 и округляли до целого; при записи результата в базы данных - делили на 100. Голь на выдумки...  :)

Кстати, в VBA есть тип Currency, который не дает ошибок округления при суммировании чисел, имеющих не более 4 знаков после десятичной запятой.
Изменено: sokol92 - 05.11.2020 15:19:23
Владимир
 
 
 
#18
05.11.2020 15:15:04
Цитата
FearDog написал:
Почему Excel это не учли, непонятно. с 0,1 же все ок =)
FearDog, чем не устраивает применить формат ячейки с двумя знаками после запятой? (все проблемы для директора решены)

Цитата
sokol92 написал:
умножали предварительно суммы на 100 и округляли до целого; при записи результата в базы данных - делили на 100.
Владимир, при одинаковой размере числа, запись дробного и целого числа, получалось не тоже самое?
Изменено: bedvit - 05.11.2020 15:18:30
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#19
05.11.2020 15:19:39
Цитата
bedvit написал:
FearDog , чем не устраивает применить формат ячейки с двумя знаками после запятой? (все проблемы для директора решены)
В редакторе формул видно. Текстовый формат все решает.
Проблемы то, в общем-то, уже нет. Так, дискуссия...
Изменено: FearDog - 05.11.2020 15:19:46
 
 
 
#20
05.11.2020 15:21:52
Дробные числа даже с одним десятичным знаком (отличном от 5) имеют погрешность представления в формате double (в отличие от целых, имеющих не более 15 цифр).
Изменено: sokol92 - 05.11.2020 15:25:15
Владимир
 
 
 
#21
05.11.2020 15:31:42
sokol92, Владимир, Excel не согласен с тобой
=10,8-10,7                    (Excel) = 0,100000000000001
=(10,8*10-10,7*10)/10  (Excel) = 0,100000000000000
Или это не корректный пример?
Изменено: bedvit - 05.11.2020 15:52:11
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#22
05.11.2020 15:36:10
Цитата
FearDog написал:
В редакторе формул видно.
Если Директор достаточно грамотен для того, чтобы заглянуть в редактор формул, то вероятно ему можно на пальцах объяснить причины и следствия этой особенности MS Excel
Тем более, что его всегда можно попросить лично внести в ячейку число 33000,098
:)
 
 
 
#23
05.11.2020 15:52:01
Цитата
bedvit написал:
Владимир, Excel не согласен с тобой
Другой алгоритм:
1. 10,8 * 100=1080 (и округляем до целого). Аналогично 10,7 -> 1070.
2. 1080-1070=10.
3. Результат 10 / 100 (представим в double с минимально возможной погрешностью).

Сейчас напишу UDF - функцию, аналогичную Sum, которая без погрешности суммирует дробные числа, содержащие до 4 знаков после запятой.

Код
Option Explicit
Function ExactSum(ParamArray args()) As Double
  Dim result As Currency, arg, v
  On Error Resume Next
  
  For Each arg In args
    If IsObject(arg) Then
      arg = arg.Value
    End If
    If IsArray(arg) Then
      For Each v In arg
        result = result + CCur(v)
      Next v
    Else
      result = result + CCur(arg)
    End If
  Next arg
  
  On Error GoTo 0
  ExactSum = result
End Function
Изменено: sokol92 - 05.11.2020 16:11:33
Владимир
 
 
 
#24
05.11.2020 16:02:48
Цитата
sokol92 написал:
Другой алгоритм:
формат-то все равно double. Вопрос в дробной части, в ней вся погрешность этого формата. Decimal без этой проблемы, но им пользоваться можно с некоторыми особенностями (VBA).
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#25
05.11.2020 16:09:11
Цитата
bedvit написал:
Вопрос в дробной части
После умножения суммы в копейках на 100 и округления до целого погрешности нет. В функции из #23 используется тот же метод (с учетом специфики внутреннего представления типа Currency).
Владимир
 
 
 
#26
05.11.2020 16:39:19
Цитата
bedvit: это не волшебство, а решение твоего вопроса (чем не устраивает?)
это не решение, а костыль. Да, разумеется, я бы так и сделал для красивой картинки в отчёте, но мне не нравится, что мелкомягкие просто "задокументировали" собственное бессилие и типа всё ок
Цитата
FearDog: Можно спокойно написать класс, который будет корректно выполнять операции с точностью свыше 100 знаков после запятой
вот именно. И хочется, чтобы в таком глобальном продукте этот вопрос был решён, а то стыдоба ей-богу
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#27
05.11.2020 17:50:33
Цитата
FearDog написал:
Можно спокойно написать класс, который будет корректно выполнять операции с точностью свыше 100 знаков после запятой
да давно уже написан. Вот и я ранее приобщался (любое количество символов насколько хватит оперативки, для Excel - сколько влезет в ячейку 32 тыс.)
Но кому нужна такая точность?
Цитата
Jack Famous написал:
это не решение, а костыль.
Это не костыль, это особенность работы чисел с плавающей точкой.
а какие есть альтернативы? (кроме форматов с копейками, которые считаются через целые, примерно так же, как выше Владимир оформил)
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#28
05.11.2020 20:41:29
Цитата
bedvit: какие есть альтернативы?
в вычислениях встраивать в движок округление до 14-15 знаков, если по-другому стандарт не позволяет или умножение на 10^ максимальное количество знаков после запятой у участвующих членов, расчёт с целыми и последующее деление на тот же коэффициент, как Владимир выше показывал.
При вводе: я ХЗ, как исправить то, что Excel даже не может корректно отобразить ручной ввод :D

Основной посыл очень здорово сформулировал Дима:
Хочется верить, что хоть когда-нибудь описанную особенность стандарта IEEE754 Microsoft сможет победить или хотя бы сделать заплатку, которая будет производить простые вычисления не хуже 50-рублевого калькулятора  :)
К этому теперь можно добавить "и отображать числа так, как они были введены"  ;)
Изменено: Jack Famous - 05.11.2020 20:44:58
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#29
05.11.2020 22:54:35
Вариант с округлением звучит логично, в моей библе так и реализовано, считаю на один знак больше и округляю под заданную точность (вернее так считает MPIR/GMP). Почему Excel не округляет на 15м знаке, не осведомлен, возможно дело в производительности, наличия для этого функций или формата ячеек.
Цитата
Jack Famous написал:
IEEE754 Microsoft сможет победить
для меня не ясен этот момент, стандарт международный, реализован и на аппаратном уровне (в т.ч. в вашем ЦП). Мягкотелые на него не влияют. Возможно вопрос в округлении в ячейках Excel, тогда да, согласен, это было бы логично.
Изменено: bedvit - 05.11.2020 22:56:50
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#30
06.11.2020 11:51:26
Excel 2010
При вводе 33000,098 в ячейке так и отображается (при разрядности 3-9 знаков после запятой), в строке формул отображается 33000,0979999999.
При суммировании двух ячеек со значениями 33000,098 получаем 66000,196, так что проблемы нет, смущает только отображение в строке формул.
Изменено: Михаил - 06.11.2020 11:57:07
 
 
 
Страницы: 1 2 След.
Наверх