Excelでフィルターをかけても合計が変わらない|表示中の行だけを集計する方法

IT・プログラミングの仕事と学習

「Excelの売上一覧を担当者で絞ったのに合計金額が変わらない」

「フィルターで見えている行だけを足すにはどの関数を使えばいい?」

売上表や経費台帳を使う仕事では、表示している明細と合計額が合わず困ることがあります。

フィルターで表示されている行だけを合計するにはSUBTOTAL関数を使う方法があります。

例えば金額がC2:C100にあるなら、次の式が候補です。

=SUBTOTAL(109,C2:C100)

この式ではフィルターで除外された行を集計しません。手動で非表示にした行も対象から外れます。

一方で通常の \=SUM(C2:C100)\ はフィルターで隠した行の金額も足します。つまり合計が変わらないこと自体は、必ずしもExcelの故障ではありません。

この記事では5行の実例でSUM・SUBTOTALの違いを比較します。「9」と「109」の使い分け、Excelテーブルの集計行、計算結果が合わない場合の確認方法まで解説します。

まず結論|SUMとSUBTOTALは非表示行の扱いが違う

Excelには集計する行の条件に応じて複数の方法があります。

計算式 フィルターで除外された行 手動で非表示にした行 用途
=SUM(C2:C100) 含む 含む 全明細の合計
=SUBTOTAL(9,C2:C100) 含まない 含む フィルター結果の集計
=SUBTOTAL(109,C2:C100) 含まない 含まない 画面に見える行の合計

Microsoftの公式サポートでは、SUBTOTALの集計方法番号が1~11なら手動で非表示にした行を含み、101~111ならその行を除外すると説明しています。

ただしフィルターで除外された行は、9でも109でも集計されません。

「フィルターを使っているのにSUMの合計が変わらない」という場合は、まず集計セルの数式バーを確認しましょう。

ExcelのSUMはフィルターで隠れた行も合計する

フィルターは削除ではなく一時的な非表示

Excelのフィルターは条件に合わない行を隠して、必要な明細だけを画面に表示します。

例えば部署列で「営業部」だけを選択すれば、総務部や経理部の行は見えなくなります。

しかし元データは残っています。SUM関数が参照するセル範囲も自動では変わりません。

そのため \=SUM(C2:C100)\ の計算結果は、部署のフィルターを切り替えても通常は全件の合計のままです。

全件合計が必要ならSUMのままでよい

全社の売上やすべての経費を集計したいなら、SUMの動作が目的に合います。

一方で「営業部だけの売上」「10月分だけの支出」のように表示条件を切り替えたいなら、SUBTOTALが候補です。

同じ表に全件合計と表示中の合計を別々に置く方法もあります。

どの数字を報告したいか決めてから関数を選ぶことが重要です。

5行の売上一覧でSUMとSUBTOTALを比較

ここでは簡単な例を使います。

A列が取引日、B列が部署、C列が金額です。1行目は見出しで2~6行目に明細があります。

行番号 取引日 部署 売上金額
2 10月1日 営業部 100
3 10月1日 総務部 200
4 10月2日 営業部 150
5 10月2日 経理部 80
6 10月3日 営業部 120

全件合計は650

すべての行を足すと次の計算になります。

100+200+150+80+120=650

\=SUM(C2:C6)\ の結果は650です。

営業部だけを表示すると3行になる

B列のフィルターから「営業部」だけを選びます。

表示されるのは2・4・6行目です。

行番号 部署 売上金額
2 営業部 100
4 営業部 150
6 営業部 120

これらの金額だけを足すと 100+150+120=370 です。

SUMでは650のまま

フィルターをかけても \=SUM(C2:C6)\ の結果は650です。

隠れた総務部の200と経理部の80が含まれているためです。

SUBTOTAL(109)なら370になる

同じ範囲を次の式で計算します。

\=SUBTOTAL(109,C2:C6)\

営業部だけが表示されていれば結果は370です。

フィルターを解除してすべての行を表示すると650へ戻ります。

