INFO
※本記事の数式やコードは、Microsoft 365（旧Office365）にて動作検証を行っています。
ExcelのSUBTOTAL関数完全ガイド｜フィルターしても正確に集計できる万能関数
フィルターをかけたのに、合計がズレてる…。 非表示にした行まで計算されてしまう…。 そんな経験、ありませんか？
SUM関数は便利ですが、フィルターで絞り込んだ行だけを集計することができません。そこで登場するのが SUBTOTAL関数 です。
SUBTOTAL関数を使えば、フィルター中でも見えている行だけを正確に集計できます。しかも合計・平均・カウントなど、11種類の集計を1つの関数で切り替えられる超万能関数です。
目次
SUBTOTAL関数とは？何ができるの？
SUBTOTAL関数は、フィルターや非表示行を無視して、見えているセルだけを集計できる関数です。
通常のSUM関数との一番の違いは、フィルターをかけたときの挙動です。
| 比較項目 | SUM関数 | SUBTOTAL関数 |
|---|---|---|
| フィルター中の集計 | 非表示行も含めて計算 | 表示行だけを計算 ✅ |
| 手動で非表示にした行 | 含めて計算 | 集計番号によって選択可 |
| 集計の種類 | 合計のみ | 11種類を切り替え可 ✅ |
| SUBTOTAL同士の集計 | — | 二重カウントを自動で除外 ✅ |
特にデータ分析やレポート作成など、フィルターを多用する実務場面で大活躍します。
けんけんフィルターをかけながら集計したいときは、SUMではなくSUBTOTALを使うのが正解です！
SUBTOTAL関数の使い方・基本構文
基本の書き方
| 引数 | 意味 |
|---|---|
| 集計方法 | 何をするかを数字で指定（下表参照） |
| 範囲1 | 集計したいセル範囲 |
| 範囲2以降 | 省略可。複数範囲を同時に指定できる |
集計番号一覧
集計番号には 1〜11 と 101〜111 の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は「手動で非表示にした行も除外する」という違いがあります。フィルターに対してはどちらも同じ動作をします。
実際の使い方｜ステップで解説
STEP
集計したいデータを用意する
まずはExcelに集計したい表を準備します。ここでは「支店別売上一覧」を例に進めます。
STEP
集計セルにSUBTOTAL関数を入力する
合計を出したいセル（例：H2）に、以下の式を入力します。
=SUBTOTAL(9,D2:D11)9は「SUM（合計）」を意味します。D2:D11が集計したいデータ範囲です。
STEP
フィルターをかけて動作を確認する
表のヘッダー行を選択して「データ」タブ→「フィルター」を設定し、特定の支店だけに絞り込んでみましょう。
フィルター適用前の合計：500,000円 フィルターで「東京」のみ表示後の合計：180,000円（表示行のみ）
SUM関数では「500,000円のまま」ですが、SUBTOTALは自動で表示行だけを集計し直してくれます。
STEP
集計方法を切り替えてみる
合計だけでなく、平均や件数も試してみましょう。
- 平均を出す：
=SUBTOTAL(1,B2:B11) - 件数を数える：
=SUBTOTAL(2,B2:B11) - 最大値を出す：
=SUBTOTAL(4,B2:B11)
数字を変えるだけで集計の種類が切り替わります。とても便利です。
時短・効率化テクニック
テクニック①：テーブル機能と組み合わせると最強
Excelの「テーブル」機能（Ctrl+T）を使うと、集計行が自動でSUBTOTAL関数を使ってくれます。テーブルの集計行ドロップダウンから「合計」「平均」などを選ぶだけでOK。数式を手入力する必要がありません。
テクニック②：SUBTOTAL同士を合計しても二重カウントされない
複数の小計行がある表で、さらに総合計を出したいときも安心です。
=SUBTOTAL(9,B2:B20)この範囲に別のSUBTOTAL関数が入っていても、Excelが自動的に二重カウントを除外してくれます。SUM関数では起こりがちなズレが発生しません。
けんけん小計行をまたいだ総合計も、SUBTOTALなら正確に出せます！
テクニック③：「見えている行だけコピー」と組み合わせる
フィルター後に見えている行だけをコピーしたいときは、Alt+;（可視セルの選択）を使うと便利です。SUBTOTALで集計した値もそのままコピーできます。
うまくいかないとき｜トラブルシューティング
❌ フィルターをかけても合計が変わらない
原因：SUM関数を使っている SUBTOTAL関数に変更してください。SUM関数はフィルターの状態を無視します。
❌ 手動で非表示にした行まで含まれてしまう
原因：集計番号が1〜11になっている 手動非表示の行も除外したい場合は、101〜111の番号を使ってください。
- 例：
=SUBTOTAL(109,B2:B11)← 手動非表示も除外して合計
❌ #VALUE! エラーが出る
原因：集計番号に無効な値を入力している 集計番号は 1〜11 または 101〜111 の整数のみ有効です。それ以外の数値や文字列を入力するとエラーになります。
❌ 件数を数えたら0になる
原因：COUNT（番号2）を使っているのに、セルに数値でなく文字列が入っている 文字列を含む列の件数を数える場合は、COUNTA（番号3）を使いましょう。
=SUBTOTAL(3,A2:A11)❌ グループ化した行が除外されない
原因：集計番号が1〜11のため、手動で折りたたんだ行も含めて計算している グループ化（アウトライン）で折りたたんだ行を除外するには、101〜111の番号を使ってください。
まとめ
- SUBTOTAL関数は フィルターで絞り込んだ行だけを正確に集計できる
- 集計番号を変えることで、合計・平均・カウントなど 11種類の集計を1つの関数で実現できる
1〜11はフィルターのみ対応、101〜111は手動の非表示行も除外する- テーブルの集計行では自動でSUBTOTALが使われる
- SUBTOTAL同士を集計しても二重カウントが発生しない
データ集計で「フィルターしながら合計したい」と思ったら、迷わずSUBTOTALを使ってみてください。一度使うと、もう手放せなくなりますよ！
次のステップへ進もう
SUBTOTAL関数をマスターしたら、次はこちらの機能も覚えておくと実務でさらに差がつきます。
- SUMIF関数：条件に合う行だけを合計できる関数。「東京支店の売上だけ合計したい」「特定の担当者の数字を出したい」という場面で大活躍します。
- AGGREGATE関数：SUBTOTALの上位互換。エラー値を無視した集計など、さらに細かい制御ができます。SUBTOTAL関数に慣れたら次のステップとして挑戦してみましょう。
- ピボットテーブル：クリック操作だけで複雑な集計・分析ができるExcelの目玉機能。大量データを一瞬でまとめたいときに最適です。
