大量データで処理時間がかかる関数の対処方法（SumIf）
大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、
そういう場合でも、多くの場合は何らかの対策があるものです、
個別のロジックの記述でこれらに対応する方法として、一つの有効なマクロVBAコ－ドについて解説します。
大量データで処理時間がかかるサンプルデータ
普通にマクロVBAコ－ドを書いた場合
指定範囲を絞ってみる
配列を使って書いてみる
アルゴリズムを考えてみる
Dictionary（連想配列）を使う
大量データで処理時間がかかる関数の対処方法の最後に
↑以上は2015年頃に公開した内容です。
XLOOKUP+SORTが最強だった … 後日追加（2025/11/7）
普通にマクロVBAコ－ドを書いた場合
指定範囲を絞ってみる
配列を使って書いてみる
アルゴリズムを考えてみる
Dictionary（連想配列）を使う
大量データで処理時間がかかる関数の対処方法の最後に
↑以上は2015年頃に公開した内容です。
XLOOKUP+SORTが最強だった … 後日追加（2025/11/7）
大量データで処理時間がかかるサンプルデータ
以下の例で解説します。
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でのものです。
他の要因を排除する意味と比較のしやすさの意味で、行数等は固定値にしています。
コードは、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 10001
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 10001
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:F10001")
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関数でも、同様の考え方で出来るのがご理解できますでしょうか。
もし、これらの関数で処理時間がかかっているようでしたら、ぜひ試してみて下さい。
この記事は、マクロ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で自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。
XLOOKUP+SORTが最強だった … 後日追加（2025/11/4）
=LET(
ab,SORT(A:.B),
a,INDEX(ab,,1),
b,INDEX(ab,,2),
d,D:.D,
XLOOKUP(d,a,b,NA(),0,2))
・SORT関数の書式 ・SORTBY関数の書式 ・SORT関数、SORTBY関数と、ワークシートの並べ替えの違い ・最も単純な並べ替え ・複数キーでの並べ替え ・列方向（横方向）で並べ替え ・並べ替え範囲（配列）以外の基準で並べ替える ・列全体を範囲指定する場合 ・スピルによって新しく追加された関数
XLOOKUP関数（VLOOKUP関数を拡張した新関数）・XLOOKUP関数の書式 ・従来の関数の代わりとして ・XLOOKUP関数をスピルさせる ・見つからない場合 ・一致モードの使い方 ・検索モードの使い方 ・XLOOKUP関数をネストして戻り列を可変にする ・XLOOKUP関数の戻りセル範囲を別の関数で使う ・XLOOKUP関数が縦横に同時にスピルしないことについて ・スピルと新関数の練習 ・XLOOKUP関数のVBA使用例 ・スピルによって新しく追加された関数
XLOOKUPで作成する前には、以下のような数式を書いてみました。
=LET(a,A:.A,b,B:.B,d,D:.D,
MAP(SCAN(0,d,
LAMBDA(x,y,
LET(xx,ABS(x),
ix,XMATCH(y,DROP(a,xx),0,2),
IFERROR(ix+xx,IF(x=0,-1,-xx)))
)
),
LAMBDA(z,IF(z<0,NA(),INDEX(b,z,1)))
)
)しかし、この数式にSORTを組み込むと計算が終わらなくなってしまいます・・・
（LAMBDA内のXMATCHやINDEXでSORTした配列を使うと遅くなってしまう…原因がはっきりしません。）
しかも、エラー時の対応も含めて、全てXLOOKUPだけで解決してしまいます。
XLOOKUP+SORTが最強です！
同じテーマ「マクロVBA技術解説」の記事
VBAのFindメソッドの使い方には注意が必要です
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
Withステートメントの実行速度と注意点
IfステートメントとIIF関数とMax関数の速度比較
スピルって速いの?スピルの速度について
1次元配列の下限インデックスを高速に変更する関数
新着記事NEW ・・・新着記事一覧を見る
電卓とプログラムと私｜エクセル雑感(2025-12-30)
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン｜エクセル関数応用(2025-12-25)
2段階の入力規則リスト作成：最新関数対応｜エクセル関数応用(2025-12-24)
IFS関数をVBAで入力するとスピルに関係なく「@」が付く現象について｜VBA技術解説(2025-12-23)
数値を記号の積み上げでグラフ化する（■は10、□は1）｜エクセル練習問題(2025-12-09)
AI時代におけるVBAシステム開発に関する提言｜生成AI活用研究(2025-12-08)
GrokでVBAを作成：条件付書式を退避回復するVBA｜エクセル雑感(2025-12-06)
顧客ごとの時系列データから直前の履歴を取得する｜エクセル雑感(2025-11-28)
ちょっと悩むVBA厳選問題｜エクセル雑感(2025-11-28)
実績/予算ごとの3年間通算累計を出力｜エクセル練習問題(2025-11-15)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
7.繰り返し処理（For Next)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.条件分岐（Select Case）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 大量データで処理時間がかかる関数の対処方法（SumIf）
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.