Vlookup関数の実践的使い方

エクセルの関数で何を覚えたら良いと聞かれたら、Vlookup関数がまず頭に思い浮かぶ。使い勝手がいい関数であるが、エクセルに馴染みがない人にとっては、式がウニャウニャして分からない、そんな関数でもある。

利用シーン

まずは、利用シーンを上げてみよう、このシーンが思い浮かぶ人は頑張ってVlookupを覚えるとエクセル作業が効率化するだろう。

一意なキー(ユニークキー)があるか、又は作ることができるか

作れなければVlookup関数は不要だ。このページは読み飛ばしてもらって大丈夫。必要ができたときに読んでほしい。

シーン1

得意先コードと名称のデータがバラバラのシートにある。名称がぱっと見つからなくて不便。つまり、データベースから情報を参照させたい場合。

シーン2

年度別のシートを時系列データにしたい。

項目の名称は同じだけど、項目の並び順が年度ごとに違っていて、単純にコピペできない

シーン3

この情報は網羅的か調べたいとき。VlookupをAのシートに使えばA<B(AがBに含まれる)場合には全てに数値が入り、エラーが出ていればA>Bということ。さらにBのシートに使って、エラーが出なければAとBは同じ項目から構成されていることがわかる。

関数の式

=VLOOKUP($B5,$E$11:$F$13,2,0)

見慣れないと呪文みたいである。さて説明しようと思ったが、基本的な説明は他のホームページにわかりやすく書いてあるのでそこを見てもらったほうがいい。

例えばこのページとかがわかりやすい。決して、説明するのがめんどくさかったわけでは・・・、効率化のために他の人のわかりやすい説明を・・・・

式の注意点

私のページでは式の注意点に絞って記載しよう

  1. $マークはすごく重要な意味がある

    $は絶対参照と呼ばれるもので、$A1であればA列を固定し、A$1であれば1行目を固定する。$A$1であれば、A列と1行目を固定する。つまり、次のセルにコピーしたときに変えたくない項目について、$をつける。これがポイント。
    具体的には、検索キーがA列にあるとすると、セルをB1にコピーしても計算式のキーの列は変えたくない、このため$A1とする。そうすれば、B1にコピーしても$A1のままであり、2行目にコピーすれば$A2と変化してくれる。
    データベースの範囲は絶対に$A$1のように指定しよう、これは列や行が変わってもデータベースの範囲を固定させるためである。
    なお、絶対参照は式の入力欄にマウスを持っていってから、ショートカットF4で変更するのが便利である。

  2. 検索キーはデータベースの左側にないとだめ。

    最初は分かりづらかった項目。検索キーをとなる項目をデータベースの一番左においておくと覚えよう。検索キーの左の項目は検索できない。
    右にしかないときは、コピーで左に持ってくる対応をする。

  3. 右から2番めはデータベースでの相対的な位置を示す

    検索キーから1番目の列、2番目の列と数える。

 

Vlookup関数の実践的な使い方

ここからが本番更にVlookupを便利に使おう。

エラーが出てるのをなんとかしたい

なんとかする前に、本当にエラーが出ていても良いのか考えよう。#N/AはDBに検索キーがないことを意味している。つまり、Vlookup式が正しいならば検索対象>データベースの状態になっている。

ありがちなVlookupのエラーがでるケース

①検索キーがよく見ると違うケース。半角・全角、スペース有無など。こんな場合に重要なのが入力形式の統一化なのです。この場合はフィルタを掛けてみて、関数を使って置き換え、スペース削除などしてデータのクレンジングを行う。

②DBの範囲指定が違っている、$のつけ忘れなど。

Vlookup式が間違って以内ならば、IFERROR関数を使ってエラーを消そう。

=iferror(vlookup($B5,$E$11:$F$13,2,0),0)により、エラー値をゼロにすることができる。ただし、iferror関数はExcel2007以上のバージョンで使用できる関数なので注意。

それ以前のバージョンでは、IF関数とISERROR関数によりエラーを消すことができる。

=if(iseeror(vlookup($B5,$E$11:$F$13,2,0)),0, vlookup($B5,$E$11:$F$13,2,0))

よくカッコの数を間違えてしまうので、Iferrorが使えればそちらを使おう。

データベースの範囲が変わるたびに指定しなおすのが面倒

(対策1)

データベースを別シートにするか列を変えて、そのDB以外の項目がその列にないようにする。こうすることで、範囲の指定を$A:$Cのように列を固定する形式にできる。そうすれば、データベースの行が増えても自動で対応できる。ただし、データベースの項目を増やす(列を増やした)場合には、Vlookup式の列を拡張する必要がある。

(対策2)

DBの範囲をデータテーブルにする。そうすれば、Vlookup式の範囲指定をDBに追加しただけで拡張できる。Goodである。ただし、テーブル機能はExcel 2007 以降でしか使えないので注意。テーブル機能は、範囲を指定して挿入タブの表から作成する。

(対策3)

OFFSET関数とCOUTIF関数で範囲を可変にする方法があるが、1、2対応したほうが簡単。Offset関数を使うとDB範囲がパット見でわからず、チェックがしづらい欠点がある。

一意(ユニーク)なキーが無いよう。。

ない場合は自分で作ってしまおう。つまり、重複する名前の人がいたとする、そして住所は書いてある。では名前で他のデータを持ってこようとすると重複してエラーになってしまう。この場合は、名前+住所をキーとして自分で作ってしまおう。作り方は、セルを&でつなぐだけ。例えば、C1セル赤津 D1セル神奈川バツバツ番地であったら、=C1&D1のようにすることで文字列が結合され、赤津神奈川バツバツ番地となる。ー(バー)を間にはさみたかったら、=C1&’-“&D1の用にしよう。文字列を入れたい場合はダブルクオテーションで囲むようにする。

こうすることで、一意っぽいキーが作成される。ホントに一意になっているかは、データ次第であるのでそこは忘れずに検討しておこう。

この考えは複数条件でVlookup関数を使っていることと変わらないので、頭の片隅に覚えておこう。

DBの相対的な位置を列ごとに手で入力するの面倒だよう

うむ。確かに面倒ですね。頑張りましょうではなくて、相対的列番号の指定はセルを指定した場合、そのセルの中身が入ることから、1箇所だけに記載すればあとはコピペで対応が可能だ。行を絶対参照にし、列を相対参照にしてコピペすれば、列が変化している特性を利用する。

文章で説明するのは難しいが、まずはDBに検索キーの上に、例えばセルB1に1を入力、横のセル(C1)に=B1+1として、横にコピー。そうすると、相対的な列番号が入ったと思う。

ここで、Vlookupを入力する表の外側の上に、該当する範囲の相対的列番号のセルとリンクを張っていく。そのセルの位置をvlookup式の相対的列番号の箇所にC$2のように記載、コピーすれば、D$2といった形で列がずれていき、参照する数値も自動でずれていく。

商品コードが一緒でも、販売する営業所によって単価が違うんです、どうしましょう

If関数でDB範囲(テーブル)を切り替えてあげましょう。

式はこんなイメージです。=VLOOKUP($A3,IF(B3=”東京”,東京,名古屋),2,0)

V lookup編終わり、ここまで使えればきっと大丈夫。

関連記事