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

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

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

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

そこで、本記事では、

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

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

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

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

    VLOOKUP関数とは

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

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

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

    Excel_VBA_VLOOKUP_伝票

    [書式]
    =VLOOKUP(検索値, 範囲, 列番号, 検索方法の指定)

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

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

    [使用例]

    Excel_VBA_VLOOKUP_VLOOKUP解説

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

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

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

    VBAでVLookupを使う方法

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

    [書式]
    WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法の指定)

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

    [使用例]
    Sub vlookup_sample1()
    
      Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 2, False)
    
    End Sub
    [実行結果]

    Excel_VBA_VLOOKUP_VBAでVLOOKUP

    いかがでしょうか?

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

    エラーへの対処法

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

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

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

    参照と型が異なる場合

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

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

    Excel_VBA_VLOOKUP_プロパティ取得不可1

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

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

    Excel_VBA_VLOOKUP_プロパティ取得不可2

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

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

    Sub vlookup_sample2()
    
    On Error GoTo err:
    
        Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 2, False)
    
        Exit Sub
    
    err:
    
        If err.Number = 1004 Then
            Range("C3") = "値取得不可!"
        End If
    
    End Sub

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

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

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

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

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

    Sub vlookup_sample3()
    
        Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 4, False)
    
    End Sub

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

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

    Excel_VBA_VLOOKUP_プロパティ取得不可3

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

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

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

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

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

    Rangeオブジェクト.Formula = “=数式”

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

    Sub vlookup_sample4()
        Range("C3").Formula = "=VLOOKUP(B3, E3:G8, 4, False)"
    End Sub

    実行結果:
    VLookup01

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

    ぜひ覚えてください。

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

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

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

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

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

    Workbooks("ブック名.xlsx").Worksheets(“シート名”).Range((参照範囲)

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

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

    Sub vlookup_sample5()
        With Worksheets("入力")
            .Range("C3") = WorksheetFunction.VLookup(.Range("B3"), Worksheets("データ").Range("A3:C8"), 2, False)
        End With
    End Sub

    実行結果:
    VLookup02

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

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

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

    Sub vlookup_sample6()
        Dim arr() As Variant
        arr = Range("E3:G8")
        
        '参照範囲を配列に変更
        Range("C3") = WorksheetFunction.VLookup(Range("B3"), arr, 2, False)
    End Sub

    実行結果:
    VLookup03

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

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

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

    VLookupの高速化

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

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

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

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

    INDEX関数は

    =INDEX(範囲,縦位置)

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

    MATCH関数は

    =MATCH(検索値,範囲,一致か近似値か)

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

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

    Sub vlookup_sample7()
        Range("C3").Formula = "=INDEX(F3:F8, MATCH(B3, E3:E8))"
    End Sub

    実行結果:
    VLookup04

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

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

    Sub vlookup_sample9()
        Const num = 10000
        
        Dim i As Long
        For i = 1 To num
            Range("B" & i + 2) = "C" & Format(CStr(Round(num * Rnd) + 1), "00000")
            Range("E" & i + 2) = "C" & Format(CStr(i), "00000")
            Range("F" & i + 2) = "商品" & Format(CStr(i), "00000")
        Next i
    End Sub

    VLookup06

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

    Declare Function GetTickCount Lib "KERNEL32.DLL" () As Long
    
    Sub vlookup_sample8()
        Const num = 10000
        
        Dim newTimer As Long
        Dim t1 As Long, t2 As Long, t3 As Long
        
        'VLookup関数
        newTimer = GetTickCount '計測開始
        Dim i As Long
        For i = 1 To num
            Range("C" & i + 2) = WorksheetFunction.VLookup(Range("B" & i + 2), Range("E3:F" & num + 2), 2, False)
        Next i
        t1 = GetTickCount - newTimer '処理時間算出
        
        'Index関数 & Match関数
        newTimer = GetTickCount '計測開始
        For i = 1 To num
            Range("C" & i + 2) = WorksheetFunction.Index(Range("F3:F" & num + 2), _
                                    WorksheetFunction.Match(Range("B" & i + 2), Range("E3:E" & num + 2)))
        Next i
        t2 = GetTickCount - newTimer '処理時間算出
        
        'Findメソッド
        newTimer = GetTickCount '計測開始
        Dim myObj As Range
        For i = 1 To num
            Set myObj = Range("E3:E" & num + 2).Find(Range("B" & i + 2))
            Range("C" & i + 2) = Range("F" & myObj.Row)
        Next i
        t3 = GetTickCount - newTimer '処理時間算出
        
        MsgBox "VLookup関数: " & t1 & "ミリ秒" & vbCrLf & _
                "Index関数 & Match関数: " & t2 & "ミリ秒" & vbCrLf & _
                "Findメソッド: " & t3 & "ミリ秒"
    End Sub

    実行結果:
    VLookup05

    実行結果では

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

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

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

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

    まとめ

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

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

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

    LINEで送る
    Pocket

    ITエンジニアへ転職したい方におすすめ

    自分を評価してくれる企業に転職して年収を上げたい! 自分のスキルにあった独自案件を知りたい!
    エンジニアは今もっとも注目されている職業の1つ。エンジニアになって年収を増やしたい方や、あなたのスキルに見合った企業へ転職したいエンジニアの方も多いですよね。

    しかし、大手の転職媒体は扱う求人数が多くても、誰もが登録しているので競争率もかなり高くなっています。そのため、あなたの条件に見合った企業を見つけても転職するためには、相応の努力とスキルが必要となります。

    こういった媒体では、未経験からエンジニアを目指す方やエンジニア歴2〜3年で転職を考えている方にとって、最適な転職環境とはいえません。

    そこでオススメしたいのが、未経験者や若手エンジニア向けの独自案件を多く掲載している「侍ワークス」です。

    侍ワークスは、独自案件を多く掲載しているだけでなく、

    ・応募から就業まで一貫したサポート

    ・就業後もアフターフォロー

    といった経験の浅い方や初めてエンジニアを目指す方にも安心のフォロー体制が整っています。もちろん登録は完全無料!しかも案件を見るだけなら登録も不要です。

    まずは、お気軽にどんな求人があるか見てみてください。あなたにピッタリの企業がきっと見つかりますよ! 侍ワークスの求人情報を見る

    書いた人

    本多 農

    本多 農

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