Excelでフィルター後の
合計が
変わらない
とき｜SUBTOTALの
9と109を確認する

Excelで部門を絞ったのに、合計が元の金額のままになっている。そこでSUBTOTALへ変えても、手で隠した行まで含むのか、追加した最終行まで参照しているのかによって、求める小計とは違う数字になります。

最初に決めるのは「何を足したいか」です。画面に見えている行だけと、フィルターに残る行すべて（手動非表示を含む）では対象が違います。関数番号を選ぶ前に、この二つを分けて依頼します。

この記事では、同じ架空の6行を使い、フィルター除外・手動非表示・数式の参照範囲を照合します。金額は公式の関数仕様に沿って通常の算術で検算した期待値です。ExcelやOfficeでの数式実行、実画面の操作・再計算・CSV読込試験は行っていません。

この記事で分かること

• フィルター除外と手動非表示を分け、求める行からSUBTOTALの9と109を選びます
• 同じ架空6行で式と参照範囲を照合し、追加した行の参照漏れを確かめます
• 表示された小計と業務上の確定合計を分け、不明な条件は担当者へ確認します

合計を変える前に、
元の式と求める行を控える

原ブックをコピーして保全し、合計セルの実際の式、対象列、先頭と末尾のセルを控えます。「合計」という見出しだけでは、SUMなのかSUBTOTALなのか、どの範囲を見ているのかは分かりません。

Excelのテーブルの集計行も、見た目だけで決めないでください。Microsoftは、Excelテーブルの集計行の説明で、SUBTOTALを使う既定の選択肢に加えて、ほかの関数も使えると案内しています。自分の表に入っている式を読みます。

今回の原記録はA列が記録ID、B列が部門、C列が業務状態、D列が金額です。1行目が見出し、2〜7行目がデータで、金額の全範囲はD2:D7。6件の金額はすべて通常の数値です。ここでのA〜D列とセル番地は、架空の元ブック上の配置です。配布する原記録CSVは照合情報を加えた8列で、D列はdepartment（部門）、F列はamount_yen（金額）です。amount_cell列のD2〜D7は元ブックの番地を記録した値であり、このCSVへD2:D7の式をそのまま適用する取込表ではありません。以下の行番号・状態・金額は、この説明用に作った架空の指定です。

部門を「北」に絞るため、南のR03・R05はフィルターで除外されます。それとは別に、R02・R05には手動非表示の記録がある前提です。R05は両方の指定が重なっていますが、元の行は一つです。どちらの理由でも非表示だからといって、700円を二度差し引きません。

この表示状態は架空例の入力条件として明示したものです。実務では、行が見えないことだけから原因を推測せず、フィルター条件と手動非表示の指定を別々に確かめます。

9と109は、手動非表示を
含めるかで選ぶ

MicrosoftのSUBTOTALの仕様では、合計に使う番号は9と109です。どちらもフィルターで除外された行を含みません。一方、手動非表示の行は9では含み、109では含みません。

この違いを、今回の依頼へ当てはめます。金額の列と範囲を変えずに、求める行だけを先に列挙します。

差額の800円は、北に属し、手動非表示だけが指定されたR02です。南のR05はフィルターでも除外されるため、9と109のどちらにも入りません。今回の差を説明するためにR05の700円を使うと、非表示の理由を取り違えています。

「見えている行だけ」と頼まれたなら、上の固定条件では109の案になります。ただし、番号だけを覚えてすべての合計セルへ置き換えるのではなく、実際の範囲と依頼の意味も確認してください。手動非表示を含めたい集計に109を使うと、必要なR02を落とします。

合計が変わらない理由を、
式と範囲から分ける

MicrosoftのSUMの説明では、SUMは指定した値やセル範囲を加算する関数です。表示されているセルだけの合計については、SUBTOTALが案内されています。

この架空例で、元の合計セルに=SUM(D2:D7)が入っているなら、範囲内の6件を足す算術は4,500円です。フィルターで画面から消えた行の値が、元の範囲から削除されたわけではありません。見えている行の2,500円とは、そもそも足している集合が違います。

