スプレッドシートのSUBTOTAL関数の使い方｜フィルタ対応の集計を1関数で

スポンサーリンク

「スプレッドシートのフィルタで絞り込んだのに、合計値が全データのままで困る…」そんな経験はありませんか？

SUM関数で合計を出していると、フィルタで行を隠しても合計は変わりません。表示されているデータだけの合計がほしいのに、もどかしいですよね。

そんなときに使えるのがスプレッドシートのSUBTOTAL関数です。フィルタで非表示にした行を自動的に除外して、合計・平均・件数など11種類の集計を1つの関数でまかなえます。

この記事では機能番号の一覧から、実務で本当に使う活用パターン6つ、SUMやSUMIFとの使い分け、つまずきやすいFAQまで一気に紹介します。

  1. スプレッドシートのSUBTOTAL関数とは？
  2. SUBTOTAL関数の書き方（構文と引数）
    1. 基本構文
    2. 引数の説明
    3. 機能番号の一覧（1〜11 / 101〜111）
  3. スプレッドシートのSUBTOTAL関数の基本的な使い方
    1. フィルタで絞り込んだデータの合計を求める
    2. 平均や件数を求める
    3. 最大値・最小値も同じ書き方でOK
  4. 機能番号1〜11と101〜111の違い
    1. 動作の違いを具体例で確認する
    2. どちらを使うべき？
  5. SUBTOTAL関数の実務活用パターン6選
    1. パターン1: フィルタ付き売上ダッシュボード
    2. パターン2: 小計行がある表での総合計（二重集計の防止）
    3. パターン3: フィルタ後の連番（行番号の振り直し）
    4. パターン4: SUMIFと組み合わせて固定集計＋フィルタ集計を1画面に
    5. パターン5: 条件付き書式で「表示中の行だけ」を色付け
    6. パターン6: SUBTOTAL同士の組み合わせで「表示行の加重平均」
  6. SUBTOTAL関数でよくあるエラーと対処法
    1. 機能番号の指定ミス（#VALUE!）
    2. フィルタしても合計値が変わらない
    3. 平均で #DIV/0! が出る
    4. 数値が文字列型になっている
  7. 似た関数との違い・使い分け
    1. SUM関数との使い分け
    2. SUMIF・SUMIFSとの使い分け
    3. QUERY関数との使い分け
    4. FILTER関数との組み合わせ
  8. ExcelのSUBTOTAL関数との違い
    1. Excelの「AGGREGATE関数」に注意
    2. 引数上限の違い
  9. よくある質問（FAQ）
    1. Q. SUBTOTALで条件指定（カテゴリ別の合計）はできますか？
    2. Q. SUBTOTAL(9, …) と SUBTOTAL(109, …) はどちらが速いですか？
    3. Q. フィルタを解除しても合計が変わってしまうのはなぜ？
    4. Q. SUBTOTALの結果をさらに別のSUBTOTALで使えますか？
    5. Q. ARRAYFORMULAでSUBTOTALを使えますか？
    6. Q. 既存のSUM関数をSUBTOTALに置き換えても安全ですか？
  10. SUBTOTALのネスト除外を使った多段階集計
    1. 3階層の集計表でも二重カウントなし
    2. フィルタと多段階集計の組み合わせ
  11. SUBTOTAL vs AGGREGATE：スプレッドシートでの代替手段
    1. AGGREGATEが持つSUBTOTALにない機能
    2. スプレッドシートでのエラー無視集計
  12. SUBTOTAL関数 vs ピボットテーブル：どちらを選ぶ？
    1. 比較表
    2. SUBTOTALを選ぶべき場面
    3. ピボットテーブルを選ぶべき場面
  13. まとめ｜SUBTOTAL関数でフィルタ対応の集計を1関数に集約
    1. この記事で紹介した関数・関連記事

スプレッドシートのSUBTOTAL関数とは？

スプレッドシートのSUBTOTAL関数（読み方: サブトータル関数）は、フィルタで非表示にした行を除外して集計できる関数です。

名前の「SUBTOTAL」は「小計」を意味します。「機能番号」を切り替えるだけで合計・平均・件数・最大値・最小値など11種類の集計を1つの関数で扱えます。

最大の特長はフィルタとの連動です。通常のSUM関数はフィルタで行を隠しても全行を合計します。SUBTOTAL関数なら表示されている行だけを集計してくれますよ。

