SUBTOTAL関数の使い方｜フィルター対応の集計
SUBTOTAL関数は、フィルターで絞り込んだデータだけを集計できる関数です。SUM関数やAVERAGE関数では非表示の行も含めて計算されてしまいますが、SUBTOTAL関数なら表示されている行だけを対象にできます。11種類の集計方法を使い分けられる汎用的な関数です。
SUBTOTAL関数の基本構文
=SUBTOTAL(集計方法, 参照1, [参照2], ...)引数の説明
| 引数 | 説明 | 必須/任意 |
|---|---|---|
| 集計方法 | 使用する集計関数を指定する番号 | 必須 |
| 参照1 | 集計対象のセル範囲 | 必須 |
| 参照2以降 | 追加の集計対象範囲 | 任意 |
集計方法の番号一覧
SUBTOTAL関数では、集計方法を番号で指定します。1〜11と101〜111の2つのグループがあります。
| 集計関数 | 番号（1〜11） | 番号（101〜111） |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
1〜11と101〜111の違い
| 番号グループ | フィルターで非表示の行 | 手動で非表示にした行 |
|---|---|---|
| 1〜11 | 除外する | 含める |
| 101〜111 | 除外する | 除外する |
1〜11はフィルターで非表示にした行のみ除外します。101〜111はフィルターに加えて、行の非表示（右クリックで「非表示」にした行）も除外します。
フィルターと連動した合計
最も基本的な使い方は、オートフィルターと組み合わせた集計です。
売上データのフィルター集計
以下の売上データにオートフィルターを設定している場合を想定します。
| A列：部門 | B列：担当者 | C列：売上金額 |
|---|---|---|
| 営業1課 | 田中 | 1200000 |
| 営業2課 | 佐藤 | 980000 |
| 営業1課 | 山田 | 1450000 |
| 営業2課 | 鈴木 | 1100000 |
| 営業1課 | 高橋 | 870000 |
フィルターで「営業1課」だけを表示した状態での合計を求めます。
# SUM関数の場合（フィルターに関係なくすべて合計する）
=SUM(C2:C6) → 常に5600000
# SUBTOTAL関数の場合（表示行だけを合計する）
=SUBTOTAL(9, C2:C6) → 営業1課のみ表示時は3520000SUBTOTAL関数はフィルターの状態に応じて自動的に結果が変わります。
フィルターなしの状態
フィルターを解除してすべての行が表示されている場合、SUBTOTAL関数はSUM関数と同じ結果（5600000）を返します。
11種類の集計方法を使い分ける
合計（番号9）
最もよく使われる集計方法です。表示されている行の合計を返します。
=SUBTOTAL(9, C2:C100)平均（番号1）
表示されている行の平均値を返します。
=SUBTOTAL(1, C2:C100)カウント（番号2、3）
番号2はCOUNT関数に相当し、数値セルの個数を返します。番号3はCOUNTA関数に相当し、空白以外のセルの個数を返します。
# 数値セルの個数
=SUBTOTAL(2, C2:C100)
# 空白以外のセルの個数
=SUBTOTAL(3, B2:B100)最大値・最小値（番号4、5）
表示行だけの最大値・最小値を返します。
# 最大値
=SUBTOTAL(4, C2:C100)
# 最小値
=SUBTOTAL(5, C2:C100)標準偏差・分散（番号7〜8、10〜11）
統計的な分析で使用します。番号7（STDEV）は標本の標準偏差、番号8（STDEVP）は母集団の標準偏差を返します。
# 標本の標準偏差
=SUBTOTAL(7, C2:C100)
# 母集団の標準偏差
=SUBTOTAL(8, C2:C100)手動で非表示にした行の扱い
101〜111の番号を使うと、手動で非表示にした行も除外して集計できます。
使い分けの例
月次報告書で特定の行を非表示にして集計する場合を想定します。
# フィルター非表示のみ除外（手動非表示は含む）
=SUBTOTAL(9, C2:C100)
# フィルター非表示と手動非表示の両方を除外
=SUBTOTAL(109, C2:C100)グループ化（アウトライン）で行を折りたたんでいる場合にも、101〜111の番号を使うと折りたたんだ行を除外して集計できます。
実務での推奨
特別な理由がなければ、101〜111の番号を使用することを推奨します。フィルターと手動非表示の両方に対応できるため、意図しないデータが集計に含まれるリスクを減らせます。
SUBTOTAL関数同士のネスト回避
SUBTOTAL関数には、参照範囲内にある他のSUBTOTAL関数の結果を無視する特徴があります。
小計と総合計の二重計上を防ぐ
以下のように小計行にSUBTOTAL関数を使い、最後に総合計を求める場合を考えます。
| 行 | 内容 | 金額 |
|---|---|---|
| 2 | 商品A | 1000 |
| 3 | 商品B | 2000 |
| 4 | 小計 | =SUBTOTAL(9, C2:C3) |
| 5 | 商品C | 3000 |
| 6 | 商品D | 4000 |
| 7 | 小計 | =SUBTOTAL(9, C5:C6) |
| 8 | 総合計 | =SUBTOTAL(9, C2:C7) |
SUM関数で総合計を求めると、小計行の値も加算されて二重計上になります。しかしSUBTOTAL関数は、範囲内のSUBTOTAL関数の結果を自動的に除外するため、正しい総合計（10000）が返されます。
実務での活用例
売上データの集計ダッシュボード
フィルターと組み合わせた集計ダッシュボードを作成します。
# 売上合計
=SUBTOTAL(109, C2:C1000)
# 件数
=SUBTOTAL(103, A2:A1000)
# 平均売上
=SUBTOTAL(101, C2:C1000)
# 最高売上
=SUBTOTAL(104, C2:C1000)
# 最低売上
=SUBTOTAL(105, C2:C1000)フィルターで部門や期間を絞り込むと、すべての集計値が連動して更新されます。
テーブル機能との併用
Excelのテーブル機能を使用している場合、テーブルの集計行ではSUBTOTAL関数が自動的に設定されます。集計行のドロップダウンから集計方法を選択できます。
在庫管理での活用
商品カテゴリでフィルタリングし、表示されている商品の在庫数合計や平均在庫単価を確認する場面で活用できます。
# 在庫数合計
=SUBTOTAL(109, D2:D500)
# 平均在庫単価
=SUBTOTAL(101, E2:E500)よくあるエラーと対処法
#VALUE!エラー
集計方法の番号が正しくない場合に発生します。1〜11または101〜111の範囲で指定してください。
集計結果が変わらない
フィルターではなく、条件付き書式やセルの色で絞り込んでいる場合、SUBTOTAL関数は対応しません。行自体が非表示にならなければ、SUBTOTAL関数の結果は変わりません。
参照範囲にエラーがある場合
参照範囲内に#N/Aや#VALUE!などのエラーセルがあると、SUBTOTAL関数もエラーを返します。エラーを無視して集計したい場合はAGGREGATE関数を使用してください。AGGREGATE関数はSUBTOTAL関数の上位互換で、19種類の集計方法に対応しエラー値の除外も可能です。
まとめ
SUBTOTAL関数は、フィルターと連動した集計を行うための関数です。
- 集計方法を番号で指定し、11種類の集計関数に対応する
- 1〜11はフィルター非表示行を除外、101〜111はさらに手動非表示行も除外
- SUBTOTAL関数同士のネストでは二重計上が自動的に回避される
- テーブル機能の集計行でも使用されている
- エラー値を無視したい場合はAGGREGATE関数を使用する
なお、SUBTOTAL関数はGoogle スプレッドシートでも同じ構文で利用できます。フィルターを使ったデータ分析では、SUM関数やAVERAGE関数の代わりにSUBTOTAL関数を使うことで、表示データだけの正確な集計結果を得られます。