【Excel】フィルタ後の表示セルだけ合計する方法｜SUBTOTAL関数で非表示行を除外

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

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

SUM関数は、フィルタで見えなくなった行や、手動で非表示にした行があっても、指定した範囲の数値をそのまま合計します。

そのため、見えているセルだけを集計したい場合は、SUM関数ではなくSUBTOTAL関数を使うのが基本です。

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

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

この記事では、Excelでフィルタ後の表示セルだけを正しく合計する方法と、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関数を使う方が安全です。

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関数を使う方がシンプルです。

関数に慣れていない場合は、最初からAGGREGATE関数まで覚えようとせず、SUBTOTAL(9,範囲)とSUBTOTAL(109,範囲)の違いを先に押さえるのがおすすめです。

SUBTOTAL関数でよくある間違い

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

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

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

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

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

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

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

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

9と109を混同してしまう

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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