Числовая точность в Microsoft Excel

Как и другие электронные таблицы, Microsoft Excel работает только с ограниченной точностью, поскольку для описания чисел он сохраняет только определенное количество цифр (он имеет ограниченную точность ). За некоторыми исключениями, касающимися ошибочных значений, бесконечностей и денормализованных чисел, Excel выполняет вычисления в формате с плавающей запятой двойной точности из спецификации IEEE 754. (кроме чисел Excel использует еще несколько типов данных ). Хотя Excel позволяет отображать до 30 знаков после запятой, его точность для любого конкретного числа составляет не более 15 значащих цифр , а точность вычислений может быть еще меньшей из-за пяти проблем: округление , усечение и двоичное хранение , накопление отклонений операндов в вычислениях и самое худшее: отмена при вычитаниях соотв. «Катастрофическая отмена» при вычитании значений одинаковой величины.

Точность и двоичное хранение

Excel поддерживает 15 цифр в своих числах, но они не всегда точны; математически нижняя строка должна быть такой же, как верхняя, в 'fp-math' шаг '1 + 1/9000' приводит к округлению вверх, поскольку первый бит 14-битного хвоста '10111000110010' падающей мантиссы за пределами таблицы при добавлении 1 получается «1», это округление не отменяется при повторном вычитании 1, поскольку на этом этапе нет информации о происхождении значений. Таким образом, при «повторном вычитании» 1 остается мантисса, оканчивающаяся на «100000000000000» вместо «010111000110010», что представляет собой значение «1,1111111111117289E-4», округленное Excel до 15 значащих цифр: «1,1111111111173E-4».

Конечно, математическое 1 + x − 1 = x , «математика с плавающей запятой» иногда немного отличается, и в этом нельзя винить Excel. Несоответствие указывает на ошибку. Все ошибки находятся за пределами 15-й значащей цифры промежуточного значения 1+x, все ошибки находятся в старших цифрах конечного результата, что является проблематичным эффектом «отмены».

На верхнем рисунке отображена дробь 1/9000 в Excel. Хотя это число имеет десятичное представление, представляющее собой бесконечную строку единиц, Excel отображает только первые 15 цифр. Во второй строке к дроби добавляется цифра один, и Excel снова отображает только 15 цифр. В третьей строке из суммы вычитается единица с помощью Excel. Поскольку в сумме есть только одиннадцать единиц после запятой, истинная разница при вычитании «1» составляет три нуля, за которыми следует строка из одиннадцати единиц. Однако разница, о которой сообщает Excel, составляет три 0, за которыми следует 15-значная строка из тринадцати единиц и двух дополнительных ошибочных цифр. Таким образом, числа, которые рассчитывает Excel, не являются числами, которые он отображает. Более того, ошибка в ответе Excel — это не просто ошибка округления, это эффект в вычислениях с плавающей запятой, называемый «отменой».

