VLOOKUP関数は実務をやる上で
よく使われる関数の1つです。
職種にもよると思いますが、
SUM関数やIF関数の次によく使う
と言っても過言ではないでしょう。
しかし意外と難しい関数でもあると思います。
なぜならエラーが起こりやすいからです。
ネットで調べて自分で式を入力しても
#N/Aになってしまうことないでしょうか？
または、１度目はうまく出来たが、
式をコピーしたり、
別の機会で使ってみたらうまくいかかった
ってことないでしょうか？
この関数の重要な点はVLOOKUP関数
そのものの使い方だけではなく、
この関数を使う時によくあるエラーの原因と
その対処法をセットで覚えることです。
この記事を読むことで
VLOOKUP関数でうまくいかない、
"あるある"の悩みとその解決方法を
がわかるようになります。
下記に無料のサンプルエクセルを
置いたのでダウンロードして下さい。
このサンプルを使って解説をするので、
ダウンロードすることで、
よりわかりやすくなると思いますし、
あなたの業務にも
そのまま流用することが
できると思います。
Excelは解説を読みこんで
理解するよりも、
いかに正解を手元に置いて、
パクって自分で手を動かして使い、
壁に当たったら
調べるという繰り返しが
一番の上達の近道です。
なぜならわからないことや、
悩んでいたことがわかることで、
インパクトがあり
記憶に残りやすいからです。
VLOOKUP関数とは
検索値したい文字列と
データ検索範囲を指定します。
するとデータ範囲の一番左の列で
検索されます。
そして合致したらその範囲の
左から何番目の列のデータ
を拾ってきてね！という関数です。
ポイントは２つです。
縦（列）で検索するという点と
データ範囲の一番左は検索したい
文字列がある項目にすること
（この例だとオーダー番号）です。
ここで例です。
とあるオーダー番号（A)を
データ範囲から探して合致したら
その1Qの個数を拾いたいとします。
こちらが計算式です。
最後の検索範囲は
通常完全一致の"0"を選択する
で大丈夫です。
計算式の中身の解説
オーダー番号7E1-6431-211を
グレーのデーター範囲の一番左
から探してきてね。
合致したらデータ範囲の左から8番目の列の
データを拾ってきてね。
答えは900になりました。
よくあるエラーの原因
1.範囲が動いてしまっている
Vlookupの計算式を入れた時、
その式をすぐ横や下にコピーすると
データ範囲や検索値も
1列また1行ずれてしまい
エラーが起こってしまいます。
試しに新しく検索したいオーダー番号を
入力して計算式を上から下にコピーしてみると
#N/Aになってしまいました。最悪なやつです。
絶対参照
そういう時のためにデータ範囲
を絶対参照にしましょう。
絶対参照とは計算式の中で
行または列の前に＄マークをいれることで
固定するという意味です。
こうすることで式を
横や下にコピーした時に
データー範囲が壊れることを
防ぐことができます。
例えば絶対参照を使って
下に式をコピーしてみると
絶対参照を使った計算式
絶対参照の＄を使うことで
検索したいものはK列で固定、
データ範囲はBからI列で固定するという
設定をしました。
なので例えば下に式をコピーしても
データ範囲が変わることなく
正しくデータ検索ができました。
テーブル化する
もしデータが今後増えいくことが確実なら
テーブル化してしまうことをオススメします。
なぜならテーブル化してしまえはデータが追加されても
範囲が可変するので数式のメンテが不要になるからです。
テーブルのやり方
1.データ範囲を選択してから"CNTR+T"を押して下さい。
下記ダイアログボックスが出てきます。
選んだ範囲が問題なければOKを押して下さい。
2.そうしたら、テーブルデザインのメニュータブの中の
テーブル名を適当に変更します。
変更前
変更後
3.VLOOKUP関数の範囲指定の時に
先ほど指定した"price"と打ちます。
残りは通常と同じように入力すれば完成です。
テーブル化することで
通常のセル番地よりも視覚的にみやすく、
かつデータを追加しても範囲が自動で
可変されることが大きなメリットです。
2.データに半角スペースが混ざっている
パッと見では区別できないエラーがあります。
データに半角スペースが入っている場合です。
例ですが下記の場合、
一見7E1-6343-211というデータで
合致しているにも関わらず
データを拾ってきてくれません。
実はグレーのデータ範囲の最後に半角スペースを
いれたため、完全一致にならず、
データを拾ってくれませんでした。
実務において
何らかのエラーでデータに半角スペースが
入ってしまうことはよくあると思います。
しかしそのままにするとVLOOKUPする時、
一見すると同じデータなのに拾ってくれない
といった問題が起こるのです。
目視では到底わからないのが厄介な点ですが、
よく遭遇してきました。
そこで使われるのがTRIM関数です。
TRIM関数
そんな時はデータのお掃除が必要です。
その時に使うのがTRIM関数です。
TRIM関数はそのセルの余計な空白を
削除してくれる関数です。
計算式
計算式はシンプルです。
掃除したいセルを選ぶだけです。
こうしてTRIM関数を使って
余計な空白をの除くと、
7E1-6343-211の該当する
データ1500を拾ってきて
くれるようになりました。
エラー値を非表示にする
条件一致しない時に出てしまう
エラー値を非表示にしたい
時はどうすれば良いのでしょうか？
IFERROR関数との組合わせ
そんな時はIFERROR 関数の中に
Vlookup関数を入れ込んで使います。
こうすることでエラー値を
非表示にすることができました。
尚計算式の""が空白にする意味になります。
もし０と表示したければ下記のようにします。
もし、エラーの時「要確認」という
文字列を表示させたければ、
つまり、文字列を表示するためには
""で文字を挟みます。
文字がないつまり空白にするなら
""そのままとします。
まとめ
VLOOKUP関数は、縦列条件で一致した場合、
特定の列から該当するデータを拾ってくるという関数です。
この関数の計算式のポイントは２つです。
横（行）の検索はできず、
縦（列）で検索するという点と
データ範囲の一番左は
検索したい文字列がある項目にする点です。
またこの関数にはよくエラー値が
出てしまいます。
なのでそのエラーの典型例を把握して
事前に対応策を知っていることが
VLOOKUP関数を使いこなすポイントです。
その具体例として絶対参照を使って
範囲を固定すること、
TRIM関数を使って
データの余計な余白を削除する
ことをお伝えしました。
表の見た目でエラー値をそのまま表現させない
やり方としてIFERROR関数との組み合わせも
ご紹介しました。
次回の記事では
VLOOKUP関数の続きを書きたいと思います。
今日は単純に条件一致したものを
拾ってくる純粋なやり方でしたが、
様々な検索方法があるので
よく使う事例をご紹介するつもりです。
例えば左から4文字で検索したり、
複数セルを合算した条件検索をしたりすることもできます。
それでは良い週末を！
