エクセルで初心者を卒業して中級者に成長するためには、VLOOKUP関数の習得は避けられません。
エクセルは表計算ソフトですので、表からデータを効率よく抜き出すことが求められます。VLOOKUP関数をしっかり理解して使いこなせるようになれば、手入力にかける手間が大幅に減ります。
この記事を読むことで
- VLOOKUP関数の使い方が分かる
- エクセルでのデータ作成や抽出の効率が向上する
といった効果を期待できます。
VLOOKUP関数を使う前の準備
VLOOKUP関数を使うためには、データを入力する入力表と、設定が入力されているマスタ表が必要になります。
まずは、参照元の表を作成します。1行1データの整理された表を作るように心がけましょう。
表の作り方については、過去に記事を書いていますので、よかったら参考にしてください。
また、VLOOKUP関数の場合は、左端の列にコード番号を設定するようにして下さい。コード番号が対象のデータの顔となります。
左端以外にコード番号を設定すると、コード番号より右にある項目を参照できません。
次に、検索先の欄を作成します。
ここでは、商品CDにコード番号を入力し、マスタ表からのデータを取得できるように入力表を構成します。
VLOOKUP関数の引数
VLOOKUP関数は４つの型から成り立ちます。
- 第一引数・・・検索値
- 第二引数・・・検索範囲
- 第三引数・・・列番号
- 第四引数・・・検索の型
第一引数の検索値は、コード番号を入力します。ここで表の左端のコードが関係してきます。
第二引数は、参照元の表を設定します。通常は「A3:F50」などのようにセルのアドレスを指定します。
一方で、範囲は名前を設定することも出来ます。
名前の定義については、こちらの記事をよかったら参考にしてください。
第三引数には、データを抽出する列が左から何列目にあるかを指定します。
第四引数は、慣れないうちは0を設定しましょう。
0の替わりに「FALSE」を入力しても、同じ結果が出力されます。1またはtrue は近似値を検索します。
| 型 | 検索の方法 |
|---|---|
| 0(FALSE) | 完全一致 |
| 1(TRUE) | 近似値 |
第二引数と第三引数の設定は、必要に応じて絶対参照を用いるとよいでしょう。
VLOOKUP関数を使ってみる
ここで、実際にVLOOKUP関数を使ってみます。
ここに、商品のデータを入力したマスタ表があります。
表の左端は、①商品コード、次に②商品名・③品種・④産地・⑤単価と続きます。
このマスタ表をもとに、売上の実績を入力します。
第一引数の検索値は、絶対参照を含む「$B2」を指定します。
第二引数は、絶対参照を含む「$J$1:$N$8」を入力します。
第三引数では、品名の列番号である「2」を入力します。
他の項目についても、列番号を確認して適切な数値を入力します。
マスタ表のデータを反映させることができました。
エラーを回避する方法
VLOOKUP関数で第一引数のコード番号がマスタ表に存在しない場合、エラーを表示します。
エラーを発生させないためには、2種類の対処方法があります。
一つ目は入力規則を使う方法、2つ目は関数を使う方法です。
入力規則で入力値を制限すれば、エラーを発生させる確率がかなり下がります。
数式でエラーを発生させないようにするためには2つの方法があります。
- ２つの組み合わせ(VLOOKUP・IFERROR)
- 3つの組み合わせ(VLOOKUP・ISERROR・IF)
関数を組み合わせたところ、エラーを回避させることができました。
ここで注意すべきこととして、取得するデータが文字であるか、数値であるかによって、第二引数の指定方法が変わってきます。
文字であれば空白を表示する「""」を指定します。
数値であれば0を入力します。
数値が反映されるところで「””」を指定した場合、ここでは掛け算して算出される金額にエラーが発生します。
逆に、文字を指定するべきところに0を指定した場合、表の見た目が悪くなります。
2003以前であれば、VLOOKUP関数・ISERROR関数とIF関数の3種類の組み合わせになります。数式が長くなりますので、多少見づらくなります。
VLOOKUP関数を快適に使うための補助機能
VLOOKUP関数は、Excelで完結するデータベースを作る場合には欠かせません。
特に、商品マスタからデータを取り出して、購入や販売の実績データを作るなど、入力を行う作業が最もよく使われるところです。
VLOOKUP関数を効率的に使い続けるためには、Excelの機能を併用すると操作性が更に向上します。
入力規則を設ける
第一引数の検索値のセルに入力規則を設けると、先ほども述べたような入力ミスによるエラーの回避、ドロップダウンリストによる入力支援などの効果があります。
表に名前をつける
第二引数の範囲となるマスタ表に名前をつけることで、数式が見やすくなります。
セルに名前を付ける方法については、過去に記事を書いていますので、よかったらこちらを参考にしてください。
テーブル設定を活用
Excel2007以降をお使いの場合は、マスタ表をテーブル設定しておくこともできます。
ホームタブの「テーブルとして書式設定」をクリックして、設定することができます。
マスタ表と入力表の両方をテーブル設定した状態です。
テーブル設定を行うと、表に名前がつけられます。
テーブルはデータが入力されると範囲が自動で追加されます。
マスタ表のデータが増えた時に引数の修正作業が不要になります。
INDIRECT関数を使えば、入力規則のリスト設定がメンテナンス不要になります。
但し、絶対参照が使えませんので用途によって使い分けると良いでしょう。
その他の検索関数について
HLOOKUP関数
HLOOKUP関数は、横長の表を参照するために作られた関数です。
マスタ表の一行目にコード番号を入れる必要があります。
これは一般的に使われる縦長のマスタ表です。
一方、こちらはHLOOKUP関数を使うことを想定した横長のマスタ表です。
引数はVLOOKUP関数と同じですが、第三引数は列名ではなく、行名を指定します。
LOOKUP関数
LOOKUP関数は、引数の使い方がVLOOKUP関数とは異なります。
第一引数は検索値で一緒ですが、第二引数は検索範囲、第三引数は対応範囲になります。
第四引数はありません。検索範囲は近似値でも対応するようです。
マスタ表は縦長でも横長でも対応できます。
これは、一般的な縦長の表から作られたLOOKUP関数です。
これは、横長の表から作られたLOOKUP関数です。
LOOKUP関数は、マスタ表の左端にコード番号を設定する必要がありません。
MATCH関数とINDEX関数の組み合わせ
複数の関数を組み合わせることで、VLOOKUP関数と同じ結果を出すことが出来ます。
MATCH関数は対象のデータが何番目にあるかを求めます。
INDEX関数は表の何行目と何列目にどんなデータがあるかを求めます。
XLOOKUP関数
XLOOKUP関数は、Office365向けに作られた新しい関数です。
列番号の変更にも対応しています。また、エラーの回避にもXLOOKUP自体で対応しています。
その他の新たな機能もXLOOKUP関数には含まれているそうです。
従来のVLOOKUP関数と比べても、かなりメンテナンス性が向上するのではないでしょうか？
注意点は、Microsoft365以外のバージョンでは対応していません。
職場のOfficeのバージョンを確認してから使いましょう。
まとめ
VLOOKUP関数は、表から特定のデータを取り出すことができる便利な関数です。
エクセルを熟知された方からは、
- VLOOKUP関数は実務では使えない
- 表の列が増えると対応できない
などの見解がありますが、VLOOKUP関数は初心者になじみやすく、データベース的な感覚を身に付けることが出来る優れた関数です。
VLOOKUP関数を完全に習得できていれば、MATCH関数とINDEX関数の組み合わせに挑戦すべきだと思います。
しかし、どうやって検索したらよいか分からない人にとっては、関数の組み合わせはハードルが高く、ほとんどの人が途中で挫折するでしょう。
VLOOKUP関数をマスター出来れば、関連する関数や標準機能を芋づる式に覚えることが出来、間違いなくExcelのスキルが大幅にアップします。
まずは、VLOOKUP関数から使いこなせるようになりましょう。
本日もありがとうございました。