【VBA】Dictionaryの使い方まとめ【辞書の検索と存在の確認が高速です】
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」で確認して登録できました。
登録されているキーを登録するとエラー
登録されているキーを登録してしまうとエラーとなってしまいます。
登録の際にエラーとなってしまう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コードを実行してみます。
結果は、「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」で行と列を反転します。
これで、「キー」と「アイテム」を一括でセルに貼り付けることができます。
キーとアイテムを「一括」で取得できました。
「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」でループして取得
「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」番目から始まるのがポイントです。
「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」に登録されていない場合は登録していきます。
すべての商品をループして、重複しないリストを作成したら、「キー」をセルに入力します。
これで、重複しないリストを作成できます。
「重複しないリスト」ができました。
「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行のデータを用意しました。
では、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」で合計値を計算できました。
大量データで確認
では、大量データでやってみます。
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行」のデータを用意しました。
では、VBAコードを実行してみます。
条件に一致する合計値を計算できました。
かかった時間は、「0.19 秒」です。
「ForとIf」を使って力技でやると、「111.02 秒」かかりますので、「Dictionary」を使うとかなり高速化できます。
「ForとIf」を使って、条件に一致する合計値を算出した結果については、こちらでまとめています。
「SumIfs」機能を「Dictionary」で高速化
「SumIfs関数」のように条件が複数あっても、「Dictionary」で高速化することもできます。
複数条件を、区切り文字を使って結合して「キー」に登録して使うことで、「SumIfs関数の機能」を高速化します。
「SumIfs」機能を「Dictionary」で高速化する方法について、詳細はこちらでまとめています。
おわりに
この記事では、「Dictionary」の基本的な使い方から応用までを、ご紹介しました。
Dictionaryを使うメリットは、「辞書の検索」と「存在の確認」が高速ということです。
この特性を活かして、「重複しないリストの作成」や「SumIf関数の機能」、「SumIfsの機能」を高速化することができます。
「Dictionary」は、VBAを高速化したい場合にかなり役立ちます。
「Dictionary」の使い方をマスターしていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す