SUBTOTAL関数にできることをまとめると、次のとおりです。

  • フィルタで絞り込んだデータだけを合計・平均・カウントする
  • 機能番号の切り替えで11種類の集計を使い分ける
  • 範囲内の他のSUBTOTAL関数を無視する（小計と総合計の二重カウントを防ぐ）
  • 手動で非表示にした行の扱いも機能番号で制御できる

「フィルタ付きの集計表」や「部署別小計＋総合計の集計表」を作るなら、まず最初に覚えたい関数です。

NOTE

SUBTOTAL関数はGoogleスプレッドシートの全バージョンで使えます。Excelとの互換性も高く、ファイルのやり取りでも安心ですよ。Excelとの細かい差は後半の「Excelとの違い」で整理しています。

SUBTOTAL関数の書き方（構文と引数）

基本構文

=SUBTOTAL(機能番号, 範囲1, [範囲2], ...)

カッコの中に「何で集計するか（機能番号）」と「どこを集計するか（範囲）」を指定します。

引数の説明

引数必須/任意説明
機能番号必須集計方法を表す番号（1〜11 または 101〜111）
範囲1必須集計の対象となるセル範囲
範囲2, …任意追加の集計範囲（最大30個）

ポイントは「機能番号」です。この番号で合計・平均・件数などの集計方法を指定します。

機能番号の一覧（1〜11 / 101〜111）

SUBTOTAL関数の機能番号は2つのグループに分かれます。1〜11と101〜111で「対応する集計方法」は同じで、違いは「手動で非表示にした行を含めるかどうか」だけです。

機能番号（含む）機能番号（除外）集計方法対応する関数
1101平均AVERAGE
2102数値の個数COUNT
3103空白以外の個数COUNTA
4104最大値MAX
5105最小値MIN
6106積PRODUCT
7107標本標準偏差（手元データをサンプルとみなす偏差）STDEV
8108母標準偏差（手元データが母集団とみなす偏差）STDEVP
9109合計SUM
10110標本分散VAR
11111母分散VARP

よく使うのは 9（合計）・1（平均）・2（件数）・4（最大値）・5（最小値） の5つです。まずはこの5つを覚えておけば、ほとんどの集計表を作れますよ。

TIP

「合計は9」と覚えにくい場合は「SUMのSは9番目のアルファベット」と覚えると忘れにくいです。「平均は1（先頭）・件数は2（並んだ2番目）」もリスト順なので覚えやすいですよ。

スプレッドシートのSUBTOTAL関数の基本的な使い方

フィルタで絞り込んだデータの合計を求める

実際にSUBTOTAL関数を使ってみましょう。

次のような売上データがあるとします。

01 data sample table
行A列（商品）B列（カテゴリ）C列（売上）
2ボールペン文房具5,000
3お菓子食品8,000
4ノート文房具3,000
5飲料食品12,000
6クリップ文房具2,000

全データの合計を求めるには、次の数式を入力します。

=SUBTOTAL(9, C2:C6)
02 formula subtotal sum

機能番号 9 は「合計（SUM）」です。結果は 30,000 です（5,000+8,000+3,000+12,000+2,000）。

ここでB列のフィルタで「文房具」だけに絞り込んでみましょう。SUM関数なら値は変わりませんが、SUBTOTAL関数は自動的に再計算されます。

表示後の結果: 10,000（5,000 + 3,000 + 2,000）

フィルタに連動して合計値が変わるのがSUBTOTAL関数の強みです。フィルタを解除すれば、また30,000に戻ります。

平均や件数を求める

機能番号を変えるだけで、同じ範囲を別の集計方法に切り替えられます。

=SUBTOTAL(1, C2:C6)

機能番号 1 は「平均（AVERAGE）」です。全データの平均は 6,000 です。フィルタで「文房具」に絞ると約 3,333（10,000÷3）になります。

=SUBTOTAL(2, C2:C6)

機能番号 2 は「件数（COUNT）」です。全データは 5件、「文房具」だけなら 3件 になります。

このように数式の機能番号を1つ変えるだけで、さまざまな集計に対応できますよ。

03 result subtotal functions

最大値・最小値も同じ書き方でOK

合計や平均と同じ感覚で、最大値・最小値も取れます。

=SUBTOTAL(4, C2:C6)   → 12,000（全データの最大値）
=SUBTOTAL(5, C2:C6)   → 2,000（全データの最小値）

