Excel約4分で読めます

Excelで絞り込んだ行だけ合計する|フィルターと手動で隠した行の違い

Excelで絞り込んだ行だけ合計するを表したイメージ
記事の内容を表したイメージ(AI生成)

担当別・地区別の一覧を絞り込んで合計を出したのに、画面に見えている行の合計と数字が合わない。そのような場面で確認したいのが SUBTOTAL 関数の関数番号の選び方です。

この記事では、フィルターで除かれた行と手動で非表示にした行をそれぞれどう扱うかを、公式仕様と架空例をもとに整理します。


最初の結論:関数番号で挙動が変わる

Microsoftの公式仕様によると、SUBTOTAL の関数番号は二系統に分かれますS1(2026-10-20時点)。

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

関数番号合計操作フィルタ除外行手動非表示行
9合計(SUM相当)除く含む
109合計(SUM相当)除く除く

フィルターで除かれた行はどちらの番号でも集計されません。手動で非表示にした行を除きたいかどうかが、9と109の選択基準になります。


確認すべき順序

合計が期待と違うときは、次の順で状態を確認します。

  1. フィルターが適用されているか ― 列ヘッダーの矢印があるだけでは絞り込み済みとは限りません。開いて実際の条件を確認します。
  2. 手動で非表示にした行があるか ― 行番号が連続していない箇所(例:1・2・4と3が飛ぶ)は手動非表示の可能性があります。
  3. 使っている関数番号が9か109か ― 数式バーで確認します。

状態が把握できたら、目的に合う番号を選びます。「フィルター後の表示行だけ足したい、手動非表示の行も除きたい」なら 109、「手動で隠した行も含めてよい」なら 9 です。


架空例で期待値を確認する

以下は編集部が作成した架空データを使った確認例ですS2。実在の業務データや操作記録ではありません。期待値はPythonで公式仕様をモデル化した独立検算であり、ExcelまたはGoogle Sheetsでの実機実行結果ではありません。

架空データの構造(visibility.csv)

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

行金額(A列)手動非表示の想定(B列)フィルタ除外の想定(C列)
2100FALSEFALSE
3200TRUEFALSE
4300FALSETRUE
5400FALSEFALSE

CSVのB・C列はTRUE/FALSEの目印であり、表計算アプリが自動で行を隠すわけではありません。実際に操作する場合は、A1:C5を対象にC列がFALSEの行だけをフィルター表示し、3行目(金額200)を行番号から手動で非表示にする手順が必要です。集計セルは、行を隠しても見えるようにデータ範囲の下のE7・E8に置きます。

架空例での期待値

全行の合計は 100+200+300+400 = 1000 です。

上記の操作(300をフィルター除外、200を手動非表示)を行った状態での各関数の期待値は次のとおりです。

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

数式(セル配置例)除外の対象架空例の期待値
=SUBTOTAL(9,A2:A5)(E7)フィルタ除外行(300)のみ除く700
=SUBTOTAL(109,A2:A5)(E8)フィルタ除外(300)+手動非表示(200)を除く500

9 の場合は 100+200+400=700、109 の場合は 100+400=500 というモデルです。

数式はMicrosoftの公式構文から作成していますS1。自分のExcelで確認する際は、上記の手順でデータを配置し、フィルター解除・行の再表示後に両方の数式が1000になることも合わせて確かめてください。


追加行・空欄・適用範囲の注意点

行を追加した場合 集計範囲(A2:A5 など)に含まれない行を追加しても自動では集計されません。追加後は参照範囲を確認し、新しい最終行を含むように更新してください。

空欄セルがある場合 ここで使う合計の番号9・109では、空欄は合計へ加算されません。ただし、空欄が多い場合は集計範囲の意図を確認してください。

列の非表示について SUBTOTAL の対象は行です。列を非表示にしても集計値は変わりません。

小計行が範囲内にある場合 SUBTOTAL で出した小計行が集計範囲に含まれていても、二重集計にはなりません(公式仕様S1)。ただし、SUM など別の関数で出した小計行は二重集計になるため注意が必要です。


まとめ:番号の選び方

  • フィルターで除かれた行を除き、手動非表示の行は含めてよい → 9
  • フィルター・手動非表示のどちらも除いて合計したい → 109
  • 数式が既に入っている場合はまず現在の番号を確認する

次の作業として、現在使っている集計セルの数式バーを開き、番号が意図と合っているかを確認してみてください。

この記事の確認用ファイル

架空データの記入例や、手元で確認するための素材です。

参考資料

公式資料と架空データを使って編集しています。配布CSVは値と見出しのみで、数式・書式・保護設定を含む完成ブックではありません。Excel・Sheetsの実機操作は未検証です。

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

記事一覧へ