Excelの小数の誤差は59回目まで隠れる。0.1を60回足すと「=6」がFALSEになった

Excelで0.1を下の行へ足していくと、60行目で =B60=6 がFALSEになる。セルには6と出ているのに、6とは等しくない。59行目の5.9と比べるとTRUEなので、誤差はそこまで隠れていて、60回目で表に出る。2,000行まで続けると、1,798行で合わなかった。

よく例に出る =0.1+0.2=0.3 は、ExcelではTRUEになる。Excelは小さな誤差を隠す仕組みをいくつか持っていて、1回や2回の計算では表に出てこない。崩れるのは、同じ小数を何十回も足し続けたときだ。

確かめたのはMicrosoft 365のExcel（バージョン16、ビルド20326）で、2026年10月6日。誤差の出どころは、Microsoftの浮動小数点演算を使用すると、Excelで不正確な結果が得られますを同じ日に読んだ。Excelは数値をIEEE 754の倍精度で持ち、有効桁数は15桁、0.1は2進数で割り切れずに循環する、と書かれている。

Excelで0.1+0.2が0.3にならない、は本当か

検索すると、Excelでも0.1+0.2が0.3にならないという説明が多く出てくる。手元で打つと、こうなった。

中身では、0.1+0.2は0.30000000000000004になっている。それでも比べるとTRUEで、引き算の結果は0になる。隠れ方は3つあった。

1つ目は、引き算の最後が0に近いときに0にする補正だ。Microsoftの説明では、Excel 97から入った最適化で、足し算か引き算の結果が0か0にごく近いとき、2進数との変換で生じた誤差を補うとある。補うのは式の最後の計算だけで、括弧でくくると5.55E-17が残る。=0.1+0.2-0.3=0 がFALSEなのも同じ理由で、最後の計算が比較なので補正がかからない。

2つ目は、比べるときの扱いだ。Excelは15桁にそろえてから比べているように見える。0.30000000000000004を15桁で丸めると0.300000000000000で、0.3と同じになる。この見立てで、0.1を2,000回足した各行の結果をPythonで予想すると、Excelの = の結果と2,000行すべてで一致した。ただ、Microsoftの説明の中に、比べるときに15桁にそろえるという文は見つけていない。

3つ目はINTだ。1.15*100の中身は114.99999999999999で、Pythonで切り捨てると114になる。ExcelのINTは115を返した。ROUNDDOWNもTRUNCも115だった。これも15桁にそろえてから切り捨てていると考えると、2,000行の結果がすべて説明できた。

この3つのおかげで、勤怠の分の計算はまず崩れない。出勤を7時から10時まで、退勤を16時から22時まで1分刻みで動かした65,341通りで、=INT((退勤-出勤)*1440) が分の数と合うかを数えた。Excelでは65,341通りすべてで合った。同じ計算をPythonの浮動小数点のまま切り捨てると、13,513通りで1分少なく出た。

0.1を足し続けると、60回目で「=6」がFALSEになる

隠す仕組みがあっても、誤差が積み重なって15桁の内側に入ってくると隠しきれない。B1に0.1を入れ、B2から下に =B1+0.1 の式を2,000行目まで並べて、回数÷10で出した正しい値と比べた。

59回目  5.899999999999995  15桁にそろえると 5.90000000000000  =5.9 はTRUE
60回目  5.999999999999995  15桁にそろえると 5.99999999999999  =6   はFALSE

59回目と60回目の中身は、どちらも正しい値より約5.3E-15小さい。違いは、15桁にそろえたときにそのずれが残るかどうかだった。5.9のほうは丸めると5.90000000000000に戻り、6のほうは5.99999999999999で止まる。

60回目からは、表のあちこちで外れる。2,000行のうち、= で比べて合わなかったのが1,798行。1,000回目は99.9999999999986で、セルには100と出る。列幅を広げても100のままで、TEXTで小数14桁まで出してやっと99.99999999999860が見えた。SUMでも同じで、0.1を入れたセル60個を =SUM() で足して6と比べるとFALSEだった。

困るのは、比べる式がそのまま仕事の判定になっているときだ。

=IF(B60>=6,"達成","") は、60回目で何も出さない。累計が6にわずかに届いていないからで、2,000行のうち1,280行で目標に届かない側に外れた。600回目の中身は60.00000000000058と上に外れていて、こちらは >= ならTRUEになる。ずれる向きは行によって違う。

0.1時間単位の作業時間を6分の件数に直すつもりで =INT(B60*10) と書くと、60回目は59になる。B60を10倍した中身は59.99999999999994で、INTが15桁にそろえても59.9999999999999にしかならず、60には届かない。これも1,280行で1少なく出た。

累計の列から6の行を探す =MATCH(6,B1:B2000,0) は#N/A、=COUNTIF(B1:B2000,6) は0だった。XLOOKUPも見つからない側を返した。文字列と数値が混ざったときのVLOOKUPの外れ方はVLOOKUPが合わない原因の記事で数えたが、こちらは両方とも数値で、見た目も同じ6なので、原因に気づきにくい。

足す数によって合わなくなる回数が違う。0.25と0.5は2,000回でも合う

0.1以外の数でも、2,000回ずつ足して、最初に合わなくなる回を数えた。

早いのは1.1の56回目で、遅いのは0.2の116回目だった。0.01は82回目なので、銭まである金額や、小数2桁の単価を同じ列で足し続ける表も同じところに入る。どの数でも、一度外れたあとは2,000回のうち1,700回以上で合わない。

