大体でIT

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

HOME >  Excel VBA >  データの操作
大体で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を使う方法について、こちらでまとめています。

>>参照設定して「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コードについて、詳細はこちらでまとめています。

>>登録の際にエラーとなってしまう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」の要素について、詳細を確認する

値を取得する

「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)」でループして値を取得した場合の実行速度については、こちらでまとめています。

>>「.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」の要素の「番号を指定」して削除する方法について、詳細を確認する

登録データを変更する

「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コードで作成できます。

>>「ForとIf」で重複しないリストを作成する方法について、確認する

大量データで実測

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

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」で大量データを使って重複しないリストを作成した結果については、こちらでまとめています。

>>「ForとIf」で大量データを使って重複しないリストを作成した結果について、詳細を確認する

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

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

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

「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」を使って、条件に一致する合計値を算出した結果については、こちらでまとめています。

>>「ForとIf」を使って、条件に一致する合計値を算出した結果について、詳細を確認する

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

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

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

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

>>「SumIfs」機能を「Dictionary」で高速化する方法について、詳細を確認する

おわりに

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

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

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

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

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

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す