注目キーワード

【Excel VBA】VBAでVLOOKUP関数を使用する方法を解説

  • 2024.11.15
       
【Excel VBA】VBAでVLOOKUP関数を使用する方法を解説

皆さんは、Excelで検索を行う場合にどのような方法を使用しますか？
今回は、その内の１つであるVLOOKUP関数の使い方について、基礎的な内容から応用方法まで詳しく解説していきたいと思います。

目次

VLOOKUP関数とは？

VLOOKUP関数とは、検索範囲内の左端にある列のキーと検索内容が一致した行の中から、指定した場所のデータを取得してくれる関数です。

文章だけでは分かりづらいかと思うので、下の簡単な表を例に詳しく解説していきます。

例えばこのように、それぞれの商品に関する情報をまとめた一覧の中から、特定の商品の値段と在庫数について調べたいとします。

その際に、VLOOKUP関数を次のように使用して、取得したい情報を検索します。

セルに入力してあるのは以下のようなVLOOKUP関数の基本構文です。

【基本構文】

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

検索値は検索したい値、範囲は検索内容と取得内容に対応するデータがある範囲、列番号は選択範囲内の何列目に欲しい値があるかの指定です。

今回は商品名をもとに検索を行い、その商品の値段を取得しようとしているので、実際の式はこのようになります。

=VLOOKUP(F4,B3:C7,2,FALSE)

検索するのは商品Cについてなので、検索値にはF4セルを指定します。

検索キーとなる商品名と、一致した際に取得する値段の情報を範囲内に含めたいので、B3〜C7までを検索範囲として指定します。VLOOKUP関数は、指定した範囲内の一番左端にある列のみを検索値と比較するので、指定の際には注意が必要です。

指定した範囲内において、値段に関する情報は左から2番目のC列にあるので、列番号に入力する数字は2になります。

検索方法は今回、FALSEを指定しました。FALSEは完全一致での検索、TRUEはあいまい検索になりますが、あいまい検索はデータが昇順に並んでいないと上手く機能しないため、基本はFALSEで検索をします。

結果は以下の通りです。

VLOOKUP関数によって、無事に商品Cの値段を取得することができました。
先ほど指定したC列のうち、商品Cがある場所と同じ行から、270という数字を取り出すことに成功しています。
これが、VLOOKUP関数の主な動作になります。

VBAでVLOOKUP関数を使用する方法は？

先ほどの表を引き続き参考にしながら、次はVBAでコードを記述する際の使い方について解説していきたいと思います。

VBAでVLOOKUP関数を使う際には、以下のようにコードを書きます。

WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法)

WorksheetFunctionオブジェクトを使用することで、VBAでワークシート関数が扱えるようになります。引数については、先ほど説明したものと同様です。

VBAを使用して、今度は商品Cの在庫数を取得してみましょう。以下がそのサンプルコードになります。

【サンプルコード】

Sub　sampleVlookup()

  Range("H4") = WorksheetFunction.VLookup(Range("F4"), Range("B3:D7"), 3, False)

End Sub

【実行結果】

先ほどの式ではB3〜C7までを検索範囲としていましたが、在庫数はその１つ隣のD列に配置されているので、範囲をD7まで広げています。D列は指定範囲内の左から３番目に位置するので、列番号には3を入力します。

これで、商品Cの在庫数も同様に取得することができました。

エラーが発生した場合はどうする？注意点と対処法を解説

VBAでVLOOKUP関数を使った際に、「WorksheetFunction クラスの VLookup プロパティを取得できません。」といったエラーが発生することが度々あります。

このエラーが発生する主な理由と対処法について説明していきます。

参照するセルの型が異なる場合

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

例えば、数値同士で検索したつもりが、片方は文字列として入力されていたパターンなどです。

その場合は、検索値の型を検索キー側に合わせるとエラーは解消されます。

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

検索値として指定した値が範囲内に存在しないと、エラーが発生します。

その場合、一般的なエラーの対処法として、On Errorステートメントを使う方法があります。

以下がその一例です。

【サンプルコード】

Sub sampleErr()

　On Error GoTo ErrLabel

　Range("H4") = WorksheetFunction.VLookup("商品Z", Range("B3:D7"), 3, False)

　Exit Sub

ErrLabel:

　　Range("H4") = "商品がありません"

End Sub

最初に「On Error Goto (ラベル名)」と記述することで、エラーが発生した際に任意で設定したラベル名の場所まで処理がジャンプします。今回のサンプルコードの場合、下部にある「ErrLabel:」という部分です。

実際にエラーが起きた場合に処理したい内容を、ラベル先に記述するようにしましょう。

VBAでセルに入力してデバッグする方法を解説

何かの理由でエラーが発生した時、セルに式を記述することで、原因について大まかに確認することができます。

以下の例を見てみましょう。

このケースでは、エラーが発生した結果「#N/A」とセルに表示されています。

検索値に誤った値を指定してしまったり、検索範囲の指定がずれて検索値が範囲外になってしまったりなどが原因で、一致するデータがなかった場合に表示されます。この場合は、指定した検索値や検索範囲などを見直すと良いでしょう。

また、次のようなケースも存在します。

セルに「#REF!」と表示された場合、列番号に指定した数値が検索範囲内の列数を超えた状態になっています。上の表のケースでは、指定した範囲の列数が２列のところを引数で3を指定してしまっているので、エラーが発生しています。

この場合は、列番号を範囲内の数で指定し直せば問題は解消します。

このように、セルに式を記入してみることで、実際の動作検証やエラー内容の大まかな確認を行うことができます。

また、先ほどの例ではシートのセルに直接式を記入していましたが、VBAを使ってセルに関数を入力する方法もあります。

コードで記述をする場合、RangeオブジェクトのFormulaプロパティを使用します。以下がそのコードです。

