「オートフィルタで絞り込んだのにSUMが全行を合計してしまう」「手動で非表示にした行だけ除外したい」——このジレンマを一発で解決するのがSUBTOTAL関数です。SUMやCOUNTと異なり、フィルターで絞り込まれた行や手動で非表示にされた行を自動的に無視して集計できます。
SUBTOTAL関数は「集計方法を番号で指定する」という独特の仕組みを持ちます。合計・平均・件数・最大値・最小値など11種類の集計を1つの関数で切り替えられるうえ、フィルター連動型（1～11）と手動非表示も除外する型（101～111）の2系統から選べます。ExcelテーブルのステータスバーにもSUBTOTALが内部的に使われており、業務でExcelを扱うなら必須の関数です。
本記事では、SUBTOTAL関数の基本構文・集計方法番号の全一覧・オートフィルタとの連携・手動非表示行の除外・テーブルとの組み合わせ・AGGREGATEとの比較・よくあるエラーの対処法・MOS Excel試験の出題ポイントを体系的に解説します。
SUBTOTAL関数の基本構文
SUBTOTALの構文は次の通りです。
=SUBTOTAL(集計方法, 参照1, [参照2], ...)| 引数 | 説明 | 省略 |
|---|---|---|
| 集計方法 | 1～11（フィルター除外のみ）または101～111（手動非表示も除外）の整数を指定する | 必須 |
| 参照1 | 集計対象のセル範囲（最大29範囲まで指定可能） | 必須 |
| 参照2… | 追加の集計範囲（複数の離れた範囲を指定する場合に使う） | 省略可 |
基本例：A2:A100の売上データのうち、フィルターで表示されている行だけを合計するには次のように書きます。
=SUBTOTAL(9, A2:A100)集計方法「9」がSUM（合計）に対応します。オートフィルタで行を絞り込むたびに、この数式は自動的に表示行だけの合計に更新されます。
集計方法番号の全一覧（1～11・101～111）
SUBTOTALで指定できる集計方法は2系統あります。
| 番号（フィルター連動） | 番号（手動非表示も除外） | 対応する集計 | 同等の関数 |
|---|---|---|---|
| 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 |
1～11と101～111の違い：1～11はオートフィルタで非表示になった行を除外しますが、手動で非表示にした行（行を右クリック→非表示）は含みます。101～111は手動非表示の行もカウントから外します。実務でよく使うのは合計の9・件数の3・平均の1の3つです。
オートフィルタとの連携：フィルター後に集計が自動更新される
SUBTOTALの最大の利点は、オートフィルタの絞り込みに連動して集計値が自動更新される点です。SUM関数では絞り込んでも全行が計算対象のままですが、SUBTOTALは表示されている行だけを集計します。
設定手順：
- 集計を表示したいセル（例：B1）に
=SUBTOTAL(9, B4:B1000)と入力する - データ範囲にオートフィルタを設定する（「データ」タブ→「フィルター」）
- フィルタードロップダウンで絞り込むと、B1の値が表示行の合計に自動更新される
この構成は「フィルター集計セル（上部）＋データ行（下部）」のレイアウトでよく使われます。集計セルをデータ範囲の外（上や下）に配置することで、フィルターの影響を受けずに計算式自体は常に安定します。なお、SUBTOTALはSUBTOTALが入っているセルを自動的に無視するため、集計範囲内に別のSUBTOTAL数式があっても二重計上になりません。
PR
今すぐ使えるかんたんbiz Excel関数＋組み合わせ 効率UPスキル大全 Copilot対応（技術評論社）
関数単体だけでなく組み合わせ活用まで実務目線でまとめた1冊。業務でそのまま使えるパターン集として手元に置きやすい内容です。
手動非表示行を除外する101～111番の使い方
グループ化（折りたたみ）や行の右クリック→非表示で隠した行を除外したい場合は、101番台を使います。
' 手動非表示行も除いた合計
=SUBTOTAL(109, C2:C100)
' 手動非表示行も除いた件数（空白以外）
=SUBTOTAL(103, C2:C100)使用場面の例：月次レポートで特定の行グループをグループ化して折りたたんだ状態の合計を表示したいとき。9ではグループ内の非表示行が含まれてしまいますが、109では折りたたんで非表示になった行も除外されます。
| シナリオ | 使用すべき番号 | 理由 |
|---|---|---|
| オートフィルタで絞り込んだ表示行だけを合計したい | 9（SUM相当） | フィルター非表示を除外すれば十分 |
| グループ化で折りたたんだ行も除外したい | 109（SUM相当） | 手動非表示まで除外する101番台を使う |
| フィルターとグループ化を両方使っている | 109 | 両方の非表示を確実に除外できる |
Excelテーブルとの組み合わせ
Excelのテーブル機能（「挿入」→「テーブル」）と組み合わせると、SUBTOTAL集計がさらに便利になります。テーブルで「集計行を表示する」を有効にすると、各列の下部に自動的にSUBTOTAL関数が挿入されます。
集計行の使い方：
- テーブル内のセルを選択し「テーブルデザイン」タブを開く
- 「集計行」チェックボックスをオンにする
- 集計行のセルをクリックし、ドロップダウンから「合計」「平均」「件数」等を選ぶ
- 選択した集計方法に対応するSUBTOTALが自動入力される
テーブルのフィルターで絞り込むと集計行の値も自動更新されます。テーブルに行を追加した場合も集計範囲が自動拡張されるため、範囲の更新漏れがなくなります。テーブルとSUBTOTALの組み合わせは「データ量が増え続けるリスト管理」の定番パターンです。
AGGREGATE関数との比較
Excel 2010以降ではSUBTOTALの上位版であるAGGREGATE関数が使えます。AGGREGATEはSUBTOTALにない機能を追加しています。
| 機能 | SUBTOTAL | AGGREGATE |
|---|---|---|
| フィルター行を無視 | ○ | ○ |
| 手動非表示行を無視 | ○（101番台） | ○（オプション指定） |
| エラー値を無視 | × | ○ |
| ネストされたSUBTOTAL/AGGREGATEを無視 | ○（自動） | ○（オプション指定） |
| LARGE/SMALL/MEDIAN等の拡張集計 | × | ○（19種類） |
| MOS試験の出題範囲 | ○ | △（一部バージョン） |
AGGREGATEの構文は=AGGREGATE(集計方法, オプション, 参照, [引数2])です。エラーが混在するデータでLARGE（上位N番目）を取得したいといった高度な集計にはAGGREGATEが適しています。ただしMOS試験の主要出題範囲はSUBTOTALであり、実務でも広くExcel 2007以降すべてで動作するSUBTOTALを優先して覚えることを推奨します。
実務シナリオ別の活用パターン
シナリオ1：部門別フィルターで売上合計を動的表示する
A列に部門名、B列に売上金額が入った一覧表の上部に合計表示セルを置き、部門ごとに絞り込むだけで合計が切り替わるようにします。
B1セル: =SUBTOTAL(9, B4:B1000) ' フィルター後の合計
A1セル: =SUBTOTAL(3, A4:A1000) ' フィルター後の件数フィルターで「営業部」を選択すると、B1に営業部だけの売上合計、A1に営業部の行数が表示されます。ピボットテーブルを使わずに「フィルター式サマリー行」が実現できます。
シナリオ2：グループ化（折りたたみ）した小計を除外して総合計を出す
各グループに小計行があり、さらに総合計を出したいとき、小計行もデータ行と同じ列にある場合は二重計上になります。SUBTOTALは範囲内に別のSUBTOTALがあると自動的にスキップするため、次のように書けます。
' 小計行にも同じSUBTOTAL関数を使えば、総合計に二重計上されない
小計行: =SUBTOTAL(9, B2:B5)
総合計: =SUBTOTAL(9, B2:B100) ' 小計行のSUBTOTALは自動除外されるこの性質を活用して「明細行にSUBTOTAL、小計行にSUBTOTAL、総合計にSUBTOTAL」という3層構造の集計表が作れます。SUM関数では小計を除外するために範囲を細かく指定する必要がありますが、SUBTOTALならすべて同じ範囲で指定できます。
シナリオ3：フィルター件数を元に進捗率を計算する
フィルター後の完了件数と全件数から進捗率を算出する構成です。
' D列に「完了」「未完了」が入っている場合
フィルター後の行数: =SUBTOTAL(3, D4:D1000)
全行数（参考）: =COUNTA(D4:D1000)
進捗率: =SUBTOTAL(3, D4:D1000) / COUNTA(D4:D1000)担当者でフィルターをかけると、その担当者が持つタスクの完了率が自動計算されます。
よくあるエラーと原因・対処法
| 症状・エラー | 主な原因 | 対処法 |
|---|---|---|
| #VALUE! | 集計方法に1～11・101～111以外の値が入っている | 集計方法を正しい整数（1～11または101～111）に修正する |
| フィルターしても合計が変わらない | SUM関数を使っていてSUBTOTALに変更していない | SUM→SUBTOTAL(9,…)に書き換える |
| 手動非表示行が合計に含まれる | 1～11番台を使っている | 101～111番台に変更する（例：9→109） |
| 二重計上される | データ範囲内のSUBTOTAL小計行をSUMで集計している | 総合計もSUBTOTALで計算する（自動除外機能が働く） |
| エラー値がある行で#VALUE!になる | SUBTOTAL自体はエラー値を無視できない | AGGREGATE関数（オプション6=エラー無視）に切り替える |
集計値がゼロになる場合のチェック手順：① フィルター解除して全行表示→ ② 集計方法番号が正しいか確認（合計=9）→ ③ 参照範囲が実際のデータ行を包含しているか確認→ ④ データ列に文字列が混入していないかCOUNT(範囲)で数値セル数を確認。
MOS Excel試験でのSUBTOTAL出題ポイント
MOS Excel 365＆2019では、データ管理と集計に関するスキル項目でSUBTOTALが出題されます。テーブルの集計行操作と組み合わせた出題が多い点が特徴です。
- 集計方法番号の対応：合計=9、件数=3、平均=1の3つは必ず暗記する。試験では「合計を求める数式を入力せよ」という問題でSUBTOTAL(9, …)の入力が求められる
- テーブルの集計行設定：テーブルで集計行を有効化し、列ごとに「合計」「平均」などの集計方法をドロップダウンで選択する操作は頻出
- フィルターとの連携確認：SUBTOTALを設定後、フィルターで絞り込んだ状態での集計値が正しいか検証する操作が問われる
- 1～11と101～111の違い：手動非表示行を除外するケースでは101番台を選ぶことを問う問題がある
- SUBTOTAL範囲内のSUBTOTAL除外：小計行を含む範囲で総合計を求める際、二重計上にならないことを確認する問題が出ることがある
MOS試験 SUBTOTAL関連チェックリスト
| 確認ポイント | 操作内容 | 難易度 |
|---|---|---|
| 基本構文の入力 | =SUBTOTAL(9, B2:B100)を正しく入力できる | ★☆☆ |
| 集計方法番号の選択 | 合計・件数・平均に対応する番号（9・3・1）を正しく指定する | ★★☆ |
| テーブル集計行の設定 | テーブルデザインタブから集計行を有効化し集計方法を選択する | ★★☆ |
| フィルター連動の確認 | フィルター前後でSUBTOTALの値が変化することを確認する | ★★☆ |
| 手動非表示行の除外 | 101番台を使って行のグループ化折りたたみ時も除外する | ★★★ |
| 小計行との組み合わせ | 小計行を含む範囲でSUBTOTALを使い二重計上にならないことを確認する | ★★★ |
まとめ：SUBTOTALはフィルター集計の基本関数
本記事のポイントをまとめます。
- SUBTOTALの役割：フィルターや非表示行を除いた行だけを集計する。SUM・AVERAGE・COUNTの代わりに使うことでフィルター連動集計が実現する
- 番号の2系統：1～11はオートフィルタで非表示の行を除外、101～111は手動で非表示にした行も除外する。合計は9または109
- SUBTOTALの自動除外：集計範囲内に別のSUBTOTALがあっても二重計上されない。小計行をまとめる総合計も同じ範囲で安全に計算できる
- テーブルとの相性：テーブルの集計行はSUBTOTALが内部使用されており、行の追加・フィルターに自動対応する
- AGGREGATEへの拡張：エラー無視・LARGE/MEDIANが必要な場合はAGGREGATEに移行する。MOS試験はSUBTOTALが主要範囲
- MOS試験の頻出操作：合計(9)・件数(3)・平均(1)の番号対応、テーブル集計行の設定、フィルター連動の確認が中心
SUBTOTAL関数を習得すると、フィルターを多用する業務集計表で「絞り込むたびに電卓で再計算する」作業がゼロになります。売上レポート・タスク管理・在庫一覧など、日々フィルターをかける表であれば必ずSUBTOTALへの置き換えを検討してください。MOS試験対策としては、テーブル集計行の操作とフィルター連動の動作確認をセットで練習することが合格への近道です。
