【Excel】「絞り込んだのに合計が変わらん！」フィルタ集計の罠はSUBTOTAL関数で解決せよ

お疲れ様です。「メリ爺の事務攻略万歳！」へようこそ。管理人のメリ爺です。

現場ごとの経費を集計しようとして、Excelでフィルタ（▼ボタン）をポチポチ。「よし、これで『東京現場』だけの経費が出たな」と思って合計金額を見たら……

「あれ？金額が絞り込む前と同じだぞ？」

こんな経験、ありませんか？そして結局、「Excelは信用できん！」と怒りながら、画面を見つつ電卓を叩いて再計算していませんか？

それ、Excelが壊れているのではありません。あなたが使っている「SUM（サム）関数」の仕様（ワナ）なのです。

今回は、フィルタで見えているデータだけを正しく合計する「SUBTOTAL（サブトータル）関数」の使い方を解説します。

結論：SUM関数は「隠れている行」も足してしまう

普段、足し算をする時に使っている =SUM( ) という関数。こいつは非常に真面目なので、フィルタ機能で行が非表示になって隠れていても、頑なに「全部」足し続けます。

だから、「東京現場」だけに絞り込んでも、合計金額には「大阪」も「名古屋」も含まれたままなのです。

我々がやりたいのは「今、画面に見えているものだけ合計したい」ですよね。その願いを叶えるのがこの関数です。

=SUBTOTAL(9, 範囲)

（読み方：サブトータル）

「ん？カッコの中の 9 って何だ？」と思いましたね。ここが唯一の難しいポイントですが、理屈は抜きにしてこう覚えてください。

SUBTOTAL関数は、「9」と書けば合計するし、「3」と書けば個数を数える、万能選手なんです。今回は足し算がしたいので「とりあえず 9 と書く！」と覚えておけばOKです。

百聞は一見にしかず。実際に、この2つの関数を並べて、フィルタを掛けてみた結果がこれです。

「SUM関数」は微動だにしませんが、「SUBTOTAL関数」はちゃんと絞り込んだデータの合計になっていますね。 これで、いちいち電卓を叩く必要はなくなります。

ここで、メリ爺からもう一つアドバイス。皆さんは合計欄（SUM）を、表の一番下に作っていませんか？

実はフィルタ集計をする場合、「一番下」はNGです。なぜなら、フィルタを掛けて行が非表示になると、一番下の合計行まで一緒に隠れて見えなくなってしまう事故が多発するからです。

「集計欄は、表の上に作る」

これが、Excel玄人の鉄則です。表の上なら、どんなにフィルタを掛けても隠れることはありませんし、スクロールせずに結果が見えます。「ウィンドウ枠の固定」機能を使えば、常に見えていて最強です。

「数式を入れるのが面倒くさい」「本当に動くか試したい」

そんな方のために、SUMとSUBTOTALを並べて比較できるデモ用Excelファイルを用意しました。以下のボタンからダウンロードして、実際にフィルタをいじってみてください。

（※マクロなしの安全なファイルです）

これを確認できたら、あなたの会社のExcel帳簿も SUM から SUBTOTAL(9, ...) に書き換えてしまいましょう。

たったこれだけのことで、検算の手間がゼロになります。「Excelが合わない！」とイライラする時間を、もっと生産的な時間（あるいは休憩）に使いましょう。

もし、「ウチの複雑な帳簿でも使えるかな？」と不安があれば、お気軽にコメントで相談してください。それでは、ご安全に！