VBAのVLOOKUP関数での検索を高速化する方法

2023 9/06

2023-09-06

ExcelのVBAで大量のデータをVLOOKUP関数で取得する際に高速で検索する方法について解説します。
一番簡単に実装できて尚かつ高速なのは、セルに直接VLOOKUP関数の数式を埋め込む方法です。ここではその方法と、WorksheetFunctionを使用して計算を行う方法、Dictionaryを使用して計算を行う方法についてそれぞれ解説しますが、結論として、早くて簡単なのはVLOOKUP関数の数式を埋め込む方法。複雑だけど一番速いのはDictionaryを使用して計算を行う方法となります。

関連リンク

【エクセル】VLOOKUPをVBAで使用する方法の完全ガイド

おすすめの方法：VLOOKUP数式の埋め込み（処理時間：2.9秒）

下記のように「入力」タブの10,000個の商品IDに対して、「マスタデータ」タブ内の100,000行のデータを参照しVLOOKUPで在庫数を取得するコード例を紹介します。この例では一番簡単かつ高速で処理ができる、VLOOKUP数式の埋め込みによる方法について解説します。

この方法の特徴

少ないコードで簡単に実行できる
かなり早い
エラー処理を入れる必要がない

サンプルコード

セルに直接VLOOKUP関数の数式を代入して計算するサンプルコードです。処理にかかった秒数としては2.9秒で完了しています。エラー処理などの必要がないのでかなり楽です。

Sub vlookupSpeed()

    '処理速度計測用
    Dim startTime
    startTime = Timer
    
    'VLOOKUP関数の数式をセルに一括入力
    Range("B2:B10000") = "=VLOOKUP(A2,マスタデータ!$A$2:$C$100001,3,FALSE)"
    
    '数式を値に変換
    Range("B2:B10000").Value = Range("B2:B10000").Value

    'かかった時間を表示
    MsgBox Timer - startTime & " sec"

End Sub

コード解説

処理速度計測用の変数を定義し、この時点での時間を代入します。

Dim startTime
startTime = Timer

情報を取得したいセル範囲である「Range(“B2:B2000”)」に「=VLOOKUP(A2,マスタデータ!$A$2:$C$20001,3,FALSE)」と直接数式を代入します。この際に第2引数のデータ範囲は絶対参照にしていることに注意してください。第1引数の値は相対参照として一番上のセルを指定することで、A2、A3、A4・・・というようにひとつずつずれて計算します。

Range("B2:B2000") = "=VLOOKUP(A2,マスタデータ!$A$2:$C$20001,3,FALSE)"

下記のように値で代入し直すことで数式が入力されているセルを値に変換します。

Range("B2:B100").Value = Range("B2:B100").Value

この時点での時間からスタート時点での時間を引いて、かかった秒数をメッセージボックスで表示します。

MsgBox Timer - startTime & " sec"

WorksheetFunctionを使用してVLOOKUP（処理時間：60秒）

WorksheetFunctionでVLOOKUPを行う方法について解説します。解説する3つの方法の中ではこの方法が一番時間がかかってしまいます。

WorksheetFunctionを使用してVLOOKUPを実行し、在庫を取得している様子

この方法の特徴

かなり時間がかかる
エラー処理によって、エラー時の表示をコントロールできる
WorksheetFunctionを使用している

サンプルコード

WorksheetFunctionのVLOOKUPでデータを取得するサンプルコードです。処理にかかった時間は60秒と時間はかなりかかってしまいます。

Sub vlookupSpeed2()
    
    '処理速度計測用
    Dim startTime
    startTime = Timer
    
    '検索値と対応値を入力したい範囲を定義
    Dim A
    A = Range("A2:B10000")
    
    '以降のエラー回避
    On Error Resume Next

    'Aの行数分繰り返し
    Dim i
    For i = 1 To UBound(A, 1)

        'WorksheetFunctionでVLookup関数を使って値を取得
        A(i, 2) = WorksheetFunction.vlookup(A(i, 1), Worksheets("マスタデータ").Range("A2:C100001"), 3, False)

        If Err.Number <> 0 Then  'エラーナンバーが0以外の場合
            A(i, 2) = "該当なし"  '結果のセルに「該当なし」と表示
        End If
    Next

    'エラー回避を解除
    On Error GoTo 0
　　　　　　　
    '結果をセルに入力
    Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
    
    'かかった時間を表示
    MsgBox Timer - startTime & " sec"
    
