SUBTOTALは、フィルタで絞り込んだ結果だけを集計する関数です。
SUM は隠れている行も足してしまいます。「フィルタで東京だけ表示したのに、合計が全社のまま」——これを解決するのがSUBTOTALです。
書式
=SUBTOTAL(集計方法, 範囲1, [範囲2], ...)第1引数に番号で集計方法を指定するのが特徴です。
集計方法の番号
| 1桁 | 3桁 | 集計内容 |
|---|---|---|
| 1 | 101 | 平均（AVERAGE） |
| 2 | 102 | 数値の個数（COUNT） |
| 3 | 103 | 空欄でない個数（COUNTA） |
| 4 | 104 | 最大値（MAX） |
| 5 | 105 | 最小値（MIN） |
| 6 | 106 | 積（PRODUCT） |
| 7 | 107 | 標本標準偏差（STDEV） |
| 8 | 108 | 母標準偏差（STDEVP） |
| 9 | 109 | 合計（SUM） |
| 10 | 110 | 標本分散（VAR） |
| 11 | 111 | 母分散（VARP） |
実務で使うのは 9 と 109（合計）、次いで 3 と 103（件数）がほとんどです。
1桁と3桁の違い
ここが最も重要です。
| フィルタで隠れた行 | 手動で非表示にした行 | |
|---|---|---|
| 1桁（9など） | 除外する | 含める |
| 3桁（109など） | 除外する | 除外する |
つまり、
- フィルタだけ使うなら、どちらでも同じ結果
- 行を右クリックで非表示にすることがあるなら 3桁
迷ったら 109（3桁）を使ってください。 「見えている行だけ」という直感に一致します。
基本の使い方
| A | B | |
|---|---|---|
| 1 | 支店 | 売上 |
| 2 | 東京 | 1200 |
| 3 | 大阪 | 800 |
| 4 | 東京 | 1500 |
=SUBTOTAL(109, B2:B4)フィルタ無しなら 3500。フィルタで「東京」だけ表示すると 2700 に変わります。
数式を書き換えていないのに結果が変わる——これがSUBTOTALの働きです。
件数を数えるなら次の形です。
=SUBTOTAL(103, A2:A4)SUMとの使い分け
フィルタを使う表では、集計行は必ずSUBTOTALにしてください。 SUMのままだと、絞り込んでも数字が変わらず、見る人が誤解します。これは実務で頻繁に起きるミスです。
小計が二重計上されない仕組み
SUBTOTALには、もうひとつ重要な性質があります。
範囲内に他のSUBTOTALがあると、それを無視します。
小計行をいくつも挟んだ表で、最後に総合計を出す場面を考えてください。
A列の途中に =SUBTOTAL(109, B2:B10) ← 小計
=SUBTOTAL(109, B12:B20) ← 小計
最終行に =SUBTOTAL(109, B2:B21) ← 総合計普通の SUM で総合計を出すと、小計まで足してしまい2倍になります。 SUBTOTALなら小計を自動で除外するので、正しい総合計が出ます。
小計を含む表では、すべてSUBTOTALで統一するのが定石です。
フィルタで隠れた行だけを数える
「絞り込みで何件除外されたか」を出したいなら、全件から表示件数を引きます。
=COUNTA(A2:A100) - SUBTOTAL(103, A2:A100)表の一番上に集計を置く
集計行を表の上に置くと、フィルタをかけたときにスクロールせず確認できます。
ただし、集計行が範囲に含まれないよう注意してください。表が2行目から始まるなら、集計は1行目に置き、範囲は A2:A1000 とします。
なお、集計行を表の上に置くと、フィルタの範囲に含まれてしまうことがあります。フィルタは表の見出し行から設定してください。
Excelとの違い
SUBTOTALは共通です。 番号の意味も1桁/3桁の違いも同じです。
違いは代替手段です。
| Excel | スプレッドシート | |
|---|---|---|
AGGREGATE（エラー無視＋非表示除外） |
ある | ない |
| QUERY | ない | ある |
Excelの AGGREGATE は、SUBTOTALの機能に加えてエラー値を無視できます。範囲に #N/A が混ざっていてもエラーになりません。スプレッドシートにはこれがないので、元のエラーを IFERROR で潰しておく必要があります。
一方スプレッドシートでは、そもそもフィルタを使わずに QUERY や FILTER で絞り込む書き方が使えます。
=SUM(FILTER(B2:B100, A2:A100="東京"))画面のフィルタ状態に依存しないぶん、こちらのほうが再現性は高いです。人に渡すファイルではこちらを検討する価値があります。
フィルタと連動するという性質
小計を求める関数の最大の特徴は、フィルタで非表示になった行を集計から除外することです。通常の合計関数は、隠れている行も含めて計算します。この違いが、実務では決定的な意味を持ちます。
たとえば、取引一覧に対して特定の顧客だけを絞り込んだとき、画面に見えている行の合計が知りたくなります。通常の合計関数を表の下に置いていると、絞り込んでも数字が変わりません。全体の合計が表示され続けます。小計の関数を使えば、絞り込むたびに合計が変わり、その時点で見えている行だけの数字になります。
この動きは、データを眺めながら条件を変えて確認する作業と非常に相性が良いものです。条件付き集計の数式を書かなくても、フィルタの操作だけで様々な切り口の集計が見られます。
ただし、集計方法を番号で指定する必要があります。合計、平均、件数、最大、最小など、それぞれに番号が割り当てられています。この番号を覚えるのが最初のハードルですが、よく使うのは合計と件数と平均の三つなので、実質的にはそれだけ覚えれば足ります。
手動で非表示にした行の扱い
集計方法の番号には二系統あります。一桁の番号と、百番台の番号です。この違いは、手動で非表示にした行を含めるかどうかです。
一桁の番号は、フィルタで非表示になった行は除外しますが、手動で行を非表示にした場合は含めます。百番台の番号は、フィルタでも手動でも、非表示の行はすべて除外します。
実務では、この違いが混乱の元になります。フィルタだけを使っているなら差は出ませんが、行を右クリックして非表示にする操作を併用していると、結果が変わります。
方針としては、常に百番台を使うと決めてしまうのが分かりやすくなります。見えていない行は数えない、という一貫した動きになるためです。他人が触るファイルでも、説明が単純になります。
ただし、意図的に一時的な非表示を使い、それでも集計には含めたい場面もあります。その場合は一桁の番号を選びます。どちらを使っているかを、集計行の近くに書き添えておくと親切です。
入れ子にしたときの挙動
小計の関数には、もうひとつ便利な性質があります。範囲の中に別の小計の関数が入っている場合、それを二重に数えません。
これが役に立つのは、階層のある集計表です。部門ごとの小計行を挟みながらデータが並んでいて、最後に全体の合計を出したい場合。通常の合計関数で全体を合計すると、小計行の数字も足されてしまい、実際の倍になります。小計の関数なら、範囲の中の小計行を自動的に無視するので、正しい合計になります。
同じ理由で、途中に小計行を追加しても全体の合計は壊れません。行を挿入するたびに数式を直す必要がなくなります。
この性質を知らないと、階層のある集計表を作るたびに範囲を細かく指定して足し合わせることになります。数式が複雑になり、行を追加するたびに直す必要が出ます。
なお、通常の合計関数と混在させると、この自動的な除外は働きません。階層のある表では、すべての集計行を小計の関数で統一してください。
連番を振るという応用
やや変わった使い道として、フィルタで絞り込んだ後も連番が飛ばない番号を振る、という使い方があります。
通常、行番号から連番を作ると、絞り込んだときに番号が飛びます。三番の次が七番になる、といった見え方です。印刷して配る資料では不自然に見えます。
小計の関数で件数を数える方法を使い、自分の行までの範囲を対象にすると、見えている行だけを数えた番号になります。絞り込むたびに番号が振り直され、常に一から連続します。
範囲の指定は、開始行を固定し、終了行を自分の行にします。下にコピーすると範囲が広がっていくので、その行までに見えている件数が返ります。
印刷を前提とした一覧表では、この一手間が仕上がりの印象を変えます。手作業で番号を振り直す必要もなくなります。
フィルタを前提とした表の作り方
フィルタと連動する集計を活かすには、表そのものをフィルタ前提で設計する必要があります。
見出しは一行だけにします。二段の見出しがあると、フィルタが正しく認識されません。
データの範囲に空白行を作りません。空白行があると、フィルタの範囲がそこで途切れます。
集計行は、データの範囲から離して置きます。表のすぐ下に置くと、フィルタの対象に含まれてしまい、絞り込んだときに一緒に隠れることがあります。表の上部に置くのが安全です。上部にあれば、スクロールしても見え続けるという利点もあります。
セルの結合は使いません。結合されたセルがあると、フィルタも並べ替えも正しく動きません。
列見出しは重複させません。同じ名前の列が複数あると、どれを絞り込んでいるか分からなくなります。
この五点を守った表であれば、フィルタで自由に切り口を変えながら、常に正しい集計が表示されます。条件付きの集計関数を大量に書く必要がなくなります。
集計方法を切り替える仕組み
集計方法を番号で指定する性質を利用すると、表示する集計を切り替える仕組みが作れます。
集計方法の番号をセルに置き、その番号を参照する形で数式を書きます。番号を変えれば、合計にも平均にも件数にも切り替わります。
さらに、プルダウンで集計方法を選べるようにすれば、番号を覚える必要もなくなります。表示名と番号の対応表を作り、選んだ表示名から番号を引く形にします。
この仕組みは、同じ表を複数の見方で確認したい場合に有効です。合計で全体を把握し、平均で水準を見て、件数で規模を確認する。表を作り直さずに切り替えられます。
注意点として、集計方法によって意味が変わる列があります。金額の列は合計も平均も意味がありますが、単価の列は合計に意味がありません。切り替えられるようにしても、意味のある組み合わせだけを使ってください。
見出しにも集計方法を表示するようにしておくと、今どの集計を見ているかが分かります。選んだ表示名をそのまま見出しに反映すれば済みます。
よくある質問
Q. フィルタをかけても数字が変わりません
通常の合計関数を使っています。小計の関数に置き換えてください。
Q. 集計方法の番号が覚えられません
よく使うのは合計、件数、平均の三つです。数式を入力するときに候補が表示されるので、その場で選べます。
Q. 手動で隠した行も除きたいのですが
百番台の番号を使ってください。一桁の番号では含まれます。
Q. 小計行が二重に数えられます
範囲の中に通常の合計関数が混ざっています。すべて小計の関数に統一してください。
Q. 条件を付けて集計できますか
この関数には条件を指定する機能がありません。条件付きの集計が必要なら、条件付きの集計関数を使うか、フィルタで絞ってからこの関数を使ってください。
Q. 空白行があっても大丈夫ですか
問題ありません。空白は無視されます。
Q. 別のシートの範囲を集計できますか
できますが、別シートの表示状態には連動しません。同じシート内で使うのが本来の用途です。
Q. より柔軟な集計をしたいのですが
集計方法をより細かく指定できる関数もあります。ただし覚えることが増えるので、フィルタとの連動が目的ならこの関数で足ります。
まとめ
- 書式は
=SUBTOTAL(集計方法, 範囲) - 合計は 109、件数は 103 を覚えておけば足りる
- 1桁は手動非表示を含み、3桁は除外する。迷ったら3桁
- フィルタを使う表の集計行は、必ずSUBTOTALにする
- 範囲内の他のSUBTOTALを無視するので、小計と総合計が二重にならない