Арифметические операции с плавающей запятой могут давать неточный результат в Excel

Сводка

В этой статье рассматривается, как Microsoft Excel хранит и вычисляет числа с плавающей запятой. Это может повлиять на результаты некоторых чисел или формул из-за округления или усечения данных.

Обзор

Microsoft Excel был разработан на основе спецификации IEEE 754 для определения способа хранения и вычисления чисел с плавающей запятой. IEEE — институт инженеров электротехники и электроники, международный орган, который, помимо прочего, определяет стандарты для компьютерного программного и аппаратного обеспечения. Спецификация 754 — это широко используемая спецификация, которая описывает, как числа с плавающей запятой должны храниться в компьютере, использующем двоичную систему счисления. Она популярна по той причине, что позволяет хранить числа с плавающей запятой в разумных объемах и выполнять вычисления относительно быстро. Стандарт 754 используется в модулях операций с плавающей запятой и арифметических сопроцессорах почти всех современных микропроцессоров на базе ПК, реализующих вычисления с плавающей запятой, включая процессоры Intel, Motorola, Sun и MIPS.

При хранении чисел соответствующее двоичное число может представлять каждое число или дробное число. Например, дробь 1/10 может быть представлена в десятичной системе счисления как 0,1. Однако то же самое число в двоичном формате становится следующим повторяющимся двоично-десятичным числом:

0001100110011100110011 (и т. д.)

Это число можно бесконечно повторять. Это число не может быть представлено в конечном (ограниченном) пространстве. Поэтому при хранении это число округляется примерно на -2.8E-17 в меньшую сторону.

Однако существуют некоторые ограничения спецификации IEEE 754, которые делятся на три общие категории:

• Максимальные/минимальные ограничения
• Точность
• Повторяющиеся двоичные числа

Дополнительная информация

Максимальные/минимальные ограничения

Все компьютеры имеют максимальное и минимальное количество, которое может быть обработано. Поскольку количество битов памяти, в которых хранится число, является конечным, из этого следует, что максимальное или минимальное число, которое может храниться, также является конечным. Максимальное число, которое может храниться в Excel, — 1.79769313486232E+308, а минимальное положительное число — 2.2250738585072E-308.

Случаи, в которых мы придерживаемся стандарта IEEE 754

• Потеря точности. Потеря точности возникает в том случае, если генерируется число, которое слишком мало для представления. В IEEE и Excel результат равен 0 (за исключением того, что в IEEE есть понятие -0, а в Excel — нет).
• Переполнение. Переполнение возникает в том случае, если число слишком велико для представления. Excel использует свое специальное представление для этого случая (#NUM!).

Случаи, в которых мы не придерживаемся стандарта IEEE 754

• Ненормализованные числа. Ненормализованное число содержит 0 в экспоненте. В этом случае все число хранится в мантиссе, которая не имеет неявной ведущей единицы. В результате теряется точность, и чем меньше число, тем больше теряется точность. Числа на малом конце этого диапазона имеют только одну цифру точности.

  Пример: нормализованное число имеет неявную ведущую единицу. Например, если мантисса представляет собой 0011001, нормализованное число становится 10011001 из-за неявной ведущей единицы. Ненормализованное число не имеет неявной ведущей единицы, поэтому в нашем примере ненормализованное число 0011001 не изменяется. В данном случае нормализованное число имеет восемь значащих цифр (10011001), а ненормализованное — пять значащих цифр (11001), причем ведущие нули являются несущественными.

  Ненормализованные числа — это, по сути, обходной путь, позволяющий хранить числа, которые меньше обычного нижнего предела. Корпорация Майкрософт не реализует эту необязательную часть спецификации, поскольку ненормализованные числа по своей природе имеют переменное количество значащих цифр. Это может привести к существенной ошибке в расчетах.

• Положительная/отрицательная бесконечность: бесконечность возникает при делении на 0. Excel не поддерживает бесконечность и выдает ошибку #DIV/0! в таких случаях.

• Не число (NaN): используется для представления недопустимых операций (таких как бесконечность/бесконечность, бесконечность-бесконечность или квадратный корень из -1). NaNs позволяют программе продолжать работу с недопустимой операцией. Вместо этого Excel немедленно выдает ошибку типа #NUM! или #DIV/0!.

Точность

Число с плавающей запятой хранится в двоичном формате в виде трех частей в 65-битном диапазоне: знак, экспонента и мантисса.

Знак хранит знак числа (положительный или отрицательный), экспонента хранит степень 2, в которую возводится или на которую понижается число (максимальная/минимальная степень 2 равна +1.023 и -1.022), а мантисса хранит действительное число. Конечная область хранения мантиссы ограничивает то, насколько близкими могут быть два соседних числа с плавающей запятой (то есть точность).

Мантисса и экспонента хранятся как отдельные компоненты. В результате возможная точность зависит от размера числа (мантиссы), над которым выполняется операция. Хотя Excel может хранить числа от 1.79769313486232E308 до 2.2250738585072E-308, это возможно только в пределах 15 разрядов. Это ограничение является прямым результатом строгого следования спецификации IEEE 754 и не является ограничением Excel. Такой уровень точности встречается и в других программах электронных таблиц.

Числа с плавающей запятой представляются в следующей форме, где экспонента является двоичной экспонентой:

X = дробь * 2^(экспонента — смещение)

Дробь — это нормализованная дробная часть числа, нормализованная по той причине, что экспонента корректируется таким образом, чтобы ведущий бит всегда был равен 1. Таким образом, ее не нужно хранить, и вы получаете еще один бит точности. Вот почему существует неявный бит. Это похоже на экспоненциальное представление, где вы управляете экспонентой, чтобы слева от десятичной запятой располагалась одна цифра; за исключением двоичной системы счисления, вы всегда можете управлять экспонентой так, чтобы первый бит был 1, потому что имеются только 1 и 0.

Смещение — это значение смещения, используемое для предотвращения хранения отрицательных экспонент. Смещение для чисел одинарной точности равно 127 и 1,023 (десятичное) для чисел двойной точности. Excel хранит числа с двойной точностью.

Пример с использованием очень больших чисел

Введите следующие данные в новую рабочую книгу:

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1 

Результирующее значение в ячейке C1 будет 1.2E+200, то же значение, что и в ячейке A1. На самом деле, если сравнить ячейки A1 и C1 с помощью функции IF, например IF(A1=C1), результатом будет TRUE. Это вызвано тем, что спецификация IEEE предусматривает хранение только 15 значащих цифр точности. Для хранения приведенного выше расчета Excel потребуется точность не менее 100 разрядов.

Пример с использованием очень маленьких чисел

Введите следующие данные в новую рабочую книгу:

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

В результате значение в ячейке C1 будет равно 1.00012345678901 вместо 1.000123456789012345. Это вызвано тем, что спецификация IEEE предусматривает хранение только 15 значащих цифр точности. Для хранения приведенного выше расчета Excel потребуется точность не менее 19 разрядов.

Исправление ошибок точности

Excel предлагает два основных метода компенсации ошибок округления: функцию ROUND и параметр в рабочей книге Точность как на экране или Задать точность как на экране.

Метод 1. Функция ROUND

Используя предыдущие данные, в следующем примере функция ROUND используется для приведения числа к пяти цифрам. Это позволяет успешно сравнить результат с другим значением.

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5) 

В результате получается 1.2E+200.

D1: =IF(C1=1.2E+200, TRUE, FALSE)

В результате получается значение TRUE.

Метод 2. Точность как на экране

В некоторых случаях можно предотвратить влияние ошибок округления на работу с помощью параметра Точность как на экране. Этот параметр заставляет значение каждого числа в рабочем листе быть отображаемым значением. Чтобы включить этот параметр, выполните следующие действия.

1. В меню Файл нажмите Параметры, затем выберите категорию Дополнительно.
2. В разделе При пересчете этой книги выберите необходимую книгу, а затем установите флажок Задать точность как на экране.

Например, если выбрать формат чисел, отображающий два знака после запятой, а затем включить параметр Точность как на экране, все знаки кроме двух символов после запятой теряются при сохранении книги. Этот параметр применяется к открытой книге и всем ее листам. Отменить этот параметр для восстановления утерянной информации невозможно. Перед его включением рекомендуется сохранить книгу.

Повторение двоичных чисел и вычислений с результатами, близкими к нулю

Еще одна проблема, возникающая при хранении чисел с плавающей точкой в двоичном формате, связана с тем, что некоторые числа в десятичной системе являются конечными, но становятся бесконечными и повторяющимися в двоичной. Наиболее распространенным примером этого является значение 0,1 и похожие. Несмотря на то, что эти значения могут быть представлены в десятичной системе, такие же значения в двоичном формате становятся следующим повторяющимся двоичным числом при их сохранении в мантиссе:

000110011001100110011 (и т. д.)

Спецификация IEEE 754 не допускает особых ограничений для любого значения. Все, что можно сохранить, хранится в мантиссе, а остальное удаляется. Неточность в таком случае случае составляет около -2.8E-17 или 0,000000000000000028 при хранении.

Даже обычные десятичные дроби, например 0,0001, невозможно точно представить в двоичном формате. (0,0001 — это повторяющаяся двоичная дробь с периодом в 104 бита). Этот явление похоже на невозможность точно представить дробь 1/3 в десятичной системе (повторяющаяся дробь 0.33333333333333333333).

Например, рассмотрим следующий простой пример в Microsoft Visual Basic для приложений:

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

В таком случае в качестве выходных данных будет выведено значение 0,999999999999996. Небольшая неточность в представлении 0,0001 в двоичном формате распространяется на сумму.

Пример: добавление отрицательного числа

1. Введите следующие данные в новую рабочую книгу:

   A1: =(43,1-43,2)+1

2. Щелкните правой кнопкой мыши ячейку A1 и выберите пункт Формат ячеек. На вкладке «Числовые форматы» выберите «Инженерный» в разделе «Категория». Установите значение Знаки после запятой на 15.

Вместо значения 0,9 Excel отображает 0,899999999999999. Так как сначала вычисляется (43,1-43,2), значение -0.1 временно сохраняется и к расчету добавляется неточность, возникшая при хранении -0.1.

Пример, когда значение достигает нуля

1. В Excel 95 или более ранней версии введите в новую книгу следующее:

   A1: =1,333+1,225-1,333-1,225

2. Щелкните правой кнопкой мыши ячейку A1 и выберите пункт Формат ячеек. На вкладке «Числовые форматы» выберите «Инженерный» в разделе «Категория». Установите значение Знаки после запятой на 15.

Вместо 0 Excel 95 отображает -2,22044604925031E-16.

В Excel 97 добавлена оптимизация для устранения этой проблемы. Если в результате операции добавления или вычитания получается значение равно или близко к нулю, Excel 97 и более поздние версии компенсируют неточность, полученную в результате конвертации операнда в двоичную систему и обратно. При выполнении примера, указанного выше, в Excel 97 и более поздней версии в экспоненциальном представлении отображается правильное значение 0 или 0.000000000000000E+00.

Дополнительные сведения о числах с плавающей точкой и спецификации IEEE 754 см. на следующих веб-сайтах:

• https://www.ieee.org
• https://steve.hollasch.net/cgindex/coding/ieeefloat.html