せっかくExcelマクロ(VBA)が組めるのにマクロでもVlookupを使いまくってる。
こういう残念な方がたまに居ますね。
VlookupをはじめとするApplication.WorksheetFunctionで実行する関数は、Excelマクロの動作が重くなる原因です。直ちに使うのを止めましょう。
代わりに辞書((Dictionary)型を使いVlookupと同じ結果を実現します。
これ意外と知らない奴多いんだよな!仕方ねぇよな!事務職が無理やりExcelマクロ使うとこうなるんだよ。それも今日でおしまいだな。
ExcelマクロでVlookupを使うと遅くなる
Vlookupというか、Application.WorksheetFunctionでExcelのシート関数を呼び出す度にマクロの処理速度は遅くなっていきます。まずはその実験結果をご覧ください。
100万行と1万行でVlookupを実行すると…
次のようなVlookupを処理する場合を例に考えていきます。
- A列~D列は商品名と利用者IDのテーブル約100万件
- H列~I列は利用者IDと氏名の情報が1万件
D列の「使用者氏名」を埋めるため、Vlookupを使う事にします。
こういう処理って存在しますよね?
このVlookupの処理を単純にマクロ化すると…743秒かかりました。
そのソースコードは以下。
Sub demo01() Dim testSheet As Worksheet Set testSheet = ThisWorkbook.Worksheets(1) Dim startTime As Double Dim endTime As Double Dim i As Long 'for文用 Dim mxRow As Long '最大行 Dim buff As Variant '▼画面更新を無効化 Application.ScreenUpdating = False With testSheet startTime = Timer '開始時刻 '▼Noの最終行を取得 mxRow = .Cells(1, 1).End(xlDown).Row '▼セル参照回数を減らす為に配列へ格納 buff = .Range(.Cells(1, 1), .Cells(mxRow, 4)) '繰返し処理開始 For i = 2 To mxRow '★Vlookupを使う buff(i, 4) = _ Application.WorksheetFunction.VLookup(buff(i, 3), .Range("H1:I10001"), 2, False) Next endTime = Timer '修了時刻 '▼配列からまとめて入力 .Range(.Cells(1, 1), .Cells(mxRow, 4)) = buff End With '▼画面更新を有効化 Application.ScreenUpdating = True Debug.Print "Demo-01処理速度:" & amp; endTime - startTime End Sub
画面更新の無効化、セル参照回数を減らすなどの、基本的な高速化テクニックは全て使った状態で、(★)のVlookup処理の時間を計測しています。
処理結果はこちらです。
10分以上かかる処理を待ってられないですよね…。
辞書(Dictionary)型を使い高速化しよう
それでは、Application.WorksheetFunction.Vlookupを使わずに、辞書(Dictionary)型を使い、高速化する手順を解説しますね。
辞書(Dictionary)型で10分が1秒に改善!
まず、結論から申し上げますと処理時間は1秒になりました。w
まぁそうだろうな。これがプログラムの力ってもんよ。
そのプログラムがこちらです。
Sub demo02() Dim testSheet As Worksheet Set testSheet = ThisWorkbook.Worksheets(1) Dim startTime As Double Dim endTime As Double Dim i As Long 'for文用 Dim mxRow As Long '最大行 Dim buff As Variant '★辞書の宣言 Dim dictUser As Dictionary Set dictUser = New Dictionary '▼画面更新を無効化 Application.ScreenUpdating = False With testSheet startTime = Timer '開始時刻 '★辞書の準備 For i = 2 To 10001 If dictUser.Exists(.Cells(i, 8).Value) = False Then dictUser.Add Key:=.Cells(i, 8).Value, Item:=.Cells(i, 9).Value End If Next '▼Noの最終行を取得 mxRow = .Cells(1, 1).End(xlDown).Row '▼セル参照回数を減らす為に配列へ格納 buff = .Range(.Cells(1, 1), .Cells(mxRow, 4)) '繰返し処理開始 For i = 2 To mxRow '★Dictionaryを使う buff(i, 4) = dictUser.Item(CStr(buff(i, 3))) Next endTime = Timer '修了時刻 '▼配列からまとめて入力 .Range(.Cells(1, 1), .Cells(mxRow, 4)) = buff End With Set dictUser = Nothing '▼画面更新を有効化 Application.ScreenUpdating = True Debug.Print "Demo-02処理速度:" & amp; endTime - startTime End Sub
違いは(★)の部分です。それ以外はほぼ同じですね。
そして処理結果がこちら。
笑いが出ますよね。
辞書(Dictionary)型の使い方
それでは、辞書(Dictionary)型について解説していきます。
Dictionary型の宣言について
まず、Dictionary型の宣言いついて解説していきます。Dictionary型はデフォルトでは使えない状態のため、「参照設定」という設定が必要になります。
とても簡単なので以下画像を参考にやってみてください。
- マクロのエディターの「ツール」から「参照設定」を選択
- 参照可能なライブラリファイルの中から
- Microsoft Scripting Runtime
- にチェックを入れて「OK」を選択
これでDictionary型の変数を宣言できるようになりました。
次のように宣言をしましょう。
'★辞書の宣言 Dim dictUser As Dictionary Set dictUser = New Dictionary
これでDictionary型のdictUserという変数が誕生しました。
Dictionary型の変数に値を設定する
次に、Dictionary型の変数に値を設定する方法を解説します。
まずは、Dictionary型の構成を理解しましょう。
Dictionary型はKey(キー)とItem(アイテム)によって構成されています。Keyが検索に使う主キーで、呼び出される値がItemと覚えると分かりやすいです。Keyは重複する事ができませんが、Itemは重複可能です。
以下のイメージで値を所持しています。
Key | Item |
U-000001 | 山田正子 |
U-000002 | 佐々木浩 |
U-000003 | 池島純二 |
U-000004 | 石川恵夫 |
先ほどのソースのこの部分でDictionaryのKeyとItemに値を設定しています。
'★辞書の準備 For i = 2 To 10001 If dictUser.Exists(.Cells(i, 8).Value) = False Then dictUser.Add Key:=.Cells(i, 8).Value, Item:=.Cells(i, 9).Value End If Next
「dictUser.Exists」は既に同じKeyが利用されていない事を確認しています。
Dictionary型は存在するKeyをAddするとエラーで処理が止まりますので、
必ずExistsで存在確認を行いましょう。
Dictionary型の変数から値を取得する
お待ちかねの値取得方法です。これはとっても簡単。
次のようにkeyを指定して対応するItemを取得します。
'★Dictionaryを使う buff(i, 4) = dictUser.Item(CStr(buff(i, 3)))
Dictionary.Item([Key])でkeyに対応するItemが取得できます。
Key | Item |
U-000001 | 山田正子 |
U-000002 | 佐々木浩 |
U-000003 | 池島純二 |
U-000004 | 石川恵夫 |
- Dictionary.Item(“U-000001″) = ”山田正子”
- Dictionary.Item(“U-000002″) = ”佐々木浩”
- Dictionary.Item(“U-000003″) = ”池島純二”
- Dictionary.Item(“U-000004″) = ”石川恵夫”
このような感じで、keyを指定するだけで、次々値を取得できるんです。Vlookupと同じような感じで使えますよね。
まとめ
以上がVlookupの代わりにDictionaryを活用し、Excelマクロの処理速度を上げる方法になります。Dictionaryは他の使い方もできますが、解説していると膨大な文字数になってしまうので、この記事ではここまでとさせて頂きます。
他にも「初心者のVBAが遅い原因4つを紹介!Excelのセル参照を減らすための知識」などを紹介しています。是非ご覧ください。
コメント