Excelで「合計」や「平均」を出すとき、SUMやAVERAGEを使っていませんか？
しかし、フィルタをかけたときに“表示されているデータだけ”を集計したいなら、SUBTOTAL関数が最適です。
SUBTOTAL関数は、**フィルタや非表示行に対応した“かしこい集計関数”**で、実務での集計・レポート作成に欠かせない関数の一つです。
この記事では、SUBTOTAL関数の基本構文から使い方、活用シーン、エラー対処、応用テクニックまでを網羅的に解説します。
1. はじめに：SUBTOTAL関数とは何か？
**SUBTOTAL関数（サブトータル関数）**は、SUM関数やAVERAGE関数のような集計関数と似ていますが、
- オートフィルタによる表示／非表示を考慮して集計
- 集計方法を選べる（合計・平均・件数・最大・最小など）
という特徴があります。
フィルタで絞り込んだデータだけを合計したい、テーブルの一部を非表示にしているときに正しい平均を求めたいといった場面で大活躍します。
2. SUBTOTAL関数の基本構文と意味
=SUBTOTAL(集計方法コード, 範囲)
| 引数 | 説明 |
| 集計方法コード | 合計・平均など、何を計算するかを数値で指定（下記参照） |
| 範囲 | 集計対象のセル範囲（例：B2:B100など） |
主な集計方法コード一覧
| コード | 内容 | 非表示行の扱い（※） |
| 1 | AVERAGE（平均） | 含める |
| 2 | COUNT（数） | 含める |
| 3 | COUNTA（非空数） | 含める |
| 9 | SUM（合計） | 含める |
| 101〜109 | 上記と同様 | 非表示行を除外 |
※行全体が非表示（行の高さ0）になっている場合に限り、100番台のコードは無視されます。フィルタで隠れている行は、どちらでも無視されます。
3. SUBTOTAL関数の使い方：基本パターン
● 合計（SUM）
=SUBTOTAL(9, B2:B100)→ B2:B100の合計を返します（表示・非表示どちらも含む）
● 平均（AVERAGE）
=SUBTOTAL(1, C2:C100)
→ 平均値を返します（非表示行も含む）
● 件数（COUNT）
=SUBTOTAL(2, A2:A100)→ 数値セルの個数をカウントします
● 非表示行を除外する場合（例：合計）
=SUBTOTAL(109, B2:B100)
→ フィルタで隠れた行や手動で非表示にした行を除外して合計
4. どんな時に使うか？活用シーン
✅ フィルタで絞り込んだデータだけを集計したいとき
→ 通常のSUMでは隠れたデータも含まれてしまいますが、SUBTOTALなら表示中のデータだけを集計できます。
✅ 非表示行を無視して計算したいとき
→ 決算資料やサマリーレポートで、不要行を一時的に非表示にしておくときに便利。
✅ テーブル形式で集計を自動表示したいとき
→ Excelの「テーブル機能（Ctrl+T）」と組み合わせれば、自動で合計行にSUBTOTALが挿入されます。
✅ グループごとの集計（データ→小計）にも使われる
→ 「データ」タブの「小計」機能は、実は内部的にSUBTOTAL関数を使っています。
5. 実践サンプル：SUBTOTAL関数の使いどころ
📌 フィルタで絞った売上の合計を表示
=SUBTOTAL(109, E2:E100)→ 商品や日付でフィルタしても、表示された売上のみを合計
📌 出勤表で出勤日（〇）の件数を数える
=SUBTOTAL(3, C2:C31)
→ 出勤マークが入っている日数をカウント（空白以外を数える）
📌 顧客リストで「VIP顧客のみ」の平均購入額
→ フィルタで「VIP」だけに絞り、下のセルに
=SUBTOTAL(1, D2:D100)
→ 表示中のD列（購入額）の平均値を算出
6. よくあるエラーとその対処法
| エラー/問題点 | 原因と対処 |
| 結果が想定と違う | 集計方法コードが間違っている（例：12など存在しない番号） |
| フィルタの影響を受けない | 1〜11番のコードを使っている → 100番台コードに変更する |
| 複数の範囲で集計できない | SUBTOTAL関数は範囲1つだけ → 複数集計したい場合はSUMと組み合わせ |
| セルをコピーしてズレる | 相対参照で範囲がズレる → 絶対参照（例：$B$2:$B$100）を検討 |
7. SUBTOTAL関数と他関数の組み合わせ技
✅ IFと組み合わせて条件付き集計に
=IF(A2="売上", SUBTOTAL(9, B2:B100), "")→ 条件に合った場合だけ合計を表示
✅ OFFSETやINDIRECTと組み合わせて範囲を可変に
=SUBTOTAL(9, OFFSET(B2, 0, 0, A1))
→ A1の値に応じて集計範囲の長さを変更
✅ テーブル機能と併用で「総計行」を自動管理
- テーブルで「合計行」をオンにすると、自動的にSUBTOTALが適用されます
- ユーザーが列ごとに集計方法を変更可能（合計、平均、件数など）
8. SUBTOTAL関数の応用テクニック
🔸 表のページごとに集計を入れる（印刷レポート向け）
→ ページの最後にSUBTOTAL関数を配置して、フィルタごとに小計を表示
🔸 データの動的なビュー切替
→ フィルタを使って担当者やカテゴリを切り替えながら、集計値も即座に反映
🔸 ショートカットで素早く集計フォーム作成
Alt→Shift→→→↓で小計挿入ウィザード起動- ある列でグループ化しながら小計を挿入できる
9. まとめ：SUBTOTAL関数で「見えているデータだけ」を正しく集計しよう
SUBTOTAL関数は、単なる合計や平均ではなく、
- 表示中のデータだけを集計
- 集計の種類を選択できる
- フィルタや非表示に強い
- 表・テーブルとの相性がよい
という点で、実務で非常に信頼できる集計関数です。
SUM関数ではすべての値を足してしまいますが、SUBTOTALを使えば、“今見せたいものだけ”を正確に反映した集計が可能になります。
Excelで集計業務をする方は、ぜひSUBTOTAL関数をマスターし、正確でスマートなシートづくりを実現してください！