SUBTOTAL関数は、リストや表を集計する関数です。フィルターで非表示になった行を自動的に除いて合計できるため、絞り込み結果の確認に向いています。
一番簡単な使用例
セルC2からC8を合計し、フィルター後は表示されている行だけを集計します。
=SUBTOTAL(109,C2:C8)
この例の結果は文具へ絞り込んだ場合は4,400円です。
検証について：この記事の数式、計算結果、セル参照は、Microsoft Excel 2019（バージョン16.0）で実際に確認しています。表の内容はすべて架空のサンプルです。
この関数でできること
合計、平均、件数、最大値などを集計できます。最初の引数へ集計方法の番号を指定し、フィルターで非表示になった行は常に集計から除外されます。
関数の書き方
SUBTOTAL(集計方法, 範囲1, [範囲2], ...)
| 引数 | 意味 |
|---|---|
| 集計方法 | 実行する集計を番号で指定します。9と109はどちらも合計です。 |
| 範囲1 | 集計する最初のセル範囲です。 |
| 範囲2以降 | 必要に応じて追加するセル範囲です。 |
実際のExcelで使ってみる
カテゴリ、商品名、売上を入力し、9、109、SUMの3種類で結果を比較します。
- セルF2へ =SUBTOTAL(9,C2:C8)、セルF3へ =SUBTOTAL(109,C2:C8) を入力します。
- 比較用としてセルF4へ =SUM(C2:C8) を入力します。フィルター前は3つとも9,500円です。
- 表へフィルターを設定し、カテゴリを「文具」に絞り込みます。SUBTOTALの結果は4,400円へ変わり、SUMの結果は9,500円のままです。
サンプルファイル
数式と計算結果を確認できる完成済みファイルです。ダウンロード後、結果セルを選択すると数式バーで式を確認できます。
実務で使える例
売上一覧を店舗や担当者で絞り込み、画面に表示中のデータだけを合計したい場合に使えます。元データを消したり数式を書き換えたりせず、フィルター条件に応じた集計結果を確認できます。
初心者が間違えやすいポイント
- 9と109はどちらも合計です。9は手動で非表示にした行を含み、109は手動で非表示にした行も除外します。
- フィルターで非表示になった行は、9と109のどちらでも集計から除外されます。
- SUM関数はフィルターで非表示になった行も合計するため、表示行だけの合計には向きません。
- 集計範囲内に別のSUBTOTAL関数がある場合、その入れ子の小計は二重計算を避けるため無視されます。
関連する関数・教材
目的に合う関数を選ぶために、次の記事も確認できます。
- すべての行を単純に合計する場合はSUM関数を使います。
- 条件を数式の中で指定して合計する場合はSUMIF関数を使います。
- 顧客別購入集計の教材では、オートフィルターの基本操作を確認できます。
まとめ
SUBTOTAL関数は、フィルターで表示されている行だけを集計できる点がSUM関数との大きな違いです。合計では9または109を使い、手動で非表示にした行も除外したい場合は109を選びます。
コメント