エクセルで計算結果がおかしい

質問

エクセル2002で生産管理をやっています。おかしな計算結果が出ます。生産管理データそのままでなく、関係ない部分は省略した構成にして相談します。
1.入力データと計算式
A1：2305.6　　 　A3：SUM（A1:A2）
B1： 334.1 　 B3：SUM（B1:B2)
C1： 41.4 　 C3：SUM（C1:C2)
D1：　 3.3 　 D3：SUM（D1:D2）
D4：A3+B3+C3+D3＝2684.4
D5：D4-A1-B1-C1-D1＝0.0
D6：（A3+B3-A1-B1)/D5＝-0.7
2.おかしいところ
D6は、0÷0＝エラー　になるはずですが、-0.7になります。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

>D5：D4-A1-B1-C1-D1＝0.0

ここで、浮動小数点誤差が発生してまいす。
小数点の演算は気をつけたほうがいいです。

これでよいのかは、実際のデータを当てはめていないので、サンプルだけにしか分かりませんが、

=ROUNDDOWN(D4-A1-B1-C1-D1,1)

有効な小数点までを扱うようにしたほうがよいです。
今は、詳しく調べていませんが、本来は、正式な計算方法があります。
その一つに、やはり整数として、10倍して計算して、元に戻す方法があります。

お礼

回答ありがとうございます。割り算の結果を出す場合だけ、10倍してから、10で割るという方法もいいかもしれませんね。+-×は、関係ないような気がします。

回答No.7

Wendy02

こんばんは。#3 のWendy02です。

>+-×は、関係ないような気がします。

私も最初ないと思っていましたが、演算すべてにあるはずです。また、専用の関数以外の一般の関数を使っても、内部で発生するはずです。

小数点を含む演算の過程で、丸め誤差が発生します。単独の小数に、誤差は存在していますが、演算しない限りは、発生しません。

また、四捨五入の方法は、有効桁数を良く認識して使わないと、間違うことがありますので、気をつけないといけません。

今回は、小数点第13位に出ていたので、ROUNDDOWNで、第一までを求めましたが、有効桁数の一つ下の位に、数値を足さなくてはならなかったようです。つまり、今回は、0.01を足さないといけないわけですね。失礼しました。

後は、FIXED 関数を使う方法が、一般的な浮動小数点丸め誤差に対する方法だと思います。FIXED関数は、丸め誤差に対する専用の関数です。

FIXED関数を使う方法
=VALUE(FIXED(D4-A1-B1-C1-D1,1))

補足

すみません。説明不足かもしれません。
＋は、相当の数を足さないと、0.05まで（すなわち、下1桁に影響与えるまで）達しません。－も同様に相当の数を引かないと影響でないと思います。×も相当数掛けないと、一度くらいの掛け算では、0.05まで達しないと考えます。÷だけが、たとえば、マイナス13桁近くのデータとマイナス13桁近くのデーで割ると、そこそこの大きな数字になってしまいます。したがって、下1桁の管理をするようなデータであれば、÷の計算結果だけROUND関数やFIXED関数を使えばいいような気がするのですが。

回答No.6

chie65536

誤差です。D6の式の中の「A3+B3-A1-B1」と「D5」が「限りなく０に近い非０」になってます。
D5を「=TRUNC(D4-A1-B1-C1-D1,1)」と変えて、小数点以下１桁で切り捨てれば、めでたく「#DIV/0!」の表示が出ます。

お礼

D6とD5が、直感的には、「0」のはずなのに、「0に近い非0」になってしまうのは、代数的には間違いなのでくやしい。多分2進数が原因だと思います。
　会社では、銘柄別生産管理表が、数十枚/月あります。品質でA1、B1、C1、D1と分けて重量管理しています。そして、「A1」、「A1+A2」、「D1」の比率も算出しています。重量は、下1桁で管理しています。こういった場合は、滅多にないことですが、割り算の結果だけ関数使用すればいいと思われますがいかがでしょうか。
　ともあれ、回答者の皆さんありがとうございました。ANo.6のchie65536さんの欄でまとめてお礼させていただきます。

回答No.5

merlionXX

エクセル（というか、パソコン）特有の浮動小数点誤差が表面化しているのでしょう。
有効桁数が小数点以下、1位であれば
D5：D4-A1-B1-C1-D1を
=ROUND(ROUND(D4,1)-ROUND(A1,1)-ROUND(B1,1)-ROUND(C1,1)-ROUND(D1,1),1)
としてみてください。

お礼

回答ありがとうございます。原因がわかれば、対策もわかるわけですが、私は、割り算の結果だけをmerlionXXさんの回答のように四捨五入の関数を使うといいと考えています。

回答No.4

toshi_2000

D5の値は、1.6076E-13になります。
A3+B3-A1-B1の値は、-1.13687E-13になります。
エクセルの小数点以下の演算誤差です。

参考URL：

http://pc.nikkeibp.co.jp/pc21/special/gosa/index.shtml

お礼

回答ありがとうございます。割り算の結果が、とてつもない数字になると、おかしいとわかるのですが、それらしい数字になると生産管理のデータですから危険ですね。通常の生産管理表は、数字が埋まっていますから、質問のようなことにはなりません。入庫取り消しとか異常な状態のときになるわけですが、気づきにくくどうしようもないですよね。

回答No.2

neKo_deux

> D6は、0÷0＝エラー　になるはずですが、

A2の値が不明なので、A3の値が不明。
A3の値が不明なので、D6の値も不明。
従って、質問文の内容からは判断できません。

質問者さんがD6の計算式の「（A3+B3-A1-B1)」の部分が0になると勘違いしているのでは？くらいの事しか分かりません。

回答No.1

FEX2053

D5セルを、「書式」「セル」の「表示形式」で「標準」にしてみて下さい。
そうすると、2.5E-22 とかの「とても小さい指数の数字」が出てくるのでは
ないでしょうか。

だとすれば全然おかしいことはないと思いますけど。

お礼

回答ありがとうございます。やってみるとその通りになります。代数的には、0÷0＝エラーですよね。要は、「原因」と「間違った結果が出る場合があるのでどうしたらいいか」ですが。