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

公開日：2015-08-15　最終更新日：2020-04-09

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

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

以下の例で解説します。

大量データで処理時間がかかるサンプルデータ

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

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

つまり、10万件を1万件に集約しています。
実際にやってみると、コピー後に「再計算」がしばらく出るのが確認できると思います。

筆者のPC環境で40秒くらいかかりました。

E列が10000程度なので、再計算できますが、
E列が数万行になるような場合は、「再計算」がいつまでも終わらない状態になります。
俗にいう、計算式が重いという状態です。

そこで、

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

普通にマクロ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

これで処理時間は
104～105秒

シートでの再計算より、かなり時間がかかってしまっています。
改善点はあるでしょうか、、、

指定範囲を絞ってみる

先のVBAはデータ範囲が列全体になっていましたが、この指定範囲を絞ったらどうでしょうか


  
    
      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

これで処理時間は
104秒

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

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

配列を使って書いてみる


  
    
      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

これで処理時間は
104～105秒

全く変わりませんでした、実際には違いがあるはずなのですが、計測できる差がないという事です。
SumIfの処理時間に比べたら、データ出力の処理時間など取るに足らないという事です。
つまり、10000行程度の出力では、配列にしてもあまり意味がないのです。
もちろん、行数がもっと多いとか、計算する列数が多ければ、配列にすることで早くなります。

では、どうしようもないのでしょうか・・・
それでは、この記事の意味がなくなってしまいますね。
考えを変えてみます。

アルゴリズムを考えてみる

SumIf関数が無かったとして、これを集計することを考えてください。
あなたならどうしますか・・・

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

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

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

これをマクロ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

これなら処理時間は
6.1～6.2秒

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

処理内容を書き出すと、

・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列の上から下に向かってループしていることは想像に難くないはずです。

上記VBAではここまでのVBAと比較しやすいように配列を使っていません。
とにかく、SumIf以外の方法を模索すれば、ずっと速い処理が実現できることをお分かりいただけたでしょうか。

では最後に、この場合なら一番手軽で、多分一番早いと思われるVBAを掲載しておきます。

Dictionary（連想配列）を使う


  
    
      Sub sample5()

      　　Dim i As Long, st As Double

      　　Dim ary

      　　Application.ScreenUpdating = False

      　　st = Timer

      　　Dim myDic As New Dictionary

      　　For i = 2 To 100001

      　　　　If myDic.Exists(Cells(i, 1).Value) Then

      　　　　　　myDic.Item(Cells(i, 1).Value) = myDic.Item(Cells(i, 1).Value) + Cells(i, 2).Value

      　　　　Else

      　　　　　　myDic.Add Cells(i, 1).Value, Cells(i, 2).Value

      　　　　End If

      　　Next

      　　ary = WorksheetFunction.Transpose(myDic.Items)

      　　Range("F2").Resize(UBound(ary)) = ary

      　　Debug.Print Timer - st

      　　Application.ScreenUpdating = True

      End Sub

これなら処理時間は
約1秒

この処理は、まさにDictionary（連想配列）向きの処理になります。
以下のページに今回とほぼ似たような事例を掲載しています。

大量VlookupをVBAで高速に処理する方法について

大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。

大量データで処理時間がかかる関数の対処方法の最後に

大量データ処理において処理時間を短縮しようと思ったら、
まずは、データを並べ替えてみる事です。
そうすれば、データの特質が見えてきますので、
それから適切な処理ロジックを考えてみて下さい。
そうして考えていくことで、
VBAのより便利な機能が見えてきたり、新たな技術を習得できたりしていきます。

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

今回の事例と同じような考え方として、

【奥義】大量データでの高速VLOOKUP

・高速VLOOKUPに使用するサンプルデータ・高速VLOOKUPの数式・高速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のパフォーマンス・処理速度に関するレポート
 VBAのFindメソッドの使い方には注意が必要です
 マクロVBAの高速化・速度対策の具体的手順と検証
 動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
 遅い文字列結合を最速処理する方法について
 大量VlookupをVBAで高速に処理する方法について
 Withステートメントの実行速度と注意点
 IfステートメントとIIF関数とMax関数の速度比較
 スピルって速いの?スピルの速度について

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
大量データで処理時間がかかる関数の対処方法（SumIf）

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

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

VBA技術解説
大量データで処理時間がかかる関数の対処方法（SumIf）