Excel VBA でフィルター抽出範囲のみを集計する方法【Subtotal 関数と Sum 関数を比較】

目次

概要

オートフィルターで抽出された可視セルだけを集計したい場合には、WorksheetFunction.Subtotal が最適です。本記事では、抽出範囲の合計値とワークシート全体の合計値を同時に取得するサンプルコードを示し、SubtotalSum の違いを丁寧に解説いたします。


前提条件

項目内容
対応 ExcelMicrosoft 365 または 2016 以降
データ列列 E(売上金額など数値列を想定)
マクロ設置先標準モジュール

サンプルコード(VBA)

Sub CalculateFilteredTotals()

    Dim visibleTotal As Double      ' 抽出範囲の合計
    Dim overallTotal As Double      ' 全体範囲の合計
    Dim tgtColumn    As Range       ' 対象列
    
    ' 列 E を対象列として設定
    Set tgtColumn = ActiveSheet.Columns("E")
    
    ' 9 = SUM を意味する集計コード
    visibleTotal = WorksheetFunction.Subtotal(9, tgtColumn)
    
    ' フィルター無視の合計
    overallTotal = WorksheetFunction.Sum(tgtColumn)
    
    MsgBox "抽出範囲の合計: " & visibleTotal & vbCrLf & _
           "全体範囲の合計: " & overallTotal, _
           vbInformation, "合計値の比較"

End Sub

コードのポイント

説明
9Subtotal(9, 対象範囲) でフィルター後に可視状態のセルのみ合計を取得しています。
12Sum 関数はフィルターの有無にかかわらず列全体を集計します。
14MsgBox で抽出範囲の合計と全体範囲の合計を並べて表示し、差異を一目で確認できます。

Subtotal と Sum の違い

項目Subtotal (9)Sum
非表示行(手動)無視集計対象
フィルターで非表示無視集計対象
数式サポートありあり
主な用途フィルター後の集計全体集計

応用例

目的実装のヒント
行数を取得したい集計コード 3 (COUNTA) を使用します。
平均値を取得したい集計コード 1 (AVERAGE) を使用します。
結果をセルへ書き出すRange("H2").Value = visibleTotal のようにセルへ直接転記します。

よくある質問

質問回答
数値以外の列でも Subtotal は利用できますか。カウント系(COUNT、COUNTA など)の集計コードを指定すれば利用できます。
テーブル(ListObject)でも同じコードを使えますか。ListObject.DataBodyRange.Columns("売上") のように対象範囲を指定すると同様に動作します。

まとめ

Subtotal はフィルターや手動の非表示行を自動で除外して集計できるため、抽出結果のみの合計値を取得する場面で非常に便利です。一方、Sum はシート全体を対象とします。両者を併用して差異を確認することで、レポートの精度向上に役立ててください。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

私が勉強したこと、実践したこと、してることを書いているブログです。
主に資産運用について書いていたのですが、
最近はプログラミングに興味があるので、今はそればっかりです。

目次