エクセル浮動小数点の問題？

質問

添付画像のような表があります。
A1：A5には数値が入力してあります。
1つずつ中を確認し、表示通りであることを確認しています。
D1セルは　=ROUNDDOWN(20000/640800,2)+0.005　の数式で、0.035が表示されています。
D3セルは　=D1=A4　の数式でTRUEが表示されています。
D5セルは　=VLOOKUP(D1,A1:B5,2,FALSE)　の数式で　#N/Aエラーが返っています。
たぶん、エクセル特有の浮動小数点の問題ではないかとあたりをつけ、
D5セルを　=VLOOKUP(ROUND(D1,3),A1:B5,2,FALSE)　に変えると、ちゃんと4が返りました。
でも、そうだとすると、=D1=A4の数式でTRUEが返るのはなぜですか？
ROUND(D1,3)としないとA4とマッチしないのですから、=D1=A4の式はFALSEが返るはずですよね？

回答No.5

bunjii · ベストアンサー

>ところでVLOOKUPで参照するA列の数値ですが、こちらは計算ではなく直接　0.035　のように入力されていますが、こちらは演算誤差は考えなくてもよいのでしょうか？それとも=ROUND(0.035,3)のようにしなければいけないのでしょうか？
キーボードで直接入力された値は端数が無いので前処理は不要のようです。
見方を変えてA列の値が次のようになっているときは質問の　=VLOOKUP(D1,A1:B5,2,FALSE)　で4が返り、=VLOOKUP(ROUND(D1,3),A1:B5,2,FALSE)　では#NAが返ります。
A1=(ROW()-1)/100+0.005
A1を下へA5までコピーする。
従って、検索範囲のデータは一貫した方法で作成し、検索値は検索範囲に合わせた値に処理する必要があります。

お礼

>キーボードで直接入力された値は端数が無いので前処理は不要

それはよかった！安心しました。
何度もありがとうございました。

回答No.4

bunjii

>演算誤差はやむを得ないと思いますが端数を有効とする処理と四捨五入する処理が異なるのは困りますね。
そうですね。
ソフトの性質を知っていれば対応策が見つかります。
=VLOOKUP(ROUND(D1,3),A1:B5,2,FALSE)
あなたが考えた上記の数式は対応策の1つです。
D1セルの数式で+0.005が誤差を生じる原因なので次のように修正する方法もあるでしょう。
D1セル　=ROUND(ROUNDDOWN(20000/640800,2)+0.005,3)
D1セル　=ROUNDDOWN(20000/640800,2)+ROUND(0.005,3)　は解決になりません。

お礼

bunjiiさん、ありがとうございます。
>　D1セル　=ROUND(ROUNDDOWN(20000/640800,2)+0.005,3)
わかりました。このようにすればいいのですね。
ところでVLOOKUPで参照するA列の数値ですが、こちらは計算ではなく直接　0.035　のように入力されていますが、こちらは演算誤差は考えなくてもよいのでしょうか？それとも=ROUND(0.035,3)のようにしなければいけないのでしょうか？

回答No.3

bunjii

>ROUND(D1,3)としないとA4とマッチしないのですから、=D1=A4の式はFALSEが返るはずですよね？
浮動小数点演算の誤差によるもののようです。
=(D1+1*10^-16)=A4 → FALSE
=(D1+1*10^-17)=A4 → TRUE
=(D1-1*10^-17)=A4 → TRUE
D1の値には1*10^17未満の端数が付いていると思われます。
関数によってその端数を有効とする処理と無効（四捨五入）とする処理が異なると考えられます。
つまり、Excelのバグ（一貫性が無い）と言えるでしょう。
貼付画像はExcel 2013でVLOOKUPの検索条件を変えて検証した結果です。

お礼

ありがとうございます。
演算誤差はやむを得ないと思いますが端数を有効とする処理と四捨五入する処理が異なるのは困りますね。

回答No.2

intin

エクセルは、
自動で誤差補正が入るようなのですが、
誤差補正が、状況により入ったり入らなかったりするようです。

例えば、
下記２つは、同じ意味の式なのに、
結果が変わってしまいます。

=D1=A4　→　TRUE
=D1-A4=0　→　FALSE

だぶん、
「D1=A4」のほうは、自動誤差補正が入っていて、
「D1-A4=0」のほうは、自動誤差補正が入っていないのだと思います。

同様に、
vlookup関数についても、
自動誤差補正が入らないパターンなのだろうと思います。

このような自動誤差補正が入るか入らないかが違うため、
if関数だとTRUEなのに、vlookup関数だと値が拾えない、
という状況になってしまっているのだと思います。

参考URL：

http://www.excelspeedup.com/syousuugosa/

お礼

ありがとうございます。
自動誤差補正なんてのがあったのですね、それで納得ですが、補正したりしなかったりがあると困りますね。
マイクロソフトはその区別を公開してくれるといいのですが。

回答No.1

msMike

セル D1 には 式 =0.03+0.005 を入力したことに等しいですね。
この場合でも、セル D5 の戻り値は #N/A です。
しかしながら、セル D1 に数値 0.035 とか式 =35/1000 を入力すると、#N/A でなく 4 の正答が返ります。少し纏めると(?)
…、350/10000、=35/1000、=3.5/100 まではＯＫだけど、
=0.35/10、=0.035/1、=0.00035*100、…以上はＮＧになる。
しかし、=0.0035*10 はなぜか(たまたま？)ＯＫ。

良くはワカランですけど、とにかく小数点付きの数式は御「浮動」様のご機嫌を損ねることが多いので要注意と、私は昔から肝に銘じております。クワバラ、クワバラ。

お礼

ありがとうございます。
ほんと、御「浮動」様は気難しいですね・