ExcelマクロVBA技術解説
大量データで処理時間がかかる関数の対処方法(SumIf)

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

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


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


以下の例で解説します。

マクロ 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のパフォーマンス・処理速度に関するレポート
ExcelのVBAは遅い・重いと良く言われることが多いようですが、VBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。
マクロVBAの高速化・速度対策の具体的手順と検証
マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。
速度比較決定版【Range,Cells,Do,For,For Each】
何度も言っているのですが、RangeとCellsでどっちが早いか、とか、DoとForとFor Eachでどれが早いか とか、そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。
大量データにおける処理方法の速度王決定戦



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

エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
WorksheetFunction.Matchで配列を指定した場合の制限について
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
大量データで処理時間がかかる関数の対処方法(SumIf)
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
Withステートメントの実行速度と注意点
IfステートメントとIIF関数とMax関数の速度比較


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

VBAコードの全プロシージャー・プロパィ一覧を取得|VBAサンプル集(10月12日)
VBAでエラー行番号を取得できるErl関数|VBA技術解説(10月12日)
手動計算時の注意点と再計算方法|ExcelマクロVBA技術解説(10月9日)
引数の数を可変にできるパラメーター配列(ParamArray)|VBA入門(10月7日)
VBEの使い方:デバッグ|ExcelマクロVBA入門(10月6日)
VBAにおける配列やコレクションの起点について|VBA技術解説(10月5日)
VBEの使い方:オブジェクト ブラウザー|VBA入門(10月5日)
VBEの使い方:ウォッチ ウィンドウ|VBA入門(10月4日)
VBEの使い方:ローカル ウィンドウ|VBA入門(10月3日)
VBEの使い方:イミディエイト ウィンドウ|VBA入門(10月2日)


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

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.ひらがな⇔カタカナの変換|エクセル基本操作
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.セルに文字を入れるとは(Range,Value)|VBA入門



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

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


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




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