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

Office 365 ProPlus переименован в Майкрософт 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.22507385855072E-308.

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

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

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

Denormalized numbers are basically a workaround to allow numbers smaller than the normal lower limit to be stored. Корпорация Майкрософт не реализует эту необязательная часть спецификации, так как денормализованные номера по своей природе имеют переменное число значительных цифр. Это может позволить значительной ошибке вводить в расчеты.

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

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

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

Знак Экспонент Мантисса 1 бит знака Показатель 11 битов 1 подразумеваемая фракция бита + 52 бита

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

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

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

X = Fraction * 2^(exponent - bias)

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

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

Введите следующее в новую книгу:

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

Введите следующее в новую книгу:

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

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

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

Это приводит к 1.2E+200.

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

Это приводит к значению TRUE.

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

1. В меню File щелкните Параметры, а затем щелкните категорию Advanced .
2. В разделе При вычислении этого раздела книги выберите книгу, которая вам нужна, а затем выберите точность Set в качестве отображаемой шкатулки.

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

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

000110011001100110011 (и так далее)

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

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

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

Это позволит распечатать 0.9999999999996 в качестве вывода. Небольшая ошибка в представлении 0.0001 в двоичном варианте распространяется на сумму.

Введите следующее в новую книгу:

A1: =(43.1-43.2)+1

Щелкните правой кнопкой мыши ячейку A1, а затем щелкните Ячейки формата. На вкладке Номер щелкните Scientific under Category. Установите десятичных мест до 15.

Вместо отображения 0.9, Excel отображает 0.899999999999999999999999999999999999999999999999999999999999999999999999999. Так как (43.1-43.2) вычисляется сначала, -0.1 хранится временно, а ошибка при хранении -0.1 вводится в расчет.

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

A1: =1.333+1.225-1.333-1.225

Щелкните правой кнопкой мыши ячейку A1, а затем щелкните Ячейки формата. На вкладке Номер щелкните Scientific under Category. Установите десятичных мест до 15.

Вместо отображения 0, Excel 95 отображает -2.22044604925031E-16.

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

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