Проблемы округления в EXCEL

Выясняем, к чему может привести тот факт, что результат вычисления формулы =1,324-1,319 равен 0,00500000000000012, а не 0,005.

Рассмотрим результат вычисления в EXCEL 2007 формулы: =1,324-1,319

Результат равен не 0,005, а 0, 00500000000000012.

Возникает 2 вопроса:

• почему результат равен не 0,005, а 0,00500000000000012 ?
• может ли это привести к ошибкам вычисления ?

Причиной того, что результат вычисления вышеуказанной формулы равен не 0,005, а 0,00500000000000012 в том, что EXCEL хранит и проводит вычисления с числами на основе стандарта IEEE 754 (стандарта двоичной арифметики с плавающей запятой). Этот стандарт предписывает хранить числа с плавающей запятой в двоичном формате. Это означает, что перед тем как значение будет использовано в вычислениях, его необходимо конвертировать в десятичный формат. Проблема в том, что не все числа могут быть абсолютно точно представлены в двоичном формате с плавающей запятой. Например, 0,1 не может быть представлено в виде конечного дробного двоичного числа, т.к. 0,1 соответствует 0,0001100110011 с периодом 0011. Т.к. EXCEL оперирует с точностью до 15 значащих цифр, то округление неизбежно. Хотя точность округления из-за конвертации из двоичного формата примерно 2,8Е-17, но как показывает практика, вполне можно получить вместо 0,005 число 0,00500000000000012, а это в некоторых случаях далеко не одно и тоже.

Приведем еще один пример: =0,29*100-ЦЕЛОЕ(0,29*100)=0

Результат равен ЛОЖЬ, а не ИСТИНА, не как следовало ожидать. Обратите внимание, что формула =0,27*100-ЦЕЛОЕ(0,27*100)=0 возвращает правильный результат (ИСТИНА).

Если переписать формулу в другом виде =0,29*100-ЦЕЛОЕ(0,29*100)-0 , то результат будет -3,5527136788005E-15, что и указывает на источник ошибки (значение, хотя и мало, но совсем не равно 0).

Источник ошибки Представим ситуацию, когда, например, в Условном форматировании , задано правило форматирования для результатов вычислений. Если значение ячейки равно 0, то, результат должен быть выделен красным фоном. В случае формулы =0,27*100-ЦЕЛОЕ(0,27*100) , этого не произойдет, т.к. 0 не равен числу -3,5527136788005E-15 (см. выше).

В итоге легко получить неправильный результат работы Условного форматирования : пользователь может при просмотре таблицы с результатами вычислений легко пропустить нулевое значение.

Вывод : при сравнении результатов вычисления формул с константами никогда не полагайтесь на встроенную в EXCEL точность вычисления .

Лучше использовать следующий подход – задавайте точность округления самостоятельно, прямо в формуле. Для нашего случая это будет выглядеть так: =0,29*100-ЦЕЛОЕ(0,29*100)<0,001

Результат будет всегда ИСТИНА, даже если указать фантастическую точность =0,29*100-ЦЕЛОЕ(0,29*100)<1E-204

Другой подход – использовать явное округление =ЦЕЛОЕ(0,29*100)

Напоследок покажем, что коммутативный закон сложения (сумма не меняется от перестановки её слагаемых) в EXCEL работает не всегда (см. Файл примера ).

Формула =0,29*100-0-ЦЕЛОЕ(0,29*100)

вернет результат =0, а не -3,55Е-15. А ведь мы просто переставили 0 из формулы =0,29*100-ЦЕЛОЕ(0,29*100)-0

Другие примеры:

=0,29*100-0,29-0 (результат -3,5527136788005E-15) =1*(0,5-0,4-0,1) (результат -2,77555756156289E-17)

Весьма вероятно, что таких арифметических операций в природе существует множество.