не правильно считается разность в Excel – как исправить?

MS Office профессиональный плюс 2010
Версия 14.0.0629.5000 (64 бит)

Примеры:

8,03–7,461 = 0,568999999999999
8,03-7,462 = 0,568000000000000
8,03–7,463 = 0,566999999999999
8,03–7,464 = 0,565999999999999
8,03-7,465 = 0,5650000000000000
8,03–7,466 = 0,563999999999999
8,03-7,467 = 0,5630000000000000
8,03–7,468 = 0,561999999999999
8,03–7,469 = 0,560999999999999
8,03–7,471 = 0,558999999999999
8,03–7,472 = 0,557999999999999
8,03–7,473 = 0,556999999999999
8,03–7,474 = 0,555999999999999
8,03-7,475 = 0,5550000000000000
8,03–7,476 = 0,553999999999999
8,03–7,477 = 0,552999999999999
8,03-7,478 = 0,5520000000000000
8.03-7.479 = 0.550999999999999 Ответ

Привет.

давно известно, что калькулятор более точен, чем Excel, когда дело касается нецелых чисел. Вот почему это калькулятор для подсчета, а Excel – это электронная таблица, которая может делать гораздо больше, чем калькулятор.

Фактический источник ошибки – ошибка округления. Эта ошибка вызвана недостатком арифметики с плавающей запятой и тем фактом, что очень часто десятичные дроби являются повторяющимися дробями в двоичной системе счисления. Такие числа не могут быть представлены конечным числом битов. В связи с этим округление текста не всегда является точным, поскольку большинство компьютеров выбирают последние цифры дробной части на основе ближайшего эквивалента (с наименьшей разницей). Некоторые компьютеры не округляют, а просто обрезают (отключают) последние биты, генерируя результирующую ошибку, правильно называемую ошибкой округления (в отличие от ошибки усечения, когда расширение ряда усекается). Для получения дополнительной информации см. Введение в численные методы Питера А. Старка, Macmillian Company, 1970

.. Я готов объяснить, почему 3,1-3 = 0,1 и 4,1-4 <0,1 В основе проблемы лежит как раз в особенностях работы с плавающей запятой. Нравится: Для начала, вот формат с плавающей запятой двойной точности (который используется в Excel). И для простоты «забудьте», что байты идут снизу вверх. Вот как выглядит +1:
+1: 0 011 11111111 0000 00000000 00000000 00000000 00000000 00000000 00000000
Первый «0» – это бит знака: 0 – «+», 1 – “-“
Таким образом, группа из 11 бит – это порядок номера. Он увеличен (для этого типа формата) на 3ffh, чтобы было удобно отображать отрицательные показатели. То есть на самом деле порядок +1 равен 0, что и понятно.
Не очень понятно, как интерпретировать 52 идущих подряд нуля. Здесь надо сказать, что в формате чисел с плавающей запятой используется так называемая «нормализованная» запись чисел, то есть когда значащий бит целой части мантиссы не равен нулю – в общем случае или равно «1» в двоичной системе счисления; соответственно изменяется и показатель степени. Но если самый старший бит всегда равен «1», то зачем его хранить? Он не хранится для экономии места, он просто подразумевает:
1.0000 00000000 00000000 00000000 00000000 00000000 00000000 = 1.0e0
Теперь посмотрим, как выглядят другие наши «подопытные”:
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10011010
(обратите внимание на биты последнего байта, выделенные жирным шрифтом, они будут играть ключевую роль позже!)
+3.1: 0100 00000000 1000 11001100 11001100 11001100 11001100 11001100 11001101
+3.0: 0100 00000000 1000 00000000 00000000 00000000 00000000 00000000 00000000
Вычтите одно число из другого, и вы получите (порядок не участвует в вычитании, только мантисса и подразумеваемая «единица):
+3.1: 1.1000 11001100 11001100 11001100 11001100 11001100 11001101
+3.0: 1.1000 00000000 00000000 00000000 00000000 00000000 00000000
———————————————————————————
+0.1: 0.00000 11001100 11001100 11001100 11001100 11001100 11001101
Вся часть мантиссы оказалась равной нулю, поэтому проведем нормализацию: сдвинем ее влево на 5 позиций, добавив нули вправо (!), И, соответственно, уменьшим порядок на 5 – было 400h, стало 3fbh:
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10100000
Будьте осторожны, число дано дробным, на «1» на 47-м месте, но больше 0,1, поэтому, когда мы свернем, мы увидим то, что нам нужно, в ячейке Excel!

То же делаем и с 4.1-4.0 (но без дополнительных комментариев):
+4.1: 0100 00000001 0000 01100110 01100110 01100110 01100110 01100110 01100110
+4.0: 0100 00000001 0000 00000000 00000000 00000000 00000000 00000000 00000000
———————————————————————————
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10000000
В 47-м бите стоит «0» и, следовательно, мы получили меньше 0,1, что, честно говоря, показывает нам Excel с большой битовой глубиной. Почему он не ведет себя так же честно, когда переусердствует? И присмотритесь: в чем отличие от “эталонной” 0.1? Во втором случае, в первом ему просто не хватает точности.

Если все это проделать с последующими номерами, будет то же самое, только хуже

Так что здесь проблема не в Excel, а в двоичной арифметике.

Чтобы Excel посчитал «как на калькуляторе» (то есть округляет промежуточные вычисления до двух десятичных знаков и только затем добавляет их), вы можете использовать один из следующих методов:
1. Для вычитания используйте формулу в ячейке C1: = ОКРУГЛ (A1-B1; 3)
2. Чтобы сложить, используйте формулу окончательной суммы в ячейке C1: = СУММПРОИЗВ (ОКРУГЛ (A1: B1; 3))
3 установите «Точность, как на экране» в параметрах Excel (не рекомендуется)

Источник: https://answers.microsoft.com/ru-ru/msoffice/forum/all/not/3499f119-c4ff-428d-9010-02a82105d0b7