Microsoft Excel の数値精度

他のスプレッドシートと同様に、Microsoft Excel は、数値を表すために一定数の桁しか保持しないため (精度が限られている)、精度が限られています。エラー値、無限大、非正規化数に関する例外はありますが、Excel はIEEE 754 仕様^[1]の倍精度浮動小数点形式で計算します(数値のほかに、Excel では他のいくつかのデータ型も使用します^[2] )。Excel では小数点以下 30 桁まで表示できますが、特定の数値の精度は有効数字15 桁以下であり 、四捨五入、^[a]切り捨て、2 進数ストレージ、計算におけるオペランドの偏差の蓄積、最悪な減算時のキャンセル、または同様の大きさの値の減算時の「壊滅的なキャンセル」という 5 つの問題により、計算の精度がさらに低下する可能性があります。

上の図では、分数 1/9000 が Excel に表示されています。この数値は 1 の無限の連続である小数表現を持ちますが、Excel は先頭の 15 桁のみを表示します。2 行目では、分数に 1 が加算され、Excel はやはり 15 桁のみを表示します。3 行目では、Excel を使用して合計から 1 を減算します。合計の小数点以下の 1 は 11 個しかないため、'1' を減算したときの実際の差は、3 つの 0 とそれに続く 11 個の 1 の連続です。しかし、Excel によって報告される差は、3 つの 0 とそれに続く13個の 15 桁の連続と、2 つの余分な誤った桁です。したがって、Excel が計算する数値は、表示される数値ではありません。さらに、Excel の答えの誤差は単なる丸め誤差ではなく、浮動小数点計算における「キャンセル」と呼ばれる影響です。

Excel の計算の不正確さは、有効桁数が 15 桁であることによる誤差よりも複雑です。Excel の数値のバイナリ形式での保存も、その精度に影響します。^[3] たとえば、下の図は、いくつかのxの値に対する単純な加算1 + x − 1 を 表にしたものです。 xの値はすべて15 番目の小数点から始まるため、Excel はそれらを考慮する必要があります。合計1 + xを計算する前に、 Excel はまずx を2 進数として近似します。この 2 進数バージョンのxが単純な 2 の累乗である場合、 xの 15 桁の 10 進数の近似値が合計に格納され、図の上の 2 つの例は、エラーなしでxが回復されることを示しています。3 番目の例では、x はより複雑な 2 進数で、x = 1.110111⋯111 × 2 ⁻⁴⁹ (合計 15 ビット) です。ここで、15 ビットの数値から得られる「IEEE 754 倍精度値」は 3.330560653658221E-15 であり、これは Excel によって「ユーザー インターフェイス」用に 15 桁の 3.33056065365822E-15 に丸められ、次に 30 桁の小数点付きで表示され、1 つの「偽のゼロ」が追加されます。したがって、サンプル内の「2 進数」と「10 進数」の値は表示のみで同一であり、セルに関連付けられた値は異なります (1.1101111111111000000000000000000000000000000000000000 × 2 ⁻⁴⁹対1.110111111111110111111111111111111111111111111111111111111101 × 2 ⁻⁴⁹）。他のスプレッドシートでも同様のことが行われており、double の 53 ビット仮数部に格納できる小数桁数が異なる（例えば、1 から 8 までは 16 桁だが、1 から 2 までは 15 桁しかない）という処理が行われている。 1/2⁠と 1 の間、かつ 8 と 10 の間) の問題はやや難しく、最適には解決されていません。4 番目の例では、xは10進数であり、単純な 2 進数と等価ではありません (ただし、表示されている精度では 3 番目の例の 2 進数と一致しています)。10 進数の入力は 2 進数で近似され、その10 進数が使用されます。図の中央の 2 つの例では、何らかのエラーが発生していることがわかります。

最後の 2 つの例は、xがかなり小さい数値の場合に何が起こるかを示しています。最後から 2 番目の例では、x = 1.110111⋯111 × 2 ⁻⁵⁰ で 、合計 15 ビットです。2 進数は、非常に大まかに 2 の累乗 (この例では 2 ⁻⁴⁹ ) に置き換えられ、その 10 進数相当が使用されます。下の例では、上記の 2 進数と示されている精度で同一の 10 進数が、2 進数とは異なる方法で近似され、15 桁の有効数字に切り捨てられて除去されるため、1 + x − 1には寄与せず、x = 0になります。^[b]

2の単純な累乗ではないxの場合、 xがかなり大きい場合でも1 + x − 1 に顕著な誤差が生じる可能性があります。たとえば、x = ⁠の場合1/1000⁠ 、すると1 + x − 1 = 9.99999999999 89 × 10 ⁻⁴となり、 13 桁目の誤差となります。この場合、Excel が 2 進数への変換と 10 進数への変換をせずに、単に小数を加算および減算すれば、丸め誤差は発生せず、精度は実際に向上します。Excel には、「表示どおりに精度を設定する」オプションがあります。^[c] このオプションを使用すると、状況に応じて精度が向上したり低下したりする可能性がありますが、Excel が何をしているかを正確に知ることができます。(選択した精度のみが保持され、このオプションを逆にしても余分な桁を回復することはできません。) 同様の例がこのリンクにあります。^[4]

つまり、限られた数の2進数で数値を表すことと、15桁目を超える数値を切り捨てることを組み合わせることで、さまざまな精度の動作が導入されます。 ^[5] Excel では、15桁を超える数値を処理することで、15桁の有効数字のみで直接処理する場合よりも、計算の最後の数桁の精度が向上する場合もあれば、向上しない場合もあります。

2進数表現への変換と10進数への変換の理由、およびExcelとVBAの精度の詳細については、これらのリンクを参照してください。^[6]

1. タスクの欠点は、= 1 + x - 1「fp-math の弱点」と「Excel での処理方法」、特に Excel の丸め処理の組み合わせです。Excel は、ほとんどの結果に対して丸め処理や「ゼロへのスナップ」を行い、平均して IEEE 倍精度表現の最後の 3 ビットを切り捨てます。この動作は、数式を括弧で囲んで設定することでオフにできます。小さな値でも残ることがわかります。値を表すのに 53 ビットしかないため、より小さな値は無視されます。この場合は 1.0000000000 00000000000 000000000000 00000000000 01 で、最初のビットが を表し、最後のビット= ( 1 + 2^-52 - 1 )が を表します。 12^-52

2. 2 の累乗だけが残るのではなく、10 進数の 1 を加えたら 53 ビット内に収まるビットで構成される任意の値の組み合わせが残ります。ほとんどの 10 進数値は 2 進数で明確な有限表現を持たないため、上記のようなタスクでは「四捨五入」や「キャンセル」の影響を受けます。

たとえば、10 進数の 0.1 は IEEE の倍精度表現を持ちます0 (1).1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1010 × 2^(-4)。140737488355328.0 (2 ⁺⁴⁷ ) に加算すると、最初の 2 ビットを除くすべてのビットが失われます。したがって、'= ( 140737488355328.0 + 0.1 - 140737488355328.0) は、www.weitz.de/ieee (64 ビット) で計算した場合や、数式を括弧で囲んだ Excel で計算した場合、0.1 ではなく 0.09375 として返されます。この影響は、ほとんどの場合、意味のある丸めによって管理できますが、Excel ではこれは適用されません。これはユーザー次第です。

言うまでもなく、他のスプレッドシートにも同様の問題があります。LibreOffice Calc はより積極的な丸めを使用しますが、gnumeric は精度を維持し、精度だけでなく「精度の不足」もユーザーに表示しようとします。

精度が正確さの指標にならない例

Excelが提供する関数の精度が問題になることがあります。Altman et al . (2004) は次の例を示しています: [ ^7]母集団の標準偏差は次のように与えられます:

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

数学的には次の式と同等です:

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

しかし、最初の形式は、xの大きな値に対してより良い数値精度を保ちます。これは、xとxの差の二乗が、はるかに大きな数Σ( x ² )と(Σ x ) ²の差よりも丸めが少なくなるためです。ただし、組み込みの Excel 関数はSTDEVP、計算が高速であるため、精度の低い定式を使用します。^[5]

Excel 2010 の「互換性」関数STDEVPと「一貫性」関数はどちらもSTDEV.P、指定された値セットに対して 0.5 の母集団標準偏差を返します。ただし、この例を使用して既存の数値を 10 ¹⁵まで拡張すると、数値の不正確さが依然として示されます。その場合、Excel 2010 によって検出された誤った標準偏差は 0 になります。

単純な減算を行うと、2 つのセルに 2 つの別々の値が保存されているにもかかわらず、同じ数値が表示される場合があり、エラーが発生する可能性があります。この例は、次のセルに次の数値が設定されているシートで発生します。

${\displaystyle A1:=28.552}$

${\displaystyle A2:=27.399}$

${\displaystyle A3:=26.246}$

次のセルには次の数式が含まれています

${\displaystyle B1:=A1-A2}$

${\displaystyle B2:=A2-A3}$

セルと の両方が表示されます。ただし、セルに数式が含まれている場合は 、予想どおりに表示されず、代わりに が表示されます。 ${\displaystyle B1}$${\displaystyle B2}$${\displaystyle 1.1530}$${\displaystyle C1}$${\displaystyle B1-B2}$${\displaystyle C1}$${\displaystyle 0}$${\displaystyle -3.55271E-15}$

上記は減算に限定されません。1= 1 + 1.405*2^(-48)つのセルで試すと、Excel は表示を 1,00000000000000000000 に丸めますが、= 0.9 + 225179982494413×2^(-51)別のセルでは、同じ表示^{[d]ですが、値と表示の丸め方が異なり、Goldberg (1991) [8]}が述べている 基本要件の 1 つに違反しています 。

...「その使用がユーザーにとって透過的であることを確認することが重要です。たとえば、電卓では、表示された値の内部表現がディスプレイと同じ精度に丸められていない場合、その後の演算結果は非表示の数字に依存し、ユーザーにとって予測不可能になります。」...

この問題は Excel に限定されず、たとえば LibreOffice calc でも同様に動作します。

丸め誤差が問題にならないように、ユーザーの計算は注意深く整理する必要があります。例として、2次方程式を解く場合が挙げられます。

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

この方程式の解（根）は、二次方程式の公式によって正確に決定されます。

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

これらの根の 1 つが他に比べて非常に大きい場合、つまり平方根が値bに近い場合、 2 つの項の減算に対応する根の評価は、四捨五入 (キャンセル?) のために非常に不正確になります。

平方根の テイラー級数公式を使って丸め誤差を決定することが可能である： ^[9]

${\displaystyle {\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).}$

その結果、

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

これは、bが大きくなるにつれて、最初に生き残った項、たとえば ε が次のようになることを示しています。

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

どんどん小さくなります。bと平方根の数値はほぼ同じになり、差は小さくなります 。

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

このような状況では、すべての有効数字がb の表現に使用されます。たとえば、精度が 15 桁で、これら 2 つの数値bと平方根が 15 桁まで同じである場合、差は差 ε ではなく 0 になります。

以下に概説する別のアプローチにより、より高い精度が得られます。^{[e] 2つの根を}r  ₁とr  ₂ で表すと、二次方程式は次のように表すことができます。

${\displaystyle \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  ₁となり、2 つの形式を比較すると、おおよそ次のようになります。

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

その間

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

したがって、おおよその形式は次のようになります。

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

これらの結果は四捨五入誤差の影響を受けませんが、b ^2がacに比べて大きくない限り正確ではありません。

つまり、Excel を使用してこの計算を行う場合、根の値が離れるにつれて、四捨五入の誤差を制限するために、計算方法を二次方程式の直接評価から他の方法に切り替える必要があります。方法を切り替えるポイントは、係数aとbのサイズによって異なります。

図では、 Excel を使用して、c  = 4 および c  = 4 × 10 ^{5の場合の二次方程式}x ²  +  bx  +  c  = 0の最小の根を求めています。二次方程式の公式を使用した直接評価と、広く間隔を置いた根に対する上記の近似値との差が、bに対してプロットされています。最初は、広く間隔を置いた根法の方がb値が大きいほど正確になるため、これらの方法の差は小さくなります。ただし、あるb値を超えると、四捨五入により二次方程式 (より小さいb値に適している) は精度が悪くなるのに対し、広く間隔を置いた根法 (大きいb値に適している) は精度が上がり続けるため、差は大きくなります。方法を切り替えるポイントは大きな点で示され、c値が大きいほど点も大きくなります。 b値が大きい場合、上向きの曲線は二次方程式の Excel の四捨五入誤差であり、その不安定な動作によって曲線が波打っています。

精度が問題となる別の分野は、積分の数値計算と微分方程式の解の分野です。例としては、シンプソンの法則、ルンゲ・クッタ法、シュレーディンガー方程式のヌメロフアルゴリズムがあります。^[10] Visual Basic for Applicationsを使用すると、これらの方法はすべてExcelで実装できます。数値計算では、関数を評価するグリッドを使用します。関数は、グリッドポイント間で補間されるか、隣接するグリッドポイントを見つけるために外挿されます。これらの式では、隣接する値の比較が行われます。グリッドの間隔が非常に細かい場合は、丸め誤差が発生し、使用される精度が低いほど丸め誤差が悪化します。間隔が広いと、精度が低下します。数値計算手順をフィードバックシステムと考えると、この計算ノイズはシステムに適用される信号と見なすことができ、システムが慎重に設計されていない限り不安定になります。^[11]

Excelはデフォルトで8バイトの数値を扱いますが、VBAにはさまざまなデータ型があります。Doubleデータ型は8バイト、Integerデータ型は2バイトで、汎用の16バイトのVariantデータ型は、 VBA変換関数CDecを使用して12バイトのDecimalデータ型に変換できます。^[12] VBA計算における変数型の選択には、ストレージ要件、精度、速度を考慮する必要があります。