Excel VBAでSumIfが遅い…を解決！大量データをサクサク計算する方法

問題の整理：SumIfが遅い理由をやさしく理解

大量データで SumIf（SUMIF/SUMIFS）を使うと、計算やマクロの実行が遅くなります。理由は主に3つです。範囲が広いほど比較回数が増えること、同じ条件で何度も同じ検索をやり直していること、そしてシート上のセルに何度もアクセスして往復が発生することです。これらは少しずつ積み重なり、行数が増えるほど時間が急に伸びます。

また、数値と文字列の「見た目は同じでも型が違う」ケースも、内部で変換が走って遅くなることがあります。さらに、揮発性関数や再計算の設定が影響する場合もあります。まずは「どこで時間がかかっているか」を測り、比較できる土台を作ることが大切です。

この記事では、まず再現できるサンプルを用意し、素直な実装のベースラインを確認します。そのうえで、範囲を絞る、配列で一括読み込み、アルゴリズムの見直し、Dictionary によるグループ集計、そして数式側（LAMBDA）での代替まで、段階的に改善していきます。

なぜ件数が増えると急に遅くなるの？

比較や参照の回数は行数に比例して増えます。さらに同じ条件で繰り返し検索すると、回数が二重三重にふくらみます。これが「少し増えると急に遅い」の正体です。

サンプルデータと再現手順（計測の土台）

改善を比べるには、同じ条件で測ることが大切です。ここでは、10万行規模でも縮小できるように、列の構成と測定の手順をそろえます。列は「日付」「カテゴリ」「金額」「メモ」の4列を想定します。カテゴリごとに金額を合計するケースを例にします。

計測は Timer 関数で開始と終了の時刻を取り、差を秒で出します。初回実行はキャッシュが効かず遅いことがあるため、2回目の実行時間も記録し、双方をメモします。

“`vba

‘ 計測用の小さなヘルパー

Private Function Sec() As Double

    Sec = Timer

End Function

Private Sub LogTime(tag As String, ByRef t As Double)

    Debug.Print tag & “: ” & Format(Timer – t, “0.000”) & “s”

    t = Timer

End Sub

“`

乱数でカテゴリ（例: A〜E）と金額を入れたシートを用意し、各手法の時間を測ります。すべて同じブック、同じデータで比較します。

初回だけ遅い影響はどう切り分ける？

同じコードを続けて2回以上実行し、1回目と2回目の時間を分けて記録します。比較には2回目以降の値を使うと傾向が見えやすくなります。

素直なVBA（WorksheetFunction/Evaluate）の実装と課題

まずは分かりやすいベースラインです。WorksheetFunction.SumIf をループの中で何度も呼ぶと、式の評価とシート参照が繰り返されます。

“`vba

Sub Baseline_SumIf()

    Dim t As Double: t = Timer

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(“Data”)

    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Dim i As Long, cat As String, val As Double

    For i = 2 To lastRow

        cat = ws.Cells(i, 2).Value ‘カテゴリ

        val = Application.WorksheetFunction.SumIf(ws.Range(“B2:B” & lastRow), cat, ws.Range(“C2:C” & lastRow))

        ws.Cells(i, 5).Value = val ‘結果をE列に出力

    Next

    Debug.Print “Baseline_SumIf:”, Format(Timer – t, “0.000s”)

End Sub

“`

Evaluate で同等の式を評価する方法もありますが、結局は毎回同じ範囲に対して評価が走ります。どちらも「何度も同じことをしている」点がボトルネックです。

WorksheetFunction と Evaluate はどちらが速い？

環境や式の内容で差が変わります。重要なのは「呼ぶ回数を減らす」ことで、どちらを選ぶかよりも再評価の回数のほうが影響が大きいです。

範囲を絞る：最小必要範囲の特定

まず効くのは、対象範囲を必要最小限にすることです。A:A のような全列指定は簡単ですが、無駄が増えます。最終行を取得して、実データのある範囲だけを参照します。

“`vba

Function LastRow(ws As Worksheet, col As Long) As Long

    LastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row

End Function

Sub NarrowRange()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(“Data”)

    Dim lr As Long: lr = LastRow(ws, 2)

    Dim rngCat As Range, rngVal As Range

    Set rngCat = ws.Range(“B2:B” & lr)

    Set rngVal = ws.Range(“C2:C” & lr)

    ‘ ここから先の手法は rngCat / rngVal を使う

End Sub

“`

名前付き範囲を使って、更新のたびに端点を再設定する方法もあります。まずは「広すぎる範囲」を避けるだけで、目に見える改善になることが多いです。

全列指定と必要範囲指定の差は？

行数が多いほど差が広がります。全列指定は空セルも含むため、比較や走査に余計な時間がかかります。

配列一括読み込み：Variant配列＋一次ループ

次は、Range をそのまま何度も読むのをやめて、配列に一度だけ読み込みます。シートとVBAの間の行き来（COMの呼び出し）を減らすことで、大きく短縮できます。

“`vba

Sub ArrayScan()

    Dim t As Double: t = Timer

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(“Data”)

    Dim lr As Long: lr = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    Dim cats As Variant, vals As Variant

    cats = ws.Range(“B2:B” & lr).Value2

    vals = ws.Range(“C2:C” & lr).Value2

    Dim i As Long

    Dim result() As Double

    ReDim result(1 To UBound(cats, 1))

    ‘ 例：各行のカテゴリと一致する値の合計を逐次更新

    ‘ （実際には後述のDictionaryのほうが効率的）

    For i = 1 To UBound(cats, 1)

        If IsNumeric(vals(i, 1)) Then

            result(i) = result(i) + CDbl(vals(i, 1))

        End If

    Next

    Debug.Print “ArrayScan:”, Format(Timer – t, “0.000s”)

End Sub

“`

配列化すると、文字列の数値や空白の扱いなど、型の差が表に出ます。比較前に正規化（Trim、CStr、CDbl など）を行うと、結果が安定します。

配列でメモリ不足にならない？

行×列の要素数に比例してメモリを使います。数十万行でも通常は問題ありませんが、列が多い場合は必要な列だけに絞る、分割して読み込むなどで調整できます。

アルゴリズム再設計：同条件の再計算をゼロに

SumIf を何度も呼ぶ代わりに、「条件→合計」を一度だけ作ればよい場面が多いです。たとえば、カテゴリごとに合計を作っておき、必要なときにその表から取り出します。これで同じ計算を繰り返す無駄がなくなります。

ソートしてからまとめて走査する、あるいは条件ごとにバケット（箱）を用意して数字を入れていく、といった設計も効果的です。二重ループでも、ループの内側で重い操作（シート参照、式評価）をしないなら、十分実用的な速度になります。

“`vba

‘ キー（カテゴリ）ごとに合計をキャッシュする方針

Private Type SumCache

    Key As String

    Total As Double

End Type

“`

二重ループは常に悪い？

内側で重い処理をしなければ、二重ループでも速いことがあります。重要なのは「重い処理の回数」と「データのまとまり方」です。

Dictionaryでグループ集計：SumIf多発の置換

Scripting.Dictionary を使うと、「カテゴリ→合計」というマップを一度で作れます。キーが存在しなければ0で初期化し、値を足していきます。最後に結果をまとめて書き戻すことで、シート往復と重複計算を減らせます。

“`vba

Sub DictGroupSum()

    Dim t As Double: t = Timer

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(“Data”)

    Dim lr As Long: lr = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    Dim cats As Variant, vals As Variant

    cats = ws.Range(“B2:B” & lr).Value2

    vals = ws.Range(“C2:C” & lr).Value2

    Dim d As Object: Set d = CreateObject(“Scripting.Dictionary”)

    Dim i As Long, k As String, v As Double

    For i = 1 To UBound(cats, 1)

        k = CStr(cats(i, 1))

        If IsNumeric(vals(i, 1)) Then v = CDbl(vals(i, 1)) Else v = 0

        If d.Exists(k) Then

            d(k) = d(k) + v

        Else

            d.Add k, v

        End If

    Next

    ‘ 結果の書き戻し例（カテゴリ一覧を作ってからマッチさせるなど）

    ‘ ここでは簡単にイミディエイトに出力

    Dim key As Variant

    For Each key In d.Keys

        Debug.Print key, d(key)

    Next

    Debug.Print “DictGroupSum:”, Format(Timer – t, “0.000s”)

End Sub

“`

早期バインドを使う場合は、参照設定で「Microsoft Scripting Runtime」を有効にし、Dim d As New Scripting.Dictionary と書きます。遅延バインド（CreateObject）なら参照設定は不要です。

Dictionary と Collection の違いは？

Dictionary はキーの有無を調べやすく、キー一覧や存在確認が簡単です。Collection はキー検索が弱く、集計用途では Dictionary のほうが扱いやすいです。

LAMBDA＋ヘルパー関数：数式だけで再利用

最近の Excel では、LAMBDA や動的配列関数でグループ集計を表計算側だけで行えます。VBA を書けないチームでも共有しやすい利点があります。

例として、カテゴリ別の合計を作るヘルパーを組み合わせます。ユニークなカテゴリ一覧を出し、その横に合計列を並べます。

“`

=UNIQUE(B2:B100000)

=MAP(G2:G?,LAMBDA(k,SUMIF(B2:B100000,k,C2:C100000)))

“`

さらに LAMBDA 名を付けておけば、同じブック内で再利用できます。ただし、対象バージョンや関数の互換性に注意します。Power Query で同じことを作る選択肢もあり、更新や整形が中心ならそちらのほうが向く場合もあります。

Power Query とどちらを選ぶ？

頻繁に構造変換や結合、クレンジングが必要なら Power Query が便利です。数式でシート上に結果を直接出したい、学習コストを下げたい場合は LAMBDA＋動的配列が手軽です。

まとめ＆チェックリスト：実装順と落とし穴

まずはシンプルな順番で試します。

• 範囲を絞る（全列指定をやめる）
• 計測の土台を作る（ベースラインの時間を記録）
• 配列で一括読み込みしてシート往復を減らす
• アルゴリズムを見直し、同条件の再計算をゼロにする
• Dictionary で一括集計してから書き戻す
• 数式（LAMBDA/動的配列）や Power Query の活用も検討する

よくある落とし穴は、文字列と数値の型が混在して比較に失敗すること、隠れた再計算やイベントで時間がかかること、そして「測らずに感覚で判断する」ことです。必ず時間を測り、改善の前後を比べましょう。

それでも遅い時の最後の選択肢は？

処理を分割してバッチ化する、前処理でデータを小さくする、外部ツール（データベース、Power Query）の利用を検討します。必要なら計算量の多い部分を別のアプリで行い、Excel は表示と最終整理に集中させます。