ただし手動で非表示にした行が残っている場合は109がその行も除外します。その点は後で詳しく説明します。

Windows版Excelで表示行だけの合計を作る手順

STEP1:原本を保存して現在の数値を控える

既存の集計式を書き換える前に元のブックを保存します。

業務用なら作業用コピーを作れるか確認してください。

全件の件数と合計、現在のフィルター条件を控えておくと修正後の照合に役立ちます。

共有台帳では管理担当者の承認なく集計式を変えないようにしましょう。

STEP2:フィルターの条件を確認する

[データ]→[フィルター]から、対象列の条件を確認します。

例えば「部署=営業部」に加えて「月=10月」も設定されていないか見てください。

複数の列にフィルターが設定されていると、そのすべてを満たす行だけが対象になります。

STEP3:金額列の正しい範囲を確認する

C2:C100のように、金額の明細だけを参照する範囲を決めます。

見出し行は通常含めません。途中に集計行や報告用の合計が入っていないかも確認しましょう。

C101が合計セルなら \=SUBTOTAL(109,C2:C101)\ のように自分自身を含める式は避けてください。

STEP4:まず別の空きセルへ式を入力する

現在のSUMをすぐに消さず、別の空きセルへ次の式を入力して比較します。

\=SUBTOTAL(109,C2:C100)\

例えばE2を検算用セルに使えます。

ただしそのセルがほかの業務で使われていないことを確認してください。

STEP5:表示中の明細だけを手で足してみる

表示されている行が少なければ金額を手計算してみましょう。

5行の例では営業部だけの合計は370です。

SUBTOTALの結果が370なら期待する表示行の集計と一致します。

STEP6:フィルターを解除して結果を比べる

フィルターをすべて解除します。

手動非表示の行がなければSUBTOTAL(109)は全件合計650に戻ります。

再び営業部だけを選択して370になるか確認してください。

フィルターの切替と数値の変化が連動しているかを実際に確かめることが大切です。

SUBTOTALの「9」と「109」は何が違う?

どちらも合計を求める番号です。

違いは手動で非表示にした行を含むかどうかです。

9は手動で非表示にした行を含む

\=SUBTOTAL(9,C2:C6)\

この式ではフィルターで除かれた行は集計されません。

一方で行番号を選んで[非表示]にした行は計算に含まれます。

営業部だけのフィルターを設定したとき、4行目をさらに手動で非表示にしても結果は370です。

109は手動で非表示にした行を除く

\=SUBTOTAL(109,C2:C6)\

こちらは手動で隠した行も計算から除きます。

営業部だけのフィルターに加えて4行目を手動で隠すと、画面には100と120が表示されます。

結果は 100+120=220 です。

行の状態 SUBTOTAL(9) SUBTOTAL(109)
全5行を表示 650 650
営業部だけフィルター表示 370 370
さらに4行目を手動で非表示 370 220

どちらを選ぶべき?

「現在画面に見えている明細だけ」を集計するなら109を検討します。

「フィルターの条件だけを合計へ反映し、手動で隠した行も含めたい」なら9が適しています。

Excelを引き継いだ場合は手動で非表示にした行があるか確認しましょう。

9と109の違いはフィルターではなく手動非表示の扱いです。

Excelテーブルの[集計行]を使う方法

売上表がExcelテーブルとして管理されている場合は、関数を直接入力するほかに[集計行]を使えます。

STEP1:元の一覧がテーブルか確認する

Excelテーブルは[挿入]→[テーブル]などで作成する専用のデータ管理機能です。

セルに罫線があるだけの表とは異なります。

テーブル内のセルを選ぶと[テーブル デザイン]に相当するタブが表示されます。

STEP2:[集計行]を有効にする

Windows版Excelでは次の操作を行います。

  1. テーブル内のセルをクリックする
  2. [テーブル デザイン]タブへ進む
  3. [集計行]にチェックを入れる
  4. テーブル下部に追加された集計行を確認する

タブ名や表示位置はExcelのバージョンによって変わることがあります。

STEP3:金額列で[合計]を選ぶ