文房具に絞り込んだあとは、最大値が5,000・最小値が2,000に変わります。集計表の上部に「合計・平均・件数・最大・最小」の5行を並べておくと、フィルタ操作だけでミニダッシュボードが完成しますよ。

機能番号1〜11と101〜111の違い

SUBTOTAL関数には機能番号が2セットあります。どちらもフィルタで非表示にした行は除外しますが、手動で非表示にした行の扱いが異なります。

非表示の方法1〜11101〜111
フィルタで非表示除外する除外する
手動で行を非表示（右クリック→行を非表示）含める除外する
グループ化で折りたたまれた行含める除外する

つまり、1〜11は「フィルタ非表示」だけを除外します。101〜111は「フィルタ非表示」に加えて「手動非表示・グループ化での折りたたみ」も除外します。

動作の違いを具体例で確認する

5行の売上データ（5,000 / 8,000 / 3,000 / 12,000 / 2,000）で、3行目（3,000）と5行目（2,000）を手動で「行を非表示」にした場合を考えてみましょう。

=SUBTOTAL(9,  C2:C6)   → 30,000（手動非表示も合計する）
=SUBTOTAL(109, C2:C6)  → 25,000（手動非表示の3,000+2,000を除外）

両方ともフィルタを使えば同じように表示行だけを合計しますが、手動非表示の有無で結果が変わるのがポイントです。

どちらを使うべき？

迷ったら 101〜111 を使うのがおすすめです。

理由はシンプルで、「画面に表示されている行だけを集計したい」という直感に一番近い動きをするからです。フィルタを解除した状態でも、手動で隠した行は集計から外せます。

ただし、「内訳行は非表示にしているけれど集計には含めたい」というケースでは1〜11を使います。たとえば「明細を畳んでサマリーだけ見せたいが、合計値は明細を含めた金額にしたい」というレポートが該当しますよ。

NOTE

Googleスプレッドシートでは「フィルタビュー」を使うことが多いので、1〜11と101〜111の差が実際に出るのは「行の非表示」「グループ化」機能を併用するときだけです。フィルタだけなら動作は同じになります。

SUBTOTAL関数の実務活用パターン6選

基本がわかったところで、仕事でよく使うパターンを6つ紹介します。コピペで使えるようにサンプル数式付きです。

パターン1: フィルタ付き売上ダッシュボード

フィルタで部門や月を切り替えながら、合計・平均・件数・最大・最小を1画面で確認する集計表です。

合計:   =SUBTOTAL(109, D2:D1000)
平均:   =SUBTOTAL(101, D2:D1000)
件数:   =SUBTOTAL(102, D2:D1000)
最大値: =SUBTOTAL(104, D2:D1000)
最小値: =SUBTOTAL(105, D2:D1000)

5つの数式を表頭に並べるだけで、フィルタを切り替えるたびに数字がリアルタイムに更新されます。ピボットテーブルを使うほどではないけれど、ちょっとした集計を見たいときに便利ですよ。

範囲はD1000など余裕を持って広めに取っておくと、データが追加されても数式を直す必要がありません。

パターン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 と小計が重複して正しい値になりません。請求書や経費精算書のような「小計＋総合計」が必要な書類では、SUBTOTAL一択ですよ。

パターン3: フィルタ後の連番（行番号の振り直し）

フィルタで絞り込んでも連番が崩れないようにする定番テクニックです。COUNTA（機能番号3）を使います。

（A2セル以降に入力）
=SUBTOTAL(3, $B$2:B2)

$B$2:B2 のように、開始セルを絶対参照・終了セルを相対参照にするのがコツです。下にコピーすると $B$2:B3 $B$2:B4 … と範囲が広がります。フィルタで途中の行が隠れると、その行はカウントされず、表示中の行だけに1から番号が付き直されますよ。

報告書の「No.」列をフィルタ対応にしたいときに役立つテクニックです。

パターン4: SUMIFと組み合わせて固定集計＋フィルタ集計を1画面に

SUBTOTAL関数自体は条件指定に対応していないため、条件付き集計はSUMIF関数・SUMIFS関数を使い、「フィルタ後の総合計だけ」をSUBTOTALに任せる役割分担がおすすめです。

