Excel VBA VLOOKUPの高速化

高速化

今回はExcel関数のVOOKUPをVBAで高速化する方法をご説明します。

VLOOKUP関数はExcel関数の中でも、使用頻度が非常に高いです。

Excel2016から改善されて非常に早くなりました。

しかし、Excel2016でも改善されたのはExcel関数のVLOOKUP関数とMatch関数に限った改善のようです。

そこで、VBAで高速でVLOOKUPを使用(再現)する方法いくつか記載したいと思います。

今回はあえて、膨大なデータを使用して処理速度を計測してみました。

Countif、SumifやVBAの高速化は、次の記事をご覧ください。

1.VLOOKUP高速化の方法

VBAで高速にする方法は、Dictionary(連想配列)オブジェクトを使用する方法と、Excel2016以降だとセルに直接「VLOOKUP関数の数式を埋め込む」方法です。

上記の主な違いとしては、セルに算出結果を値として出力できるか、数式が埋め込まれるかの違いが発生します。

Excel2016の改善では「WorksheetFunction.VLookup」の速度改善が行われていないため、「WorksheetFunction.VLookup」を使用すると膨大な処理時間を要します。

数式が埋め込まれると、再計算が発生したりするため、数式を避けたい場合は「Dictionary 」をおすすめします。

また、誤差の範囲ですが、「Dictionary 」 の方が若干早いです。

Excel2013以前のOfficeを使用している方は「Dictionary 」の方法一択で利用して下さい。

高速化の前に簡単にですが、Dictionaryの使い方も説明します。

2.Dictionary(連想配列)の使い方

VLOOKUPの方法の前にDictionary(連想配列)の 使い方を簡単に説明します。

Dictionary(連想配列)はキー(Key)とデータ(Item)をセットで格納することが出来ます。

基本的には重複ないリストを作成するときなどに使用するオブジェクトです。

格納されているデータの指定方法は、キー(Key)を使用して指定します。

キー(Key)を指定する事でセットになっているデータ(Item)を抽出する事が出来ます。

今回の動作としては、作成したリストから検索値を基にItemを抽出します。

詳細は「Excel VBA Dictionaryの使い方」をご覧ください。

3.VBAでVLOOKUPを高速にするコード

さっそくそれぞれの方法を、サンプルデータとコードでご説明します。

以下のような10万行×10万行(並びをランダム)のデータを用意しました。

A列に10万行の商品名、D列にランダムに並び替えた商品名10万行と、E列に価格のデータです。


A列の値を検索値として、D列を検索して一致した行のE列の値を取得してB列に結果を出力します。

自分の環境(Excel2016)では単純に「WorksheetFunction.VLookup 」のループで、処理した場合は約20分かかりました。

Dictionary(連想配列) を使った高速VLOOKUP

Dictionaryを使用した場合は、「WorksheetFunction.VLookup」で20分かかった処理が約2秒で処理されます。

Sub Sample2()

        Dim SearchArray  As Variant
        Dim RefArray    As Variant
        Dim Keyval      As String
        Dim ItemVal     As Long
        Dim MaxRow      As Long
        Dim i           As Long
        Dim n           As Long
        Dim myStr       As String
        Dim myDic       As Object
            
        MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
        SearchArray = Range(Cells(2, 1), Cells(MaxRow, 2)) '①A列と出力用にB列も配列格納
        RefArray = Range(Cells(2, 4), Cells(MaxRow, 5)) '②参照データとしてD、E列を格納
        
        Set myDic = CreateObject("Scripting.Dictionary")
        
        For n = 1 To UBound(RefArray) '参照用の配列を要素数分ループ
                            
            Keyval = RefArray(n, 1) '③Keyを格納
            ItemVal = RefArray(n, 2) '④Itemを格納
            
            '登録されていなければ登録
            '※Dictionaryは重複登録出来ない
            '今回のサンプルデータは初めから重複はありません。
            If Not myDic.Exists(Keyval) Then
            
                myDic.Add Keyval, ItemVal
                
            End If
            
        Next n
        
        For n = 1 To UBound(SearchArray ) '検索用配列の要素数分ループ
        
            Keyval = SearchArray (n, 1)
            
            SearchArray (n, 2) = myDic(Keyval) '検索値のKeyでItemを抽出
        
        Next n
        
        Range(Cells(2, 1), Cells(MaxRow, 2)) = SearchArray '結果出力
        
        Set myDic = Nothing
    
    End Sub
