大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、「Dictionary」の基本的な使い方から応用までを解説していきます。「Dictionary」は「辞書の検索」と「存在の確認」がかなり高速です。この特性を活かして「重複しないリストの作成」や「SumIf関数の機能」、「SumIfs関数の機能」を高速化できます。

はじめに

この記事では、「Dictionary」の基本的な使い方から応用までを、ご紹介します。

Dictionaryを使うメリットは、「辞書の検索」と「存在の確認」が高速ということです。

この特性を活かして、「重複しないリストの作成」や「SumIf関数の機能」、「SumIfsの機能」を高速化することができます。

「Dictionary」は、VBAを高速化したい場合にかなり役立ちます。

「Dictionary」の使い方をマスターしていきましょう。

では、「Dictionary」の使い方を徹底解説していきます。

この記事を読むメリット

  • 「Dictionary」の使い方がわかります
  • 「Dictionary」を使ってVBAを高速化できます

目次

Dictionaryを使う

「Dictionary」を使ってみます。

CreateObjectを使う

「Dictionary」を使うには、2つの方法があって、「CreateObject」を使う方法と、「参照設定」して使う方法です。

「CreateObject」を使った方が、ブックの共有が簡単です。

「CreateObject」を使う

「CreateObject」を使って、次のように記載します。

Sub TEST1()
    
    '辞書を設定
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
            
End Sub

これで、「Dictionary」を使うことができます。

参照設定なしで実行できる

「CreateObject」を使えば、「参照設定なし」で使うことができます。

Sub TEST2()
    
    '辞書を設定
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    MsgBox "実行しました。"
    
End Sub

「Dictionary」を使うための参照設定を外してみます。

参照設定を外しておく

では、実行してみます。

参照設定なしで実行できる

実行できました。

こんな感じで、CreateObjectを使えば、「参照設定なし」で実行できます。

ブックを共有したい場合に便利ですね。

参照設定をしてDictionaryを使う

参照設定をするメリットは、VBAコードの「候補を自動で表示」してくれる点です。

「参照設定」をしてDictionaryを使う方法について、こちらでまとめています。

キーとアイテムを登録する

「キー」と「アイテム」を登録してみます。

使うVBAコードは、「.Add」です。

キーとアイテムを登録する

キーとアイテムを登録するVBAコードは、こんな感じになります。

Sub TEST3()
    
    '辞書を作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '辞書に登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
End Sub

キーとアイテムを登録すると、こんな感じで「Dictionary」に登録されます。

キーとアイテムを登録したイメージ

「Dictionary」は、「キー」と「アイテム」をペアで登録します。

登録したキーとアイテムを出力する

登録したキーとアイテムを出力してみます。

Sub TEST4()
    
    '辞書を作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '辞書に登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    '辞書の値を出力する
    Dim B
    For Each B In A
        Debug.Print B & " " & A(B)
    Next
    
End Sub

では、VBAコードを実行してみます。

登録したキーとアイテムを出力する

登録した「キー」と「アイテム」を出力できました。

セルの値からキーとアイテムを登録

セルの値からキーとアイテムを登録してみます。

セルの値を登録したい場合は、「.Value」を付けるのがポイントです。

Sub TEST5()
    
    '辞書を作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    For i = 2 To 6
        'セルの値を辞書に登録
        A.Add Cells(i, "A").Value, Cells(i, "B").Value
    Next
    
    '辞書の値を出力
    Dim B
    For Each B In A
        Debug.Print B & " " & A(B)
    Next
    
End Sub

セルにキーとアイテムを入力しておきます。

セルにキーとアイテムを入力しておく

では、実行してみます。

セルの値からキーとアイテムを登録

セルの値からキーとアイテムを登録できています。

セルの値を登録するときの注意点

セルの値から登録する際に、「.Value」を付けないで登録すると「セル範囲」が登録されてしまいます。

なので、「Dictionary」を「値で検索することができない」です。

セルの値を登録するときの注意点について、詳細はこちらでまとめています。

