大量データで処理時間がかかる関数の対処方法（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秒
改善点はあるでしょうか、、、
指定範囲を絞ってみる
先のVBAはデータ範囲が列全体になっていましたが、この指定範囲を絞ったらどうでしょうか
これで処理時間は
104秒
全く変わりません、ワークシート関数は良くできています。
マクロを覚えて、少したったくらいの方に多いように思いますが、
「配列を使うと早くなる」
これも盲信している人がいるようです。
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コ－ドにしてみましょう。
これなら処理時間は
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を掲載しておきます。
あなたならどうしますか・・・
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秒
以下のページに今回とほぼ似たような事例を掲載しています。
大量VlookupをVBAで高速に処理する方法について
大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合 シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。
大量データで処理時間がかかる関数の対処方法の最後に
大量データ処理において処理時間を短縮しようと思ったら、
まずは、データを並べ替えてみる事です。
そうすれば、データの特質が見えてきますので、
それから適切な処理ロジックを考えてみて下さい。
そうして考えていくことで、
VBAのより便利な機能が見えてきたり、新たな技術を習得できたりしていきます。
今回はSumIf関数を例にしましたが、
CountIf関数でも、VlookUp関数でも、同様の考え方で出来るのがご理解できますでしょうか。
もし、これらの関数で処理時間がかかっているようでしたら、ぜひ試してみて下さい。
今回の事例と同じような考え方として、
まずは、データを並べ替えてみる事です。
そうすれば、データの特質が見えてきますので、
それから適切な処理ロジックを考えてみて下さい。
そうして考えていくことで、
VBAのより便利な機能が見えてきたり、新たな技術を習得できたりしていきます。
CountIf関数でも、VlookUp関数でも、同様の考え方で出来るのがご理解できますでしょうか。
もし、これらの関数で処理時間がかかっているようでしたら、ぜひ試してみて下さい。
【奥義】大量データでの高速VLOOKUP
・高速VLOOKUPに使用するサンプルデータ ・高速VLOOKUPの数式 ・高速VLOOKUPの数式解説 ・高速VLOOKUPの補足
この記事は、マクロVBAではなく、ワークシート関数についてですが、
考え方として、非常に参考になると思います。
また、そもそも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 ・・・新着記事一覧を見る
新旧マスタの差異比較｜Power Query（M言語）入門(2023-02-28)
有効な最新単価の取得｜Power Query（M言語）入門(2023-02-26)
有効な最新単価の取得｜Power Query（M言語）入門(2023-02-21)
グルーブ内の最小・最大｜Power Query（M言語）入門(2023-02-17)
2つのテーブルのマージ｜Power Query（M言語）入門(2023-02-15)
「売上」が数値の行のみ取り込む｜Power Query（M言語）入門(2023-02-13)
A列のヘッダー名を変更する｜Power Query（M言語）入門(2023-02-11)
CSVのA列が日付の行だけを取り込む｜Power Query（M言語）入門(2023-02-10)
列数不定のCSVの取り込み｜Power Query（M言語）入門(2023-02-09)
別ブックの最終シートの取り込み｜Power Query（M言語）入門(2023-02-08)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.Excelショートカットキー一覧｜Excelリファレンス
9.エクセルVBAでのシート指定方法｜VBA技術解説
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 大量データで処理時間がかかる関数の対処方法（SumIf）
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。