コードの説明

検索用の配列(SearchArray )を用意してAとB列を格納します。

参照先用の配列(RefArray)を用意してDとE列を格納します。

次にDictionary(連想配列) をSETします。

「myDic.Add Keyval, ItemVal」でD列の値をKeyとし、E列の値をItemとして格納します。

格納したら検索用の配列を順番にループし、「DictionaryのKey」をもとにItemを抽出します。

抽出したItemは予め格納していた検索用の配列の2列目に追加して行きます。

最後に一括でセルに出力しています。

計測コードは記載していませんが、検証結果の図です。

セルに数式を直接入力する高速VLOOKUP

ループで直接数式をセルに入力する方法です。

こちらの方法であればExcel2016であれば、改善された速度で処理する事が可能です。

データは同じ10万行×10万行のデータを使用しています。

こちらのコードは文字列として数式を直接入力するだけの単純なコードです。

処理結果は約5秒です。

Sub Sample3()

        Dim SearchArray As Variant
        Dim MaxRow      As Long
        Dim i           As Long
        Dim myStr       As String
        
        MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
        SearchArray = Range(Cells(2, 1), Cells(MaxRow, 2)) 'A列と出力用にB列も配列格納
        
        For i = 1 To UBound(SearchArray ) 'A列要素数分ループ
        
            myStr = SearchArray (i, 1) '配列の値を格納
        
            SearchArray (i, 2) = "=VLOOKUP(A" & i + 1 & ",$D$2:$E$100001,2,0)"
        Next i
        
        Range(Cells(2, 1), Cells(MaxRow, 2)) = SearchArray '結果出力
        
    End Sub

4.Application.WorksheetFunction.VLookupで処理

一応「Application.WorksheetFunction.VLookup 」を使用した場合のコードもサンプルとして掲載します。

処理時間はExcel2010でも2016でも約20分でした。

Sub Sample4()

        Dim SearchArray As Variant
        Dim MaxRow      As Long
        Dim i           As Long
        Dim myStr       As String
        
        
        MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
        SearchArray = Range(Cells(2, 1), Cells(MaxRow, 2)) 'A列と出力用にB列も配列格納
        
        For i = 1 To UBound(SearchArray ) 'A列要素数分ループ
        
            myStr = SearchArray (i, 1) '配列の値を格納
        
            SearchArray (i, 2) = _
            Application.WorksheetFunction.VLookup(myStr, Range(Cells(2, 4), Cells(MaxRow, 5)), 2, False)
        
        Next i
        
        Range(Cells(2, 1), Cells(MaxRow, 2)) = SearchArray '結果出力
        
    End Sub

5.処理結果

Excelのバージョンと処理方法処理速度
Excel2013以前の数式埋め込み10分
Excel2016以降の数式埋め込み5秒
VBAのWorksheetFunction.VLookup20分
VBAのDictionary2秒

Excel2016以降限定ですが数式埋め込みとDictionaryが圧倒的に早いですね。

Excel2013以前の数式や、全バージョンの「WorksheetFunction.VLookup」はデータ量次第では問題ないですが、データ量が多かったり、処理回数が多くなるとあまりおすすめできない結果となりました。

6.サンプルデータダウンロード

今回使用したサンプルデータとSample１~4のコードが記載したデータです。

参照列とご自身の環境に合わせて頂くだけで使用できると思います。

Sample２のコメント①～④の範囲を書き換えると適用範囲が変わります。

3MBと少し重いです。

SampleFile1ダウンロード

7.まとめ

今回ご紹介したVLOOKUP関数の高速化はデータ量が多くなればなるほど差が出ます。

あくまで、方法の1つですので他にももっと良い方法があると思います。

数式埋め込み方法は全ての関数が改善されたわけではない様で、Officeのバージョンでも違ってしまうため、個人的にはDictionary(連想配列)をおすすめします。

Dictionary(連想配列)を使うといろいろな処理が高速化出来ますので、VLOOKUP関数に限らず

工夫次第で他の関数も高速化出来ます。

スポンサーリンク
タイトルとURLをコピーしました