エクセルで集計作業をしていると、「表示されているデータだけを合計したい」「フィルターで絞った結果だけの平均を出したい」と思ったことはありませんか？
そんなときに便利なのが SUBTOTAL関数 です。
通常のSUM関数ではできない、非表示行を除外した集計や、フィルターに連動した動的な集計が可能になります。
この記事では、SUBTOTAL関数の特徴から具体的な使い方、さらに応用的な活用例までをやさしく解説します。
この記事のゴール
- フィルターで表示されたデータだけを正しく集計できるようになる
- SUBTOTAL関数の構文と番号の意味を理解し、適切な集計方法を選べるようになる
- 「動的な集計」が可能になり、報告資料作成やチェック作業の効率が上がる
SUBTOTAL関数の特徴は？
SUBTOTAL関数の一番の特徴は、「表示されているデータだけを対象に集計できる」ことです。
これは、フィルターや手動で非表示にされた行を含まないで合計や平均などを出したいときに非常に便利です。
SUM関数だと非表示の行まで集計されてしまいますね。
さらに、SUBTOTAL関数は集計の種類を番号で選べるようになっており、合計だけでなく、平均や最大値、最小値なども対応可能です。
SUBTOTAL関数の構文
SUBTOTAL関数の構文は、その機能の幅広さに対してとてもシンプルです。
=SUBTOTAL(集計方法, 参照)
| 集計方法： | 集計したい計算方法を指定する番号です。 合計を求める場合は「9」、平均を求める場合は「1」となります。 |
| 参照 ： | 集計対象となるセル範囲を指定します。 |
SUBTOTAL関数の集計方法の種類
SUBTOTAL関数は様々な集計方法に対応していますが、その中でも実用性の高いものをご紹介します。
| 関数 | 指定番号 | 指定番号（非表示行を除く） |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| SUM | 9 | 109 |
手動で非表示にした行を除きたい場合は、100番台を活用しましょう！
SUBTOTAL関数の具体例
例えば、A列に売上データがある場合、以下のように入力します
=SUBTOTAL(9,B2:B15)
この式では、フィルターなどで表示されている行のデータだけを合計します。
活用例① 非表示行を含めない
売上リストにフィルターをかけて特定の条件だけを表示しているとき、普通のSUM関数では非表示の行も合計されてしまいます。
しかし、SUBTOTAL関数であれば
=SUBTOTAL(9,B2:B15)
のように記述するだけで、今表示されている売上だけを正しく集計できます。
フィルターを多用する業務の中で、こうした集計が正確かつ瞬時にできるのは大きな強みになります！
活用例② 小計を無視して合計
集計対象の範囲内に、すでに小計が含まれているケースもありますよね。
たとえば部署ごとの売上小計がすでに入っている場合、SUM関数だとその小計を二重にカウントしてしまうことがあります。
そんなときも、SUBTOTAL関数なら他のSUBTOTAL関数の結果を自動的に除外してくれるため、二重のカウントを防ぐことができます。
（参考）AGGREGATE関数について
実はSUBTOTAL関数には「上位互換」とも言える存在があります。それが AGGREGATE関数 です。
AGGREGATE関数では、SUBTOTALよりも多くの集計方法が使えるうえに、エラー値の無視や、より柔軟な集計が可能です。
例えば、
=AGGREGATE(9,7,B2:B15)
のような数式を組んでみます。
集計業務に慣れてきたら、AGGREGATE関数にも挑戦してみましょう！
まとめ
SUBTOTAL関数は、見た目に合わせた正確な集計ができる便利な関数です。
- フィルターで絞ったデータだけを合計したい
- 非表示の行は無視して平均を出したい
- 小計を除いて全体の合計を知りたい
そんな悩みを持つ方にぴったりの関数です。
構文さえ覚えてしまえば、活用の幅はぐっと広がりますよ。
日々の業務に取り入れて、効率アップに役立ててください！