End Sub

コード解説

処理速度計測用の変数を定義し、この時点での時間を代入します。

 Dim startTime
 startTime = Timer

検索値の列と対応値を出力するための列の範囲を変数Aに配列として代入します。このようにいったん配列として定義することで処理スピードが格段に上がります。

 Dim A
 A = Range("A2:B10000")

VLOOKUPで検索値が見つからない場合のエラー対策として、「On Error Resume Next」を定義し、以降のエラーを無視するようにします。

 On Error Resume Next

Aの行数を「UBound(A, 1)」で取得し、Aのデータの数だけ繰り返すFOR文を書きます。

 Dim i
 For i = 1 To UBound(A, 1)

FOR分の中にVLOOKUPを実行して対応値をデータの分だけ取得していきます。

A(i, 2) = WorksheetFunction.vlookup(A(i, 1), Worksheets("マスタデータ").Range("A2:C100001"), 3, False)

検索値が見つからない場合は「該当なし」を代入するように設定します。

If Err.Number <> 0 Then
    A(i, 2) = "該当なし"
End If

エラー回避の解除を行います。

On Error GoTo 0

結果をセルに入力します。

Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A

この時点での時間からスタート時点での時間を引いて、かかった秒数をメッセージボックスで表示します。

MsgBox Timer - startTime & " sec"

Dictionaryを使用して対応値取得（処理時間：0.9秒）

ここで紹介する方法の中では最速の方法としてDictionaryを使用して対応値を取得する方法について解説します。データの量が膨大な場合はこちらの方法をおすすめします。

この方法の特徴

処理速度は最速
辞書として宣言した変数にデータ情報を入れ直す処理をしている
コードが少し複雑な作りをしている

サンプルコード

Dictionaryを使用して対応値取得するサンプルコードです。処理にかかった時間は0.9秒と処理の速さとしては最速なので、膨大な量のデータを処理する場合はこちらの方法がおすすめです。

Sub vlookupSpeed3()

    '処理速度計測用
    Dim startTime
    startTime = Timer
    
    'Aを辞書として定義
    Dim A
    Set A = CreateObject("Scripting.Dictionary")

　　　　　　　'Bにマスタデータの内容を格納
    Dim B
    B = Worksheets("マスタデータ").Range("A2:C100001")
    
    'Bの情報をAに代入
    Dim i
    For i = 1 To UBound(B, 1)
        A.Add B(i, 1), B(i, 3)
    Next

    '検索値範囲を定義
    Dim C
    C = Range("A2:B10000")
    
    'Cに対応値を代入
    For i = 1 To UBound(C, 1)
        C(i, 2) = A(C(i, 1))
    Next
    
    '結果をセルに入力
    Range("A2").Resize(UBound(C, 1), UBound(C, 2)) = C

    'かかった時間を表示
    MsgBox Timer - startTime & " sec"
    
End Sub

コード解説

処理速度計測用の変数を定義し、この時点での時間を代入します。

Dim startTime
startTime = Timer

変数Aを辞書として定義します。

Dim A
Set A = CreateObject("Scripting.Dictionary")

辞書へのデータ登録用に変数Bに参照データのセル範囲を代入します。

Dim B
B = Worksheets("マスタデータ").Range("A2:C100001")

辞書へ参照データを登録します。

Dim i
For i = 1 To UBound(B, 1)
    A.Add B(i, 1), B(i, 3)
Next

変数Cに検索値の列と出力する列の範囲を代入します。

Dim C
C = Range("A2:B10000")

検索値に対する対応値を辞書を参照して格納していきます。

For i = 1 To UBound(C, 1)
    C(i, 2) = A(C(i, 1))
Next

結果をセルに入力します。

Range("A2").Resize(UBound(C, 1), UBound(C, 2)) = C

この時点での時間からスタート時点での時間を引いて、かかった秒数をメッセージボックスで表示します。

MsgBox Timer - startTime & " sec"

ショートカットキーの練習

当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトをブックマークに登録し定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。

下のスタートボタンを押して練習モードに入ります。

STARTSTOP

※入力モード切り替え ctrl + shift + alt + space

PRE

現在の問題をリストから除外

除外したリストをリセット

現在の除外状況のURL取得

コピー

ガイドモード

リピートモード

※推奨ブラウザ Google Chrome

もっとショートカットキーの練習をする