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

コードは、A1~A10001までの1万種類です。
そして、
E列にユニーク化したコードA1~A10000があります。
F列に各コードの数量合計を求めます。
=SUMIF(A:A,E2,B:B)
このように入力して下にコピーすれば求められるものです。
実際にやってみると、コピー後に「再計算」がしばらく出るのが確認できると思います。

E列が数万行になるような場合は、「再計算」がいつまでも終わらない状態になります。
俗にいう、計算式が重いという状態です。
以下の計測は、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秒
改善点はあるでしょうか、、、
指定範囲を絞ってみる
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行程度の出力では、配列にしてもあまり意味がないのです。
もちろん、行数がもっと多いとか、計算する列数が多ければ、配列にすることで早くなります。
それでは、この記事の意味がなくなってしまいますね。
考えを変えてみます。
アルゴリズムを考えてみる
あなたならどうしますか・・・
E列の1データについて、10万行の中から探しますか・・・
そんな非効率事はしないはずです。
これを10000回繰り返せばよいのです。
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秒
・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以外の方法を模索すれば、ずっと速い処理が実現できることをお分かりいただけたでしょうか。
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秒
以下のページに今回とほぼ似たような事例を掲載しています。
大量データで処理時間がかかる関数の対処方法の最後に
まずは、データを並べ替えてみる事です。
そうすれば、データの特質が見えてきますので、
それから適切な処理ロジックを考えてみて下さい。
そうして考えていくことで、
VBAのより便利な機能が見えてきたり、新たな技術を習得できたりしていきます。
CountIf関数でも、VlookUp関数でも、同様の考え方で出来るのがご理解できますでしょうか。
もし、これらの関数で処理時間がかかっているようでしたら、ぜひ試してみて下さい。
考え方として、非常に参考になると思います。
また、そもそもVBAの最低限の速度対策は必須です。
以下を参考にしてください。
同じテーマ「マクロVBA技術解説」の記事
大量データにおける処理方法の速度王決定戦
新着記事NEW ・・・新着記事一覧を見る
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)
PIVOTBY関数(縦軸と横軸でグループ化して集計)|エクセル入門(2023-11-19)
GROUPBY関数(縦軸でグループ化して集計)|エクセル入門(2023-11-18)
PY関数(Pythonコードをセル内で実行)|エクセル入門(2023-11-17)
画像「セルに配置」のVBAについて(365の新機能)|VBA技術解説(2023-11-13)
スピルのゴーストの範囲を選択するVBA|ツイッター出題回答 (2023-10-31)
エクセル試験3:月間の所定労働時間|エクセル練習問題(2023-10-04)
エクセル試験2:所得税の計算|エクセル練習問題(2023-10-04)
エクセル試験1:曜日別の平均客単価|エクセル練習問題(2023-10-04)
列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.条件分岐(IF)|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.マクロとは?VBAとは?VBAでできること|VBA入門
10.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 大量データで処理時間がかかる関数の対処方法(SumIf)
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。