次の4案は同じ原記録を比較したものです。「式側の期待値」は実Excelの観測値ではなく、指定範囲と仕様に沿った検算値です。

F04は109を使っていても、最終セルがD6で止まっています。D7のR06、1,000円が入らないため、F03との差額は1,000円です。関数番号の変更だけでは直りません。依頼が全6行を対象にしていることを確認してから、参照範囲をD2:D7にする案を渡します。

範囲を広げるときも、末尾へ無条件に足し続けないでください。別表や集計セルまで含まないかを確認します。この例は単一の縦範囲にある通常の数値だけを扱い、途中の入れ子小計や別シートをまたぐ参照は含めていません。

見えている小計を、
確定済みの業務合計と呼ばない

現在見えているR04は「取消」ですが、表示小計の条件は業務状態を見ていません。そのため、F03の2,500円にはR04の300円も含みます。SUBTOTALを109に変えただけで、取消の処理まで済むわけではありません。

もし依頼が「北部門の確定済みだけ」なら、対象はR01・R02・R06で、合計は3,000円です。手動非表示のR02を含み、表示されている取消のR04を除くため、見えている行の集合とは一致しません。

この業務条件の決め方は、Excel月次集計で重複・取消・空欄を確かめる方法で扱っています。確定合計が欲しいときは、そちらのように対象状態を決めて集計します。行を隠す操作を取消の代わりに使ったり、見えている小計をそのまま月次の確定値と呼んだりしないでください。

フィルターや非表示は、元データの削除でも閲覧権限の設定でもありません。表示の都合と、業務上採用する記録の条件を別に残すことが必要です。

不明な条件があれば、
式の変更を保留する

合計だけが送られてきて、式や範囲が分からない場合は、SUMから109へ変える案を確定できません。非表示の理由が不明な場合や、依頼が手動非表示を含むか決まっていない場合も同じです。

確認相手には、直す数式より先に、次の情報を求めます。これは未送信の依頼文例です。

合計の対象を確認したいので、原ブックを保全したうえで、
合計セルの現在の数式と参照範囲を教えてください。
求めるのは「見えている行だけ」でしょうか、
「フィルターに残る全行（手動非表示を含む）」でしょうか。
フィルターで除外された行と、手動非表示の行も別々に確認したいです。
確定／取消などの業務状態を条件にする場合は、その条件もお願いします。
確認できるまでは、元の式や行を変更しません。

この例は、横方向の範囲、非表示列、文字列になった金額、空欄・エラー、入れ子のSUBTOTALなどの診断を含みません。MicrosoftもSUBTOTALを縦範囲向けに説明しています。条件が違う表へ、そのまま計算結果を当てはめないでください。

確認シートを使い、
式だけ
でなく対象IDを読み戻す

配布物は、原記録と表示状態を控える表、式と依頼を比べる表、手読メモの最小構成です。空欄CSVは見出しだけなので、架空例を消す作業から始める必要はありません。

• 原記録・表示状態の空欄CSV
• 同じ6件を記入した原記録CSV
• 数式と目的の空欄確認CSV
• 4案の照合を記入した確認CSV
• Pythonなしで確認する手読メモ

CSVはブックのフィルターや非表示設定を実行するファイルではありません。true／falseは、この例の指定を記録した値です。数式の欄にも「式: 」という接頭辞を付け、CSVを開いただけで計算する数式として渡さない形にしています。

最初は原記録CSVのR01〜R06を一行ずつ読み、依頼に含むIDへ印を付けます。次に確認CSVの式の参照範囲と対象IDを比べ、最後に金額を足します。2,500円という金額が合うだけでなく、R01・R04・R06を足しているかまで読み戻してください。

実際のブックへの変更は、依頼の意味・表示状態・範囲を確認した担当者が行う次の工程です。この配布物や算術検算は、Officeでの実行・互換性・表示検査の代わりにはなりません。