今回は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.VLookup | 20分 |
VBAのDictionary | 2秒 |
Excel2016以降限定ですが数式埋め込みとDictionaryが圧倒的に早いですね。
Excel2013以前の数式や、全バージョンの「WorksheetFunction.VLookup」はデータ量次第では問題ないですが、データ量が多かったり、処理回数が多くなるとあまりおすすめできない結果となりました。
6.サンプルデータダウンロード
今回使用したサンプルデータとSample1~4のコードが記載したデータです。
参照列とご自身の環境に合わせて頂くだけで使用できると思います。
Sample2のコメント①~④の範囲を書き換えると適用範囲が変わります。
3MBと少し重いです。
7.まとめ
今回ご紹介したVLOOKUP関数の高速化はデータ量が多くなればなるほど差が出ます。
あくまで、方法の1つですので他にももっと良い方法があると思います。
数式埋め込み方法は全ての関数が改善されたわけではない様で、Officeのバージョンでも違ってしまうため、個人的にはDictionary(連想配列)をおすすめします。
Dictionary(連想配列)を使うといろいろな処理が高速化出来ますので、VLOOKUP関数に限らず
工夫次第で他の関数も高速化出来ます。