文房具カテゴリの合計（フィルタ無視・固定）: =SUMIF(B2:B6, "文房具", C2:C6)
フィルタ後の総合計（フィルタ連動）:           =SUBTOTAL(9, C2:C6)

集計表の上段にSUMIFで主要カテゴリを並べ、最下段でSUBTOTALを使ってフィルタ後の数字を出す。この組み合わせがダッシュボードの定型パターンですよ。

パターン5: 条件付き書式で「表示中の行だけ」を色付け

SUBTOTALは「集計関数」ですが、条件付き書式の判定にも使えます。

条件付き書式のカスタム数式:
=SUBTOTAL(103, INDIRECT("B" & ROW())) > 0

INDIRECT("B" & ROW()) で「自分の行のB列」を1セルだけの範囲として渡し、SUBTOTALのCOUNTA（機能番号103）でカウントします。フィルタで非表示の行はSUBTOTALが0を返すので、表示中の行だけに書式が適用されます。

「フィルタ中の行だけ色を付けてスクショを撮りたい」「印刷時に隠れた行が色付きで残らないようにしたい」というときに便利ですよ。

パターン6: SUBTOTAL同士の組み合わせで「表示行の加重平均」

販売単価と数量があるデータで、フィルタ後の加重平均単価を求めたい場合の例です。

表示行の売上合計:    =SUBTOTAL(9, D2:D100)
表示行の数量合計:    =SUBTOTAL(9, E2:E100)
表示行の加重平均単価: =SUBTOTAL(9, D2:D100) / SUBTOTAL(9, E2:E100)

SUBTOTAL自体に「加重平均」はないので、「売上の合計」÷「数量の合計」で求めます。両方ともSUBTOTALを使えば、フィルタを切り替えるたびに加重平均が再計算されますよ。

単純なAVERAGE（機能番号1）だと「単価の単純平均」になってしまうので、商品ごとの販売数量が違う場合は数式を変える必要があります。

SUBTOTAL関数でよくあるエラーと対処法

SUBTOTAL関数で困ったときの原因と対策をまとめました。

エラー/症状原因対処法
#VALUE!機能番号が範囲外（0、12〜100、112〜）1〜11 または 101〜111 を指定する
#DIV/0!平均（1/101）で対象データが0件IFERRORで「-」やゼロを表示する
結果が0範囲に数値がない（文字列型の数字）VALUE関数で数値に変換
フィルタしても値が変わらないSUM関数を使っているSUBTOTAL関数に置き換える
小数点以下が出る機能番号1（AVERAGE）の仕様ROUND関数で丸める
手動非表示が反映されない機能番号1〜11を使っている101〜111に変える

機能番号の指定ミス（#VALUE!）

最も多いミスが機能番号の入力間違いです。

=SUBTOTAL(0, C2:C10)   → #VALUE!
=SUBTOTAL(12, C2:C10)  → #VALUE!
=SUBTOTAL(100, C2:C10) → #VALUE!

機能番号は1〜11または101〜111です。0やそれ以外の数値を指定すると #VALUE! エラーになります。

合計なら 9 または 109、件数なら 2 または 102 と覚えておきましょう。

フィルタしても合計値が変わらない

SUBTOTAL関数ではなくSUM関数を使っていないか確認してください。SUM関数はフィルタの状態に関係なく全行を合計します。

=SUM(C2:C10)         → フィルタに連動しない
=SUBTOTAL(9, C2:C10) → フィルタに連動する

