はじめに
Excelでデータを集計する際、フィルタをかけて絞り込みながら「合計」や「平均」を出したい場面は多いですよね。
しかし、通常の SUM関数 を使うと、非表示になっている行まで計算に含まれてしまいます。
そこで役立つのが SUBTOTAL関数 です。
SUBTOTAL関数なら「表示されているデータだけ」や「フィルタ後の値だけ」を対象に計算でき、分析やレポート作成に大変便利です。
SUBTOTAL関数の基本
書式
=SUBTOTAL(集計方法, 範囲1, [範囲2], …)
- 集計方法 : どの計算を行うかを指定する番号
- 範囲 : 集計対象となるセル範囲
集計方法の番号と対応内容
| 番号 | 内容 | 非表示セルを含む | フィルタで非表示は含まない |
|---|---|---|---|
| 1 | 平均 | あり | 101で指定すると除外 |
| 2 | 個数 | あり | 102で指定すると除外 |
| 3 | 数値の個数 | あり | 103で指定すると除外 |
| 9 | 合計 | あり | 109で指定すると除外 |
👉 一般的によく使うのは 9（合計） と 109（フィルタ後の合計） です。
具体例：フィルタ後の合計を計算
SUM関数の場合
=SUM(B2:B10)
→ フィルタで非表示にした行も合計される。
SUBTOTAL関数の場合
=SUBTOTAL(109, B2:B10)
→ フィルタで表示されているデータだけ合計される。
平均や件数にも活用可能
平均を求める
=SUBTOTAL(101, B2:B10)
→ フィルタ後の値だけを平均。
件数を数える
=SUBTOTAL(103, B2:B10)
→ 数値が入力されているセルだけをカウント。
応用テクニック
テーブル機能と組み合わせる
Excelの「テーブル」と併用すると、フィルタを切り替えるだけでSUBTOTAL関数が動的に集計結果を更新してくれる。
複数範囲の集計
=SUBTOTAL(109, B2:B10, D2:D10)
複数列をまとめて合計することも可能。
注意点
- 手動で非表示にした行 は「除外」扱いにならない場合がある
- 集計方法の番号を間違えると意図しない結果になる
- ピボットテーブルを使ったほうが柔軟なケースもある
まとめ
- SUBTOTAL関数は「表示されているデータだけ」を対象に計算できる
- 代表的なのは 109（合計）、101（平均）、103（数値の個数）
- テーブルやフィルタと組み合わせると効率的
- 大規模集計ではピボットテーブルも検討
コメント