【Excel】SUBTOTAL関数を使ったフィルター後のデータ集計

Excelでデータをフィルターする場合、通常の合計関数（SUM）などを使うと、非表示の行（フィルターで除外された行）まで集計に含まれてしまいます。そこで活用できるのが SUBTOTAL関数 です。ここでは、フィルターを設定した表で表示内容だけを集計する方法を解説します。

SUBTOTAL関数とは？

SUBTOTAL関数 は、表やリストなどの部分的な集計（小計）を取るための専用関数です。引数に「集計方法」と「範囲」を指定する形式で、合計・平均・最大値・最小値・個数などをフィルターの状態に合わせて動的に計算できます。

書式は以下のようになります：

=SUBTOTAL(集計方法, 範囲1[, 範囲2, ...])

集計方法のコード	関数の内容	非表示行への対応
1	AVERAGE	○
2	COUNT	○
3	COUNTA	○
4	MAX	○
5	MIN	○
9	SUM	○
101	AVERAGE	×
102	COUNT	×
…	…	×

※ 1ケタのコード（1～11）と 100 を足したコード（101～111）では、手動で非表示にした行を含めるかどうか が異なります。1ケタの場合は「非表示行を除外」するのに対し、100番台は「手動で非表示にした行も集計対象」となります。

フィルター後の合計を出す例

1. データ範囲を用意 売上データや在庫データなど、複数行にわたるリストを想定します。
2. フィルターを設定
   • データの見出し行を選択し、メニューの「データ」タブ → 「フィルター」をクリック
   • 絞り込みたい条件を指定
3. 集計セルを用意
   • フィルターを設定したいデータ範囲外のセル（例えば最下部）にカーソルを合わせる
   • =SUBTOTAL(9, A2:A100) のように入力（9はSUMを意味する）
   • これで、現在フィルターで表示されている行の合計が得られます

例：売上金額の合計を求める

=SUBTOTAL(9, D5:D100)

D列に売上金額が入っているとすると、9(SUM)を使うことで合計を計算します。フィルターを操作するたびに表示行だけで合計を再計算してくれます。

他の集計方法もフィルター対応

• 平均: =SUBTOTAL(1, 範囲)
• 最大値: =SUBTOTAL(4, 範囲)
• 最小値: =SUBTOTAL(5, 範囲)
• 件数（数値のみ）: =SUBTOTAL(2, 範囲)
• 件数（空欄以外をカウント）: =SUBTOTAL(3, 範囲)

これらはすべてフィルターを考慮してくれるため、簡単に部分集計が可能です。

100番台コードの使い分け

SUBTOTAL(109, 範囲) など、集計方法のコードが100番台の場合は「手動で非表示にした行」も集計に含まれます。もし行を手動で「非表示」に設定して除外したいケースなら、1ケタのコードを使いましょう。逆に、手動で非表示にしている行も計算したいなら100番台を使います。

• 9 (SUM) : フィルターで除外されている行＋手動で非表示にされている行を集計に含めない
• 109 (SUM) : フィルターで除外されている行は除外するが、手動で非表示にした行は含める

使用上の注意

1. テーブル機能との組み合わせ
   • Excelのテーブル機能に変換すると、自動で合計行を表示できるオプションがあります。そこでSUBTOTAL関数が内部的に使用されるため、同じロジックで集計が行われます。
     • 対象のテーブルを選択した状態でテーブルタブを開き、「集計行」を選択
       
       
2. セル範囲の拡張に注意
   • データ数が増えるとき、範囲指定を動的にするか、テーブル機能にしておくと更新が楽です。
3. フィルターの複数条件にも対応
   • SUBTOTAL関数はどんな条件でフィルタしても表示行のみを集計するため、複雑な抽出条件にも自動対応できます。

まとめ

SUBTOTAL関数を使うと、フィルターによって表示されている行だけを合計・平均・最大値などで集計できます。標準的なSUM関数だと非表示行も含めて計算してしまいますが、SUBTOTALなら手動で非表示にした行やフィルターによる除外を意識してくれる点が大きな強み。

「部分的に絞り込んで、その合計を一瞬で出したい」 というシーンはExcelでよくあります。ぜひSUBTOTAL関数を活用して、迅速かつ正確な集計を実現してみてください。