Неточности в расчетах Excel сложнее ошибок из-за точности до 15 значащих цифр. Хранение чисел в двоичном формате Excel также влияет на его точность. Для иллюстрации на нижнем рисунке представлено простое сложение 1 + x − 1 для нескольких значений x . Все значения x начинаются с 15-го десятичного знака, поэтому Excel должен их учитывать. Прежде чем вычислить сумму 1 + x , Excel сначала аппроксимирует x как двоичное число. Если эта двоичная версия x представляет собой простую степень 2, в сумме сохраняется 15-значное десятичное приближение к x , а два верхних примера рисунка указывают на восстановление x без ошибок. В третьем примере x — более сложное двоичное число, x = 1,110111⋯111 × 2. ⁻⁴⁹ (всего 15 бит). Здесь «двойное значение IEEE 754», полученное в результате 15-битного числа, равно 3,330560653658221E-15, которое округляется Excel для «пользовательского интерфейса» до 15 цифр 3,33056065365822E-15, а затем отображается с 30 десятичными цифрами и получает одну «поддельную» цифру. добавлен ноль, поэтому «двоичные» и «десятичные» значения в образце идентичны только при отображении, значения, связанные с ячейками, разные (1,1101111111111100000000000000000000000000000000000000 × 2 ⁻⁴⁹ против 1,1101111111111011111111111111111111111111111111111101 × 2 ⁻⁴⁹ ). Аналогичное делается и в других электронных таблицах, обрабатывающих различное количество десятичных цифр, которые могут быть точно сохранены в 53-битной мантиссе «двойного числа» (например, 16 цифр между 1 и 8, но только 15 между ⁠ 1 / 2 ⁠ и 1 и между 8 и 10) довольно сложно и решается «неоптимально». В четвертом примере x — десятичное число, не эквивалентное простому двоичному числу (хотя оно соответствует двоичному числу из третьего примера с отображаемой точностью). Десятичный ввод аппроксимируется двоичным числом, а затем это используется десятичное число. Эти два средних примера на рисунке показывают, что имеется некоторая ошибка.

Последние два примера иллюстрируют, что произойдет, если x — довольно маленькое число. Во втором из последнего примера x = 1,110111⋯111×2. ⁻⁵⁰ ; Всего 15 бит. Двоичный код очень грубо заменяется одинарной степенью 2 (в этом примере 2 ⁻⁴⁹ ) и используется его десятичный эквивалент. В нижнем примере десятичная дробь, идентичная двоичной дроби выше с показанной точностью, тем не менее аппроксимируется иначе, чем двоичная, и исключается путем усечения до 15 значащих цифр, не внося вклада в 1 + x − 1 , что приводит к x = 0. .

Для x , которые не являются простыми степенями 2, заметная ошибка в 1 + x − 1 может возникнуть, даже если x довольно велико. Например, если х = ⁠ 1/1000 × = ⁠ , тогда 1 + − 1 9,99999999999 89 x 10 ⁻⁴ , ошибка в 13-й значащей цифре. В этом случае, если Excel просто складывает и вычитает десятичные числа, избегая преобразования в двоичный формат и обратно в десятичный, ошибки округления не произойдет, и точность действительно будет лучше. В Excel есть опция «Установить отображаемую точность». При этом варианте, в зависимости от обстоятельств, точность может оказаться лучше или хуже, но вы будете точно знать, что делает Excel. (Сохраняется только выбранная точность, и невозможно восстановить дополнительные цифры, изменив эту опцию.) Некоторые подобные примеры можно найти по этой ссылке.

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

Причины преобразования в двоичное представление и обратно в десятичное, а также более подробную информацию о точности в Excel и VBA можно найти по этим ссылкам.

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 бит после добавления десятичной 1. Поскольку большинство десятичных значений не имеют четкого конечного представления в двоичном виде, они будут страдать от «округления» и «отмены» в задачах, подобных описанным выше.

Например, десятичное число 0,1 имеет двойное представление IEEE. 0 (1).1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1010 × 2^(-4); при добавлении к 140737488355328.0 (что составляет 2 ⁺⁴⁷ ) он потеряет все свои биты, кроме первых двух. Таким образом, из '= ( 140737488355328.0 + 0,1 - 140737488355328.0) оно вернется как 0,09375 вместо 0,1 при расчете с помощью www.weitz.de/ieee (64 бит), а также в Excel с круглыми скобками вокруг формулы. Этим эффектом в большинстве случаев можно управлять путем значимого округления, которое Excel не применяет: это остается на усмотрение пользователя.

Излишне говорить, что другие электронные таблицы имеют аналогичные проблемы: LibreOffice Calc использует более агрессивное округление, в то время как gnumeric пытается сохранить точность и сделать точность видимой для пользователя.

Примеры, когда точность не является показателем точности

Статистические функции

Ошибка в расчете стандартного отклонения в Excel 2007. Все четыре столбца имеют одинаковое отклонение 0,5.