既存の集計表をフィルタ対応にしたい場合は、SUM関数をSUBTOTAL関数に置き換えるだけで対応できますよ。Ctrl+H（置換）で SUM( → SUBTOTAL(9, に一括置換するのが速いです。

平均で #DIV/0! が出る

フィルタで全行が隠れた状態でAVERAGE（機能番号1/101）を使うと、対象データが0件になり #DIV/0! エラーになります。

=IFERROR(SUBTOTAL(101, C2:C100), "-")

IFERROR関数でラップして「-」やゼロを返すようにすると、ダッシュボードが見苦しくなりませんよ。

数値が文字列型になっている

CSVインポート後など、見た目は数字でも内部的に文字列になっているケースがあります。SUBTOTALは文字列型の数字を集計しないため、結果が0になります。

セルの左上に緑の三角マークが出ていれば文字列型のサインです。範囲を選択して「データ」→「数値を変換」、または =VALUE(A2) で別列に数値化してから集計してください。

似た関数との違い・使い分け

SUBTOTAL関数と似た機能を持つ関数を比較します。

関数名フィルタ連動条件指定集計種類
SUMしないできない合計のみ
SUMIFしない1条件合計のみ
SUMIFSしない複数条件合計のみ
SUMPRODUCTしない配列条件合計（積和）
QUERY参照範囲次第SQLライクに自由集計＋抽出
SUBTOTALするできない11種類

SUM関数との使い分け

最も重要な違いは「フィルタ連動」の有無です。

  • フィルタを使わない集計 → SUM関数がシンプルで最適
  • フィルタで絞り込みながら集計 → SUBTOTAL関数が必須
  • 小計と総合計がある表 → SUBTOTAL関数で二重集計を防止

「フィルタを使うならSUBTOTAL、使わないならSUM」と覚えるのが一番シンプルです。

SUMIF・SUMIFSとの使い分け

条件指定で集計したいならSUMIF・SUMIFSです。SUBTOTALは条件を直接指定できません。

判断基準はシンプルです。「画面のフィルタ操作で結果を切り替えたい」ならSUBTOTAL、「セル上の数式で条件を固定したい（メールに貼る集計レポートなど）」ならSUMIF/SUMIFSを選びます。

両方とも使い分けて、フィルタダッシュボード（SUBTOTAL）と固定集計表（SUMIF）を1シートに同居させることもよくありますよ。

QUERY関数との使い分け

QUERY関数はSQLライクな構文で「抽出＋集計」が一発でできる強力な関数です。

  • 「フィルタを使いつつ既存の表の上に集計だけ表示したい」→ SUBTOTAL
  • 「別領域に集計表そのものを生成したい」→ QUERY

QUERYは「結果テーブルを別に作る」、SUBTOTALは「既存テーブルの上に数値を出す」と役割が違います。レポートのレイアウトに合わせて選んでください。

FILTER関数との組み合わせ

FILTER関数で抽出した結果をSUBTOTAL関数で集計するパターンもあります。

=SUBTOTAL(9, FILTER(C2:C100, B2:B100="文房具"))

ただし、この使い方では実はSUM関数でも同じ結果が得られます。FILTER関数が先にデータを絞り込んでいるためです。

=SUM(FILTER(C2:C100, B2:B100="文房具"))

SUBTOTAL関数が真価を発揮するのは「シート上のフィルタ機能」との連動です。数式内でデータを絞り込む場合はSUM関数やSUMIF関数のほうがシンプルですよ。

ExcelのSUBTOTAL関数との違い

SUBTOTAL関数の動作はExcelとほぼ同じです。同じファイルをExcelとGoogleスプレッドシートで開いても、SUBTOTALに関しては結果がほぼ完全に一致します。

項目GoogleスプレッドシートExcel
基本動作同じ同じ
機能番号1〜11、101〜1111〜11、101〜111
引数の上限最大30個最大254個
二重集計回避同じ同じ
フィルタ連動同じ同じ
上位互換関数なしAGGREGATE関数あり

Excelの「AGGREGATE関数」に注意

Excelには上位互換である AGGREGATE関数 があり、エラー値も無視できます。GoogleスプレッドシートにはAGGREGATEがないため、エラーセルを含む範囲で集計したい場合はIFERRORで事前にエラーを潰すか、別列に整形して対処してください。

たとえばC列にエラーが混在する場合、別列D列に =IFERROR(C2, 0) で整形してから =SUBTOTAL(9, D2:D100) で集計するのが定石です。

引数上限の違い

引数の上限に違い（30 vs 254）はありますが、実務で30個の範囲を1つの数式に渡すことはまずありません。ExcelのSUBTOTAL関数と同じ書き方で使えますよ。

よくある質問（FAQ）

Q. SUBTOTALで条件指定（カテゴリ別の合計）はできますか？

A. できません。SUBTOTAL関数自体は条件指定に対応していないので、カテゴリ別の合計を出したいならSUMIF関数・SUMIFS関数を使ってください。「画面のフィルタで絞り込んだ全体の合計」はSUBTOTAL、「特定カテゴリの合計」はSUMIFという役割分担になりますよ。

Q. SUBTOTAL(9, …) と SUBTOTAL(109, …) はどちらが速いですか？

A. 計算速度はほぼ同じです。違いは「手動で非表示にした行を含めるかどうか」だけです。レポートでは「画面に見えている数字＝集計値」のほうが直感的なので、迷ったら109など100番台を選ぶのが無難ですよ。

Q. フィルタを解除しても合計が変わってしまうのはなぜ？

A. 100番台（101〜111）を使っているうえに、手動で非表示にした行があるからです。フィルタの状態とは関係なく、手動非表示の行は100番台では常に除外されます。フィルタ解除後も全行を合計したい場合は、機能番号を1〜11（手動非表示を含む側）に変えてください。

Q. SUBTOTALの結果をさらに別のSUBTOTALで使えますか？

A. 使えます。むしろこの「他のSUBTOTAL関数を無視する」仕様こそが、小計＋総合計を作るときの最大のメリットです。総合計のSUBTOTAL範囲に小計のSUBTOTALセルが含まれても、二重カウントされずに正しい合計が出ますよ。

Q. ARRAYFORMULAでSUBTOTALを使えますか？

A. ARRAYFORMULA内でSUBTOTALを使うと、フィルタ連動が正しく動かないケースがあります。SUBTOTALは「単一セルで1つの集計値を返す関数」として使うのが基本です。配列で結果を並べたい場合はQUERY関数・SUMIF関数を組み合わせるほうが安定しますよ。

Q. 既存のSUM関数をSUBTOTALに置き換えても安全ですか？

A. 安全です。フィルタを使わない状態では、=SUBTOTAL(9, A1:A100) と =SUM(A1:A100) は同じ結果になります。今後フィルタを使う可能性のある集計セルは、最初からSUBTOTAL（9または109）で書いておくと、後で書き換える手間がなくなりますよ。

SUBTOTALのネスト除外を使った多段階集計

SUBTOTAL関数が「範囲内の他のSUBTOTAL関数を無視する」という仕様は、多段階の集計表を作る際に非常に強力です。

3階層の集計表でも二重カウントなし

たとえば「担当者別明細 → 部署小計 → 事業部中計 → 全社総合計」のような3階層の集計表でも、すべてSUBTOTALで書けば二重カウントは起きません。

（明細行）
D4: 山田　5,000
D5: 佐藤　3,000
（部署小計）
D6: 営業1課 小計 =SUBTOTAL(9, D4:D5)   → 8,000

（明細行）
D8: 田中　4,000
D9: 鈴木　6,000
（部署小計）
D10: 営業2課 小計 =SUBTOTAL(9, D8:D9)  → 10,000

（中計行）
D11: 営業部 中計 =SUBTOTAL(9, D4:D10)  → 18,000（小計セルを含む範囲でOK）

（全社総合計）
D20: 全社 合計 =SUBTOTAL(9, D4:D19)    → 正しく全明細の合計になる

D11の数式は「D4:D10」という範囲を指定しており、D6（小計セル）を含んでいますが、内部がSUBTOTAL関数であれば自動的にスキップされます。D20でも同様で、小計・中計のセルはすべて無視されて明細行の合計だけが集計されます。

フィルタと多段階集計の組み合わせ

この仕様を活用すると、多段階集計表にフィルタをかけたときも集計値が自動的に更新されます。たとえば担当者でフィルタをかけると、その担当者を含む部署小計・事業部中計・全社総合計がすべて同時に更新されます。

SUMで同じ構造を作ると、小計行や中計行にSUM数式が入っている行が隠れずに残り、総合計が二重・三重にカウントされてしまいます。小計行が1行でもあるレポートはSUBTOTAL一択と覚えておきましょう。

SUBTOTAL vs AGGREGATE：スプレッドシートでの代替手段

ExcelにはSUBTOTALの上位互換として「AGGREGATE関数」がありますが、Googleスプレッドシートにはありません。AGGREGATEができてSUBTOTALができないことと、スプレッドシートでの代替手段を整理します。

AGGREGATEが持つSUBTOTALにない機能

機能SUBTOTALAGGREGATE（Excelのみ）
エラー値を無視して集計できないできる
非表示行を無視（条件指定）1〜11/101〜111で制御より細かく制御可能
LARGE・SMALL・パーセンタイルできないできる
集計種類11種類19種類

スプレッドシートでのエラー無視集計

AGGREGATEがないスプレッドシートで「エラーを無視して合計したい」場合の代替手段は3つあります。

方法1: 整形列を使う

D列（整形）: =IFERROR(C2, 0)   ← エラーを0に変換した作業列
集計:        =SUBTOTAL(9, D2:D100)

作業列が増えますが最もシンプルで安定します。

方法2: IFERROR込みの配列数式（スプレッドシート独自）

=SUMPRODUCT(IFERROR(C2:C100, 0) * SUBTOTAL(103, OFFSET(C2, ROW(C2:C100)-ROW(C2), 0)))

SUBTOTAL(103, …)でフィルタ表示フラグを取得し、SUMPRODUCT内でIFERRORと掛け合わせます。ただしデータ量が多いと動作が重くなるため、実務では作業列（方法1）が推奨です。

方法3: QUERY関数で集計

=QUERY(C2:C100, "SELECT SUM(C) WHERE C IS NOT NULL LABEL SUM(C) ''")

QUERYはNULLを自動的に無視するため、エラーをIFERRORで0変換した後にQUERYで集計する組み合わせも使えます。

NOTE

ExcelファイルをスプレッドシートにインポートしてAGGREGATEが含まれていると、スプレッドシートでは「未対応関数」としてエラー表示になります。Excelと共同作業するファイルにはAGGREGATEを使わずSUBTOTAL+IFERRORで統一しておくと相互運用性が上がります。

SUBTOTAL関数 vs ピボットテーブル：どちらを選ぶ？

SUBTOTAL関数とピボットテーブルは、どちらも「データを集計する」ツールですが、使い方の場面が異なります。

比較表

観点SUBTOTAL関数ピボットテーブル
操作方法セルに数式を入力UIで設定・ドラッグ
元データとの連動リアルタイム自動更新手動で「更新」が必要
フィルタ連動する（即時反映）ピボット独自のフィルタ
レイアウト変更数式を書き直すドラッグで簡単
集計軸の変更数式の変更が必要ドラッグで即時
既存表への組み込み○（既存表の上に表示）△（別シートや別領域に生成）
大量データの処理重くなりやすい最適化済みで高速
他の数式との連携○（数式の中に組み込める）△（結果セルの参照は可能）

SUBTOTALを選ぶべき場面

  • 既存の表の上部や下部に集計行を置きたい
  • フィルタ操作の都度リアルタイムで数字を確認したい
  • 他の数式と組み合わせて動的なダッシュボードを作りたい
  • シート構造をシンプルに保ちたい

ピボットテーブルを選ぶべき場面

  • 集計軸（行・列）を頻繁に切り替えて多角的に分析したい
  • 数千〜数万行の大量データを集計したい
  • クロス集計（行と列の2軸で集計）が必要
  • 月別・担当者別など複数の切り口で同時に集計したい

結論: 「既存の表にフィルタ集計を付け加えたい」ならSUBTOTAL、「大量データを多角的に分析したい」ならピボットテーブルが向いています。両方をシートに共存させて使い分けるのがベストプラクティスです。

まとめ｜SUBTOTAL関数でフィルタ対応の集計を1関数に集約

スプレッドシートのSUBTOTAL関数は、フィルタで非表示にした行を除外して集計できる「フィルタ連動の万能集計関数」です。

ポイントを整理します。

  • 構文は =SUBTOTAL(機能番号, 範囲) の2引数だけ
  • 機能番号で合計(9)・平均(1)・件数(2)・最大(4)・最小(5)など11種類の集計を切り替える
  • 1〜11は手動非表示を含む、101〜111は手動非表示も除外する（迷ったら101〜111）
  • 範囲内の他のSUBTOTAL関数を無視するので、小計＋総合計の二重集計を防止できる
  • フィルタを使うならSUBTOTAL、使わないならSUM、条件指定ならSUMIF・SUMIFS
  • ExcelのSUBTOTALとも基本動作は同じ。エラー無視が必要なExcelファイルではAGGREGATEを使う

まずは =SUBTOTAL(9, 範囲) でフィルタ対応の合計から試してみてください。フィルタを切り替えるたびに値が変わるのを見ると、便利さが一気に実感できますよ。

ダッシュボードを作るなら、合計・平均・件数・最大・最小の5行を表頭に並べるだけで「フィルタ対応のミニ集計表」が完成します。今日から使えるテクニックなのでぜひ試してみてくださいね。

この記事で紹介した関数・関連記事

スポンサーリンク
タイトルとURLをコピーしました