Excel約4分で読めます

ExcelのSUBTOTALとSUMの違い|フィルター時の合計

ExcelのSUBTOTALとSUMの違いを表したイメージ
記事の内容を表したイメージ(AI生成)

フィルターをかけたのに合計が変わらない――そう感じたときは、セルに入っているのが SUM 関数である場合がほとんどです。SUM はフィルターで行が隠れても、隠れた行を含めた全行を合計します。一方、SUBTOTAL 関数はフィルターで表示されている行だけを集計します。この違いを把握すれば、「どちらを使うか」の判断は迷わなくなります。

この記事では、SUM と SUBTOTAL の動作の違い、SUBTOTAL の書き方と引数の選び方、よく起きる誤用、手動で行を非表示にした場合の注意点を順に説明します。


SUMとSUBTOTALで何が違うか

SUM(A2:A100) と書いた場合、フィルターで行を絞り込んでも、A2からA100のすべての数値が計算に含まれます。フィルターは「画面に見えるかどうか」を変えるだけで、セルの値は消えていないからです。

SUBTOTAL(9, A2:A100) と書いた場合は、フィルターで非表示になった行を除いて合計します。フィルターを切り替えるたびに合計が連動して変わるので、「今表示している件数の合計」を確認したいときに使います。

簡単に言うと、次のように使い分けます。

  • フィルター後の表示行だけを合計したい → SUBTOTAL
  • フィルターに関係なく全行を合計したい → SUM

どちらが「正しい」わけではなく、目的に合わせて選ぶものです。


SUBTOTALの書き方と引数の意味

SUBTOTAL の構文は次のとおりです。

=SUBTOTAL(集計方法, 範囲1, [範囲2], ...)

「集計方法」には数字を入れます。よく使う数字と対応する集計の種類を以下に示します。

表は横にスクロールできます

数字集計の種類
1AVERAGE(平均)
2COUNT(数値の個数)
3COUNTA(空白以外の個数)
9SUM(合計)
10VAR(分散)

合計を求めるときは 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 を使ってください。変更前にファイルをコピーして元のバージョンを残しておくと、元に戻しやすくなります。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