Excelが16桁以上の計算を間違える話。
実験環境
Microsoft Office Professional Plus 2016 (Build 13328.20356)でテストを行っています。
前提
Excelでは16桁以上の数値を扱えず、上から16桁以上の入力は0になってしまうという問題（というか仕様）はよく知られていると思います。
下の画像のように16桁を入力した状態でEnterを押して確定すると、
この画像のように1桁目が0になってしまいます。（なお、Excelでは桁数が多いと自動で指数表記になってしまうので、セルの書式設定から"数値"を選択しています。）
下の画像のように17桁を入力した状態でEnterを押して確定すると、
この画像のように1桁目と2桁目が0になります。
この仕様については、ちゃんとMicrosoftのドキュメントにも記載があります。
(なので、1000,0000,0000,0000と入れても、1000,0000,0000,0001と入れても、... 1000,0000,0000,0009と入れても全部1000,0000,0000,0000になります。)
問題
さて、Excelは本当に上から15桁までしか記録していないのでしょうか？？？
結論から言うと、表示はされないけど、（条件によっては）記録はされています。
詳細
まず、16桁の数値に対して計算をしてみて、Excelがどのような挙動をするのか確認してみます。
その1（足し算）
1000,0000,0000,0000に対して足し算をしてみます。
16桁目は0になるため、
1000,0000,0000,0000 + 1 = 1000,0000,0000,0000
となります。
なので、 1000,0000,0000,0000と1000,0000,0000,0000 + 1 を比較すると"TRUE"（等しい）となります。
では足す数を増やしていきましょう。
"+ 1" ~ "+ 5"までは 1000,0000,0000,0000ですが、 "+ 6" ~ "+15"は1000,0000,0000,0010となっています。
そして、 1000,0000,0000,0000 + 5 = 1000,0000,0000,0000
と、Excel上の見た目は1000,0000,0000,0000であるにも関わらず、 1000,0000,0000,0000 と 1000,0000,0000,0000 + 5 を比較すると、"FALSE"(等しくない)となります。←ここが記事のタイトル部分
ここから、内部データと表示に差があることが分かります。 （内部は浮動小数で扱っていて、一定の閾値以下なら同一値として扱ったりしているのかなと推測。）
ここから先、便宜的に、16桁以上の数値を記録している謎なものを”内部データ”、Excelの画面に表示されている見た目から読み取れるデータを"表示データ"と記します。
なお17桁で同じ表を作ったものが下記の画像
18桁で同じ表を作ったものが下記の画像です。
ここから、必ずしも、上から15桁目で四捨五入ならぬ五捨六入を行っているわけでもなさそうです。
その２（条件付き書式）
Excelの機能の条件付き書式のカラースケールを0,10,20の入ったセルに対して掛けると、次の画像のように、3色に塗り分けられます。
しかし、16桁の計算のA+Bのセルに掛けると、次の画像のように、グラデーションで塗り分けられます。
ということで、条件付き書式では、内部データを使って動作しているんだな。ということが分かります。
ということで、ここから先はカラースケールを用いて数値をざっくり確認していきます。
その３（テキスト→文字列変換）
ExcelではValue関数を利用すると、文字列から数値へ変換できます。 では16桁の数値の文字列を変換するとどうなるでしょうか？
きれいに2色に塗り分けられました。
15桁目までが保存され、それ以外は0で埋められるという、セルにキーボードで打ち込んだ時と同じ挙動をしていることが分かります。
その４（文字列型数値の自動変換）
Excelの機能として、文字列として入力された数値の計算を行えるという機能があります。
それを使うと次のようなことができます。（セルの左上に緑の三角が付いているのが、セルの中身が文字列であることを意味します。）
文字列の'100に対して数値の2を足すことで102という解を求めることができます。
ちなみに、文字列同士を足したりもできます。
便宜的に、この機能を"自動変換"と呼ぶこととします。
（ちなみにこの自動変換機能、四則演算やSQRT関数では動くけど、SUM関数やAVARAGE関数では機能しないという闇が深い機能なのですが、それはまた別の機会に...)
文字列数値に対して+0をすることで自動変換してみます。
きれいに2色に塗り分けられました。テキスト→文字列変換と同じ挙動をしています。
その５（文字列変換）
その３とは逆に、数値から文字列に変換してみます。 ExcelではTEXT関数を利用すると、数値から文字列へ変換できます。
TEXT TEXT関数で変換後の結果は表示データと同一になりました。
その６（コピペ）
下記の練成した表示データと内部データに差がある数値を他のブックにコピペしてみます。
余計なデータが残らないように"値の貼り付け"から"値"を選択して貼り付けます。
見た目を整え、条件付き書式を付けると
（残念ながら？）表示データと異なる内部データもコピーされてしまっていることが分かります。
その７（データ構造）
実際に、Excelブックの中でデータがどのように記録されているのか、内部を覗いてみます。
Excelのファイルの中身はOffice Open XMLで定義されています。
今回はSheet1にデータを書いたので、 ファイル内のxl\worksheets\sheet1.xmlの中身を確認します。
2行目のデータを確認すると、 ”C2”に、f=A2+B2だけではなく、v=1000,0000,0000,0001との記述があります。 これが内部データの実体でした。
結論
- Excelは表示されているデータの他に内部のデータを持っている。（正確には表示データが内部データと等しいとは限らない）
- 入力時やテキストからの変換時は仕様に合わせた上位15桁以上は0で埋めるという処理が行われているが、内部データについてはその限りではない。
- 16桁以上の値の比較は謎の処理が走っていて内部データそのままでも表示データでもない比較が行われている。 比較を行う際はTEXT関数で文字列に変更してから比較を行う。
- 16桁以上の値の数値をExcelブック/シート間でコピペすると、内部データもコピーされてしまうので注意。
解決策
残念ながらExcel単体でできる解決策らしい解決策はないので、16桁以上の数値が出てくる場合はExcelで計算しないように気を付けましょう。
おまけ
Excelの16桁問題は、「クレジットカードの番号を文字列ではなく、数値として記録してしまい、最後の一文字が消えてしまった」みたいなシチュエーションで良く話の俎上に載ります。
上記の話を踏まえると、実はExcelファイルの中身にはデータは残っているので、サルベージできる可能性が僅かながらあります。
ただ、Excelのみ使っている限りは入力の段階でデータが消えてしまっている可能性が高いので、他のソフトウェアやマクロを利用した様な場合に限りると考えられます。
（これぐらいしかこの記事が役に立つシチュエーションを思いつかなかった…）
