Excelでフィルター後の合計を出す方法|SUBTOTALの9・109を比較

Excelでフィルターをかけたのに合計が変わらないときは、合計セルの式と、どの行を集計したいかを確認します。今回の実機例では、SUMは全6件の210のまま、SUBTOTALは抽出した3件の60になりました。

この記事では、数値が入った縦方向の小さな表を使い、フィルターと手動非表示を分けて合計を確認します。元表から別シートへコピーする方法ではなく、同じ表の表示状態に応じた集計が目的です。コピーして渡したい場合は、表示中の行だけコピーする手順を参照してください。

全件の合計か、表示中の行の合計かを決める

フィルターで行が見えなくなっても、その値が元表から消えたわけではありません。全件合計と抽出分の合計は、どちらも用途があります。報告用の表では「全件合計」「区分Aの表示行合計」のように、数字の対象を見出しに書いておくと取り違えを防げます。

まず合計セルを選び、数式バーで式を読みます。この記事の例で比較する式は次の3つです。実際の表では、C2:C7を金額などの明細範囲へ置き換えます。合計セル自身を集計範囲へ含めないでください。

セル比較する数式この例で確かめること
E2=SUM(C2:C7)全6件の合計
F2=SUBTOTAL(9,C2:C7)フィルターと手動非表示での違い
G2=SUBTOTAL(109,C2:C7)表示行を対象にした合計

SUBTOTALの9と109は、手動非表示の扱いが違う

Microsoft公式では、SUBTOTALの集計方法9は合計で、手動非表示の行を含みます。109も合計ですが、手動非表示の行を除外します。フィルターで対象外になった行は、どちらも除外する説明です。縦方向の明細を対象とする関数で、列を隠す場合へ同じ扱いを当てはめないでください。

ただし、手動非表示だけの表と、フィルター中にさらに行を隠した表は、作業状態を分けて確認します。後述の混在操作では9も値を除外しました。9なら画面で隠れている行を必ず全部含む、と画面だけから判断しないことが大切です。

架空6件でフィルター後の合計を確認する

2026年11月3日、Windows版デスクトップExcelで次の架空データを入力しました。見出しを含む範囲はA1:C7、数値の範囲はC2:C7です。実在の取引や顧客情報は使っていません。

ID区分金額
R001A10
R002A20
R003A30
R004B40
R005B50
R006B60
単位を付けずに計算を確かめるための架空値です。
  1. 元の業務用ブックは保全し、検証用の新規ブックへ上表を入力します。
  2. E2・F2・G2へ先ほどの3式を入力し、全件表示でいずれも210になることを確認します。
  3. A1:C7を選び、フィルターを設定します。今回の操作ではCtrl+Shift+Lを使いました。
  4. 区分列のフィルターボタンを開き、Aだけを選択して確定します。表示対象はR001・R002・R003です。
  5. 表示されたIDと10+20+30=60を照合し、3式の結果を比べます。

この状態ではSUMが210、SUBTOTALの9・109がともに60でした。SUMの210はこの式と範囲では全件合計です。表示行だけの数字が必要なら、全件合計のセルを再計算するだけでなく、目的に合う集計式を使っているか確認します。

手動非表示と混在操作も分けて実測した結果

表の状態SUMSUBTOTAL 9SUBTOTAL 109
全6件を表示210210210
区分Aでフィルター2106060
Aで抽出後、R002の3行目をCtrl+9で隠す2104040
フィルターを外して全件表示に戻した後、R002だけ手動非表示210210190
同じ6件・同じ3式で観測した値。混在操作の結果を手動非表示だけの条件と区別しています。

手動非表示だけのケースでは、表示IDはR001・R003・R004・R005・R006でした。109の190は10+30+40+50+60と一致し、9とSUMは隠した20も含む210でした。

一方、Aで抽出している途中でR002を隠したケースは、9も40になりました。この操作後にExcel内部でその行がどの非表示区分として扱われたかまでは確認していません。仕様上の説明から60と推測して補わず、今回の観測値を残しています。混在した表で非表示分を含めたい場合は、作業用コピーで条件と非表示を整理し、対象IDと金額から期待する合計を先に確かめてください。

保存した検証用xlsxは、最後の「手動非表示だけ」の状態です。閉じて開き直した後も210・210・190を確認し、保存ファイル内の明細数値、3式、3行目の非表示状態も読み戻しました。途中のフィルター状態すべてを別々のxlsxで保存した検証ではありません。

合計が合わないときは、範囲と対象IDへ戻る

式の範囲に追加行が入っているか、別の列にもフィルターが残っていないか、金額が数値として入力されているかを確認します。合計が一致しただけでは、対象IDも同じだとは限りません。対象行の一覧と数値の両方を照合してください。

行の順序も不自然な場合は、合計の問題と分けて非表示行・フィルターがある表の並べ替え確認へ進めます。この記事の実測は小さな通常範囲で、テーブルの集計行、グループ化、小計の混在、エラー値、文字列の金額、Mac版・Web版は動作確認していません。正確な製品ビルド番号も今回の実測記録では未確認です。

参照資料

Microsoft Support「SUBTOTAL 関数」(2026年11月3日参照)。集計方法と非表示の公式説明、および今回の実機観測は分けて記載しています。

この記事を書いた人

メーカーで設計開発とマーケティング企画に携わってきた個人が運営しています。報告書の作成、Excelでの集計、会議の運営など、仕事の文章と数字の扱いで迷いやすい言葉の違いを、判断の手順と例文で整理しています。

Smart-HowToをフォローする
Excel・ツール・AI
Smart-HowToをフォローする
Smart HowTo
タイトルとURLをコピーしました