VBA高速化

【検証】ExcelマクロでVlookupを使うと遅い！辞書(Dictionary)で高速化しよう

VBA高速化
2020.05.05

せっかくExcelマクロ(VBA)が組めるのにマクロでもVlookupを使いまくってる。
こういう残念な方がたまに居ますね。

VlookupをはじめとするApplication.WorksheetFunctionで実行する関数は、Excelマクロの動作が重くなる原因です。直ちに使うのを止めましょう。

代わりに辞書((Dictionary)型を使いVlookupと同じ結果を実現します。

P.Man
P.Man

これ意外と知らない奴多いんだよな！仕方ねぇよな！事務職が無理やりExcelマクロ使うとこうなるんだよ。それも今日でおしまいだな。

ExcelマクロでVlookupを使うと遅くなる

slow

Vlookupというか、Application.WorksheetFunctionでExcelのシート関数を呼び出す度にマクロの処理速度は遅くなっていきます。まずはその実験結果をご覧ください。

100万行と1万行でVlookupを実行すると…

次のようなVlookupを処理する場合を例に考えていきます。

  1. A列～D列は商品名と利用者IDのテーブル約100万件
  2. H列～I列は利用者IDと氏名の情報が1万件
vlookup

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処理の時間を計測しています。

処理結果はこちらです。

Demo０１処理結果

10分以上かかる処理を待ってられないですよね…。

辞書(Dictionary)型を使い高速化しよう

speed

それでは、Application.WorksheetFunction.Vlookupを使わずに、辞書(Dictionary)型を使い、高速化する手順を解説しますね。

辞書(Dictionary)型で10分が1秒に改善！

まず、結論から申し上げますと処理時間は１秒になりました。ｗ

P.Man
P.Man

まぁそうだろうな。これがプログラムの力ってもんよ。

そのプログラムがこちらです。

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は重複可能です。

以下のイメージで値を所持しています。

KeyItem
U-000001山田正子
U-000002佐々木浩
U-000003池島純二
U-000004石川恵夫
Dictionary型のイメージ

先ほどのソースのこの部分で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が取得できます。

KeyItem
U-000001山田正子
U-000002佐々木浩
U-000003池島純二
U-000004石川恵夫
Dictionary型のイメージ
  • Dictionary.Item(“U-000001″)　＝　”山田正子”
  • Dictionary.Item(“U-000002″)　＝　”佐々木浩”
  • Dictionary.Item(“U-000003″)　＝　”池島純二”
  • Dictionary.Item(“U-000004″)　＝　”石川恵夫”

このような感じで、keyを指定するだけで、次々値を取得できるんです。Vlookupと同じような感じで使えますよね。

まとめ

以上がVlookupの代わりにDictionaryを活用し、Excelマクロの処理速度を上げる方法になります。Dictionaryは他の使い方もできますが、解説していると膨大な文字数になってしまうので、この記事ではここまでとさせて頂きます。

他にも「初心者のVBAが遅い原因４つを紹介！Excelのセル参照を減らすための知識」などを紹介しています。是非ご覧ください。

コメント

タイトルとURLをコピーしました