Excelで集計作業をしていると、データの一部だけを合計したい、フィルターをかけた状態で集計したい、または平均やカウントも同時に求めたいという場面が多くあります。そんなときに役立つのがSUBTOTAL 関数です。この記事では、SUBTOTAL 関数の基本から応用まで、初心者でも理解できるようにわかりやすく解説します。これを読めば、日々の表計算がより効率的になります。関数の仕様や引数の詳細はMicrosoft公式リファレンスも参考になります。
SUBTOTAL関数とは？
SUBTOTAL 関数は、Excelで集計作業を行う際に使用される便利な関数で、指定した範囲に対して合計、平均、最大値、最小値などの集計結果を計算できます。特にフィルターや非表示行に対応して集計できるのが大きな特徴です。
書式：
=SUBTOTAL(集計方法番号, 範囲1, [範囲2], …)
集計方法番号によって動作が変わります。
集計方法番号の一覧
SUBTOTAL 関数では、集計方法番号を指定することで集計内容を変えられます。
- 1：AVERAGE（平均）
- 2：COUNT（数値が入力されているセルの個数）
- 3：COUNTA（空白以外のセルの個数）
- 9：SUM（合計）
- 4：MAX（最大値）
- 5：MIN（最小値）
さらに、101〜111の番号を使うと非表示行を無視する集計が可能です。例えば、109はSUM（合計）＋非表示行を除外になります。
基本的な使い方
例1：単純な合計
A1〜A10の合計を求める場合：
=SUBTOTAL(9, A1:A10)
これでフィルターを使った場合、表示されているデータのみ合計されます。
例2：非表示行を無視して平均
A1〜A10の平均を求めたいが、非表示行は無視する場合：
=SUBTOTAL(101, A1:A10)
SUBTOTAL 関数とSUM関数の違い
| 特徴 | SUM関数 | SUBTOTAL関数 |
|---|---|---|
| フィルター対応 | × | ○ |
| 非表示行の除外 | × | ○（101〜系番号使用時） |
| 複数集計方法 | ×（合計のみ） | ○（平均、最大、最小など） |
つまり、フィルターや非表示行を考慮して集計したいなら、SUM関数よりもSUBTOTAL 関数を使うべきです。
応用例
- 売上表で条件付き集計
営業担当別の売上データをフィルターで絞り、その合計を自動表示。SUBTOTAL 関数を使えば、フィルター操作に応じて合計が変動します。
=SUBTOTAL(9, C2:C100) （売上金額がC列の場合） - データ分析の一次集計
ピボットテーブルを作るまでもない小規模な集計で便利。非表示行を除外して集計すれば、見たいデータだけ即座に確認可能。
SUBTOTAL 関数を使うときの注意点
- 集計範囲にさらにSUBTOTALがある場合、その値も計算に含まれてしまうことがあるので二重計算に注意。
- フィルターと非表示行の違いに注意。フィルターは自動で除外されますが、手動で非表示にした行は番号9や1では除外されません。手動非表示を無視するには101〜系の番号を使いましょう。
実務での使いどころ
- 営業成績の集計
- 商品別売上管理
- プロジェクトのタスク進捗集計
- 部署ごとの経費計算
SUBTOTAL関数は単なる合計関数ではなく、条件に応じた柔軟な集計ツールです。より詳しい実践的な使用例はYouseful.jpの解説記事でも学べます。
まとめ
SUBTOTAL 関数は、フィルターや非表示行を考慮した集計が可能な万能関数です。SUM関数だけでは難しい場面でも、集計方法番号を使い分けることで、平均・合計・最大値などを柔軟に計算できます。Excelでの集計作業が多い人にとっては、習得必須の関数と言えるでしょう。
