ExcelのSUBTOTAL関数：フィルター表示行だけを正しく集計する方法
フィルター後の表示セルだけを合計・カウント・平均できるSUBTOTAL関数の使い方を徹底解説。「9」と「109」の違いや手動非表示への対応、小計の重複カウント防止機能まで分かりやすくまとめました。
著者 Ayoub Farid · · 3 分で読めます
SUM関数を使うと、フィルターで除外した非表示の行まで合算されてしまいます。一方、SUBTOTAL関数なら現在画面に見えている行だけを対象に集計できます。フィルターを適用する表の集計には、まさに最適なExcel関数です。
構文
=SUBTOTAL(集計方法, 参照1, ...)| 関数 | 手動で非表示にした行を含める | 手動で非表示にした行を除外する |
|---|---|---|
| AVERAGE（平均） | 1 | 101 |
| COUNT（数値の個数） | 2 | 102 |
| COUNTA（データの個数） | 3 | 103 |
| MAX（最大値） | 4 | 104 |
| MIN（最小値） | 5 | 105 |
| SUM（合計） | 9 | 109 |
どちらの番号を指定しても、フィルターによって隠された行は自動的に除外されます。100番台（101〜109など）を指定した場合は、右クリックから手動で非表示にした行も計算から除外されます。
基本的な使い方の例
売上データがセル範囲 D2:D500 に入力されているとします。表の上に次の数式を入力します。
=SUBTOTAL(9,D2:D500)フィルターで「北日本支社」だけに絞り込むと、その支社の売上だけが合算されます。フィルターを解除すれば、自動的に全体の合計額に戻ります。
表示されている行数をカウントする
=SUBTOTAL(3,A2:A500)この数式を使えば、「現在のフィルター条件に一致している行が何件あるか」をすぐに確認できます。
他のSUBTOTAL関数の結果を二重集計しない
同じ列内にグループごとの小計としてSUBTOTAL関数が入っている場合、総合計を求めるSUBTOTAL関数はそれらの小計セルを自動でスキップします。そのため、二重集計になる心配がありません（「データ」タブの「小計」機能を使うと、こうした行を自動で挿入してくれます）。
AGGREGATE関数：エラー値もスキップできる高機能版
=AGGREGATE(9,7,D2:D500)最初の引数「9」はSUM（合計）を意味し、第2引数の「7」は非表示行およびエラー値を無視して集計する指定です。
テーブル機能なら自動で挿入可能
表をExcelテーブル化（Ctrl+T）している場合、「テーブルデザイン」タブから「集計行」にチェックを入れるだけで、SUBTOTAL関数の数式が自動的に挿入されます。
よくある質問
Excelで表示されているセルだけを合計するにはどうすればよいですか？
=SUBTOTAL(9,セル範囲) を使用します（手動で隠した非表示行も除外したい場合は 109 を指定してください）。
SUBTOTAL関数の「9」と「109」の違いは何ですか？
どちらもフィルターで絞り込まれた非表示行は除外されますが、109は行番号の右クリックなど「手動で非表示にした行」も計算から除外します。
フィルターをかけてもSUM関数の合計値が変わらないのはなぜですか？
SUM関数は、画面上で隠れている非表示行も含めてすべて加算する仕様だからです。表示中のセルだけを集計したい場合は、SUBTOTAL関数に置き換えてください。
GoogleスプレッドシートでもSUBTOTAL関数は使えますか？
はい、Googleスプレッドシートでもまったく同じ集計方法の番号で利用できます。