Вопрос или проблема
Функция MOD в Excel должна возвращать остаток от деления в Excel
Я считаю, что функция =MOD(0.9, 0.3) должна возвращать 0, так как, по сути, 0.3*3 это 0.9
Но она возвращает 5.55112E-17
Есть подсказки по поводу того, в чем дело?
Это не ошибка! Excel использует двойную точность IEEE-754, и, как и любые другие числа с плавающей запятой в двоичной системе, невозможно точно представить десятичные значения, такие как 0.9 или 0.3. Фактические значения просто очень близки к ним,
0.90000000000000002220446049250313080847263336181640625
и
0.299999999999999988897769753748434595763683319091796875 если быть точным.
Результат операции по модулю равен
5.5511151231257827021181583404541015625 × 10-17, что является очень маленьким значением, почти нулем, и Excel отображает его округленным до 5.55112E-17, так что здесь нет ничего неправильного! Почти все языки программирования используют двоичные числа с плавающей запятой и дадут тот же результат для аналогичных операций
Чтобы это исправить, вы можете:
- Изменить число отображаемых десятичных знаков или вручную округлить результат до 1 десятичного знака (так же, как и количество десятичных знаков во входных данных), и Excel правильно покажет 0, или
- Использовать VBA, который имеет свой собственный тип decimal (а также целые типы с высокой точностью). Вы можете выполнять операции в VBA напрямую или писать пользовательские функции для использования в Excel
Повторяющиеся двоичные числа и вычисления, которые дают результаты, близкие к нулю
Еще одна запутанная проблема, влияющая на хранение чисел с плавающей запятой в двоичном формате, заключается в том, что некоторые числа, которые являются конечными, не повторяющимися числами в десятичной системе, являются бесконечными, повторяющимися числами в двоичной системе. Наиболее распространенным примером этого является значение 0.1 и его вариации. Хотя эти числа могут быть идеально представлены в десятичной системе, то же самое число в двоичном формате становится следующим повторяющимся двоичным числом, когда оно хранится в мантиссе:
000110011001100110011 (и так далее)
Спецификация IEEE 754 не делает никаких особых допущений для какого-либо числа. Она хранит то, что может в мантиссе, и обрезает остальное. В результате возникает ошибка около -2.8E-17, или 0.000000000000000028, когда оно сохраняется.
Даже обычные десятичные дроби, такие как десятичное 0.0001, не могут быть точно представлены в двоичном формате. (0.0001 — это повторяющаяся двоичная дробь с периодом 104 бита). Это похоже на то, почему дробь 1/3 не может быть точно представлена в десятичной системе (повторяющийся 0.33333333333333333333).
Дальнейшее чтение
- Арифметика с плавающей запятой может давать неточные результаты в Excel о детальной реализации Excel и способах исправления/обхода проблемы
- Ломается ли арифметика с плавающей запятой? по техническим причинам, влияющим на все приложения, использующие двоичную арифметику с плавающей запятой
- Числовая точность в Microsoft Excel
- Что каждый программист должен знать об арифметике с плавающей запятой
- Что каждый компьютерный ученый должен знать об арифметике с плавающей запятой
Excel использует Visual Basic для своего программирования. Таким образом, Microsoft ссылается на операцию mod как на
Неточность с плавающей запятой
Когда вы работаете с числами с плавающей запятой,
имейте в виду, что они не всегда имеют точное представление в
памяти. Это может привести к неожиданным результатам от некоторых операций,
таких как сравнение значений и оператор Mod.
В моем случае MOD(0.15,0.05) возвращает 0.05, когда должно быть ноль (0.05 входит ровно три раза в 0.15). Это выглядит как ошибка.
Так что я избегаю функции MOD() и вместо этого использую:
num1/num2 - trunc(num1/num2))*num2
округленное до 10 десятичных знаков.
Похоже, что это ошибка с плавающей запятой в Excel
http://www.excelforum.com/excel-formulas-and-functions/927962-mod-function-behaving-badly.html
Ответ или решение
Функция MOD в Excel, выполняющая операцию остатка от деления, может возвращать неожиданные результаты из-за особенностей представления чисел с плавающей запятой (floating-point) в памяти компьютера. Рассмотрим обращение функции =MOD(0.9, 0.3), которое возвращает 5.55112E-17, вместо ожидаемого значения 0.
Причина проблемы:
Excel использует стандарт IEEE-754 для представления чисел с плавающей запятой, что означает, что определенные десятичные числа не могут быть точно представлены в двоичной системе. В данном случае, числа 0.9 и 0.3 представляются как:
- 0.90000000000000002220446049250313080847263336181640625 (фактическое значение 0.9)
- 0.299999999999999988897769753748434595763683319091796875 (фактическое значение 0.3)
Когда вы выполняете операцию MOD с этими числами, результатом является незначительная разница, которая округляется и отображается Excel как 5.55112E-17. Это значение очень близко к нулю, но отличающееся от него, что и вызывает недоумение.
Как исправить проблему:
-
Округление результата: Вы можете округлить результат вычисления, например, до одного знака после запятой. Используйте функцию
ROUND, как показано ниже:=ROUND(MOD(0.9, 0.3), 1) -
Изменение числа отображаемых знаков: В настройках Excel вы можете изменить количество отображаемых знаков после запятой, тем самым скрыв незначительное значение.
-
Использование VBA: Для более точных вычислений можно воспользоваться языком программирования VBA, который поддерживает тип данных
Decimalс более высокой точностью. Вы можете создать пользовательскую функцию для работы с более точными числами.
Function ModuloDecimal(num1 As Decimal, num2 As Decimal) As Decimal
ModuloDecimal = num1 - Int(num1 / num2) * num2
End Function- Использование альтернативных методов: Вы можете воспользоваться альтернативными формулами для вычисления остатка, например:
=num1 - INT(num1 / num2) * num2
Дополнительная информация:
Некоторые дробные значения, такие как 0.1 и 0.3, являются конечными и не повторяющимися в десятичной системе, но превращаются в бесконечные повторяющиеся двоичные числа в двоичной системе. Это приводит к потерям точности, аналогично тому, как дробь 1/3 не может быть точно представлена в десятичной системе (0.333…).
Для более подробной информации о проблемах с числами с плавающей запятой в Excel, вы можете ознакомиться с документацией Microsoft или другими источниками, упомянутыми в обсуждении выше.