SUBTOTAL関数の使い方｜フィルタで絞り込んだ分だけ集計する

SUBTOTALは、フィルタで絞り込んだ結果だけを集計する関数です。

SUM は隠れている行も足してしまいます。「フィルタで東京だけ表示したのに、合計が全社のまま」——これを解決するのがSUBTOTALです。

書式

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

第1引数に番号で集計方法を指定するのが特徴です。

集計方法の番号

1桁	3桁	集計内容
1	101	平均（AVERAGE）
2	102	数値の個数（COUNT）
3	103	空欄でない個数（COUNTA）
4	104	最大値（MAX）
5	105	最小値（MIN）
6	106	積（PRODUCT）
7	107	標本標準偏差（STDEV）
8	108	母標準偏差（STDEVP）
9	109	合計（SUM）
10	110	標本分散（VAR）
11	111	母分散（VARP）

実務で使うのは 9 と 109（合計）、次いで 3 と 103（件数）がほとんどです。

1桁と3桁の違い

ここが最も重要です。

	フィルタで隠れた行	手動で非表示にした行
1桁（9など）	除外する	含める
3桁（109など）	除外する	除外する

つまり、

• – フィルタだけ使うなら、どちらでも同じ結果
• – 行を右クリックで非表示にすることがあるなら 3桁

迷ったら 109（3桁）を使ってください。 「見えている行だけ」という直感に一致します。

基本の使い方

	A	B
1	支店	売上
2	東京	1200
3	大阪	800
4	東京	1500

=SUBTOTAL(109, B2:B4)

フィルタ無しなら 3500。フィルタで「東京」だけ表示すると 2700 に変わります。

数式を書き換えていないのに結果が変わる——これがSUBTOTALの働きです。

件数を数えるなら次の形です。

=SUBTOTAL(103, A2:A4)

SUMとの使い分け

やりたいこと	使う関数
常に全件の合計	SUM
表示中の行だけの合計	SUBTOTAL(109, …)
条件で絞った合計（フィルタ不要）	SUMIF / SUMIFS

フィルタを使う表では、集計行は必ずSUBTOTALにしてください。 SUMのままだと、絞り込んでも数字が変わらず、見る人が誤解します。これは実務で頻繁に起きるミスです。

小計が二重計上されない仕組み

SUBTOTALには、もうひとつ重要な性質があります。

範囲内に他のSUBTOTALがあると、それを無視します。

小計行をいくつも挟んだ表で、最後に総合計を出す場面を考えてください。

A列の途中に  =SUBTOTAL(109, B2:B10)   ← 小計
             =SUBTOTAL(109, B12:B20)  ← 小計
最終行に     =SUBTOTAL(109, B2:B21)   ← 総合計

普通の SUM で総合計を出すと、小計まで足してしまい2倍になります。 SUBTOTALなら小計を自動で除外するので、正しい総合計が出ます。

小計を含む表では、すべてSUBTOTALで統一するのが定石です。

フィルタで隠れた行だけを数える

「絞り込みで何件除外されたか」を出したいなら、全件から表示件数を引きます。

=COUNTA(A2:A100) - SUBTOTAL(103, A2:A100)

表の一番上に集計を置く

集計行を表の上に置くと、フィルタをかけたときにスクロールせず確認できます。

ただし、集計行が範囲に含まれないよう注意してください。表が2行目から始まるなら、集計は1行目に置き、範囲は A2:A1000 とします。

なお、集計行を表の上に置くと、フィルタの範囲に含まれてしまうことがあります。フィルタは表の見出し行から設定してください。

Excelとの違い

SUBTOTALは共通です。 番号の意味も1桁/3桁の違いも同じです。

違いは代替手段です。

	Excel	スプレッドシート
AGGREGATE（エラー無視＋非表示除外）	ある	ない
QUERY	ない	ある

Excelの AGGREGATE は、SUBTOTALの機能に加えてエラー値を無視できます。範囲に #N/A が混ざっていてもエラーになりません。スプレッドシートにはこれがないので、元のエラーを IFERROR で潰しておく必要があります。

一方スプレッドシートでは、そもそもフィルタを使わずに QUERY や FILTER で絞り込む書き方が使えます。

=SUM(FILTER(B2:B100, A2:A100="東京"))

画面のフィルタ状態に依存しないぶん、こちらのほうが再現性は高いです。人に渡すファイルではこちらを検討する価値があります。

まとめ

• – 書式は =SUBTOTAL(集計方法, 範囲)
• – 合計は 109、件数は 103 を覚えておけば足りる
• – 1桁は手動非表示を含み、3桁は除外する。迷ったら3桁
• – フィルタを使う表の集計行は、必ずSUBTOTALにする
• – 範囲内の他のSUBTOTALを無視するので、小計と総合計が二重にならない

関連する関数

• – SUM — 常に全件を合計する
• – SUMIF — 条件で絞った合計（フィルタ不要）
• – FILTER — 条件に合う行を抽出する
• – QUERY — 絞り込みと集計をまとめて書く
• – 関数一覧に戻る