• 公開日：
  • 更新日：

ExcelのVLOOKUP関数をVBAで高速に処理する方法

VBAでExcelのVLOOKUP関数のように検索する際、大量のデータを処理をすると動作が重くなり、実行時間がかかってしまう場合があります。

この記事ではExcelのVLOOKUP関数をVBAで使用して高速に処理する方法を解説していきます。VBAの記述の仕方によっては実行時間に大きな差がでることがありますのでどれくらい差がでるか検証しております。VBAを記述する際にご参考ください。

検索元の表データの用意

検索するための元になるデータを用意します。数十行だと計測してもわかりづらいので今回は10万行×2列のデータを用意しました。

ちなみにExcelでは現在（2019年9月12日時点）、最大1,048,576行まで対応が可能です。

検索元の表データ

上記の表が、今回作成した検索元となる表データです。「商品コード」と「商品名」の2列あります。値はそれぞれ「1001」「A100」からオートフィルで1ずつ加算されるように入力しています。

検索元の表データ

上記の図は表データの最終行になります。10万行のデータなので見出しの分をプラス1をして「100,001」行目までデータを入力しています。このデータをもとにVLOOKUP関数とVBAを使って検索した結果を入力していきます。

VLOOKUP関数を使用する場合

まずVBAではなくExcelのVLOOKUP関数だけで入力する方法を確認しましょう。

VLOOKUP関数を使用する例

「商品コード」だけのデータが入った別シート（Sheet2）を用意しました。B2セルを選択し、『=VLOOKUP(A2,Sheet1!$A$2:$B$100001,2,FALSE)』を入力します。

検索値が「A2」、検索範囲が「Sheet1のA2からB100,001までの範囲」、列番号は「2（Sheet1の商品名列）」、検索の型は「FALSE（完全一致）」を指定しています。

VLOOKUP関数の結果

B2セルに「A100」と表示されました。A2セルを検索値としてSheet1の商品コード列から「1001」を検索し、該当行の2番目列（商品名）の「A100」を返すことができました。

他の行にも適用させたい場合は、B2セルの右下を最終行まで引っ張ることで同様にVLOOKUP関数が処理をして値を返します。

VLOOKUP関数で入力した場合、データ量がさらに多くなると再計算などに時間がかかる場合があります。今回はVBAを使用してExcelのVLOOKUP関数と同じように検索できるようにしていきます。

VBAを使用する方法

VBAでExcelのVLOOKUP関数と同じように検索する場合、VBAの記述によっては実行時間に差がでます。まずVBAでコードを記述するまでの準備をしましょう。

Visual Basic Editorの起動～コード入力まで

VBEの起動

VBAを記述するために「Visual Basic Editor」を起動します。【開発】タブを選択し、【Visual Basic】を押します。

標準モジュールの挿入

「Visual Basic Editor」が起動したら左側のナビゲーション「プロジェクト - VBAProject」ウィンドウからVBAを実行したいブック内の【「Microsoft ExcelObjects」を右クリック】します。次に右クリックメニューから【挿入】、【標準モジュール】の順に選択します。

コード入力

コード入力のウィンドウが表示されるのでここにVBAのコードを記述していきます。

VLookupメソッドを使用する方法

VBAにおいてVLOOKUP関数はワークシート関数（ワークシートのセルに入力する関数）になります。VBAからワークシート関数を呼び出すには、ApplicationオブジェクトのWorksheetFunctionプロパティを使用します。

WorksheetFunctionプロパティでVLOOKUP関数に該当するものとして「VLookupメソッド」がありますのでこれを使って記述する例をご紹介します。

式としては「WorksheetFunction.VLookup(検索値,検索範囲,列番号,検索の型)」のように記述します。引数の指定は、ExcelのVLOOKUP関数と同じです。

サンプルコード

コードの記述

下記のVBAのコードをコピー＆ペーストします。

※検索範囲、出力範囲に関してはご自身の状況によって変更ください。下記のコード例ではSheet3に検索値、出力範囲を指定しています。

Sub Sample()

    Dim SearchKey As Range '検索値
    Dim SearchRange As Range '検索範囲
    Dim OutputRange As Range '出力範囲
    Dim i As Long
    
    Set SearchKey = Worksheets("Sheet3").Range("A2:A100001")
    Set SearchRange = Worksheets("Sheet1").Range("A2:B100001")
    Set OutputRange = Worksheets("Sheet3").Range("B2:B100001")
    
    Application.ScreenUpdating = False
    
    For i = 1 To SearchKey.Rows.Count
        OutputRange(i, 1) = WorksheetFunction.VLookup(SearchKey(i, 1), SearchRange, 2, False)
    Next
    
    Application.ScreenUpdating = True
    
End Sub

