行数が多いExcelシートを操作するのにVBAで関数を使うと遅い
VlookUp関数をVBAで使うにあたって、対象となるデータが少ない場合は特に問題ないのですが、多くなるにつれてデータ処理に時間がかかります。
私は仕事で20万行のデータに10万行のマスタデータからコードを抽出する必要があり、VBAでWorkSteetFunctionのVlookUp関数を使用すると数時間単位で時間がかかってしまうことがありました。
行数の多いExcelについてはVBAの処理速度が遅くなってしまうことが多くの人にとって悩みになると思います。
私の事例では連想配列（Dictionaryオブジェクト）を使用してコードを書きなおすことで処理速度が格段に速くなりましたので、連想配列（Dictionary）オブジェクトの使い方について何回かに分けてご紹介します。
今回はVlookup関数の代用としてDictionaryオブジェクトを使用する方法についてご紹介します。
Dictionaryオブジェクトの使用するための事前準備
事前準備についてはこちらのページに記載しておりますので詳しく知りたい方はリンク先をご確認ください。
また、セルの値を取得するのにそのまま取得するのではなく、配列に一度格納する方法を使用しております。
これはVBAの高速化の手順で最も有効な手段の1つです。
詳細はこちらのページに記載しておりますので併せてご確認いただけるとありがたいです。
スポンサーリンク
今回処理を行うワークシートについて
私が今回業務で行ったものの一部になるのですが、商品の仕入伝票一覧に商品マスタの「YJコード」というコードを結合するものです。
（データの内容は変更していますので、世の中に存在しない商品などが記載されています。）
商品の仕入一覧が約20万行、商品マスタが約10万行あり、VlookUpを使用するだけでは時間がかかっていました。
サンプルの伝票一覧と商品マスタは下記のようなイメージです。
VlookUp関数を使用した値の記載方法について
Dictionaryオブジェクトを使用したVBAコードがどれくらい高速になるかを検証するために、通常のVlookUp関数をWorkSheetFunctionで呼び出した場合と比較することにします。
配列に格納してからセルの値を変更するものも用意しています。
コードは「伝票一覧」の最後の列に「商品マスタ」に記載されている「YJコード」を追記するものになります。
Debug.Print “開始” & “_” & Time ’時間の測定
Dim l
Dim マスタ下端行
マスタ下端行 = Sheets(“商品マスタ”).Cells(Rows.Count, 1).End(xlUp).Row
Dim 伝票下端行
伝票下端行 = Sheets(“伝票”).Cells(Rows.Count, 1).End(xlUp).Row
Dim i
Sheets(“伝票”).Cells(1, 9) = “YJコード”
On Error Resume Next ’VlookUpで引きあたるものがないとき無視する
For i = 2 To 伝票下端行
Sheets(“伝票”).Cells(i, 9) = WorksheetFunction.VLookup(Sheets(“伝票”).Cells(i, 4), Range(Sheets(“商品マスタ”).Cells(1, 1), Sheets(“商品マスタ”).Cells(マスタ下端行, 4)), 2, False)
Next i
On Error GoTo 0
Debug.Print “終了” & “_” & Time ’時間の測定
End Sub
こちらはセルに直接値を書き込むのではなく、配列に格納して変更を加えてから一括でセルに書き込むフローになります。
一般的な処理ではステップは増えますが処理速度が高速になります。
’セルに直接書き込むのではなく配列に格納して変更した後、一括で書き込む
Debug.Print “開始” & “_” & Time ’時間の測定
Dim l
Dim マスタ下端行
マスタ下端行 = Sheets(“商品マスタ”).Cells(Rows.Count, 1).End(xlUp).Row
Dim 参照範囲
参照範囲 = Range(Sheets(“商品マスタ”).Cells(1, 1), Sheets(“商品マスタ”).Cells(マスタ下端行, 4))
Dim 伝票下端行
伝票下端行 = Sheets(“伝票”).Cells(Rows.Count, 1).End(xlUp).Row
’セルに直接書き込むよりスピードアップのために配列に格納する
Dim 伝票範囲
伝票範囲 = Range(Sheets(“伝票”).Cells(1, 1), Sheets(“伝票”).Cells(伝票下端行, 9))
Dim i
伝票範囲(1, 9) = “YJコード”
On Error Resume Next ’VlookUpで引きあたるものがないとき無視する
For i = 2 To 伝票下端行
伝票範囲(i, 9) = WorksheetFunction.VLookup(伝票範囲(i, 4), 参照範囲, 2, False)
Next i
On Error GoTo 0
Range(Sheets(“伝票”).Cells(1, 1), Sheets(“伝票”).Cells(伝票下端行, 9)) = 伝票範囲
Debug.Print “終了” & “_” & Time ’時間の測定
End Sub
Dictionaryオブジェクトを使用したVBAの使用方法とサンプルコードについて
キーは商品固有の値「JANコード」としています。
キーに持ってくるのは重複のないものなら何でも大丈夫ですが、半角・全角・ひらがな・カタカナが全部揃えないといけないので日本語の名称などは使いにくいと思います。
こちらもそのままセルの値を取得するか、もしくはセルを配列に格納してから参照、書き込みをするかの2パターン用意しています。
’配列を用いない
Debug.Print “開始” & “_” & Time ’時間の測定
’Dictionaryの利用準備
’’参照設定をする場合
’Dim dictJANYJ As Dictionary
’Set dictJANYJ = New Dictionary
’参照設定をしない場合
Dim dictJANYJ As Object
Set dictJANYJ = CreateObject(“Scripting.Dictionary”)
Dim l
Dim マスタ下端行
マスタ下端行 = Sheets(“商品マスタ”).Cells(Rows.Count, 1).End(xlUp).Row
’JANをKey、YJをItemに格納
’セルの値を直接Dictionaryに格納する場合、Textプロパティを指定しないとうまくいかない
For l = 2 To マスタ下端行
If dictJANYJ.Exists(Sheets(“商品マスタ”).Cells(l, 1).Text) = False Then ’キーは重複できないので、存在有無を確認してから追加
dictJANYJ.Add Key:=Sheets(“商品マスタ”).Cells(l, 1).Text, Item:=Sheets(“商品マスタ”).Cells(l, 2).Text
End If
Next l
’dictJANYJに格納した値を参照してセルに記載
Dim 伝票下端行
伝票下端行 = Sheets(“伝票”).Cells(Rows.Count, 1).End(xlUp).Row
Dim i
Sheets(“伝票”).Cells(1, 9) = “YJコード”
For i = 2 To 伝票下端行
Sheets(“伝票”).Cells(i, 9) = dictJANYJ.Item(Sheets(“伝票”).Cells(i, 4).Text)
Next i
Debug.Print “終了” & “_” & Time ’時間の測定
End Sub
Debug.Print “開始” & “_” & Time ’時間の測定
’Dictionaryの利用準備
’’参照設定をする場合
’Dim dictJANYJ As Dictionary
’Set dictJANYJ = New Dictionary
’参照設定をしない場合
Dim dictJANYJ As Object
Set dictJANYJ = CreateObject(“Scripting.Dictionary”)
Dim l
Dim マスタ下端行
マスタ下端行 = Sheets(“商品マスタ”).Cells(Rows.Count, 1).End(xlUp).Row
Dim 参照範囲
参照範囲 = Range(Sheets(“商品マスタ”).Cells(1, 1), Sheets(“商品マスタ”).Cells(マスタ下端行, 4))
’JANをKey、YJをItemに格納
For l = 2 To マスタ下端行
If dictJANYJ.Exists(参照範囲(l, 1)) = False Then ’キーは重複できないので、存在有無を確認してから追加
dictJANYJ.Add Key:=参照範囲(l, 1), Item:=参照範囲(l, 2)
End If
Next l
’dictJANYJに格納した値を参照してセルに記載
Dim 伝票下端行
伝票下端行 = Sheets(“伝票”).Cells(Rows.Count, 1).End(xlUp).Row
’セルに直接書き込むよりスピードアップのために配列に格納する
Dim 伝票範囲
伝票範囲 = Range(Sheets(“伝票”).Cells(1, 1), Sheets(“伝票”).Cells(伝票下端行, 9))
Dim i
伝票範囲(1, 9) = “YJコード”
For i = 2 To 伝票下端行
伝票範囲(i, 9) = dictJANYJ.Item(伝票範囲(i, 4))
Next i
Range(Sheets(“伝票”).Cells(1, 1), Sheets(“伝票”).Cells(伝票下端行, 9)) = 伝票範囲
Debug.Print “終了” & “_” & Time ’時間の測定
End Sub
処理速度の差について集計
①WorkSheetFunctionでVlookUp関数を呼び出して使用する方法、②今回の連想配列（Dictionaryオブジェクト）を使用する方法を比較してみました。
それぞれの方法において、VBA高速化で有名なのセル値を配列に格納して抽出・書き込む方法も試しています。
商品マスタシート・伝票シートいずれも行数は100,000行にして測定した速度結果は下記の通りです。
| WorkSheetFunctionの VlookUp |
Dictionary オブジェクト |
|
|---|---|---|
| セルの値を配列へ 格納しない |
0:11:13 | 0:00:32 |
| セルの値を配列へ 格納する |
2:05:08 | 0:00:04 |
WorkSheetFunctionでVlookUp関数を用いた場合、配列をセルに格納することで逆に速度が著しく低下してしまったのは驚きでした。
原因としては私が実験した環境において、「セルの値を配列に格納することでメモリが上限に達してしまったこと」が推測されますが、詳細は不明です。
セルを配列に格納した際の速度アップはセルの参照・セルへの値の書き込みの部分が高速化されることによるのですが、WrorkSheetFunctionのVlookUp関数はそのものの処理が重く、セルの値の参照やセルへの書き込みが律速ではないようです。
そしていずれにしても①セルの値を配列に格納して②Dictionaryを使用するという両方のステップを行えば最も高速に処理が可能になることがわかりましたので、今後はこの方法を使っていこうと思います。
11分かかる処理が4秒になるのはものすごい改善だと思います。
今回はVlookUpの代わりになるDictionaryオブジェクトを使用する方法をご紹介しました。
最初のKeyとItemの値を格納する方法を変更すればSumIfsなどの代わりとしても使えるようになりますのでその方法については別の記事で紹介します。
コメント