Точность функций, предоставляемых Excel, может быть проблемой. Альтман и др . (2004) приводят такой пример: Стандартное отклонение генеральной совокупности, определяемое по формуле:

${\sqrt {\frac {\Sigma (x-{\bar {x}})^{2}}{n}}}={\sqrt {\frac {\Sigma \left[x-\left(\Sigma x\right)/n\right]^{2}}{n}}}\ ,$

математически эквивалентно:

${\sqrt {\frac {n\Sigma x^{2}-\left(\Sigma x\right)^{2}}{n^{2}}}}\ .$

Однако первая форма обеспечивает лучшую числовую точность для больших значений x , поскольку квадраты разностей между x и x приводят к меньшему округлению, чем различия между гораздо большими числами Σ( x ² ) и (Σ x ) ² . Встроенная функция Excel STDEVPОднако используется менее точная формулировка, поскольку она требует более быстрых вычислений.

Обе функции «совместимости» STDEVP и функция «согласованности» STDEV.P в Excel 2010 возвращает стандартное отклонение совокупности 0,5 для заданного набора значений. Однако численную неточность все же можно продемонстрировать на этом примере, расширив существующую цифру, включив в нее 10 ¹⁵ , после чего ошибочное стандартное отклонение, найденное Excel 2010, будет равно нулю.

Вычитание результатов вычитания

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

$A1:=28.552$

$A2:=27.399$

$A3:=26.246$

и следующие ячейки содержат следующие формулы

$B1:=A1-A2$

$B2:=A2-A3$

Обе ячейки $B1$ и $B2$ отображать $1.1530$.Однако если клетка $C1$ содержит формулу $B1-B2$затем $C1$ не отображается $0$ как и следовало ожидать,но отображает $-3.55271E-15$ вместо.

Вычитаниями вышеизложенное не ограничивается, попробуйте = 1 + 1.405*2^(-48) в одной ячейке Excel округляет значение до 1,00000000000000000000, а = 0.9 + 225179982494413×2^(-51) на другом, том же дисплее выше, различное округление значения и отображения нарушает одно из элементарных требований Голдберга (1991). кто утверждает:

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

Проблема не ограничивается Excel, например, LibreOffice Calc действует аналогичным образом.

Ошибка округления

Пользовательские вычисления должны быть тщательно организованы, чтобы гарантировать, что ошибка округления не станет проблемой. Пример возникает при решении квадратного уравнения :

$ax^{2}+bx+c=0\ .$

Решения (корни) этого уравнения в точности определяются квадратичной формулой :

$x={\frac {-b\pm {\sqrt {b^{2}-4ac}}}{2a}}.$

Когда один из этих корней очень велик по сравнению с другим, то есть когда квадратный корень близок к значению b , оценка корня, соответствующего вычитанию двух членов, становится очень неточной из-за округления (отмены? ).

Ошибку округления можно определить, используя формулу ряда Тейлора для квадратного корня:

${\sqrt {b^{2}-4ac}}=b\ {\sqrt {1-{\frac {4ac}{b^{2}}}}}\approx b\left(1-{\frac {2ac}{b^{2}}}+{\frac {2a^{2}c^{2}}{b^{4}}}+\cdots \right).$

Следовательно,

$b-{\sqrt {b^{2}-4ac}}\approx b\left({\frac {2ac}{b^{2}}}-{\frac {2a^{2}c^{2}}{b^{4}}}+\cdots \right),$

указывая на то, что по мере того, как b становится больше, первый оставшийся член, скажем, ε:

$\varepsilon ={\frac {2ac}{b}},$

становится все меньше и меньше. Числа для b и квадратного корня становятся почти одинаковыми, и разница становится небольшой:

$b-{\sqrt {b^{2}-4ac}}\approx b-b+\varepsilon .$

