TOPページ > Excel・IT開発 > Excel実務のノウハウ > VLOOKUP関数が思うように使えない場合

VLOOKUP関数が思うように使えない場合|Excel実務のノウハウのお勉強

このエントリーをはてなブックマークに追加

VLOOKUP関数は他の関数と少し違うかも・・

VLOOKUP関数は、Excelでは良く使われる機能の1つだと思いますが、SUMやIFなどと比べると少し敷居が高いかもしれません。
SUMやIFでは基本的には「1つのセル」を(複数)扱うのに対して、VLOOKUP関数の場合は立体的な関数(配列数式的)となり、表全体が対象範囲となるためです。とはいえ、悩むほど大げさなものではなく、使っていけば慣れてきます。
本ページでは問い合わせの多いVLOOKUPのエラーについて考えてみたいと思います。
なお、VLOOKUPの効果や使い方(関数挿入の仕方)などはヘルプなどでご確認ください。

注意! エラーでないエラー!?

使い慣れてない方がVLOOKUP関数を使うと、エラーでないのにエラーと判断する場合があります(ただ、厳密にいえばエラーなのですが、ここでは他のエラーと意味合いが違うために、このように表現している点でもあるので注意してください)。

下記の画像は「エラーがない」場合です。赤枠の部分に「3」と入力すると、左の表からそれに該当する「スイカ」が赤枠下の「商品名」に表示されています。
VLOOKUPエラーなし

一方、下記は赤枠の部分に「10」と左の表に存在しない番号を入力したところです。 VLOOKUPエラーあり
予想通りエラー「#N/A」が表示されました。しかしこれはエラーであることはエラーですが、VLOOKUPの式自体は間違っていません(強いて言えば「10」と存在しない番号を入力したことが間違いです)。ここが気を付けるべきポイントの1つです。
言い換えると、「あ~、エラーじゃん、式間違えてるのかよー」等と思ってしまうと解決の糸口が遠くなってしまうので注意が必要です。

決してVLOOKUPに限った話ではないですが、この点についてはエラーの認識を正しく伝えるように(特に誰かに電話やメールで聞くときに)注意が必要です。通常は「関数・数式でエラー」というと、式の設定が間違っている、データがおかしいなどというところに問題の原因をフォーカスすると思われます。


良く見かけるVLOOKUPのエラー

弊社で良く見かけるエラーの原因は、表の選択が(気づかぬうちに)ずれている場合とVLOOKUPの式の設定が間違っていることがほとんどです。前者は「エラーでないエラー」の類です。
通常、VLOOKUPが登場するような表は、上記の画像のように、対象となる表とVLOOKUP関数を使うセルが、1つのシートにまとまっていることはなく、シートをまたがる形(シート2つ)で使われていることが多く、データ数も数千単位になると複雑に思えてしまい、原因を探すのに時間がかかったりします。

下記でも説明の便宜上、2つの表を1つのシートにまとめたものにしていますが実際には2シート分割するようなイメージで見るとベターかもしれません。


表の範囲設定を疑う

相対参照と絶対参照が分かると、この手の間違いは減り、更に理解もしていれば納得しながら修正ができると思います。
いずれにしても、VLOOKUPで参照となる表を選択するときの注意事項です。このエラーの特徴は上記で説明した画像のような1点(セル1個)だけにVLOOKUPの結果を表示すると言う場合でなく、式をコピーして複数のセルに表示させたい(VLOOKUPを複数使う)ときに発生します。
VLOOKUPで売上表を作る
上記の場合、右側の「売上表」の「商品名」が空白になっているので、ここにVLOOKUP関数を使って、左側の「商品一覧表」から商品名を抽出してみたいと思います。
下図のような流れです。
VLOOKUPで枠線の中をそれぞれチェックする

更に下記の画像を見てください。1つのセルだけVLOOKUP関数を入れたところで「カレー」が表示されていますが、その式の中を除くと表(の範囲)を選択している箇所があります。 VLOOKUPで表の範囲を指定

この時点で気づいた方もいらっしゃると思いますが、この式を下記のようにコピーしていきます。
VLOOKUPの式をコピー

すると下記のようになりました。
VLOOKUPで式をコピーした結果のセル
なぜか一番下の商品名だけエラー(商品が見つからないエラー)になっています(本当は「サラダ」が表示されるはず)。
先ほどのようにこのセルの(VLOOKUP関数の式の)内容を見れば、その原因が分かります。
VLOOKUPで式コピーで範囲がずれる
E7のセルは、本来対象となる表の範囲からずれていることが原因です。
なお、E3のカレーと表示されたセル以外もずれていますが、表の対象範囲がかろうじてかぶっているために結果が正しく表示されていますが、これも問題を見難くさせる要因になるので注意が必要です。
このように数式をオートフィル等でコピーしてもくずれないように適宜絶対参照にしておく必要があります。
表の参照範囲を絶対参照にする

もっともこの方法も、参照元の範囲が以下のようになると、太刀打ちできなくなるため、実際に参照を入れるときには、今後、表自体の大きさ(データ数)が変わり得るのか等も含めて考えておく必要があります。
表の参照範囲を参照するときの注意

参照元のデータの数が増えても意識しない方法として簡易で対応するには以下の方法でできます。
表の参照範囲を参照するとき列全体を選択
このように表というよりも、列全体を選択すれば何行あってもVLOOKUPの対象範囲となるため表の範囲を意識する必要がなくなります。ただし、1行目、2行目も対象範囲に含まれるので、VLOOKUPの検索にヒットしそうなら文字を削除するなどして気を付ける必要があります。


表示形式を疑う

検索したい項目の値も注意する必要があります。ここでは分かりやすい例として日付を挙げていますが、その他「セルの書式設定」で見た目の値と実際の値が違う場合に注意が必要です。
Vlookupのキーになる項目を年月にする
上記の例では正しく「りんご」と値が拾えていますが、下記の場合だとエラーになりますが、何が違うか分かるでしょうか。
Vlookupのキーになる項目を年月してもうまくいかない
ここでVLOOKUP関数の対象となっている年月の書式を変えてみます。
書式設定で表示方法を変えてみる
書式設定で年月日に変更する
このようにすると原因が分かります。これでVLOOKUPは検索される側の値と検索する値の書式も見るということが分かります。
この手の影響を受けるかもしれないのは電話番号や郵便番号だったりします。書式設定でハイフンを自動で「見た目」として表示する場合などです。
VLOOKUP関数が思うようにいかない場合にこの点もチェックしてみるといいと思います。


空白文字がないか

表示形式でもひと際気づきにくいのが空白文字です。
例えば下記のような場合です。
なぜかVOOKUPエラーになっている
期待した結果は「100」を表示してほしいところですが、エラー(見つかりませんでした)になっています。なぜでしょうか?勘のいい方ならお気づきだと思いますが、今回のVLOOKUPのエラーは、表の方の文字に問題があるのです。
なぜかVOOKUPの対象表に空白文字がある
答えは上記のように対象となっている表に空白の文字が入っているために、見つからなかったということです。
分かり難いかもしれませんが、下記のような形です。
式:「りんご」
表:「りんご 」→末尾に空白文字が入っているため、一致しなかった


CSV取り込みなどで関数がそのまま表示される(式が反映されない)

こちらはVLOOKUP関数だけの話ではなく、他の関数や数式にも当てはまりますが、CSVファイルを使った取り込みなどでセルに数式を入れても、その関数が動作せずにそのまま表示される場合があります。
セルに関数そのものが表示される
これは数式が入力されているセルの書式設定が怪しいです。

この時におかしいセルの書式設定が下記の「文字列」になっている場合、これが原因です。
せるの書式設定を確認

この事象は特にCSVファイルやテキストファイルなどから下記の方法で取り込む際に「文字列」として読み込み、かつ(読み込み後に)例えば新しく追加した列がこの文字列の設定を引き継いでしまっていることが考えられます。
テキストファイルウィザードでの取り込み

テキストファイルウィザードで文字列を指定

この場合の回避方法は、正しく表示されないセルで「文字列」の書式設定になっているところを「標準」にします。
書式設定で標準にする

この状態ではまだそのままなので、そのセルをクリック(またはF2キーを押す)します。
セルの中にカーソルを置く

最後にこの状態で「Enterキー」を押すと関数や数式の「結果」が表示されるようになります。
セルの結果が表示される


正しい値が拾えない、値が違う場合

式自体は間違ってないにも関わらず、返ってくる結果(値)がおかしい場合は、参照元のデータ自体を疑ってみます。VLOOKUPは参照元データに検索値が複数あると最初に見つかったデータを返す仕様になっています。
VLOOKUPデータが重複している

こちらについては以前に本サイトで説明しているページVLOOKUP関数の落とし穴に気をつけるも確認してみてください。


VLOOKUPの仕様について

これもVLOOKUP関数に限ったことではないですが、関数には仕様というルールがあります。この仕様から外れた設定をしたときに「エラーですよ」とセルに表示して教えてくれる場合もあれば、残念ながら教えてくれない場合もあります。
やっかいなのは教えてくれない場合です。関数からみれば、「仕様に基づいた結果」としてセルに表示するだけですが、作業者から見ると予期しない結果だったりするので注意が必要になります。
このような点に関心ある方は上記と重複しますがVLOOKUP関数の落とし穴に気をつけるもご覧ください。


このエントリーをはてなブックマークに追加

Excel実務のノウハウ 関連ページ


グローウィン有限会社
東京都三鷹市下連雀3-27-1 三協ビル2階-B 地図を見る
トップページ
入力代行関連
Excel・IT開発
中国アテンド
会社概要
プレスリリース
お問い合わせ
お問い合わせ内容一覧

英文ページ(English)
中文ページ
プレスリリース お問い合わせフォーム