エクセル時短・トラブル解決

Excelでフィルター後の合計をSUBTOTALで正しく出す方法

Excelでフィルターをかけた後の合計を出したいなら、SUBTOTAL関数を使うのが最短です。普通のSUMだと非表示の行まで合計してしまいますが、SUBTOTALなら表示されている行だけを集計できます。仕事中に「フィルターしたのに合計が合わない…」と焦りやすいポイントなので、ここでサッと解決しておきましょう。

結論：フィルター後の合計はSUBTOTAL関数を使えばOKです

フィルター後の表示行だけ合計するなら =SUBTOTAL(9,範囲) を使います。手動で非表示にした行も除外したいなら =SUBTOTAL(109,範囲) です。

まず押さえたいのは、SUBTOTALの最初の数字です。ここで集計方法が決まります。

  • 9：合計。フィルターで非表示の行は除外します
  • 109：合計。フィルターで非表示の行に加えて、手動で非表示にした行も除外します

たとえば金額がC2:C100に入っているなら、次のように入力します。

  • =SUBTOTAL(9,C2:C100)
  • =SUBTOTAL(109,C2:C100)

普段の業務でフィルター集計をするだけなら、まずはSUBTOTAL(9,範囲)を覚えておけば十分ですよ。

Excelでフィルター後の合計を出す手順

ここでは、売上表や一覧表でフィルターした結果だけを合計する基本手順を、順番に説明します。

  1. 合計したい表を開きます
  2. 表内のどこかをクリックします
  3. 「データ」タブをクリックします
  4. 「フィルター」をクリックします
  5. 見出し行の▼をクリックして、必要な条件で絞り込みます
  6. 合計を表示したいセルをクリックします
  7. =SUBTOTAL(9,合計範囲) と入力します
  8. Enterキーを押します

たとえば、C列の金額を合計したいなら、合計欄に =SUBTOTAL(9,C2:C100) と入力すればOKです。これで、フィルター後に見えている行だけが合計されます。

オートSUMから入れる方法

関数を手入力するのが不安なら、リボンから入れる方法もあります。

  1. 合計を表示したいセルをクリックします
  2. 「ホーム」タブをクリックします
  3. 「Σ オートSUM」の▼をクリックします
  4. 「その他の関数」または環境によっては「集計」を選びます
  5. 関数一覧からSUBTOTALを選びます
  6. 集計方法に9、参照範囲に合計したいセル範囲を指定します
  7. OKをクリックします

ただ、実務では手入力のほうが速いことが多いです。特に何度も使うなら、SUBTOTALの形をそのまま覚えてしまうのが時短になります。

SUMではダメなのはなぜ？

SUM関数は非表示の行も含めて合計するからです。つまり、フィルターで「東京支店だけ表示」にしていても、SUMは大阪支店や名古屋支店の金額まで足してしまいます。

一方でSUBTOTALは、フィルターで隠れた行を自動で除外してくれます。なので、一覧表を絞り込んで確認する仕事ではSUMよりSUBTOTALのほうが実務向きです。

SUBTOTALの「9」と「109」の違い

ここはつまずきやすいので、短く整理しておきます。

  • 9：フィルターで非表示になった行は除外する
  • 109：フィルターで非表示になった行に加えて、手動で非表示にした行も除外する

普段のフィルター集計だけなら9で困らないことが多いです。ただし、行番号を右クリックして「非表示」にしている表では109のほうが安心です。

実務でよくある使い方

担当者別の売上合計を出す

  1. 担当者列にフィルターを設定します
  2. 集計したい担当者だけにチェックを入れます
  3. 売上列に対して =SUBTOTAL(9,範囲) を設定します
  4. 担当者を切り替えるたびに合計が自動更新されます

月別データの一部だけ合計する

  1. 日付列や月列にフィルターを設定します
  2. 必要な月だけ表示します
  3. 金額列にSUBTOTALを入れます
  4. 月ごとの確認作業が一気に楽になります

上手くいかない場合のチェックポイント

「SUBTOTALを入れたのに合計がおかしい」ときは、次の点を確認してみてください。

1. SUM関数を使っていないか

見た目は似ていますが、SUMではフィルター後の表示行だけを合計できません。式が =SUM(範囲) になっていたら、=SUBTOTAL(9,範囲) に直しましょう。

2. 範囲の指定がずれていないか

たとえば本当はC2:C100を合計したいのに、C1:C100やB2:B100を指定していると、結果が合いません。特に見出し行を含めてしまう、別列を選んでしまう、途中で範囲が切れている、というミスは多いです。

3. 数字が文字列になっていないか

見た目は数値でも、文字列として入力されていると正しく集計されないことがあります。左寄せになっている数字や、先頭にアポストロフィが付いたデータは要注意です。

  • セルをクリックして数式バーを確認する
  • エラーアイコンが出ていれば内容を確認する
  • 必要に応じて数値形式に直す

4. 手動で非表示にした行を除外したいのに「9」を使っている

手動で非表示にした行まで除外したい場合は、9ではなく109を使います。ここが原因で「まだ数字が多い」と感じるケースはよくあります。

5. SUBTOTALの中にさらにSUBTOTALが入っていないか

小計や別の集計セルを含む範囲を指定すると、意図しない結果になることがあります。元データの列だけを素直に指定するのが安全です。

6. フィルターが設定されていないか、条件が正しくない

そもそもフィルターが有効になっていない、または絞り込み条件が違っていると、合計も期待通りになりません。見出しに▼が表示されているか、必要な項目だけにチェックが入っているか確認しましょう。

クリック操作でフィルターを設定し直す手順

フィルター自体が怪しいときは、いったん入れ直すのが早いです。

  1. 表の中をクリックします
  2. 「データ」タブをクリックします
  3. 「フィルター」をクリックして一度解除します
  4. もう一度「フィルター」をクリックします
  5. 見出しの▼から絞り込み条件を設定し直します
  6. SUBTOTALの結果が変わるか確認します

関連する便利ワザ

テーブル化するとフィルター管理が楽です

表をテーブルにしておくと、フィルターや集計範囲の管理がしやすくなります。

  1. 表の中をクリックします
  2. Ctrl + T を押します
  3. 「先頭行をテーブルの見出しとして使用する」にチェックが入っているか確認します
  4. OKをクリックします

行が増えても範囲のズレが起きにくくなるので、毎月更新する一覧表で便利ですよ。

フィルター操作で使えるショートカット

  • Ctrl + Shift + L：フィルターのオン・オフ
  • Alt + ↓：選択中の見出しセルでフィルターメニューを開く

マウス移動を減らせるので、件数の多い表を触るときにかなり速くなります。

件数を数えたいならSUBTOTALの別番号も便利です

合計だけでなく、表示中データの件数確認にもSUBTOTALは使えます。たとえば数値が入ったセルの件数を数えたいなら、=SUBTOTAL(2,範囲) や、手動非表示も除外したいなら =SUBTOTAL(102,範囲) が使えます。

まとめ

Excelでフィルター後の合計を正しく出したいときは、SUMではなくSUBTOTALを使いましょう。基本は =SUBTOTAL(9,範囲)、手動で非表示にした行も除外したいなら =SUBTOTAL(109,範囲) です。これだけで、フィルターしたのに合計が合わないストレスをかなり減らせます。まずは今使っている表で一度試して、SUMをSUBTOTALに置き換えるところから始めてみてくださいね。

おすすめの記事