他のデータから値を取り出したい時に使うのがVLOOKUP関数です。
VLOOKUP関数は実践で本当によく使われる関数です。多分一度は聞いたことがあるのではないでしょうか。
では実際に使えてる方はどれくらいいるでしょうか。
以前、毎日の様に残業をしている同僚がいましたある日何をやっているのか覗いてみると…500件程のデータを二つ並べ両方を見比べながらデータを一件一件コピペしていました。
愕然としましたが これが実態なのかもしれません。
VLOOKUP関数とは
VLOOKUP関数はどんなことが出来るのでしょうか英語でLookupは探す、見つける、検索するなどの意味があるのはご存知でしょう。
それをVertical(垂直)にLookup(探す)のがこのVLOOKUP関数です。
なのでHLOOKUP関数というのも存在しますがこちらはHorizontal(水平)にLookup(探す)です。
VLOOKUP関数の基本
VLOOKUP関数は次の様な場面でよく使います。
上の表では左の表の会社名を右の一覧から探し出し表示しています。
引数は＝VLOOKUP(検索値,範囲,列番号,[検索方法])引数のうち検索方法は省略が可能です。
検索方法は省略するとTRUEを指定したことになります。後で述べますが出来るだけ省略は避けましょう。
まず検索値ですが探す側と探される側に同じものがあることが必須です。上の表では『会社コード』がそれに該当します。
次に範囲は探される側で検索値と同じものがある列の一番上のセルから探し出したいものがある列の一番下のセルまでを選択します。
上の表ではコードの一番上から会社名の一番下までを選択しています。
範囲の一番左側は検索値で探す相手がある列にしなければなりません。
次に列番号ですが、これは実際の列番号ではありません選択した範囲の一番左列を『1』として右側にかぞえた列数のことです。
最後が検索方法ですが、二種類あります一つは検索値と完全に一致する場合に値を持ってく『FALSE』
もう一つは近似値を含めて探し一致するものが無くてもひとつ手前の値を持ってくる『TRUE』が指定できます。
普段『TRUE』はほとんど使われることがありません何故かというと検索される側のデータ並びを検索される値で昇順(小さい順)に並べておく必要があるからです。
これでは手間がかかるのと、検索相手が存在しなくても値が表示されてしまうためです。
『FALSE』は『０』と『TRUE』は『１』と入力しても構いません。というか数字の方が簡単ですね
VLOOKUP関数の範囲は別なシートでも構いません実はこちらの方が多いかもしれませんね。
また、行数が不明なデータの場合は範囲指定での行を外しても構いません（A:Cの様に）
基本はお分かり頂けましたでしょうか。
VLOOKUP関数でも検索値の指定や範囲の指定をセルで行う場合は相対参照と絶対参照を正しく使い分けて下さい。
使い方のバリエーション
それでは次にちょっとした使い方のバリエーションを紹介します。
引数の列番号は実数で指定する必要はありません数値の入ったセルを指定することも可能です。
上の表には黄色で塗りつぶした行に列番号と同じ数字を入力しておき参照するといった方法です。
この様な表を作成する場合は最後に2行目を非表示にしておくのをお忘れなく。
何故かというと表がとても見苦しいのと誤って消された時はエラーになってしまうからです。
VLOOKUP関数はは上の例以外にも次の様な場面でもよく使われます。
上の表では会社名と住所だけですが、この使い方を応用して『見積書』や『請求書』『送り状』など様々な場面で使うことが出来ます。
VLOOKUP関数はまだまだ他の使い方があります。この後は他の使い方も紹介していきます。
VLOOKUP関数は存在チェックにも使うことが出来ます。
もうお分かりの様にVLOOKUPの最後の引数である検索方法指定を『0』(FALSE)にすると範囲内に存在しなかった場合は『#N/A』というエラーを表示します。
この方法を使って存在しないデータを見つけ出すことが可能です。
でも検索結果をそのまま使用する様な場合に私はIFERROR関数を併用して使います。
=IFERROR(VLOOKUP(検索値,範囲,列番号,検索方法),”無し”)こんな風に記述するとエラーの時には『無し』と表示されます。
VLOOKUP関数の応用
目からうろこのVLOOKUP関数で検索方法『TRUE』を効果的に使った経験を紹介します。
検索方法の『TRUE』は何のためにあるのか？私も多少疑問でした。色々試してみて凄いことに気が付きました。
その頃私が使っていたPCのスペックは余りにも脆弱でした。
しかも、20万件のデータと50万件のデータを関数で紐づける様なことを毎週やっていたのです。
普通ならACCESSを使う様なデータ件数ですが要求されるアウトプットが頻繁に変化するのでエクセルの方が便利でした。
関数を幾つも使っていたので一部を修正するだけで再計算されるので気の遠くなる時間を要しました。
「手動計算にして後から計算すれば…」という方もいるでしょうが、それも試しましたが[F9]キーを
押した途端にPCが固まってしまった様な状態になり結局他の操作すら出来なくなってしまいました。
そこで試しにやってみたのが下の式=IF(VLOOKUP(検索値,範囲,1,1)=検索値,VLOOKUP(検索値,範囲,2,1),””)
この式を解説すると もし、VLOOKUPで探した値が検索値と同じだったら、もう一度VLOOKUPで2列目を持ってくる。もし検索値と違ったら『“”』(空白)にする。
２回もVLOOKUPを実行したら余計時間がかかるのでは？と思われるでしょうが
なんと劇的に計算時間が短縮されたのです。
勿論、検索方法を『TRUE』にする時の注意点で検索されるデータを昇順に並べ替えるという作業は発生しました。
その手間を差し引いてもお釣りが来る程作業時間が短縮できたのです。
もし同じ様に大量のデータをエクセルで扱っていてVLOOKUPを使っている方がいたらお試し下さい。
VLOOKUP関数は使い方次第でとても便利な関数です。でも使っていく上で厄介なことがあり、それを次に紹介していきます。
実務で使っていく上で厄介なこととは列番号の指定です。
例にある様な少量のデータを検索するのは簡単ですが、列数が200列もあった時にはどうでしょう。
列番号を探すだけでも一苦労ですし、時にはデータの並び順が変わっていたりすると列番号も変えなければなりません。
あなたならどうしますか？「一番上に１行挿入して範囲の一番左から１,2,3と連番を付けて行けば200列あっても列を数える必要が無い」
それも方法の一つです。でも並び順が変わる可能性があるとしたら…毎回その作業を行い計算式の列番号を書き換えコピペすることになります。
ましてや並び順が変わったかどうかを判断する必要が必ず発生します。私も実はこれで失敗した経験があります。
これを回避し継続してデータを検索できる様にするために利用したのがMATCH関数です。
VLOOKUP関数の引数『列番号』をMATCH関数で持ってきてしまうという方法です。
MATCH関数はこちら↓で解説していますので詳しくはご参照下さい。
VLOOKUP関数で検索で存在しなかった場合は『#N/A』のエラーになりますが、存在はしていても対象のセルが空欄だった場合は『０』となるので、ちょっと注意が必要かもしれません。
VLOOKUP関数はデータ間の紐づけや存在チェックも出来るため非常に使える関数です。
何度も繰り返し使い、普段使い出来る様にしましょう。
範囲指定や列番号、検索方法にはルールがあるので使う時には注意が必要です。