VBA技術解説
Dictionary(ディクショナリー)のパフォーマンスについて

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2020-02-07

Dictionary(ディクショナリー)のパフォーマンスについて


Dictionary(ディクショナリー)は辞書機能で、連想配列とも呼ばれます。
この辞書は、重複は許されず、キーとデータの2つが存在します、
今回はこのDictionaryのパフォーマンス(処理速度)を検証します。


Dictionaryの基本的な使い方については、こちらを参照してください。
Dictionary(ディクショナリー)連想配列の使い方について
「Dictionaryオブジェクトについて簡単な使用例を上げて解説して欲しいです。」との要望をいただいたので、Dictionaryについて基本的な使い方を解説します。Dictionary(ディクショナリー)は名前の通り、辞書機能であり、連想配列とも呼ばれます。


Dictionaryを使う事で、大量データをユニーク化する時は高速な処理を実現できます。


本記事は、Excel2013当時の記事をOffice365(2020/2月時点)で再検証して書き直したものです。

Dictionaryの検証に使うシート

VBA マクロ Dictionary

A2~A10001まで、"A1"~"A10000"
B2~B10001まで、1~10000

これが、10002行目からもう一回繰り返されています。

20000件のデータをユニーク化して10000件のデータにします。
D列にユニーク化したキーを、E列にデータの合計を出力します。

ユニーク化(重複削除)の方法について

・配列を使って集計
・AdvancedFilterでキーをユニークにしてからSUMIF
・ピボットを使って集計
ピボット以外は、相当の時間がかかってしまいそうです。
ピボットは単純な集計でしか使いづらく、複雑な集計には対応しづらくなる場合が出てきます。
※ピボットの集計は早いです。
それでも、後述の方法の方が、このデータ量なら若干はやいです。
ピボットを含めた、速度検証は以下に掲載しました。
大量データ処理における高速化・速度対策の検証
VBAで自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。

そこで、Dictionary(ディクショナリー)を使ってみようという事です。
※大量データ集計の高速化について
大量データで処理時間がかかる関数の対処方法(WorksheetFunction)
大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、そういう場合でも、多くの場合は何らかの対策があるものです、個別のロジックの記述でこれらに対応する方法として、一つの有効なマクロVBAコ-ドについて解説します。以下の例で解説します。
こちらを参考にしてください。
集計方法によって適用可能かどうか変わりますが、適用可能なら、ここでの方法が一番よいです。

以下では、VBAサンプルコードと、その処理時間について検証します。
計測は、Corei7、メモリ16G、Office365でのものです。
処理時間については、その時のPCの状態にも依存しますので、目安としてとらえてください。
まずは、良くありそうなVBAを書いてみます。

Dictionaryでユニーク化1

Sub sample1()
  Dim myDic As New Dictionary
  Dim i As Long
  Application.ScreenUpdating = False
  Range("D1").CurrentRegion.Offset(1).ClearContents
  Debug.Print Timer
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If myDic.Exists(Cells(i, 1).Value) Then
      myDic(Cells(i, 1).Value) = myDic(Cells(i, 1).Value) + Cells(i, 2).Value
    Else
      myDic.Add Cells(i, 1).Value, Cells(i, 2).Value
    End If
  Next
  Debug.Print Timer
  For i = 0 To myDic.Count - 1
    Cells(i + 2, 4) = myDic.Keys(i)
    Cells(i + 2, 5) = myDic.Items(i)
  Next
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub

ごく普通のVBAコードではないでしょうか。
表の上から順に処理していき、
Dictionaryに登録されていれば加算、Dictionaryに登録されていなければ追加する。
これで、
約12秒
どうでしょうか、早いと言えば早いでしょうが・・・
先に書いた、
配列やAdvancedFilter+SUMIFよりは早いかなーくらいの感じでしょうか。

データ量を増やしてやってみます。
2倍にして、
A2~A20001までA1~A20000、B2~B20001まで1~20000
これを、20002行目からもう一回繰り返します。
つまり、40000件を20000件にユニーク化
これで、
約44秒

3倍にして、
A2~A30001までA1~A30000、B2~B30001まで1~30000
これが、30002行目からもう一回繰り返します。
これで、
約106秒

処理件数に対して、処理時間が、2次曲線を描いています。
10倍(10万件)で計算すると21分以上の時間になってしまいます。
さすがに、これでは・・・

上記結果から方程式を解いてみる

10000件を1として、上記の結果を数式にしてみましょう。
yを処理時間、xを件数として、2次方程式を作ります。
y = ax^2 + bx + c
これに、3回の実行結果を当てはめて、
12 = a + b + c
44 = 4a + 2b + c
106 = 9a + 3b + c

この方程式を解くと、
a = 15
b = -13
c = 10
したがって、
処理時間 = 件数^2 *15 + 件数 * -13 + 10
となります。
10(10万件)で計算すると、
1380秒になります。
23分かかる計算になります。
※指数になるので、計測時間によって係数は大きく変動します。

エクセルで方程式を解いてみる
12 = a + b + c
44 = 4a + 2b + c
106 = 9a + 3b + c

これらの式の係数をA1:C3に入れ、左辺の答えをE1:E3に入れます。

VBA マクロ Dictionary

A5セルに、
=MINVERSE(A1:C3)
スピルしないエクセルの場合は、A5:C7を選択してCtrl+Shift+Enter

E5セルに、
=MMULT(A5#,E1:E3) ・・・A5:C7を選択すると勝手にA5#となります。
スピルしないエクセルの場合は、E5:E7を選択して、
=MMULT(A5:C7,E1:E3)
これでCtrl+Shift+Enter

これについては、別ページにて詳しく記載しました。
エクセルで連立方程式を解く(MINVERSE,MMULT)
とつぜん連立方程式を解くことになりました。なぜ連立方程式を解くことになったのか、そして、どうやって答えを導き出したのか… これらを自身の覚え書きの意味も含めて記事にしておきます。ただし、行列の難しい説明は抜かして、どうやって解決したかの経緯の説明が中心になります。


閑話休題
では、そもそも、どの部分で時間がかかっているかと言うと、
最後の3万件では、
Dictionaryに入れるまでは、0.6秒くらいです。
残りの時間が、Dictionaryからセルに出力する時間でした。
つまり、Dictionaryでユニーク化して集計するのは早い
しかし、Dictionaryからセルに出力するのが遅いという事です。

では、少しVBAコードを変えてみます。
太字の部分が変更箇所になります。

Dictionaryでユニーク化2

Sub sample2()
  Dim myDic As New Dictionary
  Dim i As Long
  Application.ScreenUpdating = False
  Range("D1").CurrentRegion.Offset(1).ClearContents
  Debug.Print Timer
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If myDic.Exists(Cells(i, 1).Value) Then
      myDic(Cells(i, 1).Value) = myDic(Cells(i, 1).Value) + Cells(i, 2).Value
    Else
      myDic.Add Cells(i, 1).Value, Cells(i, 2).Value
    End If
  Next
  Debug.Print Timer
  Dim ary
  ReDim ary(myDic.Count - 1, 1)
  For i = 0 To myDic.Count - 1
    ary(i, 0) = myDic.Keys(i)
    ary(i, 1) = myDic.Items(i)
  Next
  Range(Cells(2, 4), Cells(myDic.Count + 1, 5)) = ary

  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub

セルへの出力が遅いので、一旦配列に入れてから一気にシートに出力しています。
これで、3万件データを処理してみると、
102秒
変わらない・・・秒以下の計測誤差の範囲内です。

何にしても、速くはなっていません。
そもそも、数万程度のセル出力なんて、大した話ではないのです。
配列で一括出力したくらいでは、大差ありません。
つまり、セルへの出力が遅いのではなく、
Dictionaryからデータを取り出すのが遅いのです。

Dictionaryでユニーク化3

そこで、Dictionaryからの取り出しを、直接配列に入れて処理してみます。

Sub sample3()
  Dim myDic As New Dictionary
  Dim i As Long
  Application.ScreenUpdating = False
  Range("D:E").ClearContents
  Debug.Print Timer
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If myDic.Exists(Cells(i, 1).Value) Then
      myDic(Cells(i, 1).Value) = myDic(Cells(i, 1).Value) + Cells(i, 2).Value
    Else
      myDic.Add Cells(i, 1).Value, Cells(i, 2).Value
    End If
  Next
  Debug.Print Timer
  Dim ary1
  Dim ary2
  Dim ary3
  ary1 = myDic.Keys
  ary2 = myDic.Items
  ReDim ary3(UBound(ary1), 1)
  For i = 0 To UBound(ary1)
    ary3(i, 0) = ary1(i)
    ary3(i, 1) = ary2(i)
  Next
  Range(Cells(2, 4), Cells(myDic.Count + 1, 5)) = ary3

  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub

Keys
Items

複数系になっています、コレクションや配列を理解していれば、なんとなく想像がつくのではないでしょうか。
同種のオブジェクトを複数まとめたものを「コレクション」と呼びます、コレクションもオブジェクトの一種です。例えば、Workbookオブジェクトが複数まとまったものは「Workbooksコレクション」Worksheetオブジェクトが複数まとまったものは「Worksheetsコレクション」オブジェクト名が単数形であるのに対し、
キーとデータを別々に配列に取り出しているので、
シート出力用の2次元配列に入れ直してから、シートに出力しています。

これで、3万件データを処理してみると、
約2秒
Dictionaryでユニーク化して集計するのに、0.6秒かかっていますので、、
Dictionaryからセルに出力するは、1.4秒で終わっているという事です。

今回のテスト前には、速くなるのは分かってはいましたが、
これほどの違いがあるというのは改めてお退きでもあります。

Dictionaryでユニーク化4

Dictionaryの全件を処理する場合は、For…Eachを使うのが最も良いでしょう。

Sub sample4()
  Dim myDic As New Dictionary
  Dim i As Long
  Application.ScreenUpdating = False
  Range("D1").CurrentRegion.Offset(1).ClearContents
  Debug.Print Timer
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If myDic.Exists(Cells(i, 1).Value) Then
      myDic(Cells(i, 1).Value) = myDic(Cells(i, 1).Value) + Cells(i, 2).Value
    Else
      myDic.Add Cells(i, 1).Value, Cells(i, 2).Value
    End If
  Next
  Debug.Print Timer
  Dim ary, vKey
  ReDim ary(myDic.Count - 1, 1)
  i = 0
  For Each vKey In myDic
    ary(i, 0) = vKey
    ary(i, 1) = myDic.Item(vKey)
    i = i + 1
  Next
  Range(Cells(2, 4), Cells(myDic.Count + 1, 5)) = ary

  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub
※myDic(…)これは、myDic.Item(…)これの省略形です。

これで、3万件データを処理してみると、
約2秒
Keys、Itemsで一気に取り出す場合とほぼ同じ時間で、秒以下の差異しか認められませんでした。

どちらが良いというこもないと思いますが、
基本は、For…Eachですが、
配列に入れる必要があれば、Keys、Itemsで一気に取り出してください。

最初のVBAはなぜ遅かったのか

ary(i, 0) = myDic.Keys(i)
ary(i, 1) = myDic.Items(i)
ここで時間がかかっているのは、配列として扱っているからです。
では、なぜ、これでは遅いのかという理由についてですが、
オブジェクトブラウザーで調べてみます。

マクロVBA画像

マクロVBA画像

KeysとItemsはメソッドとして実装されています。
そして、説明では、
「ディクショナリ内のすべての○○を含む配列を取得します。」
と書かれています。

内部がどうなっているかは想像になりますが、
この文章から想像できることは、
Keys,Itemsメソッド実行時に、ディクショナリ内の全てのKey,Itemを配列に変換するということです。
つまり、Keys(i)と書かれていると、
ディクショナリ内の全てのKeyを配列に変換→指定要素iを返す。
つまり、Keys(i)が実行されるたびに、全てのKeyを配列に変換しているという事になります。
これでは処理時間がかかってしまうのは当然とも言えます。

解決方法としては、
・Keys,Itemsメソッドを使わずに、Key,Itemプロパティだけで記述する
・Keys,Itemsで作成された配列を一括で受け取る

どちらかの方法になります。

Dictionaryを使わない方法

Dictionaryを使わずに速く処理する方法として、
大量データで処理時間がかかる関数の対処方法(WorksheetFunction)
大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、そういう場合でも、多くの場合は何らかの対策があるものです、個別のロジックの記述でこれらに対応する方法として、一つの有効なマクロVBAコ-ドについて解説します。以下の例で解説します。
この考え方で、さらに配列を組み合わせて処理してみます。

Sub sample4()
  Dim i As Long
  Dim j As Long
  Dim ix As Long
  Dim ary
  Dim ary1()
  Dim ary2()
  Application.ScreenUpdating = False
  Range("D1").CurrentRegion.Offset(1).ClearContents
  Debug.Print Timer
  Range("A1").CurrentRegion.Sort Key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
  ary = Range("A1").CurrentRegion
  ix = -1
  For i = 2 To UBound(ary, 1)
    If ary(i, 1) <> ary(i - 1, 1) Then
      ix = ix + 1
      ReDim Preserve ary1(1, ix)
      ary1(0, ix) = ary(i, 1)
      ary1(1, ix) = ary(i, 2)
    Else
      ary1(1, ix) = ary1(1, ix) + ary(i, 2)
    End If
  Next
  ReDim ary2(UBound(ary1, 2), UBound(ary1, 1))
  For i = 0 To UBound(ary1, 1)
    For j = 0 To UBound(ary1, 2)
      ary2(j, i) = ary1(i, j)
    Next
  Next
  Range(Cells(2, 4), Cells(UBound(ary2, 1) + 2, 5)) = ary2
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub
※次元の入れ替えで、WorksheetFunction.Transposeは件数によっては使用できません。
配列が大きすぎると、Transposeは正しく処理されません。

これですと、
2.3秒
Dictionaryより少し遅くなっていますが、遜色ない速さですね。

サイト内の関連ページ

Dictionary(ディクショナリー)連想配列の使い方について
「Dictionaryオブジェクトについて簡単な使用例を上げて解説して欲しいです。」との要望をいただいたので、Dictionaryについて基本的な使い方を解説します。Dictionary(ディクショナリー)は名前の通り、辞書機能であり、連想配列とも呼ばれます。

大量VlookupをVBAで高速に処理する方法について
大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合 シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。

大量データにおける処理方法の速度王決定戦
VBAで自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。



同じテーマ「マクロVBA技術解説」の記事

事前バインディングと遅延バインディング(実行時バインディング)
Dictionary(ディクショナリー)連想配列の使い方について
Dictionary(ディクショナリー)のパフォーマンスについて
VBAでのInternetExplorer自動操作
VBAでのSQLの基礎(SQL:Structured Query Language)
VBAで正規表現を利用する(RegExp)
VBAでメール送信する(CDO:Microsoft Collaboration Data Objects)
VBAでのOutlook自動操作
ADO(ActiveX Data Objects)の使い方の要点
特殊フォルダの取得(WScript.Shell,SpecialFolders)
参照設定、CreateObject、オブジェクト式の一覧


新着記事NEW ・・・新着記事一覧を見る

Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)
VBAで表やグラフをPowerPointへ貼り付ける|VBAサンプル集(6月19日)
アクティブシート以外の表示(Window)に関する設定|VBA技術解説(6月17日)
マクロ記録での色のマイナス数値について|エクセル雑感(6月16日)
ツイッター投稿用に文字数と特定文字で区切る|エクセル雑感(6月15日)
日付の謎:IsDateとCDate|エクセル雑感(6月14日)
IFステートメントの判定|エクセル雑感(6月13日)
インクリメンタルサーチの実装|ユーザーフォーム入門(6月12日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