フィルターをかけたのに合計が変わらない――そう感じたときは、セルに入っているのが SUM 関数である場合がほとんどです。SUM はフィルターで行が隠れても、隠れた行を含めた全行を合計します。一方、SUBTOTAL 関数はフィルターで表示されている行だけを集計します。この違いを把握すれば、「どちらを使うか」の判断は迷わなくなります。
この記事では、SUM と SUBTOTAL の動作の違い、SUBTOTAL の書き方と引数の選び方、よく起きる誤用、手動で行を非表示にした場合の注意点を順に説明します。
SUMとSUBTOTALで何が違うか
SUM(A2:A100) と書いた場合、フィルターで行を絞り込んでも、A2からA100のすべての数値が計算に含まれます。フィルターは「画面に見えるかどうか」を変えるだけで、セルの値は消えていないからです。
SUBTOTAL(9, A2:A100) と書いた場合は、フィルターで非表示になった行を除いて合計します。フィルターを切り替えるたびに合計が連動して変わるので、「今表示している件数の合計」を確認したいときに使います。
簡単に言うと、次のように使い分けます。
- フィルター後の表示行だけを合計したい →
SUBTOTAL - フィルターに関係なく全行を合計したい →
SUM
どちらが「正しい」わけではなく、目的に合わせて選ぶものです。
SUBTOTALの書き方と引数の意味
SUBTOTAL の構文は次のとおりです。
=SUBTOTAL(集計方法, 範囲1, [範囲2], ...)
「集計方法」には数字を入れます。よく使う数字と対応する集計の種類を以下に示します。
表は横にスクロールできます
| 数字 | 集計の種類 |
|---|---|
| 1 | AVERAGE(平均) |
| 2 | COUNT(数値の個数) |
| 3 | COUNTA(空白以外の個数) |
| 9 | SUM(合計) |
| 10 | VAR(分散) |
合計を求めるときは 9 を使います。=SUBTOTAL(9, C2:C50) と書けば、フィルターで表示されているC列の数値だけを合計します。
集計方法には1〜11と101〜111の2系統があります。1〜11は「フィルターで隠れた行は除く、手動で非表示にした行は含む」動作です。101〜111は「フィルターで隠れた行も、手動で非表示にした行も両方除く」動作です。この違いは後の節で詳しく説明します。
画面の表示はExcelの版や設定により異なります。引数の名称や入力補助の表示が手元の環境と異なる場合は、公式のヘルプで確認してください。
実際の入力例(架空の例です)
以下は架空の申込一覧です。
表は横にスクロールできます
| 行 | 担当者 | 金額 |
|---|---|---|
| 2 | 田中 | 5,000 |
| 3 | 鈴木 | 3,200 |
| 4 | 田中 | 4,800 |
| 5 | 山田 | 6,100 |
| 6 | 鈴木 | 2,500 |
C7セルに =SUM(C2:C6) と入れると、フィルター前後で常に 21,600 が表示されます。
C8セルに =SUBTOTAL(9, C2:C6) と入れると、フィルターなしの状態では同じく 21,600 です。担当者を「田中」だけに絞ると、C8は 9,800(5,000+4,800)に変わります。C7は絞り込み後も21,600のままです。
この動作の差が、集計表を見る人に混乱を与えることがあります。フィルターで絞り込んだ状態のまま合計のセルを見たとき、SUM では全件の数字が残り続けるので「なぜ合計が変わらないのか」と誤解されやすいです。フィルターを使う集計シートでは SUBTOTAL を使うほうが意図が伝わりやすくなります。
手動で行を非表示にした場合の注意
フィルターではなく、行番号を右クリックして「非表示」にした場合は動作が変わります。
SUBTOTAL(9, ...) ― 集計方法が1〜11の場合 ― は、手動で非表示にした行を含めて合計します。フィルターで隠した行は除きますが、手動で隠した行は除きません。
手動で隠した行も含めたくない場合は、集計方法を 109 に変えます。
=SUBTOTAL(109, C2:C50)
この 109 は「SUMのうち、手動非表示行も除く」という指定です。フィルターと手動非表示の両方に対応したいときに使います。
実務でよくある誤りは、「行を手動で隠しているのに合計が減らない、おかしい」という状況です。その場合は SUBTOTAL(9, ...) を SUBTOTAL(109, ...) に変えると意図どおりに動きます。
SUBTOTAL同士の二重集計を防ぐ
SUBTOTAL にはもう一つ便利な性質があります。集計範囲の中に別の SUBTOTAL が入っていても、その値を二重に数えません。
たとえば、各部門の小計行にそれぞれ SUBTOTAL(9, ...) を入れていて、最後の合計行でその列全体を SUBTOTAL(9, ...) で集計した場合、小計行の値は無視されて元データだけが合計されます。
SUM の場合は小計行と元データの両方を足してしまうため、意図しない二重計算が起きます。小計を含む表全体を一括で集計したいときは SUBTOTAL が適しています。
ただし、この二重集計防止は SUBTOTAL 同士のみ有効です。AGGREGATE など別の集計関数が混在する場合は動作が異なります。自分のシートにどの関数が使われているかを確認してから使ってください。
次の行動
今使っている集計シートのSUMセルをクリックして、数式バーで SUM か SUBTOTAL かを確認してください。フィルターを使う運用であれば、=SUM(範囲) を =SUBTOTAL(9, 範囲) に書き換えるだけで対応できます。手動非表示も考慮する場合は 109 を使ってください。変更前にファイルをコピーして元のバージョンを残しておくと、元に戻しやすくなります。
参考資料
- SUBTOTAL 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- SUM 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- Excel で範囲またはテーブルのデータをフィルター処理する | Microsoft Support · support.microsoft.com 参照日 2026-10-23
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
