エクセルのVLOOKUP関数で検索範囲について

質問

VLOOKUP関数の検索範囲がおかしなことになっているので教えてください

VLOOKUP(k1,a1:e43,2)のようにしたとき、この検索範囲が、ある一定の量を超えると、演算結果が０となってしまいます。
初めは、４３行を指定して問題なかったのですが、
そのファイルをコピーしていくつか修正を加えバックアップファイルを作成したところ、検索値が０となってしまいました。４３としていたところを、３０にすると正確な数値を検索します。
さらに別ののファイルでは、１５まで落ち込んでしまいました。検索値が０となるだけで、エラーにはなりません。
a1の値は、１から４３までの正数です。
確認のため、１番目しかデータを入力していません
１番目が表示されないのでは、２番目もないと考えています。
また、検索値のa1は、２０程度入力してあります。それ以降が０でも問題なく動作しています。
どなたか、ヒントになることをレスしてください。

回答No.4

chie65536 · ベストアンサー

＞それが、ファイルをコピーしたことで、崩れてしまったとは思えません

これは「単なる偶然」に過ぎません。

「コピー前は、検索結果は不定だけど、偶然、辻褄が合う検索結果が返って来た。コピー後は、検索結果は不定だけど、偶然、辻褄が合わない検索結果が返って来た」というだけの話です。

「結果は不定」の意味を良く考えて下さい。この「不定」は「毎回結果がコロコロ変る」って意味じゃありません。

この場合の「結果は不定」と言うのは「あるファイルでは、偶然、正しそうに見える結果が返る事もある。違うファイルでは、データの値が同一でも、間違った結果が返る事もある。何がその違いを左右しているのかも判らない。つまりファイルをコピーしただけで結果が変るかも知れない」って事です。

「偶然、正しそうに見える結果が『不定な結果』として返されていただけで、使い方が間違っている」のは明白です。

「第４パラメータを省略したら、検索データは『絶対に』昇順に並んでないといけない」と言う決まりがある限り、その仕様に合わせて使うしかありません。

その決まりに不満なら、エクセルを使うのをやめるか、マイクロソフトに文句を言って下さい。

お礼

そのとおりですね
偶然とは恐ろしいものです。
出来るだけ正しい使い方を心がけます。
ありがとうございました。

回答No.3

chie65536

追加。

「範囲(A1:E43)の左端の列(つまりA1～A43)は、昇順に並んでいる必要があります」と言う事は、言い替えれば「A1～A43は全部データが埋まってないとダメ」って事です。

何故なら「空白セルは、最も値が小さいので、昇順に並べた時は先頭に来なければならない」からです。

つまり、データがA1～A20にしか入力されてないなら、A21～A43は空白セルになってしまい「空白セルが先頭にないなら、昇順に並んでる事にならない」と言う事になります。

データがA1～A20にしか入力されてないなら、
VLOOKUP(K1,A1:E20,2)
と入力されている範囲に合わせるか
VLOOKUP(K1,A:E,2)
のように全行を検索範囲にしましょう。

補足

全行を検索範囲にしも結果が同じです。
検索値の値は、空白ではなく、０が入力されています。
０があっても、別のファイルでは検索は正常に機能していました。

回答No.2

chie65536

VLOOKUPの第４パラメータが省略されているので、第４パラメータはTRUEが指定された事になっています。

第４パラメータを省略するかTRUEにした時は、範囲(A1:E43)の左端の列(つまりA1～A43)は、昇順に並んでいる必要があります。

昇順に並んでいない場合「何が検索結果になるかは不定」になります。

多分「昇順に並んでいないといけない」というのを知らずに、データの修正をした時に並びを壊したのだと思います。

３０で正常、３１で異常になるファイルでは、３０番目と３１番目の大小関係が逆になっていると思われます。

１５で正常、１６で異常になるファイルでは、１５番目と１６番目の大小関係が逆になっていると思われます。

「データの並べ替えで検索テーブルが常に昇順になるようにする」か「VLOOKUPの第４パラメータをFALSEにする」かで対処して下さい。

なお「VLOOKUPの第４パラメータをFALSEにした」場合は、ソートしなくても済む代わりに、近似値は検索出来なくなります。つまり、完全に一致する値だけ検索され、見付からない場合は結果は「#N/A」になります。

補足

パラメーターは、省略しています。
また、１５番以降のデータは、０で昇順に並んでいません。
４３まで指定しているのですが、２１までが昇順になっていて、以下が０です。（１５の場合は、昇順に並んでいるデータは７です）それでも、使えていたのです。
それが、ファイルをコピーしたことで、崩れてしまったとは思えません

回答No.1

wisemac21

=VLOOKUP(k1,a1:e43,2)のデータ範囲を絶対参照にしていなからでは？
=VLOOKUP(k1,$a$1:$e$43,2)にすればどうなりますか

補足

早速回答ありがとうございます。
絶対参照でも変わりません。=VLOOKUP(k1,a:e,2)のように行番号を省略しても同じです。

なお、シートはもう少し複雑で、VLOOKUP関数を多用しています。といっても、１５０個ぐらいですが
また、a列は、他のシートにリンクしています。
でも関係ないですよね。
ファイルをコピーしたときに、こっちで出来ていたことが、こちらでは出来なくなるというのが分かりません。
さらに、出来ない度合いも違っているのがしゃくに障ります。