エクセルでフィルタした行だけ合計したい。SUMだと隠れた行も足されるんだよ

おはよう、後輩A君。

フィルタで「佐藤さんの分だけ」に絞って、合計の欄を見たことはあるかな。

⚠️ 数字が変わらないんだよね。画面には佐藤さんの行しか出ていないのに、合計は全員分のままなんだ。

私もこれで、会議の直前に慌てたことがあるよ。「佐藤さんの今月の売上はいくら？」と聞かれて、フィルタで絞って合計の欄を読み上げたんだ。⚠️ 全員分の数字を読み上げていたんだよね。

今日はこれを、実際にエクセルで測って確かめたよ。⚠️ 途中でもう1つ落とし穴が見つかったから、それも書くね。

1. まず、SUMはフィルタを気にしないんだよ

こんな表で試したよ。10行の売上だね。

　　　　日付　　担当　　金額
2行目　9/1　　佐藤　　1,000
3行目　9/2　　鈴木　　2,000
4行目　9/3　　佐藤　　3,000
5行目　9/4　　高橋　　4,000
6行目　9/5　　鈴木　　5,000
7行目　9/6　　佐藤　　6,000
8行目　9/7　　高橋　　7,000
9行目　9/8　　鈴木　　8,000
10行目　9/9　　佐藤　　9,000
11行目　9/10　高橋　10,000

全部足すと55,000だよ。佐藤さんの分だけなら 1,000＋3,000＋6,000＋9,000 で19,000だね。

表の横に、ふつうの合計の式を置いたんだ。

=SUM(C2:C11)

そしてフィルタで「担当＝佐藤」に絞ったよ。測った結果はこうだったんだ。

絞る前　　　→　SUM ＝ 55,000
佐藤で絞った　→　SUM ＝ ⚠️ 55,000（変わらない）

⚠️ SUMは、画面に見えているかどうかを気にしないんだよ。書いてある範囲（C2からC11）を、全部そのまま足すんだ。

フィルタは行を隠しているだけなんだよね。消したわけじゃないんだ。だからSUMから見ると、何も変わっていないんだよ。

2. SUBTOTALを使うと、見えている行だけになるんだよ

見えている行だけ合計したいときは、SUBTOTALを使うんだ。「サブトータル」と読むよ。日本語にすると「小計」だね。

書き方はこうだよ。

=SUBTOTAL(9,C2:C11)

最初の9は「合計してね」という合図の番号なんだ。うしろは、SUMと同じ範囲だよ。

同じように佐藤さんで絞って測ったら、こうなったよ。

絞る前　　　→　SUBTOTAL(9) ＝ 55,000
佐藤で絞った　→　SUBTOTAL(9) ＝ ⭐ 19,000

ちゃんと佐藤さんの分だけになったんだ。

⭐ そして絞りを変えると、数字も勝手に変わるんだよ。鈴木さんに絞れば鈴木さんの合計、高橋さんなら高橋さんの合計になるんだ。式を書き換えなくていいんだよね。

