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

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2017-12-12

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

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


Dictionaryの基本的な使い方については、
Dictionary(ディクショナリー)連想配列の使い方について
エクセル掲示板で、Dictionaryオブジェクトについて簡単な使用例を上げて解説して欲しいです。検討お願いしますm(_ _)m と頂いたので、分かる範囲内で解説します。実際は、私はあまり使う事はありません。
こちらを参照してください。
ここでは、

実際は、私はあまり使う事はありません。
なぜなら、配列処理で可能ですので、特にDictionaryを使う必要がないからです。
しかし、便利な時もあるので、一応は押さえておきたい機能ではあります。


と書きましたが、
大量データをユニーク化する時は高速な処理を実現できます。


以下の表で検証します。
VBA サンプル画像

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

これが、20002行目からもう一回繰り返されています。
つまり、
20000件のデータで、ユニーク化して10000件のデータです。

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


・配列を使って集計
・AdvancedFilterでキーをユニークにしてからSUMIF
・ピボットを使って集計
ピボット以外は、相当の時間がかかってしまいそうです。
ピボットは単純な集計でしか使いずらく、臨機応変に対応しずらいです。
※ピボットの集計は早いです。
 それでも、後述の方法の方が、このデータ量なら若干はやいです。
 ピボットを含めた、速度検証は以下に掲載しました。
 大量データ処理における高速化・速度対策の検証

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

そこで、Dictionary(ディクショナリー)を使ってみようという事です。

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


以下で、VBAサンプルコードと、その処理時間について検証します。
計測は、Corei5、メモリ8G、Excel2013でのものです。
処理時間については、その時のPCの状態にも依存しますので、目安としてとらえてください。


まずは、普通に書いてみます。

sample1


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に登録されていなければ追加する。
これで、
16.2秒
どうでしょう、早いと言えば早いでしょうね。
先に書いた、
配列やAdvancedFilter+SUMIFよりは早いかなーくらいの感じでしょうか。

データ量を増やしてやってみます。

2倍にして、
A2~A20001までA1~A20000、B2~B20001まで1~20000
これが、20002行目からもう一回繰り返されています。
これで、
74.5秒

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

処理件数に対して、処理時間が、2次曲線を描いています。
10(10万件)で計算すると30分近い時間がかかります。
さすがに、これでは・・・

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

この方程式を解くと、
a = 16.1
b = 10
c = -9.9
したがって、
処理時間 = 件数^2 *16.1 + 件数 * 10 - 9.9
となります。
10(10万件)で計算すると、
1700.1秒になります。
およそ30分近い時間です。

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


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


ample2
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万件データを処理してみると、
166秒
全然早くない、と言うか、遅くなってます。
しかし、差は誤差範囲程度でしょう、この記事を書きながらでの計測ですし。
何にしても、早くはなっていません。
そもそも、数万程度のセル出力なんて、大した話ではないので、
配列で一括出力したくらいでは、大差ありません。


つまり、セルへの出力が遅いのではなく、
Dictionaryからデータを取り出すのが遅いのです。



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

sample3


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万件データを処理してみると、
0.72秒
Dictionaryでユニーク化して集計するのに、0.6秒強かかっていますので、、
Dictionaryからセルに出力するは、0.1秒で終わっているという事です。
ちなみに、
10倍の、10万件(ユニーク前は20万件)でやってみると、
3.4秒
これは早いです、感動ものの早さです。

今回のテスト前には、早くなるのは分かってはいましたが、
ここまで差がつくとは、そして、10万件で、この速度で処理できるとまでは予想以上でした。


※後日談

読者様より指摘があり、
ary(i, 0) = myDic.Keys(i)
ary(i, 1) = myDic.Items(i)
ここで時間がかかっているのは、配列として扱っているからだという指摘がありました。
そこは、For Eachで処理すべきという指摘です。
確かに、KeysをFor Eachで処理し、Item(Key)で取得することで高速に処理が可能です。

Sub sample3_2()
  Dim myDic As New Dictionary
  Dim i As Long
  Dim dicKey
  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
  Dim ary3
  ReDim ary3(myDic.Count, 1)
  i = 0
  For Each dicKey In myDic.Keys
    ary3(i, 0) = dicKey
    ary3(i, 1) = myDic.Item(dicKey)
    i = i + 1
  Next

  Range(Cells(2, 4), Cells(myDic.Count + 1, 5)) = ary3
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub

sample3と、完成コードとしては大きな違いはありませんが、
KeyとItemを配列に入れずに取り出していますので、
VBAコードとしては、よりスマートになっていると思います。
処理時間としては、
実測でsample3との違いを提示するまでには至りませんでしたので割愛します。
(複数回計測してみましたが、速かったりそうでも無かったりという感じでした)

では、なぜ、
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で作成された配列を一括で受け取る

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



最後に比較として

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

sample4
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は正しく処理されません。

これですと、
1.6秒
やはり、圧倒的に早いですね。
配列の使い方と、シーケンシャル処理は最速です。
また、シートの並べ替えは、感心するほど早いです。


とはいえ、Dictionaryの早さは、予想を上回るものでした。


そこで、前言撤回です。
積極的に、Dictionaryを使って行きたいと思います。


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

これを結論とします。

配列を使って集計、AdvancedFilter、ピボット等々
これらの各種処理方法における速度比較を以下に掲載しています。
大量データにおける処理方法の速度王決定戦
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 ・・・新着記事一覧を見る

Byte配列と文字コード関数について|VBA技術解説(8月20日)
PowerQueryの強力な機能をVBAから利用する方法|VBA技術解説(8月4日)
練習問題31(セル結合を解除して値を埋める)|VBA練習問題(7月30日)
練習問題30(マトリックス→リスト形式)|VBA練習問題(7月25日)
Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説



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

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


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




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