Как и другие электронные таблицы, Microsoft Excel работает только с ограниченной точностью, поскольку сохраняет только определенное количество цифр для описания чисел (имеет ограниченную точность ). За некоторыми исключениями, касающимися ошибочных значений, бесконечностей и денормализованных чисел, Excel выполняет вычисления в формате с плавающей запятой двойной точности из спецификации IEEE 754 [1] (помимо чисел Excel использует несколько других типов данных [2] ). Хотя Excel может отображать 30 десятичных разрядов, его точность для указанного числа ограничена 15 значащими цифрами , а точность вычислений может быть еще меньше из-за пяти проблем: округление , [3] усечение и двоичное хранение , накопление отклонений операндов в вычислениях, и худшее: отмена при вычитаниях соотв. «Катастрофическая отмена» при вычитании значений с аналогичной величиной.
Точность и двоичная память
На верхнем рисунке отображается дробь 1/9000 в Excel. Хотя это число имеет десятичное представление, представляющее собой бесконечную строку единиц, Excel отображает только первые 15 цифр. Во второй строке к дроби добавляется цифра один, и снова Excel отображает только 15 цифр. В третьей строке вычитается единица из суммы с помощью Excel. Поскольку в сумме после десятичной дроби всего одиннадцать единиц, истинная разница при вычитании «1» составляет три нуля, за которыми следует строка из одиннадцати единиц. Однако разница, о которой сообщает Excel, составляет три нуля, за которыми следует 15-значная строка из тринадцати единиц и двух дополнительных ошибочных цифр. Таким образом, числа, которые вычисляет Excel, не являются числами, которые он отображает. Более того, ошибка в ответе Excel - это не просто ошибка округления, это эффект вычислений с плавающей запятой, называемый «отменой».
Погрешность расчетов в Excel сложнее ошибок из-за точности до 15 значащих цифр. Хранение чисел в двоичном формате в Excel также влияет на его точность. [4] Чтобы проиллюстрировать, на нижнем рисунке представлена таблица простого сложения 1 + x - 1 для нескольких значений x . Все значения x начинаются с 15-го десятичного знака, поэтому Excel должен их учитывать. Перед вычислением суммы 1 + x Excel сначала приближает x как двоичное число. Если эта двоичная версия x является простой степенью 2, 15-значное десятичное приближение к x сохраняется в сумме, а два верхних примера на рисунке показывают восстановление x без ошибок. В третьем примере x - более сложное двоичное число, x = 1,1 · 10111 111 × 2 −49 (всего 15 бит). Здесь двойное значение IEEE 754, полученное в результате 15-битного числа, составляет 3,330560653658221E-15, которое округлено! by excel для «пользовательского интерфейса» до 15 цифр 3.33056065365822E-15, а затем отображается с 30 десятичными цифрами, добавляется один «фальшивый ноль», таким образом, «двоичные» и «десятичные» значения в образце идентичны только на дисплее, значения, связанные с ячейками, различны (1,1101111111111100000000000000000000000000000000000000 × 2 −49 против 1,110111111111101111111111111111111111111111111101 × 2 −49 ). То же самое делают и другие электронные таблицы, обрабатывая различное количество десятичных цифр, которые могут быть точно сохранены в 53-битной мантиссе двойного числа (например, 16 цифр между 1 и 8, но только 15 цифр между 0,5 и 1 и между 8 и 10) довольно сложно и решается «неоптимально». В четвертом примере x - это десятичное число, не эквивалентное простому двоичному разряду (хотя оно согласуется с двоичным кодом третьего примера относительно отображаемой точности). Десятичный ввод аппроксимируется двоичным, а затем используется это десятичное число. Эти два средних примера на рисунке показывают, что вносится некоторая ошибка.
Последние два примера иллюстрируют, что происходит, если x - довольно небольшое число. Во втором из последнего примера x = 1,110111 111 × 2 −50 ; Всего 15 бит. Двоичный код очень грубо заменяется одной степенью двойки (в этом примере 2–49 ) и используется ее десятичный эквивалент. В нижнем примере десятичное число, идентичное приведенному выше двоичному с указанной точностью, тем не менее аппроксимируется иначе, чем двоичное, и исключается путем усечения до 15 значащих цифр, не влияя на 1 + x - 1 , что приводит к x = 0 . [5]
Для x ′, которые не являются простыми степенями двойки, заметная ошибка в 1 + x - 1 может возникнуть, даже если x достаточно велик. Например, если x = 1/1000, то 1 + x - 1 = 9,99999999999 89 × 10 −4 , ошибка в 13-й значащей цифре. В этом случае, если бы Excel просто добавлял и вычитал десятичные числа, избегая преобразования в двоичное и обратно в десятичное, ошибки округления не возникало бы, и точность фактически была бы лучше. В Excel есть опция «Установить точность как отображаемую». [6] С этой опцией, в зависимости от обстоятельств, точность может быть лучше или хуже, но вы будете точно знать, что делает Excel. (Однако следует отметить, что сохраняется только выбранная точность, и нельзя восстановить дополнительные цифры, изменив эту опцию.) Некоторые похожие примеры можно найти по этой ссылке. [7]
Короче говоря, разнообразие поведения точности вводится путем комбинации представления числа с ограниченным количеством двоичных цифр, наряду с усечением чисел после пятнадцатой значащей цифры. [8] Обработка в Excel чисел, превышающих 15 значащих цифр, иногда способствует большей точности последних нескольких значащих цифр в вычислении, чем прямая работа только с 15 значащими цифрами, а иногда и нет.
Обоснование преобразования в двоичное представление и обратно в десятичное, а также дополнительные сведения о точности в Excel и VBA см. По этим ссылкам. [9]
1. Недостатки в задачах «= 1 + x - 1» - это комбинация «слабых сторон fp-math» и «как excel справляется с этим», особенно округления excel. Excel выполняет округление и / или «привязку к нулю» для большинства своих результатов, в среднем отбрасывая последние 3 бита двойного представления IEEE. Это поведение можно отключить, задав формулу в скобках: '= (1 + 2 ^ -52-1)'. Вы увидите, что даже эта небольшая ценность сохраняется. Меньшие значения исчезнут, поскольку для представления значения имеется только 53 бита, в этом случае 1.0000000000 0000000000 0000000000 0000000000 0000000000 01, первый представляет «1», а последний - «2 ^ -52».
2. Это не только чистые степени двух выживших, но и любая комбинация значений, состоящая из битов, которые будут в пределах 53 бит после добавления десятичной единицы. Поскольку у большинства десятичных значений нет чистого конечного представления в двоичном формате, они будут страдать от «округления» и «отмены» в задачах, подобных приведенным выше.
Например, десятичное число 0,1 имеет двойное представление IEEE 0 (1). 1001100110011001100110011001100110011001100110011010 умножить на 2 ^ -4, а добавление к 140737488355328.0 (то есть 2 ^ 47) потеряет все его биты, кроме первых двух. Таким образом, из '= (140737488355328.0 + 0,1 - 140737488355328.0) он вернется как 0,09375, рассчитанный с помощью www.weitz.de/ieee (64 бит), а также в Excel с круглыми скобками вокруг формулы. Этим эффектом в основном можно управлять с помощью значимого округления, которое в Excel не применяется, это зависит от пользователя.
Излишне говорить: другие электронные таблицы имеют аналогичные проблемы, LibreOffice Calc использует более агрессивное округление, в то время как gnumeric пытается сохранить точность и сделать так же точность, как и «отсутствие», видимым для пользователя.
Примеры, в которых точность не является показателем точности
Статистические функции
Точность функций, предоставляемых Excel, может быть проблемой. Мика Альтман и др. представьте этот пример: [10] Стандартное отклонение совокупности, определяемое по формуле:
математически эквивалентно:
Однако первая форма сохраняет лучшую числовую точность для больших значений x , поскольку квадраты разностей между x и x av приводят к меньшему округлению, чем различия между гораздо большими числами Σx 2 и (Σx) 2 . Однако встроенная функция Excel STDEVP () использует менее точную формулировку, поскольку она быстрее в вычислительном отношении. [11]
И функция «совместимости» STDEVP, и функция «согласованности» STDEV.P в Excel 2010 возвращают стандартное отклонение генеральной совокупности 0,5 для данного набора значений. Однако числовую неточность все же можно показать на этом примере, расширив существующее число до 10 15 , после чего ошибочное стандартное отклонение, обнаруженное Excel 2010, будет равно нулю.
Вычитание результатов вычитания
Выполнение простых вычитаний может привести к ошибкам, поскольку две ячейки могут отображать одно и то же числовое значение при сохранении двух отдельных значений. Пример этого происходит на листе, где для следующих ячеек заданы следующие числовые значения:
и следующие ячейки содержат следующие формулы
Обе клетки а также отображать . Однако если ячейка содержит формулу тогда не отображает как и следовало ожидать, но отображает вместо.
(выше не ограничивается вычитаниями, попробуйте '= 1 + 1,405 * 2 ^ -48' в одной ячейке, excel округляет отображение до 1,00000000000000000000 и '= 0,9 + 225179982494413 * 2 ^ -51' в другой, тот же дисплей (в диапазоне выше 1 / ниже 1 округление отличается, что затрагивает большинство изменений десятичной или двоичной величины). выше, различное округление для значения и отображения нарушает одно из элементарных требований Голдберга: `` Что должен делать каждый компьютерный ученый '' Знайте об арифметике с плавающей запятой » (более или менее« священная книга »fp-math), там сказано:« Важно убедиться, что ее использование прозрачно для пользователя. Например, на калькуляторе, если внутреннее представление отображаемого значения не округляется до той же точности, что и отображение, тогда результат дальнейших операций будет зависеть от скрытых цифр и будет казаться непредсказуемым для пользователя '(проблема не ограничивается Excel, например, LibreOffice calc действует аналогично ).
Ошибка округления
Пользовательские вычисления должны быть тщательно организованы, чтобы ошибка округления не стала проблемой. Пример возникает при решении квадратного уравнения :
Решения (корни) этого уравнения точно определяются квадратной формулой :
Когда один из этих корней очень велик по сравнению с другим, то есть когда квадратный корень близок к значению b , оценка корня, соответствующего вычитанию двух членов, становится очень неточной из-за округления (отмены? ).
Ошибку округления можно определить, используя формулу ряда Тейлора для квадратного корня: [12]
Вследствие этого,
что указывает на то, что по мере увеличения b первый оставшийся член, скажем, ε:
становится все меньше и меньше. Числа для b и квадратного корня становятся почти одинаковыми, и разница становится небольшой:
В этих условиях все значащие цифры выражают b . Например, если точность составляет 15 цифр, и эти два числа, b и квадратный корень, совпадают с 15 цифрами, разница будет равна нулю, а не разнице ε.
Лучшую точность можно получить с помощью другого подхода, описанного ниже. [13] Если мы обозначим два корня через r 1 и r 2 , квадратное уравнение можно записать:
Когда корень r 1 >> r 2 , сумма ( r 1 + r 2 ) ≈ r 1 и сравнение двух форм приблизительно показывает:
пока
Таким образом, находим примерный вид:
Эти результаты не подвержены ошибке округления, но они не точны, если b 2 не велико по сравнению с ac .
Суть в том, что при выполнении этого расчета с использованием Excel, поскольку корни становятся все дальше друг от друга по величине, метод расчета должен будет переключиться с прямого вычисления квадратной формулы на какой-либо другой метод, чтобы ограничить ошибку округления. Точка переключения методов зависит от размера коэффициентов a и b .
На рисунке Excel используется для нахождения наименьшего корня квадратного уравнения x 2 + bx + c = 0 для c = 4 и c = 4 × 10 5 . Разница между прямой оценкой с использованием квадратичной формулы и приближением, описанным выше для широко разнесенных корней, отображается в зависимости от b . Первоначально разница между методами уменьшается, потому что метод широко разнесенного корня становится более точным при больших значениях b . Однако за пределами некоторого b- значения разница увеличивается, потому что квадратичная формула (подходит для меньших b- значений) становится хуже из-за округления, в то время как метод широко разнесенных корней (хороший для больших b- значений) продолжает улучшаться. Точка переключения методов обозначена большими точками и больше для больших значений c . При больших значениях b наклонная вверх кривая представляет собой ошибку округления Excel в квадратной формуле, неустойчивое поведение которой приводит к изгибу кривых.
Другая область, где точность является проблемой, - это область численного вычисления интегралов и решения дифференциальных уравнений . Примерами являются правило Симпсона , метод Рунге – Кутта и алгоритм Нумерова для уравнения Шредингера . [14] Используя Visual Basic для приложений, любой из этих методов можно реализовать в Excel. В численных методах используется сетка, в которой вычисляются функции. Функции могут быть интерполированы между точками сетки или экстраполированы для определения местоположения соседних точек сетки. Эти формулы включают сравнение соседних значений. Если сетка разнесена очень мелко, произойдет ошибка округления, и чем меньше используется точность, тем хуже ошибка округления. При большом расстоянии пострадает точность. Если числовая процедура рассматривается как система обратной связи , этот вычислительный шум можно рассматривать как сигнал, который подается в систему, что приведет к нестабильности, если система не будет тщательно спроектирована. [15]
Точность в VBA
Хотя Excel номинально по умолчанию работает с 8-байтовыми числами, VBA имеет множество типов данных. Тип данных Double составляет 8 байтов, тип данных Integer - 2 байта, а 16-байтовый тип данных Variant общего назначения может быть преобразован в 12-байтовый тип данных Decimal с помощью функции преобразования VBA CDec . [16] Выбор типов переменных при вычислении VBA требует учета требований к памяти, точности и скорости.
Рекомендации
- ^ «Арифметика с плавающей запятой может давать неточные результаты в Excel» . Редакция 8.2; Идентификатор статьи: 78113 . Поддержка Microsoft. 30 июня 2010 . Проверено 2 июля 2010 . CS1 maint: обескураженный параметр ( ссылка )
- ^ Стив Далтон (2007). «Таблица 2.3: Типы данных рабочего листа и ограничения». Финансовые приложения с использованием разработки надстроек Excel на C / C ++ (2-е изд.). Вайли. С. 13–14. ISBN 0-470-02797-5.
- ^ Округление - это потеря точности при вычитании чисел, различающихся на небольшую величину. Поскольку каждое число состоит только из пятнадцати значащих цифр, их разница неточна, если не хватает значащих цифр, чтобы выразить разницу.
- ^ Роберт де Леви (2004). «Алгоритмическая точность». Расширенный Excel для анализа научных данных . Издательство Оксфордского университета. п. 44. ISBN 0-19-515275-1.
- ^ Чтобы ввести число как двоичное, оно представляется в виде строки из степеней двойки: 2 ^ (- 50) * (2 ^ 0 + 2 ^ −1 + ⋯). Чтобы ввести число как десятичное, десятичное число вводится напрямую.
- ^ Этот параметр находится на вкладке «Параметры Excel / Дополнительно». См. Как исправить ошибки округления: метод 2
- ^ Странность сложения Excel
- ^ Роберт де Леви (2004). цитировал работу . С. 45–46. ISBN 0-19-515275-1.
- ^ Точность в Excel:
- Арифметика с плавающей запятой может дать неточные результаты : подробное объяснение с примерами последствий двоичного / 15-значного хранения.
- Почему Excel дает неправильные ответы? Архивировано 30 марта 2010 г. на Wayback Machine : Еще одно подробное обсуждение с примерами и некоторыми исправлениями.
- Что каждый компьютерный ученый должен знать о числах с плавающей запятой. Сосредоточен на примерах представления чисел с плавающей запятой.
- Визуальная базовая и арифметическая точность : ориентирована на VBA, который работает несколько иначе.
- Бернар В. Лиенгме (2008). «Математические ограничения Excel». Руководство по Microsoft Excel 2007 для ученых и инженеров . Академическая пресса. п. 31 сл . ISBN 0-12-374623-X.
- ^ Мика Альтман; Джефф Гилл; Майкл Макдональд (2004). «§2.1.1 Показательный пример: вычисление стандартного отклонения коэффициента». Численные вопросы в статистических вычислениях для социологов . Wiley-IEEE. п. 12. ISBN 0-471-23633-0.
- ^ Роберт де Леви (2004). Расширенный Excel для анализа научных данных . Издательство Оксфордского университета. С. 45–46. ISBN 0-19-515275-1.
- ^ Градштейн, Израиль Соломонович ; Рыжик Иосиф Моисеевич ; Геронимус Юрий Вениаминович ; Цейтлин Михаил Юльевич ; Джеффри, Алан (2015 г.) [октябрь 2014 г.]. «1.112. Силовой ряд». В Цвиллингере, Даниэль; Молл, Виктор Гюго (ред.). Таблица интегралов, серий и продуктов . Перевод Scripta Technica, Inc. (8-е изд.). Academic Press, Inc. стр. 25. ISBN 0-12-384933-0. LCCN 2014010276 .
- ^ Этот приближенный метод часто используется при проектировании усилителей с обратной связью, где два корня представляют время отклика системы. См. Статью о ступенчатом отклике .
- ^ Андерс Блом Компьютерные алгоритмы для решения уравнений Шредингера и Пуассона , Департамент физики, Лундский университет, 2002.
- ^ Р. У. Хэмминг (1986). Численные методы для ученых и инженеров (2-е изд.). Courier Dover Publications. ISBN 0-486-65241-6.В этой книге подробно обсуждаются округление, усечение и стабильность. Например, см. Глава 21: Неопределенные интегралы - обратная связь , стр. 357.
- ^ Джон Уокенбах (2010). «Определение типов данных». Программирование Excel 2010 Power с помощью VBA . Вайли. pp. 198 и далее и таблица 8-1. ISBN 0-470-47535-8.