「合計ならSUM関数で足りるのに、なんでSUBTOTAL（サブトータル）なんて使うの？」——最初はみんなそう思います。でも、フィルターで表を絞り込みながら集計する場面に一度でも出会うと、この関数の便利さが一気にわかります。この記事では、Excelを触りはじめたばかりの方に向けて、SUBTOTAL関数を図解と例をたっぷり使って、ゼロからやさしく説明します。
1. SUBTOTAL関数とは？ ひとことで言うと
SUBTOTAL関数を、思いきりシンプルに言うとこうです。
💡 SUBTOTALをひとことで
「合計・平均・件数など、いろいろな集計を1つでこなす関数」。しかも フィルターで見えなくなった行は集計に入れない という、他の関数にはない特技を持っています。
ふつう、Excelで合計を出すときはSUM関数、平均ならAVERAGE関数、件数ならCOUNT関数……と、目的ごとに関数を使い分けますよね。SUBTOTALは、その使い分けを「番号」で切り替えられる、いわば“万能スイッチ”のような関数です。
そして本当に大事なのはここから。SUMやAVERAGEは、フィルターで行を隠しても、隠れた行まで計算に入れてしまいます。ところがSUBTOTALは、画面に見えている行だけを集計してくれます。「絞り込んだ結果の合計が知りたい」ときに、まさにドンピシャの関数なんです。
2. 基本の書き方（構文）を覚えよう
まずは形を目に焼き付けましょう。SUBTOTAL関数の書き方はこれだけです。
=SUBTOTAL(集計方法, 範囲)
2つの部品でできています。順番に見ていきます。
| 部品 | 意味 |
|---|---|
| 集計方法（第1引数） | 「合計にする？ 平均にする？ 件数にする？」を指定する番号。たとえば 9 なら合計、1 なら平均。番号一覧は次の章で。 |
| 範囲（第2引数） | 集計したいセルの範囲。C2:C11 のように指定します。カンマで区切って複数範囲もOK。 |
たとえば、C2からC11の売上を合計したいなら、こう書きます。
=SUBTOTAL(9, C2:C11) ← 9 は「合計（SUM）」の番号
🔰 覚え方のコツ
「まず番号で“何をするか”を決めて、次に“どこを”集計するか」の順。SUMと違うのは、先頭に番号が1つ増えるだけです。=SUM(C2:C11) が =SUBTOTAL(9, C2:C11) になる、と考えると気楽ですよ。
3. 「集計方法」の番号一覧（1〜11／101〜111）
集計方法の番号は、下の表のとおりです。1〜11と、それに100を足した101〜111の2セットがあります（違いは第6章でじっくり説明します。今は「2列ある」とだけ押さえてください）。
| 集計内容 | 番号 （1〜11） | 番号 （101〜111） | 同じことをする関数 |
|---|---|---|---|
| 平均 | 1 | 101 | AVERAGE |
| 数値の個数 | 2 | 102 | COUNT |
| データの個数（空白以外） | 3 | 103 | COUNTA |
| 最大値 | 4 | 104 | MAX |
| 最小値 | 5 | 105 | MIN |
| 積（掛け算） | 6 | 106 | PRODUCT |
| 標準偏差（標本） | 7 | 107 | STDEV.S |
| 標準偏差（母集団） | 8 | 108 | STDEV.P |
| 合計 | 9 | 109 | SUM |
| 分散（標本） | 10 | 110 | VAR.S |
| 分散（母集団） | 11 | 111 | VAR.P |
✅ 初心者がよく使うのはこの4つ
ぜんぶ覚える必要はありません。実務でよく使うのは、9（合計）・3（データの個数）・1（平均）・4/5（最大・最小）くらい。まずは「9＝合計」だけ覚えれば、今日から使えます。
4. 最大の魅力①：フィルターで隠れた行は集計しない
ここがSUBTOTALの一番の見せ場です。下の売上表で比べてみましょう。
まずはフィルターをかける前。全10件の売上です。
| 店舗 | 担当 | 売上（円） |
|---|---|---|
| 新宿店 | 佐藤 | 120,000 |
| 渋谷店 | 鈴木 | 98,000 |
| 新宿店 | 高橋 | 150,000 |
| 池袋店 | 田中 | 75,000 |
| 渋谷店 | 伊藤 | 110,000 |
| 新宿店 | 山本 | 88,000 |
| 池袋店 | 中村 | 62,000 |
| 渋谷店 | 小林 | 134,000 |
| 新宿店 | 加藤 | 101,000 |
| 池袋店 | 吉田 | 79,000 |
| 合計 | 1,017,000 | |
この時点では、SUM もSUBTOTAL も同じ「1,017,000」を返します。
では、フィルターで「新宿店」だけに絞り込むと、どうなるか。グレーの取り消し線の行が、フィルターで隠れた行です。
| 店舗 | 担当 | 売上（円） |
|---|---|---|
| 新宿店 | 佐藤 | 120,000 |
| 渋谷店 | 鈴木 | 98,000 |
| 新宿店 | 高橋 | 150,000 |
| 池袋店 | 田中 | 75,000 |
| 渋谷店 | 伊藤 | 110,000 |
| 新宿店 | 山本 | 88,000 |
| 池袋店 | 中村 | 62,000 |
| 渋谷店 | 小林 | 134,000 |
| 新宿店 | 加藤 | 101,000 |
| 池袋店 | 吉田 | 79,000 |
このとき、2つの関数はまったく違う答えを返します。
| 数式 | 答え | なぜ？ |
|---|---|---|
=SUM(C2:C11) | 1,017,000 | 隠れた行も足してしまう（絞り込みを無視） |
=SUBTOTAL(9, C2:C11) | 459,000 | 見えている新宿店4件だけを合計（120,000＋150,000＋88,000＋101,000） |
✅ ここが決定的な差
「絞り込んだ結果の合計がほしい」なら、SUMではなくSUBTOTAL。しかも便利なのは、フィルターを別の店舗に切り替えるたびに、答えが自動で変わること。渋谷店に絞れば渋谷店の合計、池袋店に絞れば池袋店の合計が、同じ1つの数式でパッと出ます。
5. 最大の魅力②：SUBTOTAL同士は二重で数えない
もう1つ、地味だけどありがたい特技があります。それは「SUBTOTALは、範囲の中にある別のSUBTOTALの結果を無視する」という性質です。
言葉だと難しいので、例で見ましょう。店舗ごとに小計を作り、最後に総合計を出す表です。
| 行 | 内容 | 金額 |
|---|---|---|
| 2〜4 | 新宿店（3件） | … |
| 5 | 新宿店 小計 =SUBTOTAL(9,C2:C4) | 370,000 |
| 6〜8 | 渋谷店（3件） | … |
| 9 | 渋谷店 小計 =SUBTOTAL(9,C6:C8) | 342,000 |
| 10 | 総合計 =SUBTOTAL(9,C2:C9) | 712,000 |
総合計の範囲 C2:C9 の中には「小計」の行（C5・C9）も含まれています。
ここで注目。総合計の =SUBTOTAL(9, C2:C9) は、範囲の中に小計セル（370,000と342,000）を巻き込んでいます。ふつうに考えると「明細＋小計」を二重に足して、答えが倍近くになりそうですよね。
でも大丈夫。SUBTOTALは、範囲内にある他のSUBTOTALの結果を自動的に飛ばしてくれるので、明細だけをきれいに合計してくれます。もしここをSUMで書いていたら、小計まで足してしまって答えがおかしくなります。
🔰 だから「小計」機能でも使われている
Excelの［データ］タブにある「小計」機能（自動で小計行を挿入してくれる機能）は、裏側でこのSUBTOTALを使っています。二重集計を勝手に避けてくれるからこそ、あの機能が成り立っているんですね。
6. 「9」と「109」の違いをやさしく解説
第3章で出てきた「番号が2列ある」問題を、ここで回収します。結論はこの1点だけです。
✅ 9 と 109 の違いは「手で隠した行」の扱い
| 番号 | フィルターで隠れた行 | 右クリック等で手動で非表示にした行 |
|---|---|---|
| 9（1〜11） | 集計しない | 集計する（隠しても足す） |
| 109（101〜111） | 集計しない | 集計しない（隠せば除く） |
ポイントを整理します。
- フィルターで隠した行は、9でも109でもどちらも集計から外れます。ここは共通です。
- 差が出るのは「行番号を右クリック→非表示」などで手動で隠した行」のときだけ。
9は手動非表示を無視して足しますが、109は手動非表示も除きます。
🔰 迷ったらどっち？
フィルターしか使わないなら、9でも109でも結果は同じです。初心者のうちは、昔からある見慣れた「9」で覚えてOK。「行を手で隠しても、隠した行を集計から外したい」という場面が出てきたら、そのとき「109」を思い出してください。
7. 実例でマスター（例が豊富）
ここからは、コピペ感覚で使える実例をたっぷり並べます。すべて、下の「経費明細」を題材にします。
| No. | 日付 | 科目 | 金額（円） |
|---|---|---|---|
| 1 | 4/2 | 交通費 | 1,280 |
| 2 | 4/5 | 消耗品費 | 3,400 |
| 3 | 4/8 | 交通費 | 640 |
| 4 | 4/11 | 接待交際費 | 12,000 |
| 5 | 4/15 | 消耗品費 | 2,180 |
| 6 | 4/18 | 交通費 | 1,540 |
| 7 | 4/22 | 接待交際費 | 8,600 |
| 8 | 4/25 | 消耗品費 | 5,020 |
金額はセル D2〜D9 に入っているものとします。
例1：合計を出す（番号 9）
=SUBTOTAL(9, D2:D9) → 34,660（全件の合計）
フィルターで「交通費」だけに絞れば、同じ数式のまま自動で 3,460（1,280＋640＋1,540）に変わります。ここがSUMとの決定的な違いでしたね。
例2：件数を数える（番号 3）
=SUBTOTAL(3, D2:D9) → 8（データが入っている件数）
「消耗品費」に絞れば 3 に変わります。絞り込んだ結果が何件あるかを数えたいときの定番です。番号3（COUNTA相当）は文字でも数値でも数えるので、迷ったら3が無難です。
例3：平均を出す（番号 1）
=SUBTOTAL(1, D2:D9) → 4,332.5（全件の平均）
「接待交際費」に絞れば、その2件の平均 10,300 が出ます。「絞った条件の平均単価」を見たいときに便利です。
例4：最大・最小を出す（番号 4／5）
=SUBTOTAL(4, D2:D9) → 12,000（見えている中の最大） =SUBTOTAL(5, D2:D9) → 640（見えている中の最小）
絞り込んだ範囲での「いちばん高い経費」「いちばん安い経費」がすぐわかります。異常値のあたりをつけるのにも使えます。
例5：小計＋総合計を二重集計させずに作る
第5章の応用です。科目ごとに小計を入れ、最後に総合計を =SUBTOTAL(9, …) で作れば、小計行を巻き込んでも二重にカウントされません。SUMで組むと壊れる集計表が、SUBTOTALなら安全に組めます。
例6：フィルターしても崩れない「連番」を振る（上級テク）
これは知っておくと一生モノのテクニックです。フィルターで絞り込むと、A列に手打ちした「1・2・3…」の連番は歯抜けになってしまいます。そこで、連番をこの数式で振ります（A2に入力して下へコピー）。
=SUBTOTAL(3, $D$2:D2)
ポイントは、範囲の始まりを $D$2 と固定（絶対参照）し、終わりを D2 と固定しないこと。すると各行で「先頭から自分の行までに、いくつデータがあるか」を数え、フィルターで絞っても常に 1・2・3… ときれいに振り直されます。印刷資料の通し番号などで重宝します。
🔰 なぜ $ を付けるの？
$（ドルマーク）は「ここは固定」の目印。始点だけ固定して終点を伸ばしていくことで、範囲が1行ずつ広がり、その中の件数＝連番になる、という仕組みです。$は F4 キーで簡単に付けられます。
8. 初心者がやりがちな失敗と注意点
⚠️ 失敗①：番号を書き忘れる
=SUBTOTAL(D2:D9) のように第1引数の番号を抜かすとエラーになります。必ず「番号, 範囲」の2つを書きましょう。
⚠️ 失敗②：合計を SUM だと思い込む
「9」は合計ですが、うっかり =SUBTOTAL(1, …) と書くと平均が出ます。番号の意味を取り違えると、静かに間違った数字が出るので要注意。合計はいつも「9」と口に出して覚えましょう。
⚠️ 失敗③：他の列に SUBTOTAL を横並びにしたとき
集計行に =SUBTOTAL(…) を横に並べる分には問題ありません。ただし、SUBTOTALの結果セルを、別の SUM で足し込むと二重集計になります。集計はできるだけSUBTOTALで統一すると事故が減ります。
⚠️ 失敗④：非表示なのに数字が変わらない
行を「手で非表示」にしたのに合計が減らないときは、番号が9だからです。手動非表示も除きたいなら109に変えます（第6章参照）。フィルターの場合はどちらでも除かれます。
9. SUM・AGGREGATEとの使い分け
| 関数 | 得意なこと | こんなときに |
|---|---|---|
| SUM | ただ全部を足す | フィルターと無関係に「常に全件の合計」がほしいとき |
| SUBTOTAL | フィルターで見えている行だけを集計／小計との二重集計を回避 | 絞り込みながら合計・件数・平均を見たいとき（日常はこれで十分） |
| AGGREGATE | SUBTOTALの上位版。エラー値を無視するなど細かい制御ができる | エラーが混じった表を集計したい等、一歩踏み込んだとき |
ざっくり言えば、「全件ならSUM、絞り込みならSUBTOTAL」。この2つを押さえれば、日々の集計はほぼ回ります。AGGREGATEは「SUBTOTALで物足りなくなったら」で大丈夫です。
🧮 経理マンの一言
経理の現場で、SUBTOTALは「地味に毎日使う関数」の代表格です。私がいちばん重宝するのは、月次の経費チェックのとき。何百行もある明細をフィルターで「交際費だけ」「この部署だけ」と切り替えながら、その場で合計・件数・平均が動く——これがSUMだと、絞り込むたびに数式を組み直すハメになって、締め切り前の貴重な時間が溶けます。
それともう一つ、声を大にして言いたいのが「小計行を巻き込んでも二重に数えない」という性質。SUMで小計＋総合計の表を組んで、金額が合わずに小一時間悩んだ経験、経理なら一度はあるはずです。あれ、集計をSUBTOTALで統一するだけで、そもそも起きません。合わない原因を作らない——これは経理にとって、速さ以上に価値があります。
ただし最後に一言。便利でも、「フィルターの掛け忘れ・掛け間違い」までは関数は守ってくれません。絞り込み条件が正しいか、集計対象の範囲がズレていないかは、必ず人間の目で最終確認を。ツールは判断を速くしてくれますが、判断そのものは、やっぱり私たちの仕事です。
コメント