エクセルで同じ式なのに、数値によって値が変わる

助けてください。
非常に簡単な式ですが、数値によって結果が違い困っています。

a-bが許容内であるか場合"OK"、許容差外の場合"NG"
という数式を以下のように作りました。
(本当はもう少し複雑ですが、便宜上簡単にしました)
=IF(A2-B2<=C2,"OK","NG")

添付の画像を見ていただくと分かりますが
29.90-29.30=0.60　結果"OK"
30.00-29.40=0.60 結果"NG"
同じ数値であるにも関わらず、結果が違い困っています。
小数点2以下は全てゼロです。
色々試したところ、どうやらcの値の大きさで計算結果が変わるようなんです。

どなたかお詳しい方、ご解説ください。
よろしくお願い致します。

No.6 ベストアンサー 回答者： asciiz 回答日時： 2012/03/12 18:04

C列の小数点以下の桁数をもっと増やしてみてください。

なぜ、0.6以上・未満と判別されてしまうか、わかると思います。
何故このような誤差が出てしまうか。

コンピュータの数値表現は、2進法です。
でもそれではあまりに人間に分かりにくいため、10進数に直して表示しています。

さてここで問題なのは、10進数の「0.1」や「0.01」というのは、2進数にとってはどこまでも割りきれない、無限少数なのです。

(10進数の)0.1 ＝ 0×1/2 + 0×1/4 + 0×1/8 + 1×1/16 + 1×1/32 + 0×1/64 + 0×1/128 + 1×1/256 + …
　　　　　　　　　　　　↓
(二進数では) 0.00011001…

ですから、小数点の入った数値同士の演算には必ず誤差が入り、見た目が同じでも、その末尾の方は違っていることが多々あるわけです。

誤差が入らないこともあります。
二進数の世界で、割りきれるときです。

例えば、参考画像の下半分では、1/4、0.25単位の数値を入れてみました。
桁をいくら増やしても、誤差が出ていないことが見て取れます。(Excel2003)


現実問題として、0.60と0.59の間で判定したいなら、0.595 とか、最小単位の半分を増減して閾値にすると良いのではないでしょうか…。

No.7 回答者： asciiz 回答日時： 2012/03/12 18:08

ANo.6の添付画像を間違えましたので再掲させていただきます…。

この回答へのお礼

皆様、短時間でのご回答ありがとうございました。

エクセルの小数点にこのような事が在るとは、まったく知りませんでした。
とゆうか、知らない方のほうが多いかと思います。
マイクロソフトにはしっかり周知して貰いたいです。

ともかく、ご回答頂きました皆様ありがとうございます。
疑問か解消されすっきりしました。

また何かありましたら、よろしくお願い致します。
ペコリ

お礼日時：2012/03/12 18:23

No.5 回答者： chie65535 回答日時： 2012/03/12 17:50

どこかのセルに

=C1-0.6

と

=C2-.06

の２つを入力してみましょう。

=C1-0.6

の結果は「-2.10942E-15」となります。

これは「C1は、0.6よりも、0.0000000000000021094237467878だけ小さい」と言う意味です。

=C2-.06

の結果は「1.44329E-15」となります。

これは「C2は、0.6よりも、0.000000000000001443289932012700だけ大きい」と言う意味です。

これらの誤差は、それぞれ「29.90」「29.30」「29.40」が、コンピュータ内部の２進数の表現で「循環小数」になってしまうからです。

循環小数ですから「ピッタリ同じ値」をメモリ上で記憶するには「無限にメモリが必要」になってしまいます。

なので、仕方が無いので、小数点の下の方で「スッパリとカット」してしまいます。

この「カットした分」だけ、実際の値より小さくなってしまうのです。

カットされる量は「数値によってバラバラで不定」ですから、どれだけ小さくなるか判りません。

「29.90よりも、29.30の方が、カット量が多い」なら「29.90-29.30」の結果は「0.6よりも微妙に小さい」になります。

「30.00」は30.0のままですが「29.40」も、微妙にカットされるので「30.00-29.30」の結果は「0.6よりも微妙に大きい」になります。

嘘だと思うなら、

C1、C2のセルの表示書式を「数値」にして「小数点以下の桁数」を「15以上」にしてみましょう。

そうすると、いままで「0.6」って表示されていたセルが

0.599999999999998

と

0.600000000000001

になって「本当の値」が表示されますよ。

因みに「0.6」も「循環小数」です。

これらの誤差を出したくないなら「すべて100倍して計算して、小数点を使わない」しか方法はありません。

「29.90」は「2990」で、「29.30」も「2930」で計算するのです。

「2990-2930」を求めて、それが「60以上か、60未満か」って判定をしないと駄目なのです。

もちろん「29.90*100」とか書いてはいけません。「29.90」って書いた段階で「誤差を含んでいる」ので、１００倍しても誤差を含んだままで、無意味です。

つまり「小数点を一切使わない値と、小数点を一切使わない式のみで書いて、最後の表示の時に調整する」って事です。

No.4 回答者： sunbravery 回答日時： 2012/03/12 17:48

ちなみに下記のとおりですと問題なく通ります。

小数点以下の減算が桁オーバーになる？

=IF((A2*100-B2*100)/100<=C2,"OK","NG")

No.3 回答者： sunbravery 回答日時： 2012/03/12 17:34

答えになりませんが試したところ不思議な現象が起きました。

29.9-29.3は　0.5999999999999980となります。
30.0-29.4は　0.6000000000000010となるのです。

また
=ROUND(A2,2)-ROUND(B2,2)は　0.599999999999998
となります。

Excelは16桁が計算値の制限ですのでこの辺りに関係がありそうですが？

No.2 回答者： midorinodonchan 回答日時： 2012/03/12 16:55

すみません、全然回答になっていませんが、

Excel2010で試したところ同じ結果になりました。

私も答えが知りたいです。
どなたかお願いします。

No.1 回答者： hallo-2007 回答日時： 2012/03/12 16:50

パソコンで起きる演算誤差ですね

http://pc.nikkeibp.co.jp/pc21/special/gosa/eg1.s …
を参考にしてください。
=IF(A2-B2<=C2,"OK","NG")
は
=IF(Round(A2-B2,-10)<=Round(C2,-10),"OK","NG")
とかで適当な桁数で四捨五入します。

この回答へのお礼

早速のご回答ありがとうございます。

aとbの値はキーボードで入力しています。

今回の場合ですと、
30⇒エンター
29.4⇒エンター
のようにしているため整数(とはいいませんが)で、演算誤差ではないと思われます。
ためしに小数点以下を最大まで増やしましたが、ゼロのままです。
それとも打ち込んだだけで、誤差が出てしまうのでしょうか？
それですと何かしら問題が出てきそうで、エクセルを使いづらいです。

お礼日時：2012/03/12 17:02