ExcelマクロVBA技術解説 | 大量データで処理時間がかかる関数の対処方法(SumIf) | ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2018-02-01

大量データで処理時間がかかる関数の対処方法(SumIf)


大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、
そういう場合でも、多くの場合は何らかの対策があるものです、
個別のロジックの記述でこれに対応する一つの有効なマクロVBAコ−ドについて解説します。

以下の例で解説します。




A列にコード、B列に数量、これが10万行あります。
コードは、A1〜A1000まであります。
そして、
E列にユニーク化したコードA1〜A1000があります。
F列に各コードの数量合計を求めます。

F2セルに
=SUMIF(A:A,E2,B:B)
と入れて下にコピーすれば求められるものです。

実際にやってみると、コピー後に「再計算」がしばらく出るのが確認できると思います。
E列が1000程度なので、再計算できますが、
E列が数万行になるような場合は、「再計算」がいつまでも終わらない状態になります。
俗にいう、計算式が重いという状態です。

そこで、

この処理をまマクロにしようと考えたと仮定してください。
以下の計測は、Corei5、メモリ8G、Excel2013でのものです。
たの要因を排除する意味と、比較のしやすさの意味で、行数等は固定値にしています。


まずは、普通にマクロVBAコ−ドを書いてみると、

Sub sample1()
  Dim i As Long
  Application.ScreenUpdating = False
  Debug.Print Timer
  For i = 2 To 1001
    Cells(i, 6) = WorksheetFunction.SumIf(Columns(1), Cells(i, 5), Columns(2))
  Next
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub


これで処理時間は
13.6秒〜13.8秒

シートでの再計算より、やや時間がかかてしまっています。

改善点はあるでしょうか、

データ範囲が列全体になっていますが、範囲を絞ったらどうでしょうか。

Sub sample2()
  Dim i As Long
  Application.ScreenUpdating = False
  Debug.Print Timer
  For i = 2 To 1001
    Cells(i, 6) = WorksheetFunction.SumIf(Range("A2:A100001"), Cells(i, 5), Range("B2:B100001"))
  Next
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub


これで処理時間は
13.6秒〜13.8秒

全く変わりません、ワークシート関数は良くできています。

マクロを覚えて、少したったくらいの方に多いように思いますが、
「配列を使うと早くなる」
これも盲信している人がいるようです。

では、配列を使って書いてみましょう。

Sub sample3()
  Dim i As Long
  Dim ix As Long
  Dim ary
  Application.ScreenUpdating = False
  Debug.Print Timer
  ary = Range("E2:F1001")
  For i = 1 To 1000
    ary(i, 2) = WorksheetFunction.SumIf(Range("A2:A100001"), ary(i, 1), Range("B2:B100001"))
  Next
  Range("E2:F101") = ary
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub


これで処理時間は
13.6秒〜13.8秒

全く変わりませんでした、実際には違いがあるはずなのですが、計測できる差がないという事です。
つまり、1000行程度の出力では、配列にしてもあまり意味がないのです。
もちろん、行数がもっと多いとか、計算する列数が多ければ、配列にすることで早くなります。

では、どうしようもないのでしょうか・・・
それでは、この記事の意味がなくなってしまいますね。
考えを変えてみます。
SumIf関数が無かったとして、これを集計することを考えてください。

あなたならどうしますか・・・

E2セルのA1を、A列で探してB列を足し上げる・・・
E列の1データについて、10万行の中から探しますか・・・

A1だけでもとんでもない時間がかかってしまいますね。
そんな非効率事はしないはずです。

A列で並べ替えれば、A1から順に並ぶので、A1だけなら、簡単に求められます。
これを1000回繰り返せばよいのです。


これをマクロVBAコ−ドにしてみましょう。

