経理や総務の仕事でExcelのオートフィルターを使い、特定の部署・期間・担当者だけに行を絞り込んだあと、「画面に見えている行だけの合計」を出したい場面はよくあります。ところが合計欄にいつも通りSUM関数を入れてしまうと、フィルターで非表示になっているはずの行の数値までこっそり計算に含まれてしまい、電卓で検算した数字と合わずに首をかしげることになります。この記事では、見えている行だけを正しく集計するSUBTOTAL関数の使い方と、集計方法の番号選びで起きやすい失敗を実務目線で整理します。
SUBTOTALとSUMの決定的な違い
SUM関数は、指定したセル範囲に含まれる数値をすべて機械的に合計します。この「すべて」には、オートフィルターによって非表示になっている行の数値も含まれます。見た目には数行しか表示されていなくても、SUMは非表示行の数値までしっかり拾って合計してしまうため、フィルターで部署や商品を絞り込んだつもりが、合計金額は全部署・全商品分のままになっているという事故が起こります。SUBTOTAL関数は、このオートフィルターで非表示になった行を自動的に無視して集計してくれる関数です。同じ範囲・同じデータを指定しても、SUMとSUBTOTALでは結果が変わってくるのはこのためです。
基本の書き方
SUBTOTALは「集計方法」と「範囲」の2つの引数を取ります。集計方法には1〜11、または101〜111の番号を指定し、合計・平均・件数など何を計算するかを切り替えます。
例：C列（金額）にオートフィルターがかかっている表で、表示されている行だけの合計を出す場合
=SUBTOTAL(9, C2:C100)
集計方法「9」はSUM（合計）を意味します。1〜11の番号にはAVERAGE、COUNT、COUNTA、MAX、MIN、PRODUCT、STDEV、STDEVP、SUM、VAR、VARPの11個の関数が割り当てられており、9番目にあたるのがSUMというわけです。すべての番号を暗記する必要はなく、実務でよく使うのは合計の「9」（または後述の「109」）と、件数を数える「3」（COUNTA）の2つがほとんどです。
集計方法9と109の違い
SUBTOTALのややこしいポイントが、集計方法の番号が1〜11と101〜111の2系統に分かれていることです。どちらもフィルターで非表示になった行は自動的に除外しますが、「手動で右クリックして非表示にした行」の扱いが異なります。
9
- フィルター非表示行は自動で除外
- 手動で非表示にした行は合計に含む
109
- フィルター非表示行は自動で除外
- 手動で非表示にした行も合計から除外
覚え方
- 100を足すと「手動非表示も除外」になる
- 迷ったら109を選ぶと安全
小計行の二重集計を防ぐ仕組み
売上一覧のような表で「部署ごとの小計行」と「全体の合計行」の両方をSUBTOTALで作るケースは珍しくありません。このとき心配になるのが、小計行の数値が全体の合計に二重に足し込まれてしまわないかという点です。SUBTOTAL関数は、集計範囲の中に他のSUBTOTAL関数で作られた行（小計行）が含まれている場合、その行を自動的に集計対象から除外する仕組みになっています。そのため部署ごとの小計行を範囲に含めたまま全体の合計をSUBTOTALで求めても、二重集計になる心配はありません。ただしこれはSUBTOTAL同士の関係に限った仕組みで、小計行がSUMで作られている場合はこの自動除外は働かない点には注意が必要です。
実務でのよくある使用シーン
経理担当者であれば、月次の経費一覧からオートフィルターで特定部署だけに絞り込み、その部署の交通費や交際費の合計金額をSUBTOTALで算出して稟議書に添付する、という場面が典型的です。総務担当者であれば、勤怠データをフィルターで絞り込み、遅刻者や有給取得者だけを表示させたうえで、その対象者数を集計方法「3」（COUNTA）のSUBTOTALでカウントするといった使い方がよくあります。どちらのケースも、フィルターの条件を変えるたびに手作業で範囲を選び直す必要がなく、数式を1つ入れておくだけで絞り込みのたびに自動で数字が更新される点がSUBTOTALの実務上の利点です。
SUBTOTALでよくあるトラブルと対処法
エラーメッセージは出ないため気づきにくいのですが、実務上もっとも重大なトラブルです。合計欄にSUM関数が入っていると発生します。
対処法は、合計欄の数式をSUMから=SUBTOTAL(9,C2:C100)に置き換えることです。フィルターを変更するたびに、表示されている行だけを対象に自動で再計算されるようになります。過去に作った集計表を流用する場合は特に、合計欄が本当にSUBTOTALになっているか一度確認しておくと安心です。
フィルターとは別に「この行だけ一時的に隠したい」と右クリックで手動非表示にした行がある場合に起こりやすいミスです。
対処法は、手動非表示にした行も含めて完全に「見えている行だけ」を集計したいのであれば109を、手動非表示行は合計に残したままフィルターだけ効かせたいのであれば9を選ぶことです。用途を確認せずに片方だけを覚えて使い続けると、この手のズレに気づきにくいので注意しましょう。
前述の通り、SUBTOTAL同士であれば自動的に二重集計は防がれますが、この仕組みを知らないと不安になり、小計行を範囲から手作業で除外してしまう人がいます。
対処法は、小計行がSUBTOTAL関数で作られていることを確認したうえで、全体の合計範囲に小計行を含めてそのままSUBTOTALで集計することです。範囲を無理に分割する必要はありません。逆に小計行がSUMで作られている場合は本当に二重集計されるため、その場合のみ小計行を範囲から除く必要があります。
初心者がつまずきやすいポイントとコツ
- 集計方法の番号は9と109だけ覚えれば実務の大半に対応できる。1〜11・101〜111の全11種類を無理に暗記する必要はなく、合計を出すときの2つの番号の使い分けさえ押さえておけば十分です。
- フィルターを解除すると全行が集計対象に戻る点に注意する。SUBTOTALはあくまで「今表示されている行」を対象にする関数なので、フィルター条件を変更・解除すれば結果も自動的に変わります。特定条件時点の数字を固定して残したい場合は、値としてコピー＆貼り付けしておく必要があります。
- 非表示と削除は違う。SUBTOTALが無視するのは「非表示」になっている行であり、行そのものを削除すれば当然その数値は最初から集計対象になりません。非表示のまま運用するか削除するかは、後で条件を戻す可能性があるかどうかで判断しましょう。
- 範囲は表全体を指定してよい。フィルターの状態に応じて自動で対象行が切り替わるため、非表示行を避けて範囲を細かく指定し直す必要はありません。
まとめ
SUBTOTAL関数は、オートフィルターで絞り込んだ行だけを対象に集計してくれる、実務で欠かせない関数です。SUMのように非表示行まで拾ってしまうことがないため、「見えている行だけの合計」を正しく出したいときはSUBTOTALを使いましょう。集計方法は合計であれば9か109を、手動非表示行の扱いに応じて選び分けるのがポイントです。範囲の中に別のSUBTOTAL行が含まれていても自動的に二重集計は防がれるため、小計行と合計行を安心して同じ表の中に共存させられます。数式を一から組み立てるのが不安な方は、下のツールで日本語の空欄を埋めるだけで数式を作成できます。