Excelのフィルター後に合計が変わらない?SUBTOTALの9と109を比べる

VBA・Officeカテゴリを表すパンダのイラスト VBA・Office

この記事について この記事は、生成AIを活用した自動生成フローで作成しています。Microsoft SupportのSUBTOTAL関数の説明を確認し、フィルターと手動非表示で合計が変わる条件を整理しました。

検証ステータス:📘 公式仕様確認済み・Excel実機未確認

対応環境・できること・制限事項(確認:2026年11月17日)

項目対象・条件
対象製品Windows版Excel for Microsoft 365、Excel 2024・2021
操作場所デスクトップ版Excelの新規ブック、データのフィルターと行の非表示
契約・権限Excelを利用できる環境。Copilot、管理者権限、外部サービスは不要
保存場所PC上の練習用ブック。OneDriveやSharePointへのアップロードは不要
できることフィルターで除外した行、手動で隠した行を区別して合計する
制限実機の画面操作、Excel Web版の画面差、印刷結果は未確認

Excelの売上表で「東京だけ」を表示しても、SUM関数の合計が全国分のままなのは故障ではありません。SUMは通常、フィルターで見えなくなった行も集計します。画面に表示されている行だけを合計したいなら、SUBTOTAL関数を使います。 ただし、フィルターで除外された行と、右クリックなどで手動非表示にした行は扱いが違います。ここを混同すると、同じファイルなのに担当者ごとに数字が違うという事故につながります。

架空の売上表で試す

新しい空のブックに次の4列ではなく、まず2列だけを入力します。既存の売上台帳を直接編集する必要はありません。

セルA列:地域B列:金額
1行目地域金額
2行目東京100
3行目大阪200
4行目東京300
5行目大阪400

表の外側に集計式を置きます。D2に =SUM(B2:B5)、D3に =SUBTOTAL(9,B2:B5)、D4に =SUBTOTAL(109,B2:B5) を入力してください。初期状態では、どれも1000になる想定です。式の第1引数9と109はどちらも「合計」を表し、109の方が手動で非表示にした行を除外する指定です。

次にA1:B5を選択して[データ]→[フィルター]を有効にし、地域の候補を「東京」だけにします。表示される金額は100と300です。ここでD2のSUMは1000のまま、D3とD4のSUBTOTALは400になるのが公式仕様からの期待結果です。フィルターで除外された行は、9でも109でも集計されません。 「109だけがフィルターに対応する」と覚えると誤解します。

操作SUMSUBTOTAL(9)SUBTOTAL(109)
全行表示100010001000
地域を東京だけにフィルター1000400400
フィルター解除後、3行目を手動非表示10001000800

表は実機の測定値ではなく、Microsoft公式仕様から計算した期待値です。ご自身のExcelで一致するか確認してください。

1条件だけ変える:フィルターではなく行を隠す

フィルターを解除して全行表示に戻します。今度は行番号「3」を右クリックし、[非表示]を選択してください。大阪200の行が見えなくなります。この場合、9は手動非表示行を含むため1000、109は手動非表示行を除くため800です。これが9と109の違いを観察する最も簡単な実験です。

なぜ同じ「見えない行」でも結果が違うのでしょうか。Excelでは、フィルター条件に一致しないために表示から外れた行と、利用者が行そのものを隠した状態を別の条件として扱います。Microsoft SupportのSUBTOTAL関数には、1〜11は手動非表示行を含み、101〜111は除外すること、フィルター除外行は番号に関係なく無視することが明記されています。

数字が合わないときの切り分け

まず「フィルターが掛かっているか」「行番号が飛んでいるか」「手動で非表示にした行があるか」を別々に確認します。SUMとSUBTOTALの結果が違うときは、計算式の誤りと決めつけず、表示状態を確認します。フィルターを解除しただけでは手動非表示が解除されないことにも注意してください。

次に、式の参照範囲がB2:B5で揃っているかを見ます。合計セルを参照範囲の中へ入れると、循環参照や意図しない集計の原因になります。数値に見える文字列、空白、エラー値が混じっていないかも調べます。見た目だけで判断せず、対象セルの数式バーを確認すると切り分けやすくなります。

さらに、SUBTOTALは縦方向の列データを想定した関数です。横方向に並んだ月別の数値で列を非表示にしても、109の「手動非表示を除外する」挙動をそのまま期待してはいけません。また、参照範囲内に別のSUBTOTALがある場合は二重計上を避ける扱いがあります。複数の集計行が混在する帳票では、単純なSUMとの比較だけで正誤を決めないでください。

業務で使うときは合計の意味を決める

営業担当者が地域を絞って「いま画面に見えている案件だけ」の金額を確認するなら、SUBTOTAL(109,範囲)は分かりやすい選択です。一方、月次報告で全拠点の売上を固定して表示するなら、表示状態に影響されないSUMを使う方が適切なこともあります。どちらが正しいかは、帳票の目的によって変わります。

共有ブックでは、フィルターを操作した人の表示状態と集計結果が連動する設計を、利用者へ明記しましょう。報告用の確定値と、作業中の絞り込み集計を同じ「合計」というラベルで混在させないことが大切です。フィルター結果の画面コピーを資料へ貼る際は、条件名と集計式を一緒に残すと、後から数字を再現できます。

公式情報と未確認事項

一次資料はMicrosoft Support「SUBTOTAL関数」です。9と109の区別、フィルターの扱い、縦方向の範囲という制約を確認しました。記事内の1000・400・800は架空データと仕様から求めた期待値であり、Excel実機での操作結果ではありません。画面表示、バージョン差、共同編集、WordPress掲載後の表示は未確認です。

画面に表示した行だけを集計したいのか、隠した行も含めて全体を集計したいのかを先に決めれば、SUM・SUBTOTAL(9)・SUBTOTAL(109)を目的に合わせて使い分けられます。

文書情報

記事タイトル
Excelのフィルター後に合計が変わらない?SUBTOTALの9と109を比べる
作成日
更新日
Source URL
https://papanda925.com/?p=18708

ライセンス: 本記事のうち、当サイトが権利を有する本文・自作図表は、特記なき限り CC BY 4.0 で利用できます。生成AIを活用して作成・編集した内容を含みます。コードについて、別途ライセンス表示またはリンク先GitHubリポジトリのライセンスがある場合は、その条件を優先します。引用・第三者資料・画像・商標等は本ライセンスの対象外です。 利用ポリシー

タイトルとURLをコピーしました