集計行の金額列へ移動し、ドロップダウンから[合計]を選びます。

Microsoftの公式案内では、集計行でSUBTOTAL関数が使用されます。

例えば売上金額の列なら \=SUBTOTAL(109,[売上金額])\ のような式です。

これはExcelテーブルの構造化参照を使った書き方です。

STEP4:フィルターを切り替えて確認する

部署や月のフィルターを切り替えます。

表示された行の合計と集計行の数値が一致するか確認してください。

ただしテーブルを作っただけで[集計行]が有効になるとは限りません。

また集計行を隣の列へコピーするときはフィルハンドルを使うようMicrosoftが案内しています。通常のコピー・貼り付けでは列参照が意図どおり変わらない場合があります。

小計行を含むときは二重集計に注意

SUBTOTALで作った入れ子の小計は原則除かれる

Microsoftの公式サポートでは、SUBTOTALの参照範囲内に別のSUBTOTALによる小計がある場合は二重計算を避けるため、その小計が無視されると説明しています。

部署別のSUBTOTALを含む一覧で総合計を出すときに役立つ仕様です。

SUMで作った小計まで自動的に除外されるわけではない

明細の途中にSUMで作った小計や固定の集計値がある場合は注意が必要です。

SUBTOTALがすべての小計欄を見分けて除外するわけではありません。

例えば明細がC2:C10でC11にSUMの小計がある場合、C11まで含めると合計が重複するおそれがあります。

どこまでが明細でどこからが小計か確認してください。

集計セル自身を参照しない

C101へSUBTOTALを入れる場合はC101を範囲に含めないでください。

自分自身を参照する式は循環参照などの原因になります。

範囲の最終行と合計セルの番地を確認しましょう。

SUBTOTALを入力しても金額が合わないとき

① フィルターが実際に設定されていない

フィルターの▼が表示されているだけでは、行が絞り込まれているとは限りません。

対象列の条件を開き、不要な値が選択されていないか確認してください。

② 複数列のフィルターが残っている

「営業部だけ」と思っていたら「10月だけ」という条件も残っている場合があります。

この場合は両方の条件に合う行だけが表示されます。

設定中のフィルターを一覧にして確認しましょう。

③ 参照範囲が追加行まで届いていない

C2:C150まで売上があるのに式がC2:C100なら、新しい50行を集計できません。

最終行まで含まれているか確認してください。

集計行を含めて二重計算しないよう、範囲の末尾も点検します。

④ 金額の一部が文字列になっている

CSVから取り込んだ売上金額が文字列として保存されていると、合計へ含まれないことがあります。

数字に見えることとExcelが数値として認識していることは別です。

元のデータを保存したうえでセルの値と表示形式を確認しましょう。

⑤ 手動で非表示にした行が残っている

SUBTOTAL(109)では手動非表示の行も除外されます。

フィルターを外してもSUMと合計が一致しない場合は、手動で隠した行を確認してください。

9と109ではその行の扱いが違います。

⑥ 元の表に小計が含まれている

明細と小計を同じ列に入れている場合は、集計式の範囲に注意してください。

SUBTOTALの入れ子は除外されますが、SUMの小計や数値の固定値まで同じ扱いになるとは限りません。

⑦ Excelが手動計算になっている

入力やフィルターを変えた後に結果が更新されない場合、計算方法の設定も確認しましょう。

Windows版Excelでは[数式]→[計算方法の設定]で自動・手動を切り替えられます。

ただしデスクトップ版ではほかに開いているブックへ影響する場合があります。共有の大きな集計ファイルは担当者に確認してから変更してください。

SUM・SUBTOTAL・SUMIFをどう使い分ける?

どの方法を使うかは報告したい数字で決まります。

知りたい金額 関数の候補 使い分け
全件の金額 SUM 非表示行も含める
フィルター後の金額 SUBTOTAL(9) 手動非表示の行を含む
実際に表示中の行の金額 SUBTOTAL(109) 手動非表示の行も除外
常に営業部だけの金額 SUMIFなど フィルターと無関係に条件で計算