既に登録されているかを確認する

「Dictionary」に登録する際は、「既に登録されているか」を確認して登録するとエラーを回避して、重複しないで登録することができます。

「既に登録されているか」の確認は「.Exists」を使います。

「.Exists」で確認して登録

「.Exists」で登録の有無を確認して登録してみます。

Sub TEST6()
    
    '辞書を作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    'セルの値をループ
    For i = 2 To 6
        '辞書に登録されていない場合
        If A.exists(Cells(i, "A").Value) = False Then
            'セルの値を辞書に登録
            A.Add Cells(i, "A").Value, Cells(i, "B").Value
        End If
    Next
    
    '辞書の値を出力
    Dim B
    For Each B In A
        Debug.Print B & " " & A(B)
    Next
    
End Sub

重複した値を用意しておきます。

重複した値を用意しておく

では、VBAコードを実行してみます。

「.Exists」で確認して登録

「.Exists」で確認して登録できました。

登録されているキーを登録するとエラー

登録されているキーを登録してしまうとエラーとなってしまいます。

登録の際にエラーとなってしまうVBAコードについて、詳細はこちらでまとめています。

要素数をカウントする

「Dictionary」に登録された「要素数」をカウントするには「.Count」を使います。

「.Count」で要素数をカウント

「.Count」で要素数をカウントしてみます。

Sub TEST7()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    '要素をカウントする
    Debug.Print A.Count
    
End Sub

では、VBAコードを実行してみます。

「.Count」で要素数をカウント

結果は、「5」個となりました。

「.Count」で要素数をカウントできています。

要素は「0」番目から始まる

「Dictionary」の要素は「0」番目から始まります。

「Dictionary」の要素について、詳細はこちらでまとめています。

値を取得する

「Dictionary」の値を取得する方法です。

値を取得する方法は、大きく3パターンあります。

  • キーで検索して取得する
  • 一括で値を取得する
  • ループして値を取得する

という感じです。

ループして値を取得する方法は、「For Each」と「For」がありますのでそれぞれ解説します。

検索して「アイテム」を取得

「キー」で検索して「アイテム」を取得してみます。

「A(キー)」で「キー」とペアの「アイテム」を取得できます。

Sub TEST8()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    '検索して取得
    Debug.Print A("C")
    
End Sub

検索して「アイテム」を取得するイメージは、こんな感じです。

検索して「アイテム」を取得するイメージ

では、VBAコードを実行してみます。

検索して「アイテム」を取得

「キー」で検索して「アイテム」を取得できました。

キーとアイテムを「一括」で取得

次は、キーとアイテムを「一括」で取得してみます。

「.Keys」と「.Items」で取得することができます。

セルに入力するときには、少し工夫が必要です。

  • 貼り付け先のセル範囲を指定
  • Transposeで行と列を入れ替える

という感じで値を貼り付けます。

Sub TEST9()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    'キーを入力
    Range("A1").Resize(A.Count) = WorksheetFunction.Transpose(A.keys)
    'アイテムを入力
    Range("B1").Resize(A.Count) = WorksheetFunction.Transpose(A.items)
    
End Sub

「.Resize」を使って、貼り付け先のセル範囲を指定します。

貼り付け先のセル範囲を指定する

「Transpose」で行と列を反転します。

Transposeで行と列を反転する

これで、「キー」と「アイテム」を一括でセルに貼り付けることができます。

キーとアイテムを「一括」で取得する

キーとアイテムを「一括」で取得できました。

「For Each」でループして取得

「For Each」でループしてキーとアイテムを取得してみます。

「キー」はFor Eachでループして取得して、「アイテム」は検索して取得します。

Sub TEST10()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    '要素数分だけループ
    For Each B In A
        '値を出力
        Debug.Print B & " " & A(B)
    Next
    
End Sub

では、実行してみます。

「For Each」でループしてキーとアイテムを取得する

「For Each」でループして「キー」と「アイテム」を取得できました。

「For」でループして取得

「For」でループしてキーとアイテムを取得してみます。

  • キーとアイテムを配列に入力
  • 配列を「.Count - 1」だけループして出力

という感じで取得します。

Sub TEST11()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    'キーとアイテムを配列に入力
    Dim B, C
    B = A.keys
    C = A.items
    
    '要素分だけループする
    For i = 0 To A.Count - 1
        '値を出力
        Debug.Print B(i) & " " & C(i)
    Next
    
End Sub

「Dictionary」の要素は「0」番目から始まるのがポイントです。

Dictionaryの要素は0番目から始まる

「For」でループしてキーとアイテムを取得してみます。

「For」でループしてキーとアイテムを取得する

「For」でループしてキーとアイテムを取得できました。

「.Keys()(i)」と「.Items()(i)」は遅い

キーとアイテムは、「.Keys()(i)」と「.Items()(i)」で取得することもできます。

ただ、この方法は遅いので、「.Keys」と「.Items」で一括で取得して、ループした方が高速です。

「.Keys()(i)」と「.Items()(i)」でループして値を取得した場合の実行速度については、こちらでまとめています。

登録データを削除する

登録データを削除してみます。

削除する方法は、2つあります。

  • 初期化
  • 一部を削除

という感じです。

では、解説していきます。

Dictionaryの初期化

「Dictionary」を初期化してみます。

「.RemoveAll」でできます。

Sub TEST12()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    '辞書をループ
    For Each B In A
        '辞書を出力
        Debug.Print B & " " & A(B)
    Next
    
    '辞書を初期化
    A.RemoveAll
    
    '辞書をループ
    For Each B In A
        '辞書を出力
        Debug.Print B & " " & A(B)
    Next
    
End Sub

初期化する前の値です。

初期化する前の値

初期化する前の値

では、実行してみます。

初期化できた

初期化できた

初期化できました。

一部のキーを削除

「Dictionary」の一部のキーを削除してみます。

「.Remove」を使います。

「キー」を入力して削除

「キー」を入力して削除する方法です。

Sub TEST13()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    '辞書をループ
    For Each B In A
        '辞書を出力
        Debug.Print B & " " & A(B)
    Next
    
    '「"C"」のキーを削除
    A.Remove "C"
    
    '辞書をループ
    For Each B In A
        '辞書を出力
        Debug.Print B & " " & A(B)
    Next
    
End Sub

削除する前の値です。

削除する前の値

では、VBAコードを実行してみます。

「キー」を入力して削除できた

「キー」を入力して要素を削除できました。

番号を指定して削除

要素の「番号を指定」して削除することもできます。

キーの値を一括で取得して、配列を使ってキーを検索する方法を使います。

要素の「番号を指定」して削除する方法について、詳細はこちらでまとめています。

登録データを変更する

「Dictionary」の登録データを変更する方法です。

「キー」と「アイテム」の変更について、解説します。

キーの値を変更する

「キー」の値を変更してみます。

キーを検索して変更

キーを検索して変更する方法でやってみます。

「A.Key(キー) = 変更後のキー」でキーの値に変更後のキーを入力することで変更できます。

Sub TEST14()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    '辞書をループ
    For Each B In A
        '辞書を出力
        Debug.Print B & " " & A(B)
    Next
    
    '辞書のキーを変更
    A.Key("C") = "CCCC"
    
    '辞書をループ
    For Each B In A
        '辞書を出力
        Debug.Print B & " " & A(B)
    Next
    
End Sub

変更する前の値です。

変更する前の値

では、実行してみます。

キーを検索してキーを変更する

キーを検索して「キーを変更」するできました。

番号を指定してキーを変更

キーの番号を指定してキーを変更することもできます。

キーを一括で取得して、配列を使って検索する方法で、キーを変更します。

キーの番号を指定してキーを変更する方法について、詳細はこちらでまとめています。

アイテムの値を変更する

「アイテム」の値を変更してみます。

キーで検索して変更

キーで検索してアイテムを変更してみます。

「A(キー) = 変更後のアイテム」というように、キーで検索して変更後のアイテムを入力するとできます。

Sub TEST15()
    
    '辞書を登録
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '値を登録する
    A.Add "A", "10"
    A.Add "B", "20"
    A.Add "C", "30"
    A.Add "D", "40"
    A.Add "E", "50"
    
    '辞書をループ
    For Each B In A
        '辞書を出力
        Debug.Print B & " " & A(B)
    Next
    
    '辞書のアイテムを変更
    A("C") = "9999"
    
    '辞書をループ
    For Each B In A
        '辞書を出力
        Debug.Print B & " " & A(B)
    Next
    
End Sub

変更する前の値です。

変更する前の値

では、VBAコードを実行してみます。

キーで検索してアイテムを変更する

キーで検索して「アイテムを変更」できました。

番号を指定して変更

「番号を指定」してアイテムを変更することもできます。

キーを一括で取得して、配列を使ってキーを検索して変更後のアイテムを入力して、アイテムを変更します。

「番号を指定」してアイテムを変更する方法について、詳細はこちらでまとめています。

Dictionaryの使いどころ

Dictionaryの使いどころは、「重複しないリストの作成」や「SumIf関数の機能」、「SumIfs関数の機能」を高速化できるというところです。

「Dictionary」は「辞書の検索」と「存在の確認」が高速なので、VBAを高速化できます。

重複しないリストを高速で作成

重複しないデータを高速で作成してみます。

まずは、簡単なデータで重複しないリストを「Dictionary」で作成してみます。

重複しないリストを作成

重複しないリストを作成するVBAコードです。

手順としては、

  • 表を配列に入力
  • 値をループして登録されていない場合に登録する
  • 「キー」をセルに入力

という感じです。

Sub TEST16()
    
    Dim A
    '辞書を作成
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B
    '値を配列に入力
    B = Range("A2:A10")
    
    '値をループ
    For i = 1 To UBound(B, 1)
        '登録されていない場合
        If A.exists(B(i, 1)) = False Then
            A.Add B(i, 1), 0 '辞書に登録する
        End If
    Next
    
    'セルに値を入力
    Range("C2").Resize(A.Count) = WorksheetFunction.Transpose(A.keys)
        
End Sub

やりたいことは、重複しないリストを作成するということです。

重複しないリストを作成したい

商品をループしていきます。

「Dictionary」に登録されていない場合は登録していきます。

商品をループしてDictionaryに登録されていない場合は登録する

すべての商品をループして、重複しないリストを作成したら、「キー」をセルに入力します。

Dictionaryのキーをセルに入力する

これで、重複しないリストを作成できます。

重複しないリストができた

「重複しないリスト」ができました。

「ForとIf」で重複しないリストを作成する場合と比較しても、シンプルなVBAコードで作成できます。

大量データで実測

では、大量データで実測してみます。

Sub TEST17()
    
    t = Timer
    
    Dim A
    '辞書を作成
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B
    '値を配列に入力
    B = Range("A2:A50001")
    
    '値をループ
    For i = 1 To UBound(B, 1)
        '登録されていない場合
        If A.exists(B(i, 1)) = False Then
            A.Add B(i, 1), 0 '辞書に登録する
        End If
    Next
    
    'セルに値を入力
    Range("C2").Resize(A.Count) = WorksheetFunction.Transpose(A.keys)
    
    Debug.Print Timer - t & " 秒"
    
End Sub

50,001行のデータを用意しました。

50,001行のデータを用意

では、VBAコードを実行してみます。

重複しないリストを作成できた

重複しないリストを作成できました。

かかった時間は、「0.125 秒」です。

「ForとIf」を使って力技で重複しないリストを作成すると「81.23438 秒」かかるので、かなり高速です。

「ForとIf」で大量データを使って重複しないリストを作成した結果については、こちらでまとめています。

「複数列」で重複しないリストも作成できる

「Dictionary」を使って、「複数列」で重複しないリストも作成することができます。

複数列を「区切り文字」で結合したものを「キー」として使う、という感じでやります。

「Dictionary」を使って、「複数列」で重複しないリストも作成する方法について、詳細はこちらでまとめています。

SumIfとSumIfsを高速化する

「Dictionary」を使って、SumIfとSumIfsを高速化することができます。

「SumIf関数の機能」を高速化してみます。

「SumIf」機能を「Dictionary」で作成

まずは、簡単な表で「SumIf関数の機能」を「Dictionary」で作成してみます。

手順は、

  • ユニークの値をDictionaryに登録
  • 合計したい値をループして「アイテム」に足していく
  • 「アイテム」の値をセルに入力

という感じです。

Sub TEST18()
    
    '辞書を作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B, C
    B = Range("A2:A4") '検索元の値を配列に入力
    C = Range("D2:E10") '検索先の値を配列に入力
    
    '検索元をループ
    For i = 1 To UBound(B, 1)
        '辞書に登録する
        A.Add B(i, 1), 0
    Next
    
    '検索先をループ
    For i = 1 To UBound(C, 1)
        '辞書に登録されている場合
        If A.exists(C(i, 1)) = True Then
            '合計値を算出
            A(C(i, 1)) = A(C(i, 1)) + C(i, 2)
        End If
    Next
    
    'セルに配列を入力
    Range("B2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
    
End Sub

やりたいことは、条件に一致する合計値を計算したい、ということです。

条件に一致する合計値を計算したい

ユニークの値を「Dictionary」に登録します。

アイテムの値は「0」としておきます。

Dictionaryに登録する

商品をループして、キーを検索して、「アイテム」に値を足していきます。

商品をループしてアイテムに値を足していく

Dictionaryの「アイテム」をセルに入力します。

Dictionaryのアイテムをセルに入力する

これで、合計値を計算できます。

合計値を計算できました

「Dictionary」で合計値を計算できました。

大量データで確認

では、大量データでやってみます。

Sub TEST19()
    
    t = Timer
    
    '辞書を作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B, C
    B = Range("A2:A10001") '検索元の値を配列に入力
    C = Range("D2:E50001") '検索先の値を配列に入力
    
    '検索元をループ
    For i = 1 To UBound(B, 1)
        '辞書に登録する
        A.Add B(i, 1), 0
    Next
    
    '検索先をループ
    For i = 1 To UBound(C, 1)
        '辞書に登録されている場合
        If A.exists(C(i, 1)) = True Then
            '合計値を算出
            A(C(i, 1)) = A(C(i, 1)) + C(i, 2)
        End If
    Next
    
    'セルに配列を入力
    Range("B2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
    
    Debug.Print Timer - t & " 秒"
    
End Sub

「10,001行×50,001行」のデータを用意しました。

10,001行×50,001行のデータを用意

では、VBAコードを実行してみます。

条件に一致する合計値を計算できました

条件に一致する合計値を計算できました。

かかった時間は、「0.19 秒」です。

「ForとIf」を使って力技でやると、「111.02 秒」かかりますので、「Dictionary」を使うとかなり高速化できます。

「ForとIf」を使って、条件に一致する合計値を算出した結果については、こちらでまとめています。

「SumIfs」機能を「Dictionary」で高速化

「SumIfs関数」のように条件が複数あっても、「Dictionary」で高速化することもできます。

複数条件を、区切り文字を使って結合して「キー」に登録して使うことで、「SumIfs関数の機能」を高速化します。

「SumIfs」機能を「Dictionary」で高速化する方法について、詳細はこちらでまとめています。

おわりに

この記事では、「Dictionary」の基本的な使い方から応用までを、ご紹介しました。

Dictionaryを使うメリットは、「辞書の検索」と「存在の確認」が高速ということです。

この特性を活かして、「重複しないリストの作成」や「SumIf関数の機能」、「SumIfsの機能」を高速化することができます。

「Dictionary」は、VBAを高速化したい場合にかなり役立ちます。

「Dictionary」の使い方をマスターしていきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す