スプレッドシートのSUBTOTAL関数の使い方｜フィルタ対応の集計
「フィルタで絞り込んだのに、合計値が全データのままで困る…」そんな経験はありませんか？
SUM関数で合計を出していると、フィルタで行を隠しても合計は変わりません。表示されているデータだけの合計がほしいのに、もどかしいですよね。
そんなときに使えるのがSUBTOTAL関数です。フィルタで非表示にした行を自動的に除外して集計できます。
この記事では機能番号の一覧から実務での活用パターンまで紹介します。
SUBTOTAL関数とは？
SUBTOTAL関数（読み方: サブトータル関数）は、フィルタで非表示にした行を除外して集計できる関数です。
名前の「SUBTOTAL」は「小計」を意味します。「機能番号」を切り替えるだけで合計・平均・件数・最大値・最小値など11種類の集計を1つの関数でまかなえます。
最大の特長はフィルタとの連動です。通常のSUM関数はフィルタで行を隠しても全行を合計します。SUBTOTAL関数なら表示されている行だけを集計してくれます。
SUBTOTAL関数にできることをまとめると、次のとおりです。
- フィルタで絞り込んだデータだけを合計・平均・カウントする
- 機能番号の切り替えで11種類の集計を使い分ける
- 範囲内の他のSUBTOTAL関数を無視する（二重集計を防ぐ）
- 手動で非表示にした行の扱いも制御できる
「フィルタ付きの集計表」を作るなら必須の関数です。
NOTE
SUBTOTAL関数はGoogleスプレッドシートの全バージョンで使えます。Excelとの互換性も完全なので、ファイルのやり取りでも安心です。
SUBTOTAL関数の書き方（構文と引数）
基本構文
=SUBTOTAL(機能番号, 範囲1, [範囲2], ...)カッコの中に「何で集計するか」と「どこを集計するか」を指定します。
引数の説明
| 引数 | 必須/任意 | 説明 |
|---|---|---|
| 機能番号 | 必須 | 集計方法を表す番号（1〜11 または 101〜111） |
| 範囲1 | 必須 | 集計の対象となるセル範囲 |
| 範囲2, … | 任意 | 追加の集計範囲（最大30個） |
ポイントは「機能番号」です。この番号で合計・平均・件数などの集計方法を指定します。
機能番号の一覧
SUBTOTAL関数の機能番号は2つのグループに分かれます。
| 機能番号（含む） | 機能番号（除外） | 集計方法 | 対応する関数 |
|---|---|---|---|
| 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（合計）・1（平均）・2（件数） の3つです。まずはこの3つを覚えておけば十分ですよ。
TIP
「合計は9」と覚えにくい場合は「SUMのSは9番目のアルファベット」と覚えると忘れにくいです。
SUBTOTAL関数の基本的な使い方
フィルタで絞り込んだデータの合計を求める
実際にSUBTOTAL関数を使ってみましょう。
次のような売上データがあるとします。
| A列（商品名） | B列（カテゴリ） | C列（売上） | |
|---|---|---|---|
| 1行目 | 商品名 | カテゴリ | 売上 |
| 2行目 | ノート | 文房具 | 5000 |
| 3行目 | マウス | PC周辺機器 | 12000 |
| 4行目 | ペン | 文房具 | 3000 |
| 5行目 | キーボード | PC周辺機器 | 8000 |
| 6行目 | ファイル | 文房具 | 2000 |
全データの合計を求めるには、次の数式を入力します。
=SUBTOTAL(9, C2:C6)機能番号 9 は「合計（SUM）」です。結果は 30,000 です。
ここでB列のフィルタで「文房具」だけに絞り込んでみましょう。SUM関数なら値は変わりませんが、SUBTOTAL関数は自動的に再計算されます。
表示後の結果: 10,000（5000 + 3000 + 2000）フィルタに連動して合計値が変わるのがSUBTOTAL関数の強みです。
平均や件数を求める
機能番号を変えるだけで集計方法を切り替えられます。
=SUBTOTAL(1, C2:C6)機能番号 1 は「平均（AVERAGE）」です。全データの平均は 6,000 です。フィルタで「文房具」に絞ると約 3,333 になります。
=SUBTOTAL(2, C2:C6)機能番号 2 は「件数（COUNT）」です。全データは 5件、「文房具」だけなら 3件 になります。
このように数式の機能番号を1つ変えるだけで、さまざまな集計に対応できます。
機能番号1-11と101-111の違い
SUBTOTAL関数には機能番号が2セットあります。どちらもフィルタで非表示にした行は除外しますが、手動で非表示にした行の扱いが異なります。
| 非表示の方法 | 1〜11 | 101〜111 |
|---|---|---|
| フィルタで非表示 | 除外する | 除外する |
| 手動で行を非表示（右クリック→行を非表示） | 含める | 除外する |
つまり、1〜11はフィルタ非表示だけを除外します。101〜111はフィルタ非表示に加えて手動非表示も除外します。
どちらを使うべき？
迷ったら 101〜111 を使うのがおすすめです。
理由はシンプルです。「表示されている行だけを集計したい」という目的なら、非表示の方法に関係なく除外するほうが直感的ですよね。
ただし、手動で非表示にした行も計算に含めたい場合は1〜11を使ってください。たとえば「内訳行を非表示にしているが集計には含めたい」というケースです。
NOTE
Googleスプレッドシートでは「フィルタビュー」を使うことが多いため、1〜11と101〜111の差が出るのは「行の非表示」機能を併用するときだけです。フィルタだけなら動作は同じです。
SUBTOTAL関数の実務活用パターン
基本がわかったところで、仕事でよく使うパターンを紹介します。
パターン1: フィルタ付き売上集計表
フィルタで部門や月を切り替えながら合計・平均・件数を確認する集計表です。
合計: =SUBTOTAL(109, D2:D100)
平均: =SUBTOTAL(101, D2:D100)
件数: =SUBTOTAL(102, D2:D100)
最大値: =SUBTOTAL(104, D2:D100)
最小値: =SUBTOTAL(105, D2:D100)フィルタを切り替えるだけで、各集計値がリアルタイムに更新されます。ダッシュボード的な使い方ができますよ。
パターン2: 小計行がある表での総合計
部門ごとに小計行があるデータの場合、SUM関数で総合計を出すと小計が二重カウントされます。
| 営業部 | 100 |
| 営業部 | 200 |
| 小計 | =SUBTOTAL(9, C2:C3) → 300 |
| 総務部 | 150 |
| 小計 | =SUBTOTAL(9, C5:C5) → 150 |
| 総合計 | =SUBTOTAL(9, C2:C6) → 450（正しい） |SUBTOTAL関数は範囲内の他のSUBTOTAL関数を自動的に無視します。そのため、小計行を含む範囲で総合計を出しても二重カウントされません。
SUM関数で同じことをすると 300 + 200 + 100 + 150 + 150 = 900 と、小計が重複して正しい値になりません。
パターン3: FILTER関数との組み合わせ
FILTER関数で抽出した結果をSUBTOTAL関数で集計するパターンです。
=SUBTOTAL(9, FILTER(C2:C100, B2:B100="文房具"))ただし、この使い方では実はSUM関数でも同じ結果が得られます。FILTER関数が先にデータを絞り込むためです。
SUBTOTAL関数が真価を発揮するのは「シート上のフィルタ機能」との連動です。数式内でデータを絞り込む場合はSUM関数やSUMIF関数のほうがシンプルですよ。
よくあるエラーと対処法
SUBTOTAL関数で困ったときの原因と対策をまとめました。
| エラー/症状 | 原因 | 対処法 |
|---|---|---|
| #VALUE! | 機能番号が範囲外（0、12〜100、112〜） | 1〜11 または 101〜111 を指定する |
| 結果が0 | 範囲に数値がない（文字列型の数値） | データクリーンアップで数値に変換 |
| フィルタしても値が変わらない | SUM関数を使っている | SUBTOTAL関数に置き換える |
| 小数点以下が出る | 機能番号1（AVERAGE）の仕様 | ROUND関数で丸める |
機能番号の指定ミス
最も多いミスが機能番号の入力間違いです。
=SUBTOTAL(0, C2:C10)機能番号は1〜11または101〜111です。0やそれ以外の数値を指定すると #VALUE! エラーになります。
合計なら 9 または 109、件数なら 2 または 102 と覚えておきましょう。
フィルタしても合計値が変わらない場合
SUBTOTAL関数ではなくSUM関数を使っていないか確認してください。SUM関数はフィルタの状態に関係なく全行を合計します。
=SUM(C2:C10) → フィルタに連動しない
=SUBTOTAL(9, C2:C10) → フィルタに連動する既存の集計表をフィルタ対応にしたい場合は、SUM関数をSUBTOTAL関数に置き換えるだけで対応できます。
似た関数との違い・使い分け
SUBTOTAL関数と似た機能を持つ関数を比較します。
SUM関数との使い分け
最も重要な違いは「フィルタ連動」の有無です。
- フィルタを使わない集計 → SUM関数がシンプルで最適
- フィルタで絞り込みながら集計 → SUBTOTAL関数が必須
- 小計と総合計がある表 → SUBTOTAL関数で二重集計を防止
「フィルタを使うならSUBTOTAL、使わないならSUM」と覚えるのが一番シンプルです。
条件を指定して集計したい場合はSUMIF関数やSUMIFS関数を使いましょう。
Excelとの違い
SUBTOTAL関数の動作はExcelとほぼ同じです。
| 項目 | Googleスプレッドシート | Excel |
|---|---|---|
| 基本動作 | 同じ | 同じ |
| 機能番号 | 1〜11、101〜111 | 1〜11、101〜111 |
| 引数の上限 | 最大30個 | 最大254個 |
| 二重集計回避 | 同じ | 同じ |
| フィルタ連動 | 同じ | 同じ |
引数の上限に違いはありますが、実務で30個を超えることはまずありません。ExcelのSUBTOTAL関数と同じ書き方で使えますよ。
まとめ
SUBTOTAL関数のポイントをおさらいしましょう。
- SUBTOTAL関数はフィルタで非表示にした行を除外して集計できる
- 機能番号で合計(9)・平均(1)・件数(2)など11種類の集計を切り替える
- 1〜11は手動非表示を含む、101〜111は手動非表示も除外する
- 範囲内の他のSUBTOTAL関数を無視する（二重集計を防止）
- フィルタを使うならSUBTOTAL、使わないならSUM
まずは =SUBTOTAL(9, 範囲) でフィルタ対応の合計から試してみてください。フィルタを切り替えるたびに値が変わるのを見ると、便利さが実感できますよ。
