脱VBA初心者!VLOOKUP関数の基礎の基礎をわかりやすく解説!

Excelの表でコードや区分の横に対応する名称を出すときなど、VLOOKUP関数を使います。

しかし、初心者にとっては

「VLOOKUP関数の使い方がいまいちわからない…」
なんて方も多いのではないでしょうか?

そこで、本記事では、

  • VLOOKUP関数とは何か
  • VBAでVLOOKUP関数を使う方法
  • といった基本的な内容から、

  • エラーへの対処法
  • 別シート、別ブックを参照する方法
  • 参照範囲を配列で置き換える
  • VLookupの高速化
  • という応用的な内容までお伝えします。

    VBAのVLOOKUP関数についてわかりやすく解説していますので、ぜひ参考にしてください!

    本記事を読み終える頃には、表においてVLOOKUP関数を使えるだけでなく、VBAからも自在に使えるようになっていますよ!

    VLOOKUP関数とは

    最初に、そもそもVLOOKUP関数とは何なのかを解説します。冒頭で触れたとおり、コードや区分から対応する値を取得するものです。

    伝票を例にとると、商品コードから商品名や単価を取得すると考えてください。その具体例は以下です。

    以下の例では、商品コードと数量のみ手入力で、商品名と単価はVLOOKUP関数を使用して取得しています。金額は単価と数量の掛け算、合計はSUM関数を使って自動で計算結果を表示しています。

    Excel_VBA_VLOOKUP_伝票

    [書式] 検索値は検索したい値、範囲は対応する名称がある範囲、列番号は何列目に欲しい値があるかの指定です。

    検索方法は完全一致かあいまい検索を指定しますが、通常は完全一致で使用するため、FALSEを指定すればよいでしょう。

    [使用例]

    Excel_VBA_VLOOKUP_VLOOKUP解説

    商品名の取得を文章で説明しましょう。

  • B3セルの商品コードに対応する商品名を、C3セルに表示する。
  • 商品名は範囲H3セルからJ8セルまでにある。
  • 範囲の中で商品名は範囲の2列目にある。
  • あいまい検索はしないのでFALSE。
  • 結果、図のオレンジ色の枠にある式ができます。となりのD3セルに単価も取得して設定してみました。オレンジ色と青色の枠内の式を比較すると、動作イメージが明確になるでしょう。

    これが、Excelの表におけるVLOOKUP関数です。

    VBAでVLookupを使う方法

    ExcelVBAにおいて、コードや区分から値を取得する場合は以下のようにします。

    [書式] 次の例では、B3セルに入力した商品コードに対応する商品名を、表から取得してC3セルに表示しています。

    [使用例] [実行結果]

    Excel_VBA_VLOOKUP_VBAでVLOOKUP

    いかがでしょうか?

    キーとなる値からそれに対応する値を取り出す、といった関係データベース的な使い方ができますね。

    エラーへの対処法

    VBAでVLOOKUP関数を使ったコーディングをするのは、今まで解説してきたことで十分可能です。実は、それよりもエラーとその対処方法のほうが難しいのです。

    「WorksheetFunction クラスの VLookup プロパティを取得できません。」このエラーは特によく起こります。

    よくある理由を順に解説していきます。

    参照と型が異なる場合

    検索したい値のあるセルと、範囲で指定するセルの型が異なるときに起こります。

    以下の例では、B3セルを文字列にして型を合わせるとエラーは解消します。

    Excel_VBA_VLOOKUP_プロパティ取得不可1

    参照範囲に値が存在しない

    検索範囲に値が存在しないときにエラーが発生します。

    Excel_VBA_VLOOKUP_プロパティ取得不可2

    VBAの一般的なエラーの対処法を解説します。

    次のサンプルコードでは、実行時エラー1004が発生した場合に、C3セルに「値取得不可!」と入れています。

    最初の方にある「On Error Goto err:」は、エラーが発生したら下の方の「err:」というラベルまでジャンプせよという意味です。

    ただしエラーは1004以外にもありえるので、1004の場合のみ「値取得不可!」と返すようにしているのが「err:」の後のIf文です。

    実行時エラー1004だけでも「範囲に値が存在しない」以外にもさまざまな原因があるので、あえてザックリと「値取得不可!」としています。

    VBAからセルに記述してデバッグする方法

    以下のコードを実行してみましょう。

    1004エラーが発生します。(環境によっては、実行時にエラーが出るケースもあります。)

    検証方法を解説します。コード中のVLOOKUP関数の部分を、セルに式で記述してみましょう。

    Excel_VBA_VLOOKUP_プロパティ取得不可3

    商品名は「#REF!」と表示されており、値が取得できていません。これはVLOOKUP関数の第3引数である列番号が4となっているのが原因です。

    範囲はE3セルからG8セルまで3列しかないのに、4列目を指定してしまっています。ここでは商品名がほしいので、列番号を2と修正すれば問題は解消できます。

    想定する最終形で実際に式として記述してみる、これはとても役に立つデバッグ方法です。そして、このデバッグ方法をもう一歩さらに進めてみましょう。

    ここまでは、ExcelシートのセルにVLOOKUP関数を直接入力していましたが、セルに入力せずにVBAからVLOOKUP関数をセルに入力することもできます。

    それはRangeオブジェクトのFormulaプロパティを使う方法です。

    これで指定したRangeオブジェクトにVBAから関数式を入力することができます。

    実行結果:
    VLookup01

    これでExcelシートとVBAの開発環境(VBE)を行ったり来たりせずに、VBAの開発環境だけでデバッグをすることが可能です。

    ぜひ覚えてください。

    Formulaプロパティを使ってVBAからセルに直接関数を入力する方法については、こちらで詳しく解説しています。ぜひ参考にしてください。

    【VBA入門】関数の使い方(VBA関数一覧、ワークシート関数の入力)
    更新日 : 2019年4月28日

    別シート、別ブックを参照する方法

    ここまでは同じシート内の参照範囲を検索してきました。参照範囲が大きくなったり、勝手に変更されたくない場合などは検索結果と参照範囲を別シートや別ブックに分ける場合も考えられます。

    そんな場合は、参照範囲の指定を以下のように記述することで対応できます。

    参照先が同じブック内の場合はWorkbooks(“ブック名.xlsx”).は省略可能です。

    これは参照先のブックが開いていることが前提になります。

    実行結果:
    VLookup02

    参照範囲を配列で置き換える

    これまでは参照範囲がExcelシート上のあるセル範囲でした。VBAを使っていると、Excelシート上のセル範囲ではなく、配列を参照範囲に指定したい場合も出てきます。

    検索する参照範囲を配列にした場合についてみてみましょう。

    実行結果:
    VLookup03

    まず、配列arrにセル範囲”E3:G8″の値を格納しています。VLookup関数の参照範囲に配列arrを指定しています。

    なお、配列とセル範囲の値を共有する方法については、こちらで詳しく解説していますので、ぜひ参考にしてください。

    【VBA入門】配列総まとめ(初期化、ループ操作、コピー、結合、比較)
    更新日 : 2019年4月18日

    VLookupの高速化

    これまでは、比較的小さいデータをVLookupで参照して検索してきました。

    しかし、例えば1万件や10万件を超える大きなデータから検索を数千回繰り返したい場合もあります。そんな場合はVLookup関数の処理速度も考慮しなければ、時間がかかりすぎてしまうことになります。

    時間がかかりすぎてしまえば、代わりの手段も考えなければなりません。

    ExcelではVLOOKUP関数の代わりに、INDEX関数MATCH関数を組み合わせて使わうこともよくあります。

    INDEX関数は

    “縦位置”に”範囲”内の位置を数値で入力するとその値を返します。配列のインデックス番号を指定したら、要素の値が返ってくるのと使い方が似ていますね。

    MATCH関数は

    ”検索値”に値を入力すると”範囲”の中の位置を数値で返します。

    MATCH関数で検索値の位置を出力して、その位置を使ってある範囲内の値を抽出します。これでVLOOKUP関数と同じ結果が得られるようになります。

    実行結果:
    VLookup04

    また、VBAでは検索にはFindメソッドをよく使います。

    これらで処理速度を比較してみましょう。使うデータは以下のVBAを使って作成しています。

    VLookup06

    1万個のデータリストから商品名を抽出する作業を1万回繰り返す時間で比較します。

    実行結果:
    VLookup05

    実行結果では

  • VLookup関数で約12.5秒
  • Index関数とMatch関数で約2.7秒
  • Findメソッドで約39.3秒
  • という結果になりました。この結果からは、VLookup関数よりも代わりにIndex関数とMatch関数を使った方が処理時間を短縮できます。

    数千や数万個のデータリストから数千個のデータを検索して抽出する場合は、Index関数とMatch関数の組み合わせを使用することをオススメします。

    ちなみに、Findメソッドは処理時間がかなり遅い結果となりました。Findメソッドの使い方については、こちらのサイトで詳しく解説していますので、ぜひ参考にしてください。

    【VBA入門】Find、FindNextで検索(完全一致、部分一致、複数一致)
    更新日 : 2019年4月18日

    まとめ

    本記事では、VLOOKUP関数について解説しました。

    セルに式で記述する、VBAでも実装できる、この両方をマスターするといろんなことができるようになります。

    ぜひ、頑張ってマスターしてくださいね!

    LINEで送る
    Pocket

    最短でエンジニアを目指すなら侍エンジニア塾

    cta_under_bnr

    侍エンジニア塾は業界で初めてマンツーマンレッスンを始めたプログラミングスクールです。これまでの指導実績は16,000名を超え、未経験から数多くのエンジニアを輩出しています。

    あなたの目的に合わせてカリキュラムを作成し、現役エンジニア講師が専属であなたの学習をサポートするため効率よく学習を進めることができますよ。

    無理な勧誘などは一切ありません。まずは無料体験レッスンを受講ください。

    無料体験レッスンの詳細はこちら

    書いた人

    本多 農

    本多 農

    関西在住のITエンジニアです。普通の会社に勤務しながら、侍エンジニアのインストラクター、ライターとして活動しています。

    おすすめコンテンツ

    あなたにぴったりなプログラミング学習プランを無料で診断!

    プログラミング学習の効率を劇的に上げる学習メソッドを解説