みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

添付画像のような表があります。
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が返るはずですよね?

投稿日時 - 2016-02-19 18:10:27

QNo.9130645

困ってます

質問者が選んだベストアンサー

>ところで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までコピーする。
従って、検索範囲のデータは一貫した方法で作成し、検索値は検索範囲に合わせた値に処理する必要があります。

投稿日時 - 2016-02-21 08:14:08

お礼

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

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

投稿日時 - 2016-02-21 10:10:17

ANo.5

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

>演算誤差はやむを得ないと思いますが端数を有効とする処理と四捨五入する処理が異なるのは困りますね。
そうですね。
ソフトの性質を知っていれば対応策が見つかります。
=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) は解決になりません。

投稿日時 - 2016-02-20 15:58:14

お礼

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

投稿日時 - 2016-02-20 22:33:53

ANo.3

>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の検索条件を変えて検証した結果です。

投稿日時 - 2016-02-20 09:23:59

お礼

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

投稿日時 - 2016-02-20 15:25:46

ANo.2

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

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

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

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

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

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

参考URL:http://www.excelspeedup.com/syousuugosa/

投稿日時 - 2016-02-19 22:45:01

お礼

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

投稿日時 - 2016-02-20 15:19:05

ANo.1

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

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

投稿日時 - 2016-02-19 20:38:50

お礼

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

投稿日時 - 2016-02-20 15:16:16

あなたにオススメの質問