Microsoft(R) Excel(R) が有効数字を減らしてくる現象について

まずA1セルに1を入れ、A2セルには=A1*2と記入します。次に、A2セルのフィルハンドル(セルの右下にカーソルを合わせたときに出現する十字型のカーソル)を下にドラッグし、100セルほどコピーします。

このようにすると、2Nという数列を計算することができます。

しかし、A列の書式設定を「数値」にしてみると、途中から一の位が0になっていることがわかります。 2Nという数列のいかなる項も、一の位が0になることはありませんから、これはおかしいです。

内部で浮動小数点数演算をしているため、誤差が生じているのだ、といった考え方もあると思います。 しかし、近年の計算機が使う浮動小数点数の基数はほぼまちがいなく2であり、2Nはオーバーフローしない限りは正確に表せるはずです。 実際、後で示すように、Excelが使用している浮動小数点数は、ほぼ確実にIEEE 754 binary64(二進倍精度浮動小数点数)です。

実験

どこから正しく表示できないのか

まず、この現象が発生し始めるのはどこかを調べます。 すると、=10^15+1という数式を入力したセルは、その表示が1000000000000000となり、この現象が発生していることがわかります。 一方、=10^15-1という数式を入力したセルは、その表示が999999999999999となるため、この現象は発生していないことがわかります。

つまり、「十進表記に変換する際、その数が正確に表せるかとは無関係に、上から15桁は十進表記し、残りの桁は0で埋めて桁合わせする」というアルゴリズムになっているようです。

倍精度浮動小数点数と仮定して、どこから誤差が生じるのか

ところで、次の浮動小数点数との間隔が1を超えるようになるような最小の正の浮動小数点数は、253です。 253+1は、浮動小数点数では正確に表せません。 逆に言うと、絶対値が253未満の正の浮動小数点数について、その数より1大きい数は、浮動小数点数で表せます。

253は十進法で表すと9007199254740992ですが、Excelで計算させると最後の2が0に変わってしまいます。

内部では、実は正確に保持している

1000000000000000を入力したセルに1を足し続けるといずれ1000000000000010になります。 つまり、見た目では変な表記になっていますが、内部的には正確な値を保持しているようです。

実は、グラフを用いると、これを”可視化”することができます。

f:id:lpha_z:20191117234956p:plain

横軸をX、縦軸をYとしたとき、Y=253+Xであるような点をいくつか打ちました。

Xが負の領域では、きれいに一直線に並んでおり、正確な値が計算されていることがわかります。ただし、縦軸の目盛数値は、最終桁が0に変わってしまっています。

Xが正の領域では、がたがたとして変な感じになっています。これは、以下の二点によるものと考えるとすべての説明がつきます。

  • 253より大きな、浮動小数点数で表せる数は、253+2, 253+4, 253+6, ……のように続くので、正確に表せないときがある
  • 演算結果浮動小数点数で正確に表せない場合、最も近い浮動小数点数に丸める。ただし、そういった浮動小数点数の候補が二個ある場合、仮数部の末尾が0であるようなものを選ぶ(いわゆる偶数丸め)

やはり、ExcelIEEE 754 binary64を内部的に使っていると考えるのが妥当なようです。

強制的に15桁しか表示しないのは、十進浮動小数点数で表した時の仮数部が1である場合に精度がほぼ1桁分減ってしまうのでやめてほしいのですが……。