サンプルコード解説

    Dim SearchKey As Range '検索値
    Dim SearchRange As Range '検索範囲
    Dim OutputRange As Range '出力範囲
    Dim i As Long
    
    Set SearchKey = Worksheets("Sheet3").Range("A2:A100001")
    Set SearchRange = Worksheets("Sheet1").Range("A2:B100001")
    Set OutputRange = Worksheets("Sheet3").Range("B2:B100001")

検索値、検索範囲、出力範囲を定義し、それぞれシートのどの部分かを指定しています。

    Application.ScreenUpdating = False

    Application.ScreenUpdating = True

画面の更新を抑制（False）、復活（True）を指定しています。

    For i = 1 To SearchKey.Rows.Count
        OutputRange(i, 1) = WorksheetFunction.VLookup(SearchKey(i, 1), SearchRange, 2, False)
    Next

検索値の最終行までループしています。出力範囲のセルにVlookupメソッドで検索した結果を格納しています。

コードの実行～結果

コードの実行

実行ボタン【▶】を押します。VBAのプログラムが実行されますので処理が終わるまでお待ちください。

※実行環境によっては長くなる場合がありますが、このコード例の場合、平均で93秒ぐらいはかかります。

実行結果

検索結果のシートを見てみると、最終行まで検索結果が出力されていることを確認できます。

VLookupメソッドを使用した場合の実行時間を5回分計測してみました。

  • 1回目：90.59766秒
  • 2回目：92.71484秒
  • 3回目：90.50391秒
  • 4回目：101.7109秒
  • 5回目：92.44531秒

5回分の平均は「93.594524」秒でした。もう少し速くしたいので別の方法を試してみます。

VLOOKUP関数の数式をセルに直接入力する方法

VLookupメソッドを使用する方法では、平均93秒ほど処理にかかってしまいました。これを速くするためには複数の方法がありますが、その中からわかりやすいものを1つご紹介します。

VBAでワークシート関数を使用すると処理をするたびにすべてのシートにあるワークシート関数が再計算されてしまいます。それゆえ動作が重くなってしまいました。

VLookupメソッドを使わずにVBAで処理するためには、セルにExcelのVLOOKUP関数の数式を直接入力していく方法があります。

サンプルコード

コードの記述

下記のVBAのコードをコピー＆ペーストします。

※出力範囲に関してはご自身の状況によって変更ください。下記のコード例ではSheet3に出力範囲を指定しています。

Sub Sample()

    Dim SearchKey As Range '検索値
    Dim SearchArr As Variant '検索用格納配列
    Dim OutputRange As Range '出力範囲
    Dim i As Long
    
    Set SearchKey = Worksheets("Sheet3").Range("A2:A100001")
    Set OutputRange = Worksheets("Sheet3").Range("B2:B100001")
    
    SearchArr = Range(SearchKey, OutputRange)
    
    For i = 1 To SearchKey.Rows.Count
        SearchArr(i, 1) = "=VLOOKUP(A" & i + 1 & ",Sheet1!$A$2:$B$100001,2,0)"
    Next
    
    OutputRange = SearchArr
    
End Sub

サンプルコード解説

    Dim SearchKey As Range '検索値
    Dim SearchArr As Variant '検索用格納配列
    Dim OutputRange As Range '出力範囲
    Dim i As Long
    
    Set SearchKey = Worksheets("Sheet3").Range("A2:A100001")
    Set OutputRange = Worksheets("Sheet3").Range("B2:B100001")

検索値、出力範囲を定義し、それぞれシートのどの部分かを指定しています。ループする際、一度配列に変数を入れておくため格納用の配列を用意しました。

    SearchArr = Range(SearchKey, OutputRange)

検索用格納配列に、検索値、出力範囲を格納します。

    For i = 1 To SearchKey.Rows.Count
        SearchArr(i, 1) = "=VLOOKUP(A" & i + 1 & ",Sheet1!$A$2:$B$100001,2,0)"
    Next
    
    OutputRange = SearchArr

検索値の最終行までループしています。検索用格納配列にVLOOKUP関数の数式を格納しています。最後に出力範囲に検索用格納配列を格納しています。

コードの実行～結果

コードの実行

実行ボタン【▶】を押します。VBAのプログラムが実行されますので処理が終わるまでお待ちください。

※実行環境によっては長くなる場合がありますが、このコード例の場合、平均で3.5秒ぐらいはかかります。

実行結果

検索結果のシートを見てみると、最終行まで検索結果が出力されていることを確認できます。B列にVLOOKUP関数の数式が入っていることを確認できます。

VLOOKUP関数の数式をセルに直接入力する場合の実行時間を5回分計測してみました。

  • 1回目：3.527344秒
  • 2回目：3.601563秒
  • 3回目：3.566406秒
  • 4回目：3.546875秒
  • 5回目：3.570313秒

5回分の平均は「3.5625002」秒でした。Vlookupメソッドを使用していた場合に比べて96%処理速度が改善されました。VBAは記述の仕方によって実行速度が変わりますので遅いと感じたら処理の記述方法をもう1度確認してみましょう。