【基本構文】

Range("セル範囲").Formula = “=数式”

【サンプルコード】

Sub sampleFormula()
　Range("G4").Formula = "=VLOOKUP(F4,B3:C7,2,FALSE)"
End Sub

これで、式を記入するために毎回シートを表示する必要はなくなります。

別シート、別ブックを参照する場合はどうする？

これまでは、全て同じシート内で作業することを前提にコードを記述してきましたが、検索するデータ元と検索結果を別々に分けたい場合もあるかと思います。
その場合は、以下のように範囲を指定することで対応できます。

【基本構文】

Workbooks("ブック名").Worksheets(“シート名”).Range(”セル範囲”)

参照先のブックは開いていることが前提になります。同じブック内を参照する場合は、ブック名は省略可能です。

【サンプルコード】

Sub sampleWorksheets()

　With Worksheets("Sheet1")

　　.Range("C4") = WorksheetFunction.VLookup(.Range("B4"), Worksheets("Sheet2").Range("B3:C7"), 2, False)
    
　End With

End Sub

【実行結果】

参照範囲を配列で置き換える方法を解説

検索時の参照範囲には、配列を指定することもできます。

実際のコードについて見てみましょう。

【サンプルコード】

Sub sampleArray()

　Dim arr() As Variant
　arr = Range("B3:D7")
    
　Range("H4") = WorksheetFunction.VLookup(Range("F4"), arr, 3, False)

End Sub

配列arrに参照範囲を格納した後、VLookup関数の引数に指定しています。
このように、範囲指定を配列で行うこともできます。

VLOOKUPを高速に処理する方法は？

ここまで詳しく使い方を説明してきたVLOOKUP関数ですが、扱うデータの数が増えれば増えるほどに計算速度が遅くなり、最悪の場合Excelがフリーズしてしまうこともあります。

そこで、処理を軽くする方法の１つとして、INDEX関数とMATCH関数を組み合わせて使う方法を紹介していきたいと思います。

これまでと同じように、表の中から指定した商品の情報を取得します。今回は、商品Dの在庫数について検索してみましょう。

INDEX関数は、参照範囲の中から指定した行と列が交差する位置にあるセルの値を返してくれる関数です。そこで、行の指定にMATCH関数を使用することで、VLOOKUP関数のように検索値と一致した行の値を取得することができるようになります。

INDEX+MATCHで検索する場合は、次のようにコードを記述します。

【サンプルコード】

Sub sampleIndexMatch()
　Range("H4").Formula = "=INDEX(D3:D7, MATCH(F4,B3:B7,0))"
End Sub

【実行結果】

この方法を使う際のポイントは、VLOOKUP関数のように検索キーとする左端の列から値を取得する列までの全てを参照範囲とするのではなく、INDEX関数の参照範囲に取得データがある列を、MATCH関数の参照範囲に検索キーの列を、それぞれ1列ずつ指定することです。

VLOOKUP関数はその性質上、検索するにあたってデータベース全体を参照する必要があります。例えば上の表の場合、在庫数を取得しようとすると、B3からD7までの合計15個のセルを全て参照しなければなりません。

一方、INDEX+MATCHであれば参照するのは2列だけで済むので、セルの数も10個に減ります。データの数が膨大になるほどこの差はかなり大きいものになり、処理速度にも違いが出てきます。

また、計算が早くなることに加えて、以下のようなメリットも得ることができます。

  • 検索キーが左端になくても検索することができる
  • INDEX関数の列指定が省略できるので、範囲内の列数を数える必要がない
  • 後からデータベースに列を挿入しても参照先がずれない

使用するメリットがかなり多い方法なので、ぜひ覚えておくと良いでしょう。

まとめ

いかがでしたでしょうか？

ExcelでVLOOKUP関数を使う機会は多いと思いますので、内容をしっかりと理解して使いこなせるよう、頑張っていきましょう！

関連記事

VBAの勉強方法は？

書籍やインターネットで学習する方法があります。昨今では、YouTubeなどの動画サイトやエンジニアのコミュニティサイトなども充実していて多くの情報が手に入ります。
そして、より効率的に知識・スキルを習得するには、知識をつけながら実際に手を動かしてみるなど、インプットとアウトプットを繰り返していくことが重要です。特に独学の場合は、有識者に質問ができたりフィードバックをもらえるような環境があると、理解度が深まるでしょう。

ただ、VBAに限らず、ITスキルを身につける際、どうしても課題にぶつかってしまうことはありますよね。特に独学だと、わからない部分をプロに質問できる機会を確保しにくく、モチベーションが続きにくいという側面があります。独学でモチベーションを維持する自信がない人にはプログラミングスクールという手もあります。費用は掛かりますが、その分スキルを身につけやすいです。しっかりと知識・スキルを習得して実践に活かしたいという人はプログラミングスクールがおすすめです。

プログラミングスクールならテックマニアがおすすめ！

ITスキル需要の高まりとともにプログラミングスクールも増えました。しかし、どのスクールに通うべきか迷ってしまう人もいるでしょう。そんな方にはテックマニアをおすすめします！これまで多くのITエンジニアを育成・輩出してきたテックマニアでもプログラミングスクールを開講しています。

＜テックマニアの特徴＞
・たしかな育成実績と親身な教育　～セカンドキャリアを全力支援～
・講師が現役エンジニア　～“本当”の開発ノウハウを直に学べる～
・専属講師が学習を徹底サポート　～「わからない」を徹底解決～
・実務ベースでスキルを習得　～実践的な凝縮カリキュラム～

このような特徴を持つテックマニアはITエンジニアのスタートラインとして最適です。
話を聞きたい・詳しく知りたいという方はこちらからお気軽にお問い合わせください。

     

Otherカテゴリの最新記事