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

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2020-04-09

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


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


以下の例で解説します。

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

VBA マクロ 大量データ

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

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

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

VBA マクロ 大量データ

筆者の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に使用するサンプルデータ 以下の表で説明します。
この記事は、マクロ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関数の速度比較
スピルって速いの?スピルの速度について


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

VBA100本ノック 18本目:名前定義の削除|VBA練習問題100(11月6日)
VBA100本ノック 17本目:重複削除(ユニーク化)|VBA練習問題100(11月6日)
VBA100本ノック 16本目:無駄な改行を削除|VBA練習問題100(11月5日)
VBA100本ノック 15本目:シートの並べ替え|VBA練習問題100(11月4日)
VBA100本ノック 14本目:社外秘シート削除|VBA練習問題100(11月3日)
VBA100本ノック 13本目:文字列の部分フォント|VBA練習問題100(11月1日)
VBA100本ノック 12本目:セル結合を解除|VBA練習問題100(10月31日)
VBA100本ノック 11本目:セル結合の警告|VBA練習問題100(10月30日)
VBA100本ノック 10本目:行の削除|VBA練習問題100(10月29日)
VBA100本ノック 9本目:フィルターコピー|VBA練習問題100(10月28日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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