【Excel】フィルターした行だけ合計したい：SUBTOTALで絞り込み合計（可視セル合計）を出す方法

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

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

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

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

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

今回は、フィルタで見えているデータだけを正しく合計する「SUBTOTAL（サブトータル）関数」と、エラーがあっても計算できる最強の「AGGREGATE（アグリゲート）関数」の使い方を解説します。

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

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

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

💡 そもそも「行を挿入すると計算範囲から漏れる」でお困りなら フィルター以前に、データの追加で合計がズレる場合は、参照範囲の作り方にコツがあります。こちらの記事も参考にしてください。【Excel】行を追加しても合計がずれない！SUM範囲を自動で広げる設定方法

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

=SUBTOTAL(9, 範囲)

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

カッコの中の 9 は「合計（SUM）をしてください」という命令です。とりあえず「フィルター集計なら 9 と書く！」と覚えておけばOKです。

「SUM関数」は微動だにしませんが、「SUBTOTAL関数」はちゃんと絞り込んだデータの合計になります。

実務では、データの中に #DIV/0! などのエラーが混じることがあります。SUBTOTAL関数だとエラーが一つでもあると合計もエラーになりますが、それを回避できるのがAGGREGATE（アグリゲート）関数です。

=AGGREGATE(9, 6, 範囲)

「9（合計）」で「6（エラーを無視）」という意味です

フィルターで絞り込み、かつエラーも飛ばして集計したい現場では、こちらの方が「止まらない帳簿」として重宝されます。

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

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

これが、Excel玄人の鉄則です。ウィンドウ枠の固定を使えば、常に見えていて最強です。

「本当に動くか試したい」という方のために、SUMとSUBTOTALを並べて比較できるデモ用Excelファイルを用意しました。

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

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