エクセルVLOOKUP検索値がオートフィルはNG?

質問

エクセルのVLOOKUP関数について質問です。
「VLOOKUP( 」と入力して最初に設定する「検索値」は、オートフィルで生成した連続値の場合は検索されないことがあるのでしょうか？

例えば検索値は0から10まで整数で10個あります。
これをA1セルに0として、A2セル以下を「=A1+1」とすると、検索されない数字があります。
そこで試しに手入力にしたところ、検索されない数字はありませんでした。
その他にオートフィルしたものをコピーしてA列に「値」としてペーストして表面上は手入力と全く同じになるようにした場合は、やっぱり検索されない数字があります。
それもオートフィルの場合と全く同じ数字が検索されません。
なお書式は全てのセルで標準にしてあり、検索方法はFALSEにしました。
検索方法をTRUEにすると、FALSEの時にエラーになる数で直近のセルの値を返すようです。
これはどのような理由でこうなってしまうのでしょうか？

手入力なら問題ないようなので手入力することも考えていますが、なにしろ10000行を超えてしまうので、できればオートフィルを使いたいのです。
解決方法がありましたら教えて下さい。
宜しくお願い致します。

回答No.5

bunjii · ベストアンサー

>A1セル[　0　]
>A2セル[ =ROUND(A1,2)+0.1 ]
A2セルの数式は四捨五入してから0.1を加算していますので無意味です。
私が提示した数式はROUND関数の第1引数（数値）へ A1+0.1 と入力しています。
つまり、0.1を加算したときに浮動小数点演算で微小な誤差が稀に発生します。
A2=ROUND(A1+0.1,2)

お礼

回答をありがとうございます。
ご教示下さいました方法でエラーが出ることなく表示しました。
浮動小数点演算では完全に一致するわけではないということなのでしょうか。
この度は本当にありがとうございました。
重ね重ね、お礼申し上げます。

回答No.4

bunjii

>これをA1セルに0として、A2セル以下を「=A1+1」とすると、検索されない数字があります。
それは嘘でしょう。
小さい値の整数同士の加算では微小誤差は生じませんので、あり得ない例示です。

>これはどのような理由でこうなってしまうのでしょうか？
>A列の行番号2の数値 ( A1はタイトル　A2はゼロ、A3～20まで0.1刻みでオートフィルした連続数として (A20は1.8です)、このように左端に入力して、
B列に返す値を入力し、A列の二行目からB列の20行目までを範囲とし、列番号を2(この場合でB列)としました。
増分を小数としたときは浮動小数点演算になるため微小の値が加減されることもありますので比較値との間で不一致が発生することも起こります。
防止方法としてはA3セルに次の数式を入力してオートフィルコピーを行ってください。
=ROUND(A2+0.1)
オートフィルによる不具合ではなく浮動小数点演算の微小誤差によるものと思います。

補足

回答ありがとうございます。
セルと数式をこのようにしました。

A1セル[　0　]
A2セル[ =ROUND(A1,2)+0.1 ]　
　　↓
　A10までオートフィル

A10セル[　0.9　]

B列　B1～B10まで(返す値を手入力)　(B1は0,B2は1,B3は2 ... B10は9です)

C列　検索する値を入力、A列の0～0.9と同じ値になるように、且つオートフィルを使わずに「手入力」しました。

D列　[=VLOOKUP(C1,$A$1:$B$10,2,FALSE)　]
　　↓
　D10までオートフィル

結果:D列に表示されているのは

D1　0
D2　1
D3　2
D4　#N/A
D5　4
D6　5
D7　6
D8　7
D9　#N/A
D10 9

となっています。
このようになる理由を教えて下さい。
宜しくお願いします。

回答No.3

msMike

[No.2補足]へのコメント、
僅か最初の３行までしか読み終わってませんが、確認させてください。
》 A2はゼロ、A3～20まで0.1刻みでオートフィルした
》 連続数として (A20は1.8です)
あれ？質問文と矛盾してませんか？
》 「A1セルに0として、A2セル以下を「=A1+1」とする
と仰っていたではありませんか？
A2 を 0 とするなら、A20 は整数の 18 でしょ！
整数と少数では偉い違いです。特に Excel というよりコンピュータでは整数の計算と少数の計算では天と地の差があるかも。
だから、具体例の提示を私がお願いした次第です。

お礼

回答をありがとうございました。
しかし、C列の検索する値を、A列と全く同じ方法でオートフィルして入力した場合は、エラーなく全てのセルで値を返します。
反対に両方を手入力した場合もエラーなく全てのセルで値を返すようです。
どこに原因があるのでしょうか・・・？

補足

回答をありがとうございます。
整数と少数では違うとは思いませんでした。
結果的に嘘を書いてしまいまして、済みませんでした。
正確にはこのように入力してあります。

回答ありがとうございます。
セルと数式をこのようにしました。

A1セル[　0　]
A2セル[ =A2+0.1 ]　
　　↓
　A10までオートフィル

A10セル[　0.9　]

B列　B1～B10まで(返す値を手入力)　(B1は0,B2は1,B3は2 ... B10は9です)

C列　検索する値を入力、A列の0～0.9と同じ値になるように、且つオートフィルを使わずに「手入力」しました。

D列　[=VLOOKUP(C1,$A$1:$B$10,2,FALSE)　]
　　↓
　D10までオートフィル

結果:D列に表示されているのは

D1　0
D2　1
D3　2
D4　#N/A
D5　4
D6　5
D7　6
D8　7
D9　#N/A
D10 #N/A

となっています。
このようになる理由を教えて下さい。
宜しくお願いします。

回答No.2

msMike

》 これをA1セルに0として、A2セル以下を「=A1+1」とすると、
》 検索されない数字があります。
その検索されない数字は、具体的に何ですか？

補足

回答を、ありがとうございます。
A列の行番号2の数値 ( A1はタイトル　A2はゼロ、A3～20まで0.1刻みでオートフィルした連続数として (A20は1.8です)、このように左端に入力して、
B列に返す値を入力し、A列の二行目からB列の20行目までを範囲とし、列番号を2(この場合でB列)としました。
具体的に返ってきた値のC行は、C5(A5)(0.3)で#N/A、C10～C13(A10～A13)(0.8～1.1)で#N/A、C16～C20(A1.4～1.8)で#N/Aでした。
いろいろ試しましたところ、オートフィルで生成した数値、文字列では、セル上で検索値と寸分違わない値でもVLOOKUP関数では見えていないかのごとく振る舞っているように思いました。
なにしろ、オートフィルで生成した0.1刻みの数値を、コピーして、数値として左端の列に貼り付けても、オートフィルの場合と同じように、検索値が存在しないかのように反応を返します。
逆に、手入力で0.1刻みで入力しますと、正確に値を返してくれます。
このように、セル上では値を返してくれる値と全く同じように表示されるにもかかわらず、値を返さない数に関して、隠しパラメーターでもあるのかと思えてなりません。
どのようにすれば1万行の入力をぜすにオートフィルで生成した数で正常動作してくれるのでしょうか？
教えて下さい、宜しくお願い致します。

回答No.1

aokii

C1セルに以下の式を入れ、B1セルに3と入れてみて下さい。
=VLOOKUP(B1,A:A,1,FALSE)

あるいは、処理時間が長くかかっていて、計算が終わるまで待たなければいけないのかも。

お礼

回答をありがとうございます。
現在頭が混乱しております。
ご教示下さいました方法につきまして、整理がついてから試行させて頂きます。
まずはお礼まで。

補足

整数の場合はオートフィルでも問題なく値を返してくれることが分かりました。
回答をくださいましてありがとうございました。