VLOOKUP関数の検索範囲がおかしなことになっているので教えてください
VLOOKUP(k1,a1:e43,2)のようにしたとき、この検索範囲が、ある一定の量を超えると、演算結果が0となってしまいます。
初めは、43行を指定して問題なかったのですが、
そのファイルをコピーしていくつか修正を加えバックアップファイルを作成したところ、検索値が0となってしまいました。43としていたところを、30にすると正確な数値を検索します。
さらに別ののファイルでは、15まで落ち込んでしまいました。検索値が0となるだけで、エラーにはなりません。
a1の値は、1から43までの正数です。
確認のため、1番目しかデータを入力していません
1番目が表示されないのでは、2番目もないと考えています。
また、検索値のa1は、20程度入力してあります。それ以降が0でも問題なく動作しています。
どなたか、ヒントになることをレスしてください。
No.4ベストアンサー
- 回答日時:
>それが、ファイルをコピーしたことで、崩れてしまったとは思えません
これは「単なる偶然」に過ぎません。
「コピー前は、検索結果は不定だけど、偶然、辻褄が合う検索結果が返って来た。コピー後は、検索結果は不定だけど、偶然、辻褄が合わない検索結果が返って来た」というだけの話です。
「結果は不定」の意味を良く考えて下さい。この「不定」は「毎回結果がコロコロ変る」って意味じゃありません。
この場合の「結果は不定」と言うのは「あるファイルでは、偶然、正しそうに見える結果が返る事もある。違うファイルでは、データの値が同一でも、間違った結果が返る事もある。何がその違いを左右しているのかも判らない。つまりファイルをコピーしただけで結果が変るかも知れない」って事です。
「偶然、正しそうに見える結果が『不定な結果』として返されていただけで、使い方が間違っている」のは明白です。
「第4パラメータを省略したら、検索データは『絶対に』昇順に並んでないといけない」と言う決まりがある限り、その仕様に合わせて使うしかありません。
その決まりに不満なら、エクセルを使うのをやめるか、マイクロソフトに文句を言って下さい。
No.3
- 回答日時:
追加。
「範囲(A1:E43)の左端の列(つまりA1~A43)は、昇順に並んでいる必要があります」と言う事は、言い替えれば「A1~A43は全部データが埋まってないとダメ」って事です。
何故なら「空白セルは、最も値が小さいので、昇順に並べた時は先頭に来なければならない」からです。
つまり、データがA1~A20にしか入力されてないなら、A21~A43は空白セルになってしまい「空白セルが先頭にないなら、昇順に並んでる事にならない」と言う事になります。
データがA1~A20にしか入力されてないなら、
VLOOKUP(K1,A1:E20,2)
と入力されている範囲に合わせるか
VLOOKUP(K1,A:E,2)
のように全行を検索範囲にしましょう。
この回答への補足
全行を検索範囲にしも結果が同じです。
検索値の値は、空白ではなく、0が入力されています。
0があっても、別のファイルでは検索は正常に機能していました。
No.2
- 回答日時:
VLOOKUPの第4パラメータが省略されているので、第4パラメータはTRUEが指定された事になっています。
第4パラメータを省略するかTRUEにした時は、範囲(A1:E43)の左端の列(つまりA1~A43)は、昇順に並んでいる必要があります。
昇順に並んでいない場合「何が検索結果になるかは不定」になります。
多分「昇順に並んでいないといけない」というのを知らずに、データの修正をした時に並びを壊したのだと思います。
30で正常、31で異常になるファイルでは、30番目と31番目の大小関係が逆になっていると思われます。
15で正常、16で異常になるファイルでは、15番目と16番目の大小関係が逆になっていると思われます。
「データの並べ替えで検索テーブルが常に昇順になるようにする」か「VLOOKUPの第4パラメータをFALSEにする」かで対処して下さい。
なお「VLOOKUPの第4パラメータをFALSEにした」場合は、ソートしなくても済む代わりに、近似値は検索出来なくなります。つまり、完全に一致する値だけ検索され、見付からない場合は結果は「#N/A」になります。
この回答への補足
パラメーターは、省略しています。
また、15番以降のデータは、0で昇順に並んでいません。
43まで指定しているのですが、21までが昇順になっていて、以下が0です。(15の場合は、昇順に並んでいるデータは7です)それでも、使えていたのです。
それが、ファイルをコピーしたことで、崩れてしまったとは思えません
No.1
- 回答日時:
=VLOOKUP(k1,a1:e43,2)のデータ範囲を絶対参照にしていなからでは?
=VLOOKUP(k1,$a$1:$e$43,2)にすればどうなりますか
この回答への補足
早速回答ありがとうございます。
絶対参照でも変わりません。=VLOOKUP(k1,a:e,2)のように行番号を省略しても同じです。
なお、シートはもう少し複雑で、VLOOKUP関数を多用しています。といっても、150個ぐらいですが
また、a列は、他のシートにリンクしています。
でも関係ないですよね。
ファイルをコピーしたときに、こっちで出来ていたことが、こちらでは出来なくなるというのが分かりません。
さらに、出来ない度合いも違っているのがしゃくに障ります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) Excelの関数についての質問です。(vlookup関数) A列 B列. C 1 大阪 50. 検索 6 2023/08/11 13:35
- Excel(エクセル) エクセルのvlookupについて質問です 3 2023/01/05 15:15
- Excel(エクセル) ExcelのVLOOKUP関数 7 2022/08/23 06:46
- Excel(エクセル) Excel_マクロ_複数のシートのVLOOKUPで表示された#N/A以外に色付けをしたいです 1 2023/02/16 22:37
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Visual Basic(VBA) vba 15 2023/04/07 12:33
- Excel(エクセル) エクセルの印刷範囲をページ単位で可変にする方法 3 2022/05/23 13:04
- Excel(エクセル) エクセル 関数について質問です。 2 2022/10/03 11:14
- Visual Basic(VBA) vba 等間隔の列に対しての計算 6 2022/05/17 20:15
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
Microsoft1Officeの互換ソフト...
-
Excel ピボットテーブルで日付...
-
エクセル関数を教えてください
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
LOOKUP関数を使えばいいのでし...
-
エクセル 白黒印刷で白線を印刷...
-
【関数】先頭だけにある、半角...
-
【関数】適切な文字数の数字を...
-
Excelのチェックボックスの使い...
-
エクセルでの作業計算方法について
-
Excelのpivotについて質問です
-
WPS OFFICEでの縦書きについて
-
時間によってファイル名が変わ...
-
エクセルのセルに同じ大きさの...
-
Aというブックの1というシート...
-
エクセルの順位別一覧表の自動...
-
西暦や和暦の表示をyyyymmdd表...
-
【マクロ】エクセルにかいてあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報