「9」という番号は覚えなくても大丈夫だよ。=SUBTOTAL( まで打つと、番号の一覧が出てくるからね。

3. ⚠️ 手で隠した行は、9だと足されてしまうんだよ

ここが今日見つけた落とし穴なんだ。

フィルタを外して、今度は鈴木さんの3行を、手で非表示にしたんだよ。行番号を右クリックして「非表示」を選ぶ、あのやり方だね。見えている行の合計は、55,000 − 15,000 で40,000になるはずなんだ。

測った結果はこうだったよ。

SUM　　　　　　→　55,000（相変わらず全部）
SUBTOTAL(9)　　→　⚠️ 55,000（隠した行も足している）
SUBTOTAL(109)　→　⭐ 40,000（見えている行だけ）

⚠️ 9は、手で隠した行を足してしまったんだよ。

並べるとこうなるね。

　　　　　　　　　フィルタで隠した　　手で隠した
SUM　　　　　　　足す　　　　　　　　足す
SUBTOTAL(9)　　　⭕ 除く　　　　　　　⚠️ 足す
SUBTOTAL(109)　　⭕ 除く　　　　　　　⭕ 除く

9は「フィルタで隠れた行」しか除かないんだ。109は、隠し方に関係なく除くんだよ。

⚠️ これが怖いのは、フィルタだけ使っているうちは、9でも109でも同じ数字が出るところなんだ。佐藤さんで絞ったときは、どちらも19,000だったからね。だから「9で合ってる」と思ったまま使い続けるんだよ。

そしてある日、誰かが見やすくしようとして行を手で隠すんだ。⚠️ そこから数字がずれ始めるんだよね。式はどこも変わっていないのにね。

4. 件数を数えるときも、同じことが起きるんだよ

「何件表示されている？」を数えたいときも同じなんだ。番号を3（数える）にするんだよ。

同じ表で測ったよ。

　　　　　　　　　佐藤で絞った　　鈴木を手で隠した
COUNTA（ふつうに数える）　10件　　　　　10件
SUBTOTAL(3)　　　　⭕ 4件　　　　　⚠️ 10件
SUBTOTAL(103)　　　⭕ 4件　　　　　⭕ 7件

合計と同じ形だね。100を足した番号（103）にすると、手で隠した行も除いてくれるんだ。

⭐ 覚え方はこれだけでいいと思うよ。

100を足した番号は、手で隠した行も除く
合計なら 9 → 109
件数なら 3 → 103

5. ⚠️ 小計が入った表は、両方SUBTOTALにしないと二重に足されるんだよ

もう1つ測ったんだ。これは現場でよく見る形だよ。

　100
200
300
小計（上の3つ）
400
500
小計（上の2つ）
総計（全部）

明細を全部足すと、100＋200＋300＋400＋500 で1,500だよね。

でも総計の欄に、小計の行ごと範囲を指定すると、こうなったんだ。

　　　　　　　　　　　　総計をSUMで　　総計をSUBTOTALで
小計をSUMで作った　　　⚠️ 3,000　　　⚠️ 3,000
小計をSUBTOTALで作った　⚠️ 3,000　　　⭐ 1,500

⚠️ 3,000というのは、小計も明細と一緒に足してしまった数字なんだ。倍になっているんだよね。

⭐ SUBTOTALには、範囲の中にある別のSUBTOTALは足さないという決まりがあるんだ。だから小計も総計もSUBTOTALにすると、明細だけを足してくれるんだよ。

⚠️ ここで大事なのは、総計だけSUBTOTALにしても直らなかったことなんだ。小計がSUMのままだと、SUBTOTALはそれを「ふつうの数字」だと思って足してしまうんだよ。小計も総計も、両方SUBTOTALにしたときだけ正しくなったんだ。

6. 迷ったら109だよ

ここまでをまとめると、こう考えていいと思うんだ。

見えている行だけ合計したい　→　=SUBTOTAL(109, 範囲)
見えている行だけ数えたい　　→　=SUBTOTAL(103, 範囲)
小計を入れる表　　　　　　　→　⚠️ 小計も総計も両方SUBTOTAL
表のすぐ下に合計の行を置く　→　⭐ SUBTOTALならフィルタで消えない（SUMだと行ごと隠れた）

109にしておけば、フィルタで隠しても、手で隠しても、見えている行だけになるんだ。今日測った中で、どの場面でも見えている数字と合っていたのは109だけだったよ。

⚠️ 9を使うのは、「手で隠した行は、見えていなくても数に入れたい」ときだけだね。そういう場面は、私の仕事ではあまりなかったんだ。

7. エクセルの記事、これで18本目だよ

フィルタが途中までしか効かない　→　絞る範囲がずれる
並べ替えで行がバラバラ　　　　→　並べる範囲がずれる
VLOOKUPで#N/A　　　　　　　　→　探す範囲が足りない
フィルタした行だけ合計（今日）　　→　足す範囲に見えない行が入っている

⚠️ 並べてみると、全部「範囲」の話なんだよね。

エクセルの式は、書いてある範囲を、書いてあるとおりに計算するんだ。画面にどう見えているかは気にしないんだよ。VLOOKUPの4番目の記事で「エクセルは黙ったまま進める」と書いたけれど、今日の話もその仲間なんだ。

⚠️ 画面で見えているものと、式が見ているものは、別なんだよ。

結論：見えている行だけなら、SUBTOTALの109だよ

今日測った結果をまとめるとこうだよ。

① ⚠️ SUMはフィルタを気にしない。絞っても55,000のままだった
② SUBTOTAL(9)で、フィルタで絞った行だけ（19,000）になる
③ ⚠️ でも手で隠した行は、9だと足される（55,000のまま）
④ ⭐ SUBTOTAL(109)なら、フィルタでも手で隠しても見えている行だけ（40,000）
⑤ 件数も同じ。3より103
⑥ ⚠️ 小計がある表は、小計も総計も両方SUBTOTAL。総計だけでは二重に足される
⑦ ⭐ 表のすぐ下の合計の行も、SUBTOTALならフィルタで消えない（SUMだと行ごと隠れた）
⑧ 迷ったら109

私が会議の前に読み上げた数字は、エクセルは正しく計算していたんだよね。全部足してね、と私が書いたとおりに足していたんだ。

⚠️ 間違えていたのは、画面に見えている数字と、式が足している数字が同じだと思い込んだ私のほうだったんだ。

後輩A君も、フィルタで絞って合計を読むことがあると思うんだ。⚠️ そのとき合計の欄の式を1回だけ見てほしいんだよ。SUMだったら、それは全員分なんだ。

絞ったら数字が変わるか。それを1回確かめるだけで、読み上げる数字を間違えないんだよ。

よくある質問

Q1. 9と109、どちらを使えばいいか迷います

⭐ 迷ったら109でいいと思うよ。今日測った中で、フィルタでも手で隠しても、見えている数字と合っていたのは109だったからね。⚠️ 9を選ぶのは「手で隠した行も合計に入れたい」とはっきり決めているときだけだよ。

Q2. 合計の行を、表のすぐ下に置いても大丈夫ですか

⚠️ 合計の式によって答えが変わったよ。これも測ったんだ。

表のすぐ下（12行目）に合計の行を置いて、フィルタで佐藤さんに絞ったんだよ。エクセルは合計の行まで表の一部だと推測していたんだ。そのうえで、こうなったよ。

合計の行が SUBTOTAL　→　⭐ 合計の行は隠れずに残った（19,000を表示）
合計の行が SUM　　　 →　⚠️ 合計の行ごと隠れた
合計の行に数字を直接入力　→　⚠️ 合計の行ごと隠れた

⭐ SUBTOTALにしておくと、エクセルが「これは合計の行だ」と分かって残してくれるんだ。SUMだと、ほかの明細と同じ扱いで絞り込まれて消えてしまうんだよね。

⇒ 表のすぐ下に合計を置くならSUBTOTAL(109)にしようか。SUMのまま置くなら、表とのあいだに空の行を1つあけるか、表の横に置くほうが安全だよ。どこまでを表だと思うかは、フィルタの範囲の記事に書いたとおり、エクセルが空の行を目印に推測しているからね。

Q3. フィルタを外したら、数字は戻りますか

戻るよ。フィルタを外すと全部の行が見えるから、SUBTOTALも全部の合計（今日の表なら55,000）になるんだ。⭐ SUBTOTALはそのとき見えている行で計算し直すので、式を書き換えなくていいんだよ。

Q4. SUBTOTALを使うと、何か困ることはありますか

⚠️ 1つだけあるよ。「全員分の合計」がほしい欄に、うっかりSUBTOTALを使うことなんだ。誰かがフィルタをかけたまま保存すると、次に開いた人はその数字を全員分だと思って読んでしまうかもしれないんだよね。⇒ だから私は、全員分の合計はSUM、見えている分の合計はSUBTOTALと、欄を分けて2つ置くようにしているよ。見出しに「全体」「表示中」と書いておくと迷わないんだ。
【本日のミッション：絞ったら、合計が変わるか見てみようか】

• ☐ よく使う表でフィルタをかけて、合計の欄の数字が変わるか見てみようか
• ☐ 変わらなかったら、その式がSUMかどうか確かめて、=SUBTOTAL(109, 範囲) の欄を横に1つ足してみようか
• ☐ 小計が入っている表なら、⚠️ 小計と総計の両方がSUBTOTALになっているか見てみようか
• ☐ （後輩A君へ）数字を人に伝える前に、画面で見えているものと、式が見ているものが同じかを1回だけ疑ってみようか。⚠️ エクセルはいつも正しく計算しているんだ。ずれるのは、こちらの思い込みのほうなんだよ