例えば営業部の合計を固定して出したいならSUMIFも選択肢です。

ただしSUMIFは画面のフィルターを変更するたびに自動で同じ条件へ切り替わる仕組みではありません。

画面の表示行に連動した集計をしたいならSUBTOTALを使うと覚えると判断しやすくなります。

修正後の検算|合計と件数を照合する

表示行の金額を手で足す

少ない件数なら表示された金額を手計算するのが分かりやすい方法です。

5行の例では営業部の明細100・150・120を足して370です。

SUBTOTALの結果と一致するか確認しましょう。

部署を切り替えて別の結果も確認

総務部だけなら200、経理部だけなら80です。

それぞれの部署だけを表示して、計算結果が一致するか確認します。

すでに月のフィルターもある場合はその条件も考慮してください。

フィルターを解除して全件を照合

全行を表示し手動非表示も解除した状態なら、SUM(C2:C6)とSUBTOTAL(109,C2:C6)はいずれも650です。

この結果も確認しましょう。

ただしSUMとSUBTOTALの参照範囲が異なれば一致しない場合があります。

表示中の件数も確認する

全件に値が入っている識別列があるなら、表示中の件数を確かめられます。

例えばA2:A100のすべてに取引番号が入っている場合です。

\=SUBTOTAL(103,A2:A100)\

103はCOUNTAに相当し、フィルターで除外された行と手動非表示の行を除いて空白でないセルを数えます。

ただし参照列に空欄があるとその行を数えないため注意してください。

よくある質問

Q. フィルターしたのにSUMの合計が変わらないのは故障ですか?

故障とは限りません。SUMは通常フィルターで隠した行も集計します。

表示行だけの合計が必要ならSUBTOTALを検討しましょう。

Q. SUBTOTAL(9)と(109)はどちらが正しい?

どちらも有効な式です。

手動で非表示にした行を含める場合は9、除外する場合は109です。

フィルターで除外された行はどちらも含みません。

Q. SUBTOTAL(109)は隠した列の金額も除きますか?

SUBTOTALは基本的に縦方向の範囲を集計する関数です。

横方向の範囲で列を非表示にしても期待したように結果が変わらない場合があります。

行の非表示状態と区別してください。

Q. テーブルの集計行を使えば自動で更新されますか?

Excelテーブルの集計行で合計を選ぶとSUBTOTALの式が設定されます。

そのテーブル内でフィルターを変更すれば、表示行に応じた合計を確認できます。

ただし計算方法やテーブルの参照先に問題があれば別の確認が必要です。

Q. SUBTOTALは途中の小計も足してしまう?

SUBTOTALで作った入れ子の小計は通常除外されます。

SUMで作った小計や固定値などは対象に入る場合があるため、集計範囲を確認しましょう。

Q. 表示されている金額が0になるのはなぜ?

対象行がない、金額セルが文字列になっている、集計範囲が違うなどが考えられます。

まずフィルター条件と元データの型を確認してください。

Q. フィルターを解除しても合計が少ないのはなぜ?

109なら手動非表示行が残っている可能性があります。

数式の参照範囲が途中で終わっている場合もあります。SUMとの比較には対象範囲をそろえましょう。

まとめ|表示中の行だけならSUBTOTAL(109)を使い分けよう

Excelのフィルターで画面の行数が減っても、SUMは通常すべての明細を計算します。

表示中の行だけを集計するなら =SUBTOTAL(109,C2:C100) が候補です。

手動で非表示にした行も合計したい場合は \=SUBTOTAL(9,C2:C100)\ を使います。

Excelテーブルを利用しているなら[集計行]でもSUBTOTALを使った合計を設定できます。

最後に部署や月のフィルターを切り替え、表示明細の金額と計算結果を照合しましょう。

関数を変えるだけでなく「どの条件の合計を報告するか」を明確にすることが大切です。

参考資料・公式サイト

※公式資料の確認日:2026年10月28日。Excelのバージョンや共有設定によりメニューが一部異なる場合があります。