0.25と0.5は、2,000回足しても一度も外れなかった。0.25は4分の1で、2進数では0.01と割り切れる。最初から誤差がないので、積み重なるものもない。15分を0.25時間、30分を0.5時間と書く勤怠の表が崩れないのはこのためだ。0.1時間、つまり6分単位で記録する表は、2進数で割り切れない側に入る。

実際の表で崩れるのは、同じ小数を何十行も足す累計

ここまでは同じ数を足し続けた場合で、いちばん崩れやすい条件だ。現場の表はばらばらの値が入るので、もう少し試した。

小数1桁の値を乱数で作り、行数を変えて =SUM() の結果を、正しい合計と比べた。各300回ずつ試すと、10行、31行、60行、100行、300行では一度も外れなかった。1,000行で300回中65回外れた。小数2桁の値でも、300行までは0回、1,000行で70回だった。値がばらばらだと、誤差は上と下に散って打ち消し合う。同じ値を足すと、毎回同じ向きにずれて積み上がる。

構成比も試した。0.1%単位で合計がちょうど100%になる4項目から12項目の組を1,000通り作り、=SUM()=1 と =IF(SUM()=100%,"OK","NG") で比べた。1,000通りすべてでTRUEとOKだった。中身が0.9999999999999999になった組もあったが、15桁にそろえると1に戻る。0.1時間単位の作業時間20日分を1,000か月分足した表も、すべて合った。

崩れやすいのは次のような表だと思う。毎日0.1時間ずつ増える累計、0.1kgずつ減っていく在庫の残り、同じ単価の小数を何百行も積む明細。行数で言えば、同じ値なら60行前後、ばらばらの値なら1,000行あたりから気にしたほうがよい。

累計の式をROUNDで丸めれば、2,000行で一度も外れなかった

直し方は、累計の式そのものを丸めることだ。

=ROUND(B1+0.1,1)

行ごとに小数1桁へ丸めた累計は、2,000行すべてで正しい値と一致した。丸めた時点で、その行の値は0.1を足した正しい値にいちばん近い数に戻るので、ずれを次の行へ持ち越さない。桁数の1は、入力する値の小数の桁に合わせる。銭まである金額なら2にする。

累計の式を変えられないときは、比べる側で丸める。=ROUND(B1000,10)=100 はTRUEになった。10桁で丸めるのは、表の値より十分細かく、15桁の誤差より十分粗いからで、1でも2でも、値の桁より細かければ足りる。もう1つは、小数を使わない単位で持つことだ。時間を分の整数で、金額を銭の整数で持てば、誤差はそもそも出ない。端数の扱いを1円単位で合わせた話は割り勘を1円まで合わせる記事に書いた。

失敗したやり方も2つ書いておく。

1つは、セルの書式で小数1桁にすることだ。見た目は6.0になるが、中身は5.999999999999995のままで、比べればFALSEになる。書式は表示を変えるだけで、値は変えない。

もう1つは、ファイル、オプション、詳細設定にある「表示桁数で計算する」だ。累計の列を小数1桁の書式にしてこれをオンにすると、2,000行すべてが合うようになった。ところが同じブックで、表示形式を「0」にしたセルの12.5は13に書き換わり、オプションをオフに戻しても13のままだった。Microsoftの説明にも、ブックの全部のシートにかかり、失われたデータは元に戻せないとある。表の一部のためにブック全体の値を書き換えるので、私は使わないことにした。

小数の列を貼ると、合わなくなる行に色がつく表を作った

配布する表の「1_点検」のA列に、足していく小数の列を貼り、H2に小数の桁数を入れる。B列はふつうに足した累計、C列はROUNDで丸めた累計で、F列に判定が出る。

上の画面は、見本の1行目と55行目から65行目を並べたところだ。D列の中身の差は55行目で-3.55E-15あるのに、59行目まではTRUEで、60行目でFALSEに変わる。G列の15桁の表示が5.99999999999999になるのも60行目からだ。

見本は200行で、前半80行は毎日0.1時間の作業、後半120行は0.1時間から3.0時間までばらつく作業時間にした。結果は、比べると合わない行が23行、中身はずれているが比べると同じ行が164行、ずれのない行が13行だった。後半に入ると、赤い行はほとんど消える。累計が大きくなると、残ったずれ（200行目で-5.68E-14）が15桁の外に出て、比べると同じになるからだ。ずれ自体は消えていない。

「2_0.1を足す」は、この記事の2,000行の数を数えたシートで、=、INT、>=、丸めた累計の4通りが何行で外れるかが出る。「3_足す数ごと」は上の図の元、「4_隠れる例」は最初の表の式を並べたシートだ。Microsoftの説明にある、0.0001を10,000回足す例もシートで再現した。結果は0.999999999999906で、1と比べるとFALSEだった。

この表を作っていて意外だったのは、隠す仕組みのほうがよくできていることだった。0.1+0.2も、1.15*100の切り捨ても、勤怠の6万通りも合う。だから60回目で急に外れたときに、原因が小数にあると思いつきにくい。数値が15桁で切られる話は、Excelが16桁目を0にする記事でカード番号を使って数えている。

確かめていないこと

Excelが比べるときやINTのときに15桁にそろえている、というのは結果から見立てたもので、Microsoftの文書では確かめていない。2,000行の = とINTの結果が、この見立てと全部一致したところまでを確かめた。

Microsoftの説明では、0.0001を10,000回足すVBAの例の出力を0.99999999999996としている。私はシートで足しただけで、VBAでは動かしていない。

Excel 2016や2019、Mac版、Googleスプレッドシートでは試していない。

勤怠の65,341通りは、TIMEVALUEで時刻にした値で数えた。セルに9:00と打った値が TIME(9,0,0) と同じになることは2件で確かめたが、65,341通りすべてを手で打った値では試していない。