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



最終更新日:2016-08-14

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万件で、この速度で処理できるとまでは予想以上でした。


最後に比較として

大量データで処理時間がかかる関数の対処方法(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、ピボット等々
これらの各種処理方法における速度比較を以下に掲載しています。
大量データにおける処理方法の速度王決定戦




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

Dictionary(ディクショナリー)連想配列の使い方について
VBAでのInternetExplorer自動操作
VBAでのSQLの基礎(SQL:Structured Query Language)
VBAで正規表現を利用する(RegExp)
VBAでメール送信する(CDO:Microsoft Collaboration Data Objects)
VBAでのOutlook自動操作
ADO(ActiveX Data Objects)の使い方の要点

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

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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

    ↑ PAGE TOP