Sub sample4()
  Dim i As Long
  Dim i1 As Long
  Dim i2 As Long
  Dim total As Long
  Application.ScreenUpdating = False
  Debug.Print Timer
  For i = 2 To 100001
    Cells(i, 3) = i
  Next
  For i = 2 To 1001
    Cells(i, 7) = i
  Next
  Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
  Range("E1").Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlYes
  i1 = 2
  i2 = 2
  Do Until i2 > 1001
    total = 0
    Do Until Cells(i1, 1) > Cells(i2, 5) Or i1 > 100001
      total = total + Cells(i1, 2)
      i1 = i1 + 1
    Loop
    Cells(i2, 6) = total
    i2 = i2 + 1
  Loop
  Range("A1").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
  Range("E1").Sort Key1:=Range("G1"), Order1:=xlAscending, Header:=xlYes
  Columns(3).ClearContents
  Columns(7).ClearContents
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub


これなら処理時間は
2.5秒

格段に速いのがお分かりいただけると思います。

処理内容を書き出すと、

・C列にA:Bの今の順序を出力
・G列にE:Fの今の順序を出力
・A列で昇順に並べ替え
・E列で昇順に並べ替え
・A列とE列を順に比較しつつ同じならB列を足し上げる
・A列>E列 or A列の最終になったら、F列に合計を出力し、E列の次に移る
・C列で昇順に並べ替え、元の順に戻す
・G列で昇順に並べ替え、元の順に戻す
・C列をクリア
・G列をクリア

このようになります。

注意点としては、
E列も並べ替える必要があります。
先のサンプル画像では昇順に並んでいるように見えますが、
文字列の大小比較をする場合、
A1,A2,A3,・・・A9,A10,A11
ではありません、
A1,A10,A100,・・・A2,・・・
となります、これは実際に並べ替えてみればわかると思います。

このような、シーケンシャル処理ロジックは、
バッチ処理で大量データを扱ったことがあれば、ごく普通のロジックになります。
そして、大量データ同士の比較・集計においては、これが最も早いのです。
つまり、無駄が一切ないのです。
A列もE列も、上から下に向かって1回ずつしかループしていません。
1回のループなので、これが最も早いという事です。
sample1〜sample3もVBAコードとしては1回のループですが、
SumIf関数の中で、A列の上から下に向かってループしていることは想像に難くないはずです。

結論として、
大量データ処理において処理時間を短縮しようと思ったら、
まずは、データを並べ替えてみる事です。
そうすれば、データの特質が見えてきますので、
それから適切な処理ロジックを考えてみて下さい。


今回はSumIf関数を例にしましたが、
CountIf関数でも、VlookUp関数でも、同様の考え方で出来るのがご理解できますでしょうか。
もし、これらの関数で処理時間がかかっているようでしたら、ぜひ試してみて下さい。


今回の事例と同じような考え方として、
【奥義】大量データでの高速VLOOKUP
この記事は、マクロVBAではなく、ワークシート関数についてですが、
考え方として、非常に参考になると思います。

また、そもそもVBAの最低限の速度対策は必須です。
以下を参考にしてください。、
エクセルVBAのパフォーマンス・処理速度に関するレポート
マクロVBAの高速化・速度対策の具体的手順と検証
速度比較決定版【Range,Cells,Do,For,For Each】
大量データにおける処理方法の速度王決定戦




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

記述による処理速度の違い
速度比較決定版【Range,Cells,Do,For,ForEach】
エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
WorksheetFunction.Matchで配列を指定した場合の制限について
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について

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

VBAを定型文で覚えよう|ExcelマクロVBA技術解説(3月26日)
VBAスタンダード試験対策まとめ|MOS VBAエキスパート対策(3月16日)
ユーザーフォームとメニューの操作|MOS VBAエキスパート対策(3月14日)
ファイルの操作|MOS VBAエキスパート対策(3月14日)
ユーザーフォームの各種イベント|Excelユーザーフォーム(3月13日)
レジストリの操作|MOS VBAエキスパート対策(3月12日)
変数と配列|MOS VBAエキスパート対策(3月12日)
Colorプロパティの設定値一覧|VBA技術解説(3月12日)
APIとOLEオートメーション|MOS VBAエキスパート対策(3月11日)
エラーへの対処|MOS VBAエキスパート対策(3月10日)

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

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



  • >
  • >
  • >
  • 大量データで処理時間がかかる関数の対処方法(SumIf)

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


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





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

    本文下部へ

    ↑ PAGE TOP