map新着記事一覧アクセスランキングサイトマップ旧トップページお問い合わせホームマクロVBA応用編マクロVBA技術解説Dictionary(ディクショナリー)のパフォーマンスについて最終更新日:2020-02-07Dictionary(ディクショナリー)のパフォーマンスについてDictionary(ディクショナリー)は辞書機能で、連想配列とも呼ばれます。今回はこのDictionaryのパフォーマンス(処理速度)を検証します。Dictionaryの基本的な使い方については、こちらを参照してください。・サイト内のDictionary関連記事Dictionaryを使う事で、大量データをユニーク化する時は高速な処理を実現できます。本記事は、Excel2013当時の記事をOffice365(2020/2月時点)で再検証して書き直したものです。Dictionaryの検証に使うシートA2~A10001まで、"A1"~"A10000"B2~B10001まで、1~10000これが、10002行目からもう一回繰り返されています。20000件のデータをユニーク化して10000件のデータにします。D列にユニーク化したキーを、E列にデータの合計を出力します。・AdvancedFilterでキーをユニークにしてからSUMIF・ピボットを使って集計ピボット以外は、相当の時間がかかってしまいそうです。ピボットは単純な集計でしか使いづらく、複雑な集計には対応しづらくなる場合が出てきます。※ピボットの集計は早いです。それでも、後述の方法の方が、このデータ量なら若干はやいです。ピボットを含めた、速度検証は以下に掲載しました。そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。そこで、Dictionary(ディクショナリー)を使ってみようという事です。・大量データで処理時間がかかる関数の対処方法の最後にこちらを参考にしてください。集計方法によって適用可能かどうか変わりますが、適用可能なら、ここでの方法が一番よいです。以下では、VBAサンプルコードと、その処理時間について検証します。計測は、Corei7、メモリ16G、Office365でのものです。処理時間については、その時のPCの状態にも依存しますので、目安としてとらえてください。良くありそうなVBAを書いてみます。Subごく普通のVBAコードではないでしょうか。表の上から順に処理していき、Dictionaryに登録されていれば加算、Dictionaryに登録されていなければ追加する。配列やAdvancedFilter+SUMIFよりは早いかなーくらいの感じでしょうか。データ量を増やしてやってみます。2倍にして、A2~A20001までA1~A20000、B2~B20001まで1~20000これを、20002行目からもう一回繰り返します。約44秒3倍にして、A2~A30001までA1~A30000、B2~B30001まで1~30000これが、30002行目からもう一回繰り返します。処理件数に対して、処理時間が、2次曲線を描いています。10倍(10万件)で計算すると21分以上の時間になってしまいます。上記結果から方程式を解いてみる10000件を1として、上記の結果を数式にしてみましょう。yを処理時間、xを件数として、2次方程式を作ります。10(10万件)で計算すると、1380秒になります。23分かかる計算になります。※指数になるので、計測時間によって係数は大きく変動します。cこれらの式の係数をA1:C3に入れ、左辺の答えをE1:E3に入れます。・・・A5:C7を選択すると勝手にA5#となります。スピルしないエクセルの場合は、E5:E7を選択して、=MMULT(A5:C7,E1:E3)これでCtrl+Shift+Enterこれについては、別ページにて詳しく記載しました。最後の3万件では、Dictionaryに入れるまでは、0.6秒くらいです。残りの時間が、Dictionaryからセルに出力する時間でした。Dictionaryでユニーク化して集計するのは早い、しかし、Dictionaryからセルに出力するのが遅いという事です。VBAコードを変えてみます。太字の部分が変更箇所になります。Subセルへの出力が遅いので、一旦配列に入れてから一気にシートに出力しています。変わらない・・・秒以下の計測誤差の範囲内です。何にしても、速くはなっていません。そもそも、数万程度のセル出力なんて、大した話ではないのです。配列で一括出力したくらいでは、大差ありません。セルへの出力が遅いのではなく、Dictionaryからデータを取り出すのが遅いのです。Dictionaryでユニーク化3そこで、Dictionaryからの取り出しを、直接配列に入れて処理してみます。SubKeysItems複数系になっています、コレクションや配列を理解していれば、なんとなく想像がつくのではないでしょうか。・Collectionオブジェクトキーとデータを別々に配列に取り出しているので、シート出力用の2次元配列に入れ直してから、シートに出力しています。約2秒Dictionaryでユニーク化して集計するのに、0.6秒かかっていますので、、Dictionaryからセルに出力するは、1.4秒で終わっているという事です。今回のテスト前には、速くなるのは分かってはいましたが、これほどの違いがあるというのは改めてお退きでもあります。Dictionaryでユニーク化4Dictionaryの全件を処理する場合は、For...Eachを使うのが最も良いでしょう。Sub※myDic(...)これは、myDic.Item(...)これの省略形です。約2秒Keys、Itemsで一気に取り出す場合とほぼ同じ時間で、秒以下の差異しか認められませんでした。配列に入れる必要があれば、Keys、Itemsで一気に取り出してください。myDic.Items(i)ここで時間がかかっているのは、配列として扱っているからです。これでは遅いのかという理由についてですが、オブジェクトブラウザーで調べてみます。KeysとItemsはメソッドとして実装されています。「ディクショナリ内のすべての○○を含む配列を取得します。内部がどうなっているかは想像になりますが、この文章から想像できることは、Keys,Itemsメソッド実行時に、ディクショナリ内の全てのKey,Itemを配列に変換するということです。Keys(i)と書かれていると、ディクショナリ内の全てのKeyを配列に変換→指定要素iを返す。Keys(i)が実行されるたびに、全てのKeyを配列に変換しているという事になります。これでは処理時間がかかってしまうのは当然とも言えます。・Keys,Itemsメソッドを使わずに、Key,Itemプロパティだけで記述する・Keys,Itemsで作成された配列を一括で受け取るどちらかの方法になります。・大量データで処理時間がかかる関数の対処方法の最後にこの考え方で、さらに配列を組み合わせて処理してみます。Sub※次元の入れ替えで、WorksheetFunction.Transposeは件数によっては使用できません。配列が大きすぎると、Transposeは正しく処理されません。これですと、2.3秒Dictionaryより少し遅くなっていますが、遜色ない速さですね。大量VlookupをVBAで高速に処理する方法について大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。シートに関数を入れる場合は、以下を参照してください。以下の表で検証します。そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。Next)|VBA入門5.ひらがな⇔カタカナの変換|エクセル基本操作6.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門7.マクロって何?VBAって何?|VBA入門8.Excelショートカットキー一覧|Excelリファレンス9.並べ替え(Sort)|VBA入門10.エクセルVBAでのシート指定方法|VBA技術解説ホームマクロVBA応用編マクロVBA技術解説Dictionary(ディクショナリー)のパフォーマンスについてこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。アクセスランキングサイトマップ事業案内お問い合わせホームマクロVBA応用編マクロVBA技術解説Dictionary(ディクショナリー)のパフォーマンスについてこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。