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

Переводы статьи Переводы статьи
Код статьи: 78113 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

В этой статье

Аннотация

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

Обзор

Программа Microsoft Excel была разработана на основе спецификации IEEE 754 относительно хранения и вычисления чисел с плавающей точкой. IEEE — это институт Institute of Electrical and Electronics Engineers, международная организация, которая среди прочего определяет стандарты для оборудования и программного обеспечения для компьютеров. Спецификация 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. В этом случае в мантиссе сохраняется все число, и старший разряд мантиссы не равен 1. В результате теряется точность, и чем меньше число, тем меньше точность. Числа в наименьшем конце этого диапазона имеют только один разряд точности.
    Например: Старший разряд мантиссы нормализованного числа равен 1. Например, если мантисса представляет 0011001, нормированное число принимает значение 10011001 из-за обязательного равенства старшего разряда 1. У ненормалиованного числа старший разряд не обязательно равен единице, поэтому в нашем примере с 0011001 ненормализованное число остается тем же самым. В этом случае нормализованное число имеет восемь значимых цифр (10011001), тогда как ненормализованное число имеет пять значимых цифр (11001), а нули в начале являются незначащими.

    По существу ненормализованные числа являются решением проблемы, позволяя сохранять числа меньше обычного нижнего предела. Корпорация Майкрософт не использует эту дополнительную часть спецификации, поскольку ненормализованные числа по своей природе имеют переменное число значащих цифр. Это может привести к возникновению значительных ошибок в вычислениях.
  • Положительные/отрицательные бесконечные числа. Бесконечные числа возникают при делении на 0. Программы Excel не поддерживает бесконечные числа, и в этих случаях возникает ошибка #DIV/0!.
  • Не число (NaN). NaN используется для представления недопуситмых операций (таких как вычитание или деление бесконечных чисел или квадратный корень из -1). Значения NaN позволяют программе продолжить выполнение после недопустимых операций. Вместо этого программа 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, поскольку используется только 2 числа — 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,000123456789012345, а 1,00012345678901. Это обсуславливается тем, что в соответствие со спецификацией IEEE обеспечивается точность только до 15 значимых цифр. Для сохранения приведенного выше вычисления программе Excel потребуется точность как минимум до 19 цифр.

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

В программе Excel существуют два основных способа исправления ошибок округления: функция ОКРУГЛ и параметры рабочей книги точность как на экране и Задать точность как на экране.

Способ 1. Функция ОКРУГЛ

В следующем примере с указанными выше данными используется функция ОКРУГЛ для создания чисел с пятью знаками. Это позволяет сравнить результат с другим значением.
   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. В Excel 2003 и более ранних версиях выберите Параметры в меню Сервис.
  2. На вкладке Вычисления установите флажок точность как на экране.
  1. В Excel 2007 нажмите кнопку Microsoft Office, выберите Параметры Excel, затем выберите категорию Дополнительно.
  2. В разделе При пересчете этой книги выберите нужную книгу, затем установите флажок точность как на экране.
Например, при выборе формата чисел с отображением двух десятичных разрядов и последующей установки параметра точность как на экране при сохранении книги все данные после двух десятичных разрядов будут потеряны. Этот параметр влияет на все листы всех активных книг. Отмена данного параметра и восстановление утерянных данных невозможны. Перед установкой данного параметра рекомендуется сохранить книгу.

Повторяющиеся двоичные числа и операции с результатами, близкими к нулю

Другой проблемой сохранения чисел с плавающей точкой в двоичном формате является то, что некоторые числа, являющиеся конечными неповторяющимися числами в десятичной системе, становятся бесконечными повторяющимися числами в двоичной системе. Самым распространенным примером является число 0,1 и аналогичные. Хотя эти числа могут быть просто представлены в десятичном формате, при сохранении этого числа в мантиссе в двоичном формате оно становится следующим периодическим двоичным числом:
000110011001100110011 (и т.д.)
В спецификации IEEE 754 отсутствуют какие-либо условия для конкретных чисел; в мантиссе сохраняется все, что можно сохранить, а остальное отсекается. При сохранении это приводит к ошибке около -2,8E-17 или 0,000000000000000028.

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

Именно по этим причинам результатом простого примера в Microsoft Visual Basic for Applications
   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub
будет PRINT 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 в экспоненциальном представлении. Для получения дополнительных сведений щелкните следующие номера статей базы знаний Майкрософт.
172911 Неверный результат возведения числа 10 в очень большую/небольшую степень (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
214373 Неверный результат возведения числа 10 в очень большую/небольшую степень (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Для получения дополнительных сведений о числах с плавающей точкой и спецификации IEEE 754 посетите следующие веб-узлы:
http://www.ieee.org

http://stevehollasch.com/cgindex/coding/ieeefloat.html

Ссылки

Для получения дополнительных сведений об исправлении этих ошибок щелкните следующий номер статьи базы знаний Майкрософт:
214118 Исправление ошибок округления в арифметических операциях с плавающей точкой (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

Свойства

Код статьи: 78113 - Последний отзыв: 13 мая 2010 г. - Revision: 7.0
Информация в данной статье относится к следующим продуктам.
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
Ключевые слова: 
kbinfo KB78113

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com