サラリーマン戦士が語る、Excelで“正しい集計”を作るための必須テクニック

サラリーマンとして日々Excelと向き合っていると、 「フィルターで絞った結果だけ合計したい」 「非表示行を除外して平均を出したい」 「集計表が壊れないようにしたい」 「SUMでは正しい値にならない…」 といった場面が必ず出てきます。

そんな“実務で壊れない集計”を作るために欠かせないのが SUBTOTAL（サブトータル）関数 です。

SUBTOTALは、 フィルター後のデータだけ集計できる、Excel標準の集計関数 で、 SUM・AVERAGE・COUNT などを一つの関数で実行できます。

AGGREGATEほど高機能ではありませんが、 フィルター集計の標準解として最も使われる関数 です。

この記事では、SUBTOTAL関数の基本から応用まで、 サラリーマン戦士としての実務経験を交えながら 徹底解説します。

🔍 SUBTOTAL関数 の書式

コード

=SUBTOTAL(関数番号, 範囲)

✔ 引数の意味

• 関数番号：どの集計をするか（1〜11 / 101〜111）
• 範囲：集計対象

📘 SUBTOTALの「関数番号」一覧（実務で使うものだけ）

関数番号	内容
1 / 101	AVERAGE（平均）
2 / 102	COUNT（件数）
3 / 103	COUNTA（空白以外の件数）
9 / 109	SUM（合計）
4 / 104	MAX
5 / 105	MIN

✔ 1〜11 と 101〜111 の違い

番号	非表示行	フィルター行
1〜11	手動で非表示にした行も無視する	フィルターで非表示も無視
101〜111	手動非表示は無視しない	フィルターで非表示は無視

実務では 101〜111 を使うことが多い です。

🎯 基本例：フィルター後の合計（最も使う）

コード

=SUBTOTAL(109, B2:B100)

• 109 → SUM（フィルター行を無視）
• B2:B100 → 集計範囲

→ フィルターで絞った結果だけ合計できます。

🎯 基本例：フィルター後の平均

コード

=SUBTOTAL(101, C2:C100)

→ フィルターで絞ったデータだけ平均できます。

🔥 手動で非表示にした行も除外したい場合

コード

=SUBTOTAL(1, C2:C100)

1〜11 の番号を使うと、 手動で非表示にした行も無視できます。

🔥 フィルター × SUBTOTAL で“壊れない集計表”

例：売上表で、部署ごとにフィルターして合計を表示したい

コード

=SUBTOTAL(109, D2:D100)

→ 部署を絞るたびに自動で合計が変わります。

🧩 SUBTOTAL × OFFSET で“動的集計”

例：最新10件だけ合計

コード

=SUBTOTAL(109, OFFSET(A1, COUNTA(A:A)-10, 0, 10, 1))

OFFSETについては OFFSET関数 を参照ください。

🧩 SUBTOTAL × FILTER で柔軟な集計

例：営業部だけ抽出して合計

コード

=SUBTOTAL(109, FILTER(C2:C100, B2:B100="営業"))

FILTERについては FILTER関数 を参照ください。

📅 月次レポートで“フィルター後の平均”を出す

コード

=SUBTOTAL(101, E2:E100)

→ 月別にフィルターしても壊れない平均が作れます。

🧠 SUBTOTALが強い理由

✔ フィルター後の集計が正確

SUMではフィルター行も含まれてしまいますが、 SUBTOTALなら 表示されている行だけ 集計できます。

✔ SUM・AVERAGE・COUNT を1つの関数で実行

関数番号を変えるだけで集計方法を切り替えられます。

✔ ピボットテーブルより軽い

簡単な集計ならSUBTOTALのほうが速いです。

✔ AGGREGATEよりシンプル

AGGREGATEは高機能ですが、 SUBTOTALは フィルター集計に特化していて使いやすい です。

🛠 よくあるミスと注意点

❗ 1. 関数番号を間違える

SUM → 9 or 109 AVERAGE → 1 or 101

❗ 2. 手動非表示を無視したいのに 101〜111 を使ってしまう

→ 手動非表示も無視したいなら 1〜11 を使います。

❗ 3. 配列数式では使えない

SUBTOTALは配列計算に弱いです。

❗ 4. FILTER後の配列に使うと挙動が変わる

FILTERは「非表示」ではなく「削除」扱いになるため、 SUBTOTALの“非表示無視”は効きません。

📊 SUBTOTAL と AGGREGATE の違い

項目	SUBTOTAL	AGGREGATE
エラー無視	×	◎
上位・下位の取得	×	◎
フィルター後の集計	◎	◎
手動非表示の扱い	番号で変わる	番号で変わる
集計種類	11種類	19種類
実務向き	中級	最強

AGGREGATEについては AGGREGATE関数 を参照ください。

🧑‍💼 サラリーマン戦士が実際に使ってみた感想

サラリーマン戦士がSUBTOTALを使い始めたとき、 「フィルターで絞った結果だけ集計できるって、こんなに便利なのか…」 と本気で驚きました。

特にメリットは次のとおりです。

✔ フィルター後の集計が正確

SUMでは絶対にできない“表示行だけ集計”ができます。

✔ 集計方法を番号で切り替えられる

SUM・AVERAGE・COUNT を1つの関数で扱えるのは便利です。

✔ ピボットテーブルより軽い

簡単な集計ならSUBTOTALのほうが速く、資料もスッキリします。

✔ AGGREGATEよりシンプルで覚えやすい

エラー無視が不要ならSUBTOTALで十分です。

サラリーマン戦士として、 「フィルター集計の標準はSUBTOTAL」 と自信を持って言えます。

📝 まとめ：SUBTOTALは“フィルター集計の標準ツール”

• フィルター後のデータだけ集計できます
• SUM・AVERAGE・COUNT を1つの関数で実行できます
• 手動非表示を無視するかどうか選べます
• ピボットテーブルより軽くて便利です
• AGGREGATEよりシンプルで覚えやすいです

Excelで正確な集計を作るなら、 SUBTOTAL関数は必ず覚えておきたい関数です。