В этих обстоятельствах все значимые цифры входят в выражение b . Например, если точность составляет 15 цифр, а эти два числа, b и квадратный корень, одинаковы для 15 цифр, разница будет равна нулю вместо разницы ε.

Более высокую точность можно получить, используя другой подход, описанный ниже. Если мы обозначим два корня через r   ₁ и r   ₂ , квадратное уравнение можно записать:

$\left(x-r_{1}\right)\left(x-r_{2}\right)=x^{2}-\left(r_{1}+r_{2}\right)x+r_{1}\ r_{2}=0.$

Когда корень r   ₁ >> r   ₂ , сумма ( r   ₁ + r   ₂ ) ≈ r   ₁ и сравнение двух форм приблизительно показывают:

$r_{1}\approx -{\frac {b}{a}},$

пока

$r_{1}\ r_{2}={\frac {c}{a}}.$

Таким образом, находим примерный вид:

$r_{2}={\frac {c}{a\ r_{1}}}\approx -{\frac {c}{b}}.$

Эти результаты не подвержены ошибке округления, но они не являются точными, если только b ² велик по сравнению с переменным током .

График Excel разницы между двумя оценками наименьшего корня квадратного числа: прямая оценка с использованием квадратичной формулы (точность при меньшем b ) и приближение для широко расположенных корней (точность при большем b ). Разница достигает минимума в больших точках, а округление приводит к появлению волнистых линий на кривых за пределами этого минимума.

Суть в том, что при выполнении этого расчета с использованием Excel, поскольку значения корней становятся все дальше друг от друга, метод расчета должен будет переключиться с прямого вычисления квадратичной формулы на какой-либо другой метод, чтобы ограничить ошибку округления. Точка переключения методов зависит от размера коэффициентов a и b .

На рисунке Excel используется для нахождения наименьшего корня квадратного уравнения x. ² + bx + c = 0 для c = 4 и c = 4 × 10 ⁵ . Разница между прямым вычислением с использованием квадратичной формулы и приближением, описанным выше для широко расположенных корней, представлена ​​в зависимости от b . Первоначально разница между методами уменьшается, поскольку метод с широко разнесенными корнями становится более точным при больших значениях b . Однако за пределами некоторого значения b разница увеличивается, поскольку квадратичная формула (хорошая для меньших значений b ) становится хуже из-за округления, в то время как метод широко расположенных корней (хороший для больших значений b ) продолжает улучшаться. Точка переключения методов обозначается большими точками и становится больше для больших значений c . При больших значениях b наклоненная вверх кривая представляет собой ошибку округления Excel в квадратичной формуле, неустойчивое поведение которой приводит к извилистости кривых.

Другой областью, где точность является проблемой, является область численного вычисления интегралов и решения дифференциальных уравнений . Примерами являются правило Симпсона , метод Рунге-Кутты и алгоритм Нумерова для уравнения Шрёдингера . Используя Visual Basic для приложений, любой из этих методов можно реализовать в Excel. Численные методы используют сетку, в которой оцениваются функции. Функции могут интерполироваться между точками сетки или экстраполироваться для определения местоположения соседних точек сетки. Эти формулы включают сравнение соседних значений. Если сетка расположена очень мелко, возникнет ошибка округления, и чем меньше используемая точность, тем хуже ошибка округления. При большом расстоянии точность пострадает. Если численную процедуру рассматривать как систему обратной связи , то этот вычислительный шум можно рассматривать как сигнал, приложенный к системе, что приведет к нестабильности, если система не будет тщательно спроектирована.

Точность в VBA

работает с 8-байтовыми Хотя Excel номинально по умолчанию числами, VBA поддерживает множество типов данных. Тип данных Double составляет 8 байт, тип данных Integer общего назначения 16 байт — 2 байта, а тип данных Variant можно преобразовать в 12-байтовый тип данных Decimal с помощью функции преобразования VBA CDec . Выбор типов переменных в расчетах VBA предполагает рассмотрение требований к хранению, точности и скорости.