Excelの集計が合いません

Question

Excelで縦集計と横集計を比較し、同じならその数字を表示、違う場合は「合計が合いません」と表示するという表を作ったのですが、正しく動作しません。

データがC4:G34の範囲にあり、C35:G35にはそれぞれの列の合計（=SUM(C4:C34)）が入っています。

J4:J34にも同様に、それぞれの行の合計(=SUM(C4:I4))が入っています。

J35に下記の数式を入れ、縦方向と横方向の合計があっているかをチェックしています。

=IF(SUM(J4:J34)=SUM(C35:I35),SUM(C35:I35),"合計が合いません")

・合計の数字が「10以上」であれば、正しく動作します。10以下になる数字を入れると、その場所がどこであっても「合計が合いません」になってしまいます。

・「合計が合いません」の時に、SUM(J4:J34)とSUM(C35:I35)が本当に合っていないのか？と別セルに計算結果を出してみたところ、

　　SUM(J4:J34)　　 3.50000000000001

　　SUM(C35:I35)　　3.50000000000000

　となっていて、縦方向集計には小数点以下 第14位に不明な「1」が入っていました。

・データとして入力している数字はすべて、小数点以下 第1位までの数字（3、0.6、16など）がベタで（計算式ではなく）入っているので、この「1」がどこから来たかわかりません。

・それぞれの列の合計と、行の合計にroundをかけて小数点以下 第2位まで（=round(SUM(C4:I4),2))としてみても、小数点以下 第14位の「1」は消えませんでした。

環境は、Vista Home Premium SP2 + Excel 2007 (12.0.6683.5002) SP3 MSO です。

縦集計と横集計のチェックの計算式で、roundをかけることで運用回避していますが、なぜこうなってしまうのかすっきりしません。

どなたか教えて頂けると助かります。

よろしくお願いします。

Answer 1

コンピュータで、小数点以下がある数値を合計すると、こういう誤差が出るのが普通です。

理由は2進数では、0.1以下を表現する場合、２^-n　つまり0.5、0.25，0.125・・・・・　の合計となりますので、大半の数字が無限小数（循環小数）になり、数字を表現するビット数が有限のため、打切り誤差が生じるためです。

ですから、小数点以下の数が混じった計算結果を=で比較するのはほとんど意味がありません。

A＝Bの代わりに　ABS(A-B)＜0.00001　等違いが微小差になった場合一致していると判定せざるを得ません。

（何を微小とするかは、計算結果がどういうオーダーになるかで予め判断して置く必要があります）

Answer 2

J列の合計が3.5にならずに微小な誤差が出てくるのは、たとえばJ列の値そのものが、実際は小数点一桁の数字ではなく端数を持った数字になっているためです。

これを確認するにはJ列のセルを選択して表示形式を数値にして小数点以下15ケタ表示してみるとわかります。

>・それぞれの列の合計と、行の合計にroundをかけて小数点以下 第2位まで（=round(SUM(C4:I4),2))としてみても、小数点以下 第14位の「1」は消えませんでした。

この部分はおそらく勘違いをされていると思います（小数点2桁目で四捨五入された値が出てくるはずです）。

マックちゃん様

ご返信ありがとうございます。

J列の値を15桁表示にするという確認は、投稿前に行ないましたが、15桁表示にしても小数点第2位以下はすべて0でした。

下記に画像を添付します。

J列の各数字は、小数点以下第14位に数字を持っておらず、にもかかわらず合計では「1」が入ってしまうため、分からなくて質問しました。

行の合計にroundをかけて小数点以下 第2位までにしても、J列に表示される数字はRoundなしの場合と同じです。

Roundをかけた画像を添付します。（計算式が見えるようにしてあります。またJ列は「形式を選択してコピー」で計算式をコピーしており、他のセルも同じ計算式です）

そして、Roundありでも、J列の合計も同じです。

ですが、「2進数で表示するために発生する」という理由がわかりましたので、すっきりしました。

ありがとうございました。

Answer 3

コンピュータで、小数点以下がある数値を合計すると、こういう誤差が出るのが普通です。

理由は2進数では、0.1以下を表現する場合、２^-n　つまり0.5、0.25，0.125・・・・・　の合計となりますので、大半の数字が無限小数（循環小数）になり、数字を表現するビット数が有限のため、打切り誤差が生じるためです。

y sakuda 様

分かりやすいご説明ありがとうございました。

すっきりしました。

最後の比較時の計算式にroundをかける運用でやっていきます。

ありがとうございました。

Answer 4

> SUM(J4:J34)　　 3.50000000000001

　 SUM(C35:I35)　　3.50000000000000

J列の合計が3.5にならずに微小な誤差が出てくるのは、たとえばJ列の値そのものが、実際は小数点一桁の数字ではなく端数を持った数字になっているためです。

これを確認するにはJ列のセルを選択して表示形式を数値にして小数点以下15ケタ表示してみるとわかります。

このような誤差が出るのは小数点以下の数字を演算するときに発生する丸め誤差（2進数で表せない数字のため）によるものです。

このような丸め誤差が発生しやすいのは、加算よりも減算するときに発生しやすい傾向があります（誤差の大きさによっては自動修正機能が働くケースもあります）。

すなわち、SUM関数の場合は負の値が混ざっている場合で、たとえば12.2、-10.4、-1.6の3つの数字をSUM関数で集計すると0.199999999・・のような数字になることがわかります。

したがって、35行目のデータも同じような誤差が発生していますが、偶然誤差同士でプラスマイナス打ち消しあって見かけ上誤差が消えた可能性があります。

>・それぞれの列の合計と、行の合計にroundをかけて小数点以下 第2位まで（=round(SUM(C4:I4),2))としてみても、小数点以下 第14位の「1」は消えませんでした。

この部分はおそらく勘違いをされていると思います（小数点2桁目で四捨五入された値が出てくるはずです）。