EXCELの計算精度

Microsoft EXCELの計算精度について考えてみる．環境は

数値の内部形式について

Intel Proccessor Xeonなので当然　IEEE754である．Pentiumとも内部形式は同じはずである． IA-32 Intel® Architecture Software Developer’s Manual Volume 1: Basic Architectureにも共通であると記述してある．

内部形式（IEEE 754　倍精度形式）
IEEE 754倍精度形式
EXCELと内部表現の関係を下図に示す．


形式は±d0d1...d51 2eです．ここにeは指数部 分で，符号付整数形式．


マシンエプシロン近傍の計算

倍精度形式の場合の精度は10進16桁程度なので，その近傍を見てみる．

A列 B列 C列
入力値
　小数点桁数を指定した数値形式で表示 		＝(A列+1)-1
表示形式
小数点桁数を指定した数値形式で表示 		（B列ーA列）／A列
0.00000000000000220 0.0000000000000022204 -0.929%
0.00000000000000210 0.0000000000000019984 4.838%
0.00000000000000200 0.0000000000000019984 0.080%
0.00000000000000190 0.0000000000000019984 -5.179%
0.00000000000000180 0.0000000000000017764 1.314%
0.00000000000000170 0.0000000000000017764 -4.492%
0.00000000000000160 0.0000000000000015543 2.856%
0.00000000000000150 0.0000000000000015543 -3.621%
0.00000000000000140 0.0000000000000013323 4.838%
0.00000000000000130 0.0000000000000013323 -2.482%
0.00000000000000120 0.0000000000000011102 7.481%
0.00000000000000110 0.0000000000000011102 -0.929%
0.00000000000000100 0.0000000000000011102 -11.022%
0.00000000000000090 0.0000000000000008882 1.314%
0.00000000000000080 0.0000000000000008882 -11.022%
0.00000000000000070 0.0000000000000006661 4.838%
0.00000000000000060 0.0000000000000006661 -11.022%
0.00000000000000050 0.0000000000000004441 11.182%
0.00000000000000040 0.0000000000000004441 -11.022%
0.00000000000000030 0.0000000000000002220 25.985%
0.00000000000000020 0.0000000000000002220 -11.022%
0.00000000000000010 0.0000000000000000000 100.000%
0.00000000000000000 0.0000000000000000000 #DIV/0!
-0.00000000000000010 -0.0000000000000001110 -11.022%
-0.00000000000000020 -0.0000000000000002220 -11.022%
-0.00000000000000030 -0.0000000000000003331 -11.022%
-0.00000000000000040 -0.0000000000000004441 -11.022%
-0.00000000000000050 -0.0000000000000005551 -11.022%
-0.00000000000000060 -0.0000000000000005551 7.481%
-0.00000000000000070 -0.0000000000000006661 4.838%
-0.00000000000000080 -0.0000000000000007772 2.856%
-0.00000000000000090 -0.0000000000000008882 1.314%

　　これより私のPCでのマシンイプシロンは2^(-52)≒2.22044604925031×10-16であり， EXCELも0と表示することがわかる．この近傍では計算精度が落ちることは明らかである．このような領域で，通常の計算を そのまま行ってはいけない．適当な線形変換を行って計算するべきなのが数値計算の常識である．例えば，6.02×1023 (アボガドロ定数),1.60217733×10-19(電気素量）等を含む式があれば，適当な単位変換を行って計算するのは常識であろう．

許容指数の検討


指 数部分A 　 ＝２＾（A) 　 ＝２（−A)
1019 5.61779104644474E+306 1.78005908680576E-307
1020 1.12355820928895E+307 8.90029543402881E-308
1021 2.24711641857789E+307 4.45014771701440E-308
1022 4.49423283715579E+307 2.22507385850720E-308
1023 8.98846567431158E+307 0.00E+00
1024 #NUM! 0.00E+00

精度の検討

　群馬大学の青木氏のサイトにはEXCEL の計算精度に問題がある と記述がある．私の使用してPCのEXCELで も若干の不都合はあるが，それなりに注意して使っていれば特段の問題は起きていない． 計算の誤差は3通りあります．
  1. 2進化による丸め誤差．（EXCELでは内部形式はIEEE754の倍精度計算浮動小数点形式の計算をし， 結果を丸めるときに，最後の３ビットを使って表示形式を変更をしているようです．15-16 桁程度の精度が限界と思われます．） 10進から2進への変換，2進から10進変換でそれぞれ発生します．　
  2. 近似公式の誤差
    gammaln(n)等の計算アルゴリズムをEXCELは単純数学公式で計算しているようです．
    gammaln(n)= loge(1)+loge(2)+ ....+ loge(n)
    これでは計算精度は悪いです．しかし， FPU 8087からのハードウェア組み込み関数を使用するPI(定数）,SQRT,SIN,COS,TAN,ARCTAN,LOG EXPの精度は非常に良い．なお，ｘyはEXP（y LOG(x))で計算していると思われる．

  3. 計算のまるめ誤差

古いEXCELでは様々なバグがあるようですが，ちゃんと気をつければ大丈夫ではないかと思っています．計算する前にそのソフトウェア，内部形式等を調べ るのは常識だとの指摘もある．しかし，EXELのHELPの説明は良くないし，内部表現と処理，表示方法は別の概念だということも明確に表現できないのは残念．

項目 意味
 excelでの内部値 excelでの表示
 excelでの計算上の扱い
マシンエプシロン（machine epsilon） １の次に大きい最小の正の有限桁少数を１＋EMと 表し，EMを マシンイプシロンという．（1+EM-1)の計算結果は0となる．
 2^(-52)≒2.22044604925031×10-16
 ０と表示
 ０とみなす
最大許容指数
emax
maximum  allowable exponent
 指数部eで表現される最大整数
1023
 例（これは最大許容指数ではない）
2^1023=8.98846567431158E+307
 ←
 これ以上は
＃NUM!を返す
最小許容指数
emin
minimal allowable exponent 		指数部eで表現される最小整数
-1022
最大許容指数と原点に関して対象でないことに注意． 		例（これは最小許容指数ではない）
2^(-22)=2.2250738585072E-308

 ← これ以下は０を返す
(符号付き）
最大浮動小数点数
 もっとも大きな絶対値をもつ浮動小数点数
この数

 1.79769313486231*10^308
 通常の数
詳細を調査中

EXCELのHELPの制限値（これは表現と翻訳がおかしいです．）

有効桁数 15 桁
セルに入力できる最大値 9.99999999999999E307
処理できる正の最大値 1.79769313486231E308
処理できる負の最小値← 数学と国語がわかっていないようです．
　　
 -2.2250738585072E-308
処理できる正の最小値 2.229E-308
処理できる負の最大値←数学がわかっていないようです． -1E-307

Windows以外でのでEXCEL

さて，Mac OS X上のEXCEL-Xでの制限値は驚くだけです．

参考文献

  1. Intel, Intel Architecture Software Developer's Manual Volume 1: Basic Architecture.　Chapter 7.2 IEEE 754 Floating Point. Intel Order Number 243190.
    2. 　
  2. D. Goldberg, What Every Computer Scientist Should Know About Floating-Point Arithmetic , Computing Surveys of ACM, 1991.