vlookup関数をマスターして中級者を目指そう
ExcelやGoogleスプレッドシートといった表計算ソフトをマスターする上で中級クラスの壁の１つがこの「vlookup関数」です。他の関数との一番の違いは、関数の引数の作り方と関数の概念がちょっと複雑という点。理解出来ればとっても便利で欠かせない関数なのですが、複雑が故に理解できず挫折する人も多いですね。
この関数の特徴は、対象となるキーワードを元に別表からそれを見つけ出し、キーワードを含む行の別の列の値を取ってくるというもので、別表も用意の仕方というものがあるので、なかなかに曲者です。さらに高度な使い方をするとランク分けや複数条件の場合の値の引っ張り方などがあります。是非マスターしましょう。
目次
使用するスプレッドシート
今回はGoogleスプレッドシートで用意してみました。別表はマスターデータというシート名になっています。複数条件のケースだけは独立したシートとなっています。
vlookup関数の基本
別表（マスター）について
vlookup関数を使う場合、参照する対象となる別表（マスターと呼びます）には一定のルールがあります。ここをきちんと説明していないサイトも多いです。以下に別表の特徴をまとめておきます。
- IDや商品名などがキーワードの対象になる
- マスターでは、IDや商品名などは重複していない一覧表である必要がある
- IDや商品名の並び順はとくに気にしなくても良い
- 基本的にキーワード対象になるIDや商品名は一番左側に列を作る
特に重要なのが2.の一覧表は重複しないユニークなIDであったり、商品名などが重複していない事が前提になっています。例えば社員マスターや商品マスターなどがこの事例に当たります。一方、勤怠マスターなどの同一人物が複数重複してデータに出現するようなものには、vlookup関数は使えません（使った場合、最初にヒットしたデータを返してしまい、その次以降のデータはヒットしません）。
概要と注意点
vlookup関数の数式の作り方はちょっと複雑です。数式の要素はキーワード、検索範囲、列番号、オプションの４つです。
|
1 |
=vlookup(キーワード, 検索範囲, 列番号, オプション) |
検索範囲内の1列目が主に対象になり、キーワードで指定したものがあった場合、その行（レコードと言います）に於いて何列目の値を引っ張ってくるか？列番号で指定します（1から始まります）。オプションは通常は0を指定しておきます（ちなみに1は近似値となってしまい、このオプションは省略が可能です。その場合、既定値は1になってしまうので、通常は省略しません）。
図：vlookup関数のイメージ図
以下に数式を作る時の注意点を列挙します。
- 検索範囲の1列目にキーワードの対象が入るように通常は範囲指定する
- 範囲指定は$A$2:$E$100といったように、$記号を使って範囲を固定化しておく（数式をドラッグした場合に範囲がズレたりしないようにするため）
- 検索範囲全部が対象になってしまうので、IDなどの数値を使う場合、他のセルや列にその数値があると引っかかってしまうので、よく考えて別表は用意する必要がある。
- 検索値がヒットした列から見て、何列目なのか？を列番号で指定する。この時、ヒットしたセルより左側の列は値を取ってくる事ができない（列番号はマイナス値を使えない為）。
- 検索値よりも左側をも検索対象にしたい場合には、vlookup関数ではなく、OFFSET関数とMATCH関数、INDEX関数とMATCH関数の組み合わせなどのテクニックが必要になる。
この部分がvlookup関数が挫折するポイントです。なのでシンプルに
- 別表（マスター）の常に1列目にキーワード対象が入るようにマスターを用意する（IDは常に1列目といった具合に）
- 検索範囲固定化は常にしておく
- オプションは常に0を指定する（近似値など滅多に使わない）
といったように覚えて作ればOKです。
実際に使ってみる
例えばIDが100のものをA1のセルに入れておきます。商品マスター（ID、商品名、金額の3列。範囲はA2:C100）から検索し、金額(3列目の値）を引っ張ってきてみようと思います。
|
1 |
=vlookup(A1, '商品マスター'!$A$2:$C$100,3,0) |
キーワードはA1セル、範囲は範囲固定で商品マスターのA2:C100、金額は3列目なので3を指定。0はおまじない。これでA1の値100と一致するものがあれば、商品マスターから対象商品の金額が引っ張ってこれます。存在しない場合には、#N/Aエラーが返って来ます。これがちょっと見た目はよくないので、iferror関数と組み合わせて、エラーが返ってきた時には「結果なし」を表示するようにすると、スマートです。
|
1 |
=iferror(vlookup(A1, '商品マスター'!$A$2:$C$100,3,0),"結果なし") |
エラーの場合には、セルに「結果なし」と表示されます。実際にシンプルなこのケースを使った事例は、注文表シートにて使っています。検索結果が数値ならばそのまま計算も出来ますよ。
vlookup関数の応用
ランク付けしてみる
vlookup関数の応用として、一覧表の特定の数値（キーワードとして使います）に於いて、その数値が例えば0〜99ならばA、100〜199ならばBを返す）といったようなランク付けで使う事が可能です。このケースの場合、オプション値は0ではなく1（近似値）を使うのが特徴です。また、別表（マスター）も、範囲値で作るという特徴があります。
この事例はサンプルのランク付けシートにあります。別表は以下のように作ります。
- 1列目は数値。1行目が0で2行目が100ならば、1行目にヒットするのは、0〜99の値が対象になります。
- 2列目はランク名を適当に付けておく
- オプションは1にしておく必要があります。
vlookup関数としてはちょっと変わった使い方ですが、数式の作り方は同じです。
図：C列の値でF2:Gのランク表からランクをD列に取ってきてる
複数条件で引っ張る
vlookup関数は、AccessのDLookup関数とは異なり、別表から引っ張ってくる時に条件式（というかキーワード）を１個しか指定出来ません。その為、複数条件でヒットさせたいような別表の場合、これでは値を取ってくる事ができません。この時の別表（マスター）は、IDや商品名などで重複しない一覧表として作られていないものとなっています。通常はこういった表はvlookup関数を使うには不適当ですが、2つ条件式があれば、重複しないような作りならば、vlookup関数が使えます。
その為には条件式（キーワード）で使う為に複数条件となるよう別表に作業列を一番左に用意してあげます
- 例えば商品名と包装形態などを「&」記号でつなげた列（作業列）を用意する（これで複数条件になります）。
- &でつなげた列でもって重複しない値を作れない場合には、vlookup関数を使うには不適当です。
この時のvlookup関数の数式は以下のようなスタイルになります。
|
1 |
=vlookup(F2&G2,$A$2:$D,4,0) |
上記の数式の場合、F2とG2にあるキーワードをつなげて検索式とし、A2:Dの範囲のうち1列目であるA列にはこのキーワードが引っかかるようにB2とC2の文字列を結合した作業列を用意してあります。そして4列目の金額を引っ張ってきてるという具合です。
このパターンのサンプルは、複数条件シートに用意してあります。ですがなるべく、レコード（行）を特定出来るように、きちんとした別表を用意するようにしましょう。
※また複数条件で引っ張るこの方法は、それで一意なIDとなりえるならば、参照元・参照先のシートに同じ社員番号の人間がいる表であっても、vlookupで引っ張ることが可能になります（自社社員マスタの健保扶養者複数⇔健保側マスタ複数でも、同じ社員ID+名前で一意のIDとなるので、存在確認などがvlookupで実現できます）
