VBAで自動化 VBA一覧 フィルター結果の取得・集計 抽出・検索処理

【VBA】SUBTOTALを使いこなす方法｜フィルター対応の自動集計を完全解説

Excelでデータを集計するとき、最もよく使われるのが「SUM関数」ですが、
実務でフィルターを使う場合、非表示の行まで集計してしまうという弱点があります。

そこで登場するのが「SUBTOTAL関数」。
SUBTOTALは、フィルターで非表示になっている行を自動的に除外して集計してくれる便利な関数です。

VBAを使えば、このSUBTOTALを自動で設定・計算させることができ、
毎月の売上集計や部署別のレポート作成などをワンクリックで行えるようになります。

この記事では、VBAでSUBTOTALを活用するための仕組み・構文・応用例をわかりやすく解説します。

✅ SUBTOTAL関数とは？SUMとの違いを理解しよう

まず、SUBTOTALの基本を押さえましょう。

構文

=SUBTOTAL(集計方法, 範囲)

例：合計を求める場合

=SUBTOTAL(9, C2:C100)

この式では、C2:C100の合計を求めますが、
オートフィルターで非表示になった行は自動的に除外されます。

SUMとの違い

関数非表示行フィルター非表示行合計対象
SUM含まれる含まれるすべての行
SUBTOTAL除外可能除外される表示行のみ

ポイント

SUBTOTALは単なる合計だけでなく、平均・最大値・件数なども求められます。
集計方法は引数の番号で指定します。

集計方法番号
平均1
件数2
最大値4
最小値5
合計9

実務でよく使う式

=SUBTOTAL(9, C2:C100)   '合計
=SUBTOTAL(1, C2:C100)   '平均
=SUBTOTAL(2, C2:C100)   '件数
 参考：【Excel】引き算に使える関数名まとめ｜実務で役立つ使い方と注意点も解説

✅ VBAでSUBTOTALを設定する方法

VBAでSUBTOTAL関数をセルに自動入力することができます。
「フィルター後の表示データだけを集計する」処理を自動化したいときに非常に便利です。

コード例：SUBTOTALを自動入力

Sub InsertSubtotalFormula()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    ' データ範囲の最終行を取得
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' C列の合計をSUBTOTAL関数で計算
    ws.Range("C" & lastRow + 1).Formula = "=SUBTOTAL(9, C2:C" & lastRow & ")"
    MsgBox "SUBTOTAL関数を自動で入力しました。"
End Sub

解説

このマクロを実行すると、C列の下にSUBTOTAL関数が自動入力されます。
その後、オートフィルターをかけても、表示行だけの合計が常に反映されます。

実務での使いどころ

  • 月次レポートの売上合計を自動更新
  • 部署別データの集計セルを自動挿入
  • 抽出条件を変えても集計結果が即時反映

✅ SUBTOTALをVBAで直接計算する方法（関数を使わない）

SUBTOTAL関数をシートに入力せずに、VBAで計算結果だけを取得することも可能です。
これは、結果をメッセージボックスや別シートに表示したいときに便利です。

コード例：可視セルの合計を取得

Sub GetSubtotalValue()
    Dim rng As Range
    Dim total As Double

    ' フィルター済みのデータ範囲を設定
    On Error Resume Next
    Set rng = Range("C2:C100").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    ' 可視セルの合計を計算
    If Not rng Is Nothing Then
        total = Application.WorksheetFunction.Subtotal(9, rng)
        MsgBox "フィルター後の合計は " & total & " です。"
    Else
        MsgBox "対象データがありません。"
    End If
End Sub

解説

  • WorksheetFunction.Subtotal(9, rng) … VBAからSUBTOTAL関数を呼び出し
  • 9 は「合計」
  • フィルター結果に応じて合計が自動的に変わる

メリット

✅ 複数列のSUBTOTALをまとめて自動挿入する

複数の列で同時にSUBTOTALを出したい場合、ループを使って自動化できます。

コード例：複数列にSUBTOTALを挿入

Sub InsertSubtotalMulti()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim col As Variant

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 集計したい列の番号を配列で指定
    For Each col In Array(3, 4, 5)
        ws.Cells(lastRow + 1, col).Formula = "=SUBTOTAL(9," & _
            ws.Cells(2, col).Address(False, False) & ":" & _
            ws.Cells(lastRow, col).Address(False, False) & ")"
    Next col

    MsgBox "複数列にSUBTOTALを自動設定しました。"
End Sub

解説

  • Array(3,4,5) → C列、D列、E列を指定
  • .Formulaで数式を動的に入力
  • 最終行の下に自動で合計行を追加

実務例

  • 売上・利益・数量の3列を同時に合計
  • 日次・週次レポートの合計行を自動生成
  • 一括出力レポートの自動更新



✅ SUBTOTALをフィルター操作と組み合わせる

VBAでは、SUBTOTALとオートフィルターを組み合わせて、
「表示中データだけを自動で集計」するマクロも簡単に作れます。

コード例：フィルター＋SUBTOTAL連動

Sub FilterAndSubtotal()
    Dim ws As Worksheet
    Dim rng As Range
    Dim total As Double

    Set ws = ActiveSheet

    ' 部署が「営業部」のみ表示
    ws.Range("A1:C100").AutoFilter Field:=2, Criteria1:="営業部"

    ' 表示中セルを取得
    On Error Resume Next
    Set rng = ws.Range("C2:C100").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    ' SUBTOTALで合計を取得
    If Not rng Is Nothing Then
        total = Application.WorksheetFunction.Subtotal(9, rng)
        MsgBox "営業部の合計は " & total & " です。"
    Else
        MsgBox "営業部のデータはありません。"
    End If

    ws.AutoFilterMode = False
End Sub

処理の流れ

  1. フィルターを設定して特定の条件で抽出
  2. 表示されているデータだけをSpecialCellsで取得
  3. SUBTOTALで可視セルの合計を計算

実務での活用シーン

  • 部署・店舗・商品カテゴリ別の売上集計
  • フィルター切り替えに連動するダッシュボード集計
  • 月次処理で条件抽出後に自動集計を実行

✅ VBAでSUBTOTALを削除・再計算する方法

SUBTOTALを使ったマクロでは、繰り返し処理のたびに古い合計が残ることがあります。
その場合は、削除して再計算を行う仕組みを追加しましょう。

削除＋再挿入の例

Sub RefreshSubtotal()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    ' 既存のSUBTOTALセルを削除
    ws.Cells.Replace What:="=SUBTOTAL*", Replacement:="", LookAt:=xlPart

    ' 再計算して挿入
    ws.Range("C101").Formula = "=SUBTOTAL(9, C2:C100)"
    MsgBox "SUBTOTALを再計算しました。"
End Sub

応用

✅ よくあるトラブルと対策

トラブル内容原因解決方法
非表示行も集計されるSUBTOTALの引数が誤り9や1など「1～11」以外の番号を使わない
フィルター解除後も値が変わらない計算モードが手動Application.Calculateで再計算
SUBTOTALが複数残る古い数式を削除していない処理前にCells.Replaceで初期化
可視セルがない抽出結果0件On Error Resume Next＋If rng Is Nothingで制御

✅ まとめ：VBAでSUBTOTALを活用すれば、集計作業は自動化できる

  • SUBTOTALはフィルター対応の集計関数
  • VBAで .Formula を使えば動的に設定できる
  • WorksheetFunction.Subtotalで結果を直接取得可能
  • フィルターとの組み合わせで「表示中データだけ」を集計できる
  • 実務では売上・件数・平均など多様な集計に応用可能

VBAとSUBTOTALを組み合わせることで、
「フィルター後に自動で集計されるExcelレポート」を簡単に作成できます。
手作業で関数を挿入する必要がなくなり、毎月の報告資料づくりが驚くほどスムーズになります。

-VBAで自動化, VBA一覧, フィルター結果の取得・集計, 抽出・検索処理