ExcelマクロVBA技術解説 | Dictionary(ディクショナリー)のパフォーマンスについて | ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2017-12-12

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

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

Dictionaryの基本的な使い方については、
Dictionary(ディクショナリー)連想配列の使い方について
こちらを参照してください。

ここでは、

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


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


以下の表で検証します。


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

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

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


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

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

※大量データ集計の高速化について
大量データで処理時間がかかる関数の対処方法(WorksheetFunction)
こちらを参考にしてください。
集計方法によって適用可能かどうか変わりますが、適用可能なら、ここでの方法が一番よいです。


以下で、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

複数系になっていますし、コレクションを理解していれば、なんとなく想像がつくのではないでしょうか。
キーとデータと、別々に配列に取り出しているので、
シート出力用の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)
これでは遅いのかという理由についてですが、
オブジェクトブラウザーで調べてみます。





KeysとItemsはメソッドとして実装されています。
そして、説明では、
「ディクショナリ内のすべての○○を含む配列を取得します。」
と書かれています。
内部がどうなっているかは想像になりますが、
この文章から想像できることは、
Keys,Itemsメソッド実行時に、ディクショナリ内の全てのKey,Itemを配列に変換するということです。
つまり、Keys(i)と書かれていると、
ディクショナリ内の全てのKeyを配列に変換→指定要素iを返す
つまり、Keys(i)が実行されるたびに、全てのKeyを配列に変換しているという事になります。
これでは処理時間がかかってしまうのは当然とも言えます。
解決方法としては、
・Keys,Itemsメソッドを使わずに、Key,Itemプロパティだけで記述する
・Keys,Itemsで作成された配列を一括で受け取る

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



最後に比較として

大量データで処理時間がかかる関数の対処方法(WorksheetFunction)
この考え方で、さらに配列を組み合わせて処理してみます。

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)
これが使えないような、
ランダム処理する場合は、今回のDictionaryを使う。

これを結論とします。

配列を使って集計、AdvancedFilter、ピボット等々
これらの各種処理方法における速度比較を以下に掲載しています。
大量データにおける処理方法の速度王決定戦




同じテーマ「マクロ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)の使い方の要点

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

数値範囲で表検索するVLOOKUP近似一致|エクセル関数超技(10月5日)
エクセルVBAでのシート指定方法|VBA技術解説(9月8日)
VBAのクラスとは(Class,Property,Get,Let,Set)|VBA技術解説(8月28日)
VBAこれだけは覚えておきたい必須基本例文10|VBA技術解説(8月22日)
VBAの省略可能な記述について|ExcelマクロVBA技術解説(8月11日)
複数条件判定を行う時のコツ|ExcelマクロVBA技術解説(7月11日)
For Next の使い方いろいろ|VBA技術解説(6月14日)
VBAを定型文で覚えよう|ExcelマクロVBA技術解説(3月26日)
VBAスタンダード試験対策まとめ|MOS VBAエキスパート対策(3月16日)
ユーザーフォームとメニューの操作|MOS VBAエキスパート対策(3月14日)

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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.マクロって何?VBAって何?|ExcelマクロVBA入門
7.定数と型宣言文字(Const)|ExcelマクロVBA入門
8.繰り返し処理(For Next)|ExcelマクロVBA入門
9.とにかく書いて見よう(Sub,End Sub)|VBA入門
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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





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

    本文下部へ

    ↑ PAGE TOP