EXCELで 0.1 はなぜ 0.1 となる（表示される) のか

妙なタイトルですが

EXCELで
セルに 0.1 と入力したら 0.1 となります(表示されます)。

当たり前の事のようですが
これが
コンピュータの知識が中途半端にある者からすると実に不思議なのです。

普通の浮動小数点数の理屈からいうと
0.1と言う数値は決して0.1にならない。
0.099999.... となる筈なのですが。

で、お訊きしたいのは
・EXCELでの表示はなにか特別の丸めが行われるのか？
....セルの書式設定を行わないデフォルト状態で
・EXCELの実数表現は何か特別（画期的)な方式をとっているのか？
( 例えば0.1を正確に0.1と表現できるような....)
という事です。

ちなみに
入力した時だけの話かも？と思いBookをいったん閉じて開いてみても
0.1と表示されました。
また
この事はVBAでも同じ様で
Dim Vl As Single

Vl=0.1
Msgbox Vl

としても　0.1 と表示されます。

ググってみると
浮動小数点数の誤差についての解説は腐るほどあるのですが
この様に誤差がでない（？）理由に言及したサイトは
見つけられなかったので質問しました。

宜しくお願いします。

質問者からの補足コメント

• 疑問に思っている具体例を書きます
  
  Dim a As Double
  a = 0.1
  MsgBox a '0.1表示
  a = 10.1 - 10
  MsgBox a ' 9.999....E-2　表示（このケースだけ何故違う？）
  a = 10.1 + 10
  MsgBox a ' 20.1 表示
  
  Double変数 a の仮数部のうち小数点以下に相当する部分の
  ビットパターン(小数部の値)は
  いずれのMsgboxのケースでも同じ筈
  (加減しているのは整数なので小数部は変わらない)
  なのに表示が違うのはなぜでしょう？

    補足日時：2020/07/12 12:04

No.1 ベストアンサー 回答者： yajyokitahoy 回答日時： 2020/07/12 09:27

おかしくありません。

セル書式設定が標準設定されている。
計算するときは、小数点以下の数値にならずに、切り捨てになります。何の不思議ではないけどね

No.8 回答者： kmee 回答日時： 2020/07/12 13:54

補足について。

プログラム中に a=0.1 と書いたら、それは
a=0.1000000000000000055511151231257827021181583404541015625
と書いたのと同じになります。

a = 10.1 - 10
は
a= 10.0999999999999996447286321199499070644378662109375 - 10
'a= 0.0 99999 99999 99996 4 47286321199499070644378662109375
'→17桁目は 4 なので切り捨て + 桁が多いのでE表現
msgbox "9.9999...E-2"

# 値が近いもの同士の引き算で精度が悪くなる現象を「桁落ち」と言います
https://ja.wikipedia.org/wiki/%E8%AA%A4%E5%B7%AE …


a = 10.1 + 10
は
a= 10.0999999999999996447286321199499070644378662109375 + 10
' a = 20.100 00000 00000 0 142108547152020037174224853515625
'→17桁目は 1 なので切り捨て
msgbox "20.1"



>Double変数 a の仮数部のうち小数点以下に相当する部分の
>ビットパターン(小数部の値)は
>いずれのMsgboxのケースでも同じ筈

同じではありません。
仮数には整数部まで含まれます。最初が1.〜 となるように指数で調整します。

0.1 は 0.000 1100 1100 1100 ...
→仮数は 1.100 1100 1100 ... の 53桁、指数は -4です。
→ つまり、小数部は 2進数で56桁です

10,1 は 101.000 1100 1100 1100 ... の最初の1から数えて53桁です。
→仮数は 1.01000 1100 1100 1100 ... の 53桁、指数は3です。
→ つまり、小数部は 2進数で50桁です

小数部を比べると、桁が違います。


なお、数値演算の基本は「できるかぎり桁を多くとる」ことです。
Singleにすると、もっと誤差が大きくなります。
10.1-10 がたまたま 0.1(Single)になったとしても、他の計算ではうまくいくとは限りません。
全体を見れば、 Doubleの時よりも「違う」ケースが増えます。

No.7 回答者： kmee 回答日時： 2020/07/12 13:14

有効な桁までしか表示していない書式設定を使っているからです。

「誤差が出ない」のではありません。
「誤差部分を端数処理して、信用できる桁だけを表示する」という処理を行っています。


Excelの内部では「倍精度浮動小数点数」という形で処理されています。
https://ja.wikipedia.org/wiki/%E5%80%8D%E7%B2%BE …

細かい話はともかく、これを使って
　値 = 2進数で53桁の小数 × 2のn乗
という形で値を表わしています。
もし、54桁以上になってしまう場合は、「四捨五入に相当するような2進数の処理」をすることで、 53桁に納めます。
これによる小数54位以降の分が誤差になります。

0.1(10進数)は2進数にすると 0.000 1100 1100 1100 ... と循環小数になり,無限桁になります。
そのため、先頭の1から53桁目までに丸められます。
これが「0.1(10進数)は正確な0.1(10進数)ではない」という原理です。

この「2進53桁に丸められた浮動小数点数」を10進数に変換すると
0.10000 00000 00000 00555 11151231257827021181583404541015625
になります。これは「2進53桁に丸められた浮動小数点数」と完全に一致します。
ですから、元の「浮動小数点数」に含まれていた誤差も、そのまま誤差として残っています。

そこで、誤差を含む信用できない桁は端数処理して、信用できる桁だけを使おう、と考えます。
2進数で53桁まではあるので、それに該当する10進数の桁までは、信用していいでしょう。
2進数で53桁は、10進数では16桁程度に該当します。(log_10(2^53))

そこで、 0.################ という書式設定を適用すると
0.10000 00000 00000 0 0555 11151231257827021181583404541015625
は16桁目で端数処理されて
0.10000 00000 00000 0
となり、 # で小数点以下の0は表示しないので
0.1
になります。

「G/標準」では、上記のような桁調整を、値に合せて調整したものを使っています。
他に、桁が大きくなった場合は E表現を使うとか。

逆に 0.000000000000000000000000000000000 みたいな書式にしたら、誤差の分まで表示されるかもしれません
(今手許にExcelが無いので確認ができませんが)

msgbox v1 の方も同じです。
ここでは v1(SIngle)が暗黙に Stringに変換されています。このときに「G/標準」のような書式を使って文字列化しています。

No.6 回答者： Qchan1962 回答日時： 2020/07/12 13:07

連続投稿申し訳ありません。

目的と探究は、違いますね。頓珍漢な回答をしていたかも知れません。
誤差の検証方法はいくつかあると思いますが、探究については、最近、歳のせいかしんどいです。

似たような記憶がありましたので、結論は出ていませんが、こちらを
Microsoft コミニュティ　＜エクセル　同じ数字の足し算で順番が違うと答えが違う＞
https://answers.microsoft.com/ja-jp/msoffice/for …

No.5 回答者： Qchan1962 回答日時： 2020/07/12 12:43

＃４です。

＞浮動小数点数の誤差についての解説は腐るほどあるのですが
＞普通の浮動小数点数の理屈からいうと0.1と言う数値は決して0.1にならない。0.099999.... となる筈なのですが。

ご質問にあるように理解している事を前提としますが、補足の場合、Doubleで宣言せずSingleで宣言するべきかと
他の演算で必要がありDoubleを使う場合、意図的な制限（仕様）設定をして数値を丸めるべきと考えます。
なぜなら、すでにご理解されている通り、誤差の修正が必要だからです。
少なくともExcelの場合、=は数学上の=とは異なる解釈が含まれていると思います。（私的に）

No.4 回答者： Qchan1962 回答日時： 2020/07/12 11:20

こんにちは、

入力と表示に関しては、ご自身当たり前と認識されているようですね。
計算結果や演算となるとおっしゃっている通りだと思います。
Excelも内部的には2進数を使っていますね。
私的には、正確という表現は、立ち位置で変わると思います。

いくつか参考になると思われるサイトを書きます。

Excel で浮動小数点演算の結果が正しくない場合がある
https://docs.microsoft.com/ja-jp/office/troubles …
抜粋：
0001100110011100110011 (以降)
これは無限に繰り返すことができます。 この数は、限定された (限られた) 容量では表現できません。 そのため、この数値は格納されている場合は、約 2.8 E-17 まで切り捨てられます。

Excelはファイル内部では有効数字17桁で値を保持？
画面上には有効数字15桁で表示している

Tips: Excelでの数値表現と計算精度
h ttps://eip.econ.kanagawa-u.ac.jp/eip/excel-calc-error.html
Excelのマシンε

何れにしても限られた条件での演算と数学的なものと違いが出るのは当然のような気がします。
私は、Excelを使う時、当然Excelの仕様に従います。もしその仕様に限界、矛盾などを感じた場合は
他のアプリケーションや言語を使用すると思います。

仕様と制限などは、アプリケーションでは不可欠だと思います。
国における、法律みたいなものかと、、ちがうか、、
一部詳細を明確に出来ないのは、明確に出来ない事がらを解決するために決めるからかも知れません。

解決できませんね。すみません。。

No.3 回答者： めぐみん_ 回答日時： 2020/07/12 10:59

十数年前に仰るような現象について困っているとの質問はいくつか見かけましたが。

https://eip.econ.kanagawa-u.ac.jp/eip/excel-calc …

のように『見た目』と『内部結果』の相違についてって事ではないのでしょうか？（初級者ゆえ勘違いならすみません）

No.2 回答者： GDBF_Sti05 回答日時： 2020/07/12 10:34

当方が知る限り、コンピューターは"有限桁数"で計算するので、計算誤差は必ずあるという事が理解できてるか？がコンピュータの計算方法の基本ベースでは？

"有限桁数"で計算すると計算回数が増えれば誤差はどんどん蓄積するし、計算順序、例えば括弧があるなし等によって結果が違うこともある。

Excelがどうやって計算処理しているのかはExcelを開発した方にしか分からないしExcelが誤差を隠している可能性はあるが不明なので
Excelの計算や再計算はどのようにどういう順番でどうやって計算してるんだか全く分からない事になる。
コンピュータが有限桁数で処理をしている以上、Excelの処理が正しいとしか言えない。
※MSDNとかに記載がありそうな気がしないでもないですが確認はしていません。

有限桁数で計算するのではなく無限の桁数の計算を扱えるコンピュータを開発し、無限の桁数で計算し全く誤差の無い理論や計算方法、プログラム等が確立されたら
質問者様の疑問も解けると思いますが、出来たらコンピュータの世界がひっくり返る発明になるのではと思います。
もしあったら教えて欲しいです。
上記理由から、誤差が出ない事に言及しているサイトはあまりないと思われる。
欧米の関連サイトも含めて注意深く読み解けば、現状存在していないはずなので"説明出来ない"・"する必要が無い"ので説明していない事がある程度分かると思います。
余計に混乱するだけなので。

ちなみに専門職ではなく、意図する回答ではないかもしれませんが、おっさんアニメーターの意見ですのでまぁ参考程度に。