Excelでフィルター後の可視行だけ合計する方法｜SUBTOTALとAGGREGATEの使い分け

Excelでフィルター後の可視行だけ正確に合計したいなら、基本は SUM ではなく SUBTOTAL を使います。もっとも迷いにくいのは =SUBTOTAL(109,範囲) で、フィルターで非表示になった行に加え、手動で非表示にした行も集計から外せます。エラー値が混じる一覧なら AGGREGATE が候補です。この記事では、設定場所、前提条件、9 と 109 の違い、テーブルでの集計、失敗しやすい点、元に戻す方法まで、実務でそのまま使える形で整理します。(マイクロソフトサポート)

見た目では数行しか表示されていないのに合計が合わないのは、Excelが「表示している行」と「数式が参照する範囲」を別で扱うからです。AutoFilterで行を絞っても、SUM は通常その範囲全体を足します。表示中の行だけを合計したい場面では、最初から SUBTOTAL を選んだほうが早いです。(マイクロソフトサポート)

Excelでフィルター後の可視行だけ合計する最短の方法

金額列が C2:C1000 なら、まずは次の式を使えば十分です。109 は SUM を表し、手動で非表示にした行も除外する指定です。フィルターで除外された行は、1〜11 と 101〜111 のどちらでも無視されます。なお、範囲内に別の SUBTOTAL が入っている場合は、それらを無視して二重計上を避けます。(マイクロソフトサポート)

=SUBTOTAL(109,C2:C1000)

使い分けは次の表で覚えると迷いません。(マイクロソフトサポート)

目的	式	手動で非表示にした行
フィルターで消えた行だけ除外したい	=SUBTOTAL(9,C2:C1000)	含む
見た目どおり、可視行だけを堅く合計したい	=SUBTOTAL(109,C2:C1000)	含まない

実務では、レビュー中に数行だけ手動で隠しているシートが意外と多いです。その状態で 9 を使うと、見えていないのに合計へ入る行が出ます。「可視行だけ」と言われたら、まず 109 を疑う くらいでちょうどいいです。(マイクロソフトサポート)

SUMではなくSUBTOTALを使う理由

フィルターは、条件に合わない行を一時的に非表示にする機能です。表示を絞っても、データ範囲そのものを別の場所へ切り出しているわけではありません。Microsoftの案内でも、AutoFilterや手動の非表示で「見えているセルだけを合計したい」ときは SUBTOTAL を使う流れになっています。(マイクロソフトサポート)

たとえば売上一覧で「東京支店だけ」を表示していても、セルに =SUM(C2:C1000) と入っていれば、他支店の売上まで含まれる可能性があります。見た目の件数と合計が合わないときは、式が SUM のままになっていないかを最初に確認してください。(マイクロソフトサポート)

実務で迷わない設定手順

一覧表に見出し行があり、途中に大きな空白行や空白列がない形だと、フィルターも集計も安定します。まずは次の手順で設定してください。フィルターは通常の範囲にも設定でき、テーブルにしている場合はヘッダーにフィルターが自動で付きます。(マイクロソフトサポート)

1. 集計したい一覧のどこかを選びます。
2. データ タブの フィルター を有効にします。
3. 条件を設定して、必要な行だけ表示します。
4. 合計を出したい列の外側のセルに、次の式を入力します。

=SUBTOTAL(109,C2:C1000)

5. フィルター条件を変えて、合計が連動して変わることを確認します。

ここでのコツは、見出し行や合計セル自身を参照範囲に入れないこと です。売上列なら売上データだけを指定します。また SUBTOTAL は縦方向の列データ向けに設計されており、横方向の範囲で「非表示の列だけ除外したい」という使い方には向きません。(マイクロソフトサポート)

繰り返し集計するならテーブルの集計行が便利

同じ表を毎月更新するなら、範囲をExcelテーブルにして 集計行 を使う方法がかなり実用的です。テーブルではフィルターが最初から付き、集計行の標準メニューは SUBTOTAL を使います。実際、Microsoftの例でも集計行で選んだ合計は =SUBTOTAL(109,[列名]) の形で入力されます。(マイクロソフトサポート)

テーブルの集計行を使う流れ

1. 一覧をテーブルにします。
2. テーブルを選択し、集計行 をオンにします。
3. 売上列の集計行セルでドロップダウンから 合計 を選びます。

この方法の利点は、式を毎回打ち直さなくていいことです。列名ベースの式になるので、あとから見返しても何を合計しているかが分かりやすくなります。定例レポートや日次の管理表には特に向いています。(マイクロソフトサポート)

1つ注意したいのは、集計行の式を横にコピー＆ペーストすると列参照が正しく更新されず、不正確な値になることがある 点です。隣の列へ同じ考え方を適用したいときは、コピペではなくドロップダウンで関数を選ぶか、フィルハンドルで横にドラッグしたほうが安全です。(マイクロソフトサポート)

エラー値が混じるならAGGREGATEを使う

売上列や計算列に #N/A などのエラーが混じるときは、SUBTOTAL より AGGREGATE のほうが扱いやすい場面があります。AGGREGATE は、関数の種類に加えて「非表示行を無視する」「エラー値を無視する」といったオプションを選べるからです。(マイクロソフトサポート)

たとえば、可視行だけを合計しつつエラーも無視したいなら、次の式が使えます。(マイクロソフトサポート)

=AGGREGATE(9,7,C2:C1000)

意味は次のとおりです。(マイクロソフトサポート)

式	意味
=AGGREGATE(9,5,C2:C1000)	SUMで集計し、非表示行を無視する
=AGGREGATE(9,7,C2:C1000)	SUMで集計し、非表示行とエラー値を無視する

ただし、AGGREGATE は使い方を少し間違えやすい関数です。配列に計算式を直接入れる書き方では、非表示行やネストした集計を期待どおり無視しないことがある と公式にもあります。まずは C2:C1000 のように、範囲をそのまま渡す参照形式で使うのが無難です。(マイクロソフトサポート)

よくある失敗と対処

つまずきやすいポイントは、だいたい決まっています。先に知っておくと作業がかなり速くなります。(マイクロソフトサポート)

失敗	なぜ起きるか	対処
SUM のまま使う	フィルターで見えない行まで範囲に含まれる	SUBTOTAL(109,範囲) に置き換える
9 と 109 を混同する	手動で非表示にした行を含めるかが違う	迷ったら 109 を使う
データ > 小計 を押してしまう	これは単純な可視行合計ではなく、小計行とアウトラインを挿入する別機能	単純集計なら SUBTOTAL 関数かテーブルの集計行を使う
テーブルの集計行を横にコピペする	構造化参照が正しく更新されないことがある	ドロップダウンかフィルハンドルを使う
横方向の行合計に SUBTOTAL を使う	SUBTOTAL は縦方向の列データ向け	列単位で設計するか、別の集計方法を考える

特に混同しやすいのが、SUBTOTAL 関数 と データ タブの 小計 コマンド です。後者はグループごとの小計行を自動挿入する機能で、単純に「フィルター後の表示分だけ合計したい」という用途とは別物です。しかもテーブルではサポートされず、表を通常範囲に戻さないと使えません。(マイクロソフトサポート)

元に戻す方法と代替策

フィルターだけ戻したいとき

特定の列だけ条件を外すなら、列見出しのフィルターアイコンから Clear Filter を使います。表全体のフィルターを外したいなら、範囲やテーブル内の任意セルを選び、データ タブの フィルター をオフにします。するとすべてのデータが再表示されます。(マイクロソフトサポート)

テーブルの集計行を消したいとき

テーブルで追加した集計行は、集計行 のチェックを戻せば非表示にできます。集計行の式はテーブル機能の一部なので、普通の数式セルを消す感覚ではなく、機能をオフに戻すと考えると分かりやすいです。(マイクロソフトサポート)

データ > 小計 で追加した行を消したいとき

小計 コマンドで追加した小計行は、データ タブの 小計 ダイアログから Remove All で一括削除できます。なお、フィルターがかかった状態だと小計行が隠れて見えないことがあるため、その場合は先にフィルターをクリアしてください。(マイクロソフトサポート)

可視データだけ別シートに渡したいとき

合計だけでなく、表示中の行そのものを別シートへ貼りたい なら、デスクトップ版Excelの Visible cells only が便利です。ホーム > 検索と選択 > 条件を選択してジャンプ > 可視セル で選んでからコピーすると、見えているセルだけを対象にできます。なお、Excel for the web ではこの貼り付け制御に制約があり、必要に応じてデスクトップ版を使うほうが確実です。(マイクロソフトサポート)

迷ったらこの順番で進めればいい

フィルター後の可視行だけ正確に合計するなら、まずは =SUBTOTAL(109,範囲) を入れてください。これで大半のケースは解決します。毎回同じ表を更新するなら テーブル + 集計行、エラー値が混じるなら AGGREGATE(9,7,範囲) を検討する、という順番が実務では扱いやすいです。(マイクロソフトサポート)

最初の一歩としては、今使っている SUM を1つだけ SUBTOTAL(109,...) に置き換え、フィルター条件を変えたときに合計が期待どおり動くか確認してみてください。ここが合えば、そのシートの集計ミスはかなり減らせます。(マイクロソフトサポート)