【Excel】フィルター後の表示セルだけ合計する方法｜SUMではなくSUBTOTAL関数を使う

Excelで表にフィルターをかけたあと、表示されている数値だけを合計したいのに、「合計が変わらない」「非表示のデータまで含まれている」と困ったことはありませんか。

この原因は、SUM関数の使い方にあります。

SUM関数は、フィルターで見えなくなった行があっても、指定した範囲内の数値をそのまま合計します。

そのため、フィルター後の表示セルだけを合計したい場合は、SUM関数ではなくSUBTOTAL関数を使います。

フィルター後の表示セルだけを合計するなら、基本は =SUBTOTAL(9, 範囲) を使います。

さらに、フィルターだけでなく、手動で非表示にした行も除外したい場合は、=SUBTOTAL(109, 範囲) を使います。

この記事では、Excelでフィルター後の表示セルだけを正しく合計する方法、SUM関数では合計が合わない理由、SUBTOTAL関数の「9」と「109」の違いを整理します。

Excelでフィルター後の表示セルだけ合計するならSUBTOTAL関数を使う

Excelでフィルターを使うと、条件に合う行だけを画面に表示できます。

たとえば、売上表で「10月だけ」「担当者Aだけ」「商品カテゴリだけ」のように絞り込む場面があります。

このとき、画面上では不要な行が見えなくなっているため、表示されている数値だけを合計できているように見えます。

しかし、SUM関数で合計している場合は注意が必要です。

SUM関数は、フィルターで非表示になった行も含めて計算します。表示セルだけを合計したい場合は、SUBTOTAL関数に変更します。

表示されているセルだけを合計する基本式は、次の形です。

=SUBTOTAL(9, 合計したい範囲)

例：
=SUBTOTAL(9, D2:D20)

この式を使うと、フィルターで絞り込んだあと、表示されている行の数値だけを合計できます。

SUM関数ではフィルター後の表示セルだけ合計できない理由

SUM関数は、指定した範囲内の数値をすべて合計する関数です。

たとえば、次のような式を入力している場合、D2からD20までの数値が合計対象になります。

=SUM(D2:D20)

この範囲にフィルターをかけて一部の行が見えなくなっても、SUM関数の計算範囲は変わりません。

つまり、画面では非表示になっていても、Excelの計算上は範囲内のセルとして扱われます。

この状態で資料や報告書に数値を使うと、画面に表示されている表と合計結果が一致しないことがあります。

特に、売上表、在庫表、勤務時間表、件数管理表などでは、合計ミスがそのまま判断ミスにつながることがあります。

フィルターで絞り込んだ結果を集計する表では、SUM関数ではなくSUBTOTAL関数を使う方が安全です。

ExcelでSUBTOTAL関数とSUM関数の違い

SUM関数とSUBTOTAL関数の大きな違いは、フィルターで非表示になった行を計算に含めるかどうかです。

SUM関数は、表示・非表示に関係なく、指定した範囲内の数値を合計します。

一方、SUBTOTAL関数は、フィルターで非表示になった行を除外して集計できます。

関数	フィルターで非表示の行	向いている場面
SUM関数	含める	通常の合計を出したいとき
SUBTOTAL関数	除外できる	フィルター後の表示セルだけ合計したいとき

フィルターを使わない通常の合計であれば、SUM関数で問題ありません。

ただし、フィルター後の表示セルだけを合計したい場合は、SUBTOTAL関数を使います。

SUBTOTAL(9,範囲)でフィルター後の表示セルだけ合計する方法

フィルターで表示されているセルだけを合計する場合は、SUBTOTAL関数の集計方法に「9」を指定します。

=SUBTOTAL(9, 合計したい範囲)

9は「SUM」、つまり合計を意味する番号です。

たとえば、D列の数値を合計したい場合は、次のように入力します。

=SUBTOTAL(9, D2:D20)

この式を使っておくと、フィルターで条件を変更するたびに、表示されている行だけを対象に合計が変わります。

たとえば、月別の表で11月をフィルターから外した場合、10月と12月など、表示されている行だけを対象に合計されます。

フィルターを使う表では、合計欄を最初からSUBTOTAL(9,範囲)にしておくと、表示状態に合わせて合計を確認しやすくなります。

手動で非表示にした行も除外するならSUBTOTAL(109,範囲)を使う

SUBTOTAL関数で特に間違えやすいのが、「9」と「109」の違いです。

フィルターだけを使う場合は、SUBTOTAL(9,範囲)で表示セルだけを合計できます。

しかし、右クリックメニューなどから手動で「行を非表示」にした場合、SUBTOTAL(9,範囲)ではその非表示行も含めて合計されます。

フィルターではなく、手動で非表示にした行も除外したい場合は、SUBTOTAL(109,範囲)を使います。

手動で非表示にした行も除外して合計したい場合の式は、次の形です。

=SUBTOTAL(109, 合計したい範囲)

例：
=SUBTOTAL(109, D2:D20)

作業中に一部の行を手動で非表示にする可能性がある表では、最初からSUBTOTAL(109,範囲)を使っておくと、見えているセルだけを合計しやすくなります。

SUBTOTAL関数の9と109の違い

SUBTOTAL関数の9と109は、どちらも合計を求めるための指定です。

違いは、手動で非表示にした行を合計に含めるかどうかです。

式	フィルターで非表示の行	手動で非表示の行	使う場面
SUBTOTAL(9,範囲)	除外する	含める	フィルターだけで集計する表
SUBTOTAL(109,範囲)	除外する	除外する	手動の非表示行も除外したい表

迷った場合は、SUBTOTAL(109,範囲)を使うと、手動で非表示にした行まで除外できるため、見えている行だけを合計したい目的に合いやすくなります。

SUBTOTAL関数で合計以外も集計できる

SUBTOTAL関数は、合計だけでなく、平均、個数、最大値、最小値などにも使えます。

最初の引数に指定する番号を変えることで、集計方法を切り替えられます。

SUBTOTAL(集計方法, 範囲)

例：
合計：=SUBTOTAL(9,D2:D20)
数値の個数：=SUBTOTAL(2,D2:D20)
平均：=SUBTOTAL(1,D2:D20)

フィルターのみ除外	手動の非表示行も除外	関数	集計内容
1	101	AVERAGE	平均を求める
2	102	COUNT	数値が入っているセルを数える
3	103	COUNTA	空白ではないセルを数える
4	104	MAX	最大値を求める
5	105	MIN	最小値を求める
9	109	SUM	合計を求める

日常的な表計算でよく使うのは、合計の「9」「109」、個数の「2」「102」、平均の「1」「101」です。

フィルター後の合計を出したいだけなら、まずは「9」と「109」を覚えておけば十分です。

SUBTOTAL関数を使うと小計の二重計算も防ぎやすい

SUBTOTAL関数には、表の中に別のSUBTOTAL関数が含まれている場合、その小計部分を重複して集計しにくい特徴があります。

たとえば、月別の小計と全体の合計を同じ表の中で作る場合、SUM関数で範囲をまとめて指定すると、小計まで合計に含めてしまうことがあります。

SUBTOTAL関数を使うと、同じ範囲内にあるSUBTOTAL関数の結果を除外して集計できるため、二重計算を防ぎやすくなります。

月別小計、担当者別小計、部署別小計のように、小計と合計が混在する表ではSUBTOTAL関数が向いています。

AGGREGATE関数を使う場面

表示セルだけを合計する目的であれば、SUBTOTAL関数で十分です。

ただし、表の中にエラー値が混ざっている場合は、AGGREGATE関数を使う方法もあります。

SUBTOTAL関数でよくある間違い

合計範囲に見出し行まで含めてしまう

SUBTOTAL関数の範囲には、基本的に数値が入っているセル範囲を指定します。

見出し行まで含めても大きな問題にならないことはありますが、表の作り方によっては不要なセルまで範囲に入れてしまう原因になります。

合計したい列のデータ部分だけを指定するようにしましょう。

横方向の列非表示を除外できると思ってしまう

SUBTOTAL関数は横方向の範囲にも使えますが、非表示行を除外して集計する目的では、縦方向にデータを並べた表で使うのが基本です。

行を非表示にした場合は集計から除外できますが、列を非表示にした場合に、その列だけを自動で除外する用途には向いていません。

行の非表示を除外して集計したい場合は、縦方向にデータを並べた表で使うのが基本です。横方向に月別データを並べている表では、列を非表示にしても同じ考え方で除外できるとは限らないため注意が必要です。

9と109を混同してしまう

SUBTOTAL(9,範囲)は、フィルターで非表示になった行を除外して合計します。

SUBTOTAL(109,範囲)は、フィルターで非表示になった行に加えて、手動で非表示にした行も除外して合計します。

表を人に渡す場合や、あとで編集する可能性がある場合は、109を使っておく方が安全です。

SUBTOTAL関数に関するよくある質問

SUBTOTAL関数とSUM関数はどちらを使えばいい？

フィルターを使わない通常の合計であれば、SUM関数で問題ありません。

フィルター後の表示セルだけを合計したい場合は、SUBTOTAL関数を使います。

SUBTOTALの9と109はどちらがおすすめ？

フィルターだけで集計するなら9で対応できます。

手動で非表示にした行も除外したい場合や、あとから表を編集する可能性がある場合は109がおすすめです。

フィルターを解除するとSUBTOTALの合計はどうなる？

フィルターを解除すると、表示されている行が増えるため、SUBTOTAL関数の合計も全体の合計に戻ります。

フィルター条件を変えるたびに、表示されている行だけを対象に合計結果が自動で変わります。

Excelの集計ミスを減らすには関数の使い分けが大切

Excelの集計ミスは、難しい関数を使ったときだけ起きるものではありません。

むしろ、SUM関数のように普段からよく使う関数で、表示状態と計算結果の違いに気づかないまま使ってしまうことがあります。

特に、仕事で使う表では、次のような場面で注意が必要です。

• フィルターで条件を絞り込んで合計する
• 一部の行を非表示にして印刷する
• 月別・担当者別・部署別の小計を作る
• 集計表を他の人に共有する
• 報告資料の数値として使う

このような場面では、見えているセルだけを合計しているのか、非表示の行も含めているのかを確認することが大切です。

Excelの関数を自己流で使っていると、表面上は正しく見えても、集計結果がずれていることがあります。

毎回調べながら作業している場合や、関数の使い分けに不安がある場合は、Excel操作を体系的に学ぶことで作業ミスを減らしやすくなります。

▶ Excel操作を効率よく身につける

まとめ｜Excelで表示セルだけ合計するならSUBTOTAL関数を使う

Excelでフィルター後の表示セルだけを合計したい場合は、SUM関数ではなくSUBTOTAL関数を使います。

フィルターで表示されているセルだけを合計するなら、基本は次の式です。

=SUBTOTAL(9, 合計したい範囲)

さらに、手動で非表示にした行も除外したい場合は、次の式を使います。

=SUBTOTAL(109, 合計したい範囲)

9と109の違いを覚えておくと、フィルター後の合計ミスや、非表示行を含めたまま集計してしまうミスを防ぎやすくなります。

Excelの表で「表示されている数値だけ合計したい」と思ったときは、まずSUBTOTAL関数を使うと覚えておくと便利です。