「フィルターで絞り込んだのに、合計が全データのまま変わらない…」そんな経験はありませんか？
SUM関数はフィルターの状態に関係なく全行を合計します。表示中のデータだけを集計したいのに困りますよね。
そんなときに使えるのがSUBTOTAL関数です。フィルターで非表示にした行を自動的に除外して集計してくれます。この記事では、集計方法番号の一覧から実務での活用パターンまで紹介します。AGGREGATE関数との違いもまとめました。
SUBTOTAL関数とは？
SUBTOTAL関数（読み方: サブトータル関数）は、フィルターで非表示にした行を除外して集計できる関数です。
名前の「SUBTOTAL」は英語で「小計」を意味します。「集計方法番号」を切り替えることで、合計・平均・件数など11種類の集計を使い分けられます。
最大のメリットはフィルターとの連動です。通常のSUM関数はフィルターで行を隠しても全行を合計します。SUBTOTAL関数なら、表示されている行だけを集計してくれますよ。
SUBTOTAL関数にできることをまとめると、次のとおりです。
- フィルターで絞り込んだデータだけを合計・平均・カウントする
- 集計方法番号の切り替えで11種類の集計を使い分ける
- 範囲内の他のSUBTOTAL関数を無視する（二重集計を防ぐ）
- 手動で非表示にした行の扱いも制御できる
「フィルター付きの集計表」を作るなら、まず覚えたい関数です。
NOTE
SUBTOTAL関数はExcel 2003以降のすべてのバージョンで使えます。Microsoft 365でももちろん対応しています。
SUBTOTAL関数の書き方（構文と引数）
基本構文
=SUBTOTAL(集計方法, 参照1, [参照2], ...)カッコの中に「何で集計するか」と「どこを集計するか」を指定します。
引数の説明
| 引数 | 必須/任意 | 説明 |
|---|---|---|
| 集計方法 | 必須 | 集計に使用する関数を表す番号（1〜11 または 101〜111） |
| 参照1 | 必須 | 集計の対象となるセル範囲 |
| 参照2, … | 任意 | 追加の集計範囲（最大254個まで） |
ポイントは「集計方法」の番号です。この番号で合計・平均・件数などを切り替えます。
集計方法番号の一覧
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 |
よく使うのは 9（合計）・1（平均）・2（件数） の3つです。まずはこの3つを覚えておけば十分ですよ。
TIP
「合計の番号が覚えられない」という方へ。SUM は S で始まる = アルファベット19番目 → 9 と紐づけると忘れにくくなります。
SUBTOTAL関数の基本的な使い方
フィルターで絞り込んだデータの合計を求める
実際にSUBTOTAL関数を使ってみましょう。次のような売上データがあるとします。
| A列（担当者） | B列（部門） | C列（売上） | |
|---|---|---|---|
| 2行目 | 田中 | 営業部 | 50,000 |
| 3行目 | 佐藤 | 総務部 | 30,000 |
| 4行目 | 鈴木 | 営業部 | 80,000 |
| 5行目 | 高橋 | 総務部 | 25,000 |
| 6行目 | 伊藤 | 営業部 | 60,000 |
全データの合計を求めるには、次の数式を入力します。
=SUBTOTAL(9, C2:C6)集計方法番号 9 は「合計（SUM）」です。結果は 245,000 になります。
ここでB列のフィルターで「営業部」だけに絞り込んでみましょう。SUM関数なら値は変わりません。しかしSUBTOTAL関数は自動的に再計算されます。
フィルター後の結果: 190,000（50,000 + 80,000 + 60,000）フィルターに連動して合計値が変わるのがSUBTOTAL関数の強みです。
平均や件数に切り替える
集計方法番号を変えるだけで集計内容を切り替えられます。
=SUBTOTAL(1, C2:C6) → 平均: 49,000
=SUBTOTAL(2, C2:C6) → 数値の件数: 5
=SUBTOTAL(4, C2:C6) → 最大値: 80,000
=SUBTOTAL(5, C2:C6) → 最小値: 25,000フィルターで「営業部」に絞ると、営業部のデータだけで再計算されます。数式の番号を1つ変えるだけで対応できるのは便利ですよね。
SUBTOTAL関数の集計方法番号1-11と101-111の違い
SUBTOTAL関数には集計方法番号が2セットあります。どちらもフィルターで非表示にした行は除外します。違いは手動で非表示にした行の扱いです。
| 非表示の方法 | 1〜11 | 101〜111 |
|---|---|---|
| フィルターで非表示 | 除外する | 除外する |
| 手動で行を非表示（右クリック → 行の非表示） | 含める | 除外する |
| グループ化で折りたたみ | 含める | 除外する |
1〜11はフィルター非表示だけを除外します。101〜111はフィルター非表示に加えて、手動非表示やグループ化の折りたたみ行も除外します。
どちらを使うべき？
迷ったら 101〜111 を使うのがおすすめです。
「表示されている行だけを集計したい」という目的なら、非表示の方法に関係なく除外するほうが直感的ですよね。
ただし、手動で非表示にした行も計算に含めたい場面もあります。たとえば「内訳行を折りたたんで見やすくしているが、集計には含めたい」というケースです。こういった場面では1〜11が正解ですよ。
TIP
覚え方のコツは「100番台 = 見えない行はすべて除外」です。フィルターだけでなく手動非表示もまとめて除外したいなら、100を足すだけですよ。
SUBTOTAL関数の実務活用パターン
基本がわかったところで、仕事でよく使うパターンを紹介します。
パターン1: フィルター付きダッシュボード
フィルターで部門や月を切り替えながら集計値を確認する表です。次の5つの数式をフィルター付きの表の下に配置しましょう。
合計: =SUBTOTAL(109, D2:D100)
平均: =SUBTOTAL(101, D2:D100)
件数: =SUBTOTAL(102, D2:D100)
最大値: =SUBTOTAL(104, D2:D100)
最小値: =SUBTOTAL(105, D2:D100)フィルターを切り替えるたびにリアルタイムで値が更新されます。ちょっとしたダッシュボードとして使えますよ。
パターン2: 小計行がある表での総合計
部門ごとに小計行があるデータを考えてみましょう。SUM関数で総合計を出すと、小計が二重カウントされてしまいます。
| A列 | B列 |
|-------|------|
| 営業部 | 100 |
| 営業部 | 200 |
| 小計 | =SUBTOTAL(9, B2:B3) → 300 |
| 総務部 | 150 |
| 小計 | =SUBTOTAL(9, B5:B5) → 150 |
| 総合計 | =SUBTOTAL(9, B2:B6) → 450（正しい） |SUBTOTAL関数は範囲内の他のSUBTOTAL関数を自動的に無視します。そのため、小計行を含む範囲で総合計を出しても二重カウントされません。
SUM関数で同じ範囲を合計すると 300 + 200 + 100 + 150 + 150 = 900 になります。小計が重複して間違った値になるので注意してください。
パターン3: ステータスバーとの使い分け
Excelの画面下部にあるステータスバーには「合計」「平均」「データの個数」が表示されます。ステータスバーの値もフィルターで非表示にした行を除外してくれます。
では、なぜSUBTOTAL関数が必要なのでしょうか？
ステータスバーの値はセルに表示できません。計算結果を他の数式に使ったり、印刷に含めたりしたい場合はSUBTOTAL関数が必要です。「セルに結果を残したいならSUBTOTAL関数」と覚えておきましょう。
SUBTOTAL関数とAGGREGATE関数の違い・使い分け
Excel 2010以降では、SUBTOTAL関数の上位互換ともいえるAGGREGATE関数が使えます。両者の違いを比較してみましょう。
| 比較項目 | SUBTOTAL | AGGREGATE |
|---|---|---|
| 集計方法の数 | 11種類 | 19種類（PERCENTILEやLARGEなど追加） |
| エラー値の無視 | できない | できる（オプション6） |
| 非表示行の除外 | できる | できる |
| ネストされたSUBTOTALの無視 | できる | できる |
| 対応バージョン | Excel 2003〜 | Excel 2010〜 |
| 構文の複雑さ | シンプル | やや複雑（オプション引数あり） |
AGGREGATE関数の構文
=AGGREGATE(集計方法, オプション, 参照, [順位])AGGREGATE関数は第2引数に「オプション」があります。エラー値の無視や非表示行の除外を細かく制御できます。
| オプション | 動作 |
|---|---|
| 0 | ネストされたSUBTOTAL・AGGREGATE関数を無視 |
| 1 | 非表示の行 + ネスト関数を無視 |
| 2 | エラー値 + ネスト関数を無視 |
| 3 | 非表示の行 + エラー値 + ネスト関数を無視 |
| 4 | 何も無視しない |
| 5 | 非表示の行を無視 |
| 6 | エラー値を無視 |
| 7 | 非表示の行 + エラー値を無視 |
どちらを使うべき？
使い分けの判断基準はシンプルです。
- フィルター連動の集計だけでOK → SUBTOTAL関数がシンプルでおすすめ
- エラー値を含むデータを集計したい → AGGREGATE関数（オプション6）
- LARGEやPERCENTILEをフィルター連動で使いたい → AGGREGATE関数
- Excel 2003〜2007環境で使う可能性がある → SUBTOTAL関数
実務では「エラー値がある列の集計」にAGGREGATE関数が活躍します。たとえばVLOOKUP関数の#N/Aエラーが混在する列です。SUBTOTAL関数はエラーになりますが、AGGREGATE関数なら問題なく集計できますよ。
=AGGREGATE(9, 6, C2:C100) → エラー値を無視して合計
=SUBTOTAL(9, C2:C100) → エラー値があると #VALUE! になるAGGREGATE関数はSUBTOTAL関数より構文が複雑です。フィルター連動の合計・平均・件数だけなら、SUBTOTAL関数のほうが読みやすくておすすめですよ。
SUBTOTAL関数のよくあるエラーと対処法
SUBTOTAL関数で困ったときの原因と対策をまとめました。
| エラー/症状 | 原因 | 対処法 |
|---|---|---|
| #VALUE! | 集計方法番号が範囲外（0、12〜100、112〜） | 1〜11 または 101〜111 を指定する |
| #VALUE! | エラー値を含むセルがある | AGGREGATE関数に置き換えるか、IFERROR関数で事前にエラーを処理する |
| 結果が0 | 範囲に数値がない（文字列型の数値） | VALUE関数やデータクリーンアップで数値に変換する |
| フィルターしても値が変わらない | SUM関数を使っている | SUBTOTAL関数に置き換える |
| 小数点以下が出る | 集計方法番号1（AVERAGE）の仕様 | ROUND関数で丸める |
集計方法番号の入力ミス
最も多いミスが集計方法番号の入力間違いです。
=SUBTOTAL(0, C2:C10) → #VALUE! エラー
=SUBTOTAL(12, C2:C10) → #VALUE! エラー集計方法番号は1〜11または101〜111です。0やそれ以外の数値を指定すると #VALUE! エラーになります。合計なら 9 か 109、件数なら 2 か 102 を使いましょう。
フィルターしても合計値が変わらない
SUBTOTAL関数ではなくSUM関数を使っていないか確認してください。SUM関数はフィルターの状態に関係なく全行を合計します。
=SUM(C2:C10) → フィルターに連動しない
=SUBTOTAL(9, C2:C10) → フィルターに連動する既存の集計表をフィルター対応にしたい場合は、SUM関数をSUBTOTAL関数に置き換えるだけで対応できますよ。
似た関数との違い・使い分け
SUBTOTAL関数と似た機能を持つ関数を一覧で比較します。
| 関数名 | フィルター連動 | 条件指定 | エラー無視 | 集計の種類 |
|---|---|---|---|---|
| SUM | しない | できない | できない | 合計のみ |
| SUMIF | しない | 1条件 | できない | 合計のみ |
| SUMIFS | しない | 複数条件 | できない | 合計のみ |
| SUBTOTAL | する | できない | できない | 11種類 |
| AGGREGATE | する | できない | できる | 19種類 |
SUM関数との使い分け
最も重要な違いは「フィルター連動」の有無です。
- フィルターを使わない集計 → SUM関数がシンプルで最適
- フィルターで絞り込みながら集計 → SUBTOTAL関数が必須
- 小計と総合計がある表 → SUBTOTAL関数で二重集計を防止
「フィルターを使うならSUBTOTAL、使わないならSUM」と覚えるのが一番シンプルです。
条件を指定して集計したい場合はSUMIF関数やSUMIFS関数を使いましょう。
まとめ
ExcelのSUBTOTAL関数のポイントをおさらいしましょう。
| 項目 | 内容 |
|---|---|
| 読み方 | サブトータル関数 |
| 役割 | フィルター連動の集計（合計・平均・件数など11種類） |
| 構文 | =SUBTOTAL(集計方法, 参照1, ...) |
| よく使う番号 | 9（合計）、1（平均）、2（件数） |
| 1〜11と101〜111の違い | 手動非表示行の扱い（含む / 除外） |
| SUM関数との違い | フィルター連動するかどうか |
| AGGREGATE関数との違い | エラー無視機能の有無、集計種類の数 |
まずは =SUBTOTAL(9, 範囲) でフィルター対応の合計から試してみてください。フィルターを切り替えるたびに値が変わるのを体験すると、便利さが実感できますよ。
