Поделиться через

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

  • Применяется к: Excel 2010, Excel 2013, Excel for Microsoft 365, Microsoft Excel for Mac 2011, Excel for Mac for Microsoft 365

Итоги

В этой статье рассматривается, как 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-битном диапазоне: знак, экспонента и мантисса.

Знак Экспонента Мантисса
1 бит знака 11-битная экспонента 1 неявный бит + 52-битная дробь

Знак хранит знак числа (положительный или отрицательный), экспонента хранит степень 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. В разделе При пересчете этой книги выберите необходимую книгу, а затем установите флажок Задать точность как на экране.

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

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

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

000110011001100110011 (и т. д.)

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

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

Например, рассмотрим следующий пример в 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 см. на следующих веб-сайтах: