SUBTOTAL関数

Excelで集計表を作成する際に、グループ別に小計をとって、最後に総合計を求めたい、という時がありますね。

小計にSUM関数を使って、総合計には小計のセルを足し算する数式を使う、という方が多いと思います。

SUBTOTAL関数を使えば、もっとスマートに小計と総合計の計算ができます。

SUBTOTAL関数の最大の特徴は、SUBTOTAL関数の範囲内にSUBTOTAL関数で求めた値のセルが含まれるときは、それを無視して計算してくれることです。

つまり、SUBTOTAL関数でそれぞれの小計を求め、さらに合計も集計範囲を全体にしてSUBTOTAL関数で求めれば良いことになり、合計を求めるためにいちいち小計のセルを指定して足し算をする必要がありません。小計を求めるグループが増えたり、減ったりしても、合計を求める式を書き換えなくて済み、便利です。

【便利知識】

SUBTOTAL関数が無視するのはSUBTOTAL関数で集計されているセルのみです。SUM関数など別の関数で集計しているセルは無視してくれません。

つまり、SUBTOTAL関数を使うのであれば、すべての集計についてSUBTOTAL関数を使わなければなりません。さもないと集計が正しく行われないおそれがあります。

SUBTOTAL関数は小計を求めることができる関数ですが、集計方法の指定で平均やカウント、最大値、最小値なども計算できます。

　SUBTOTAL関数の書式　　　=SUBTOTAL(集計方法,範囲)

集計方法は、1～11、あるいは101～111の数字で指定します。

101以降はExcel2003以降に追加されたもので、「非表示のセル」の値を集計に含めないというものです。

SUBTOTAL関数は、ほとんどが小計を含んだ集計表で使われています。つまり、集計方法は圧倒的に「9」（または「109」）が指定されています。

【便利知識】

SUBTOTAL関数と類似のものにAGGREGATE関数があります。AGGREGATE関数は、SUBTOTAL関数の機能を強化した関数で、Excel2010から導入されたものです。

「集計方法」の種類がSUBTOTAL関数よりも多いこと、エラー値が表示されたセルや非表示のセルの扱いを「オプション」指定できるなど進化していますが、引数が増えてより複雑化しました。通常はSUBTOTAL関数で十分と思います。

【便利知識】

SUBTOTAL関数のもうひとつの特徴は、「オートフィルタ」機能を使ってデータが絞り込まれている時は、絞り込まれたデータのみを対象として集計してくれるという点です。

SUM関数では、オートフィルタで非表示になった範囲内のセルも集計してしまいますが、SUBTOTAL関数を使えば、フィルタを変えて表示が切り替わるごとに、表示されたデータのみを集計対象として再計算してくれます。

【便利知識】

また、A列に番号表示させるために、例えば、A2セルに「=SUBTOTAL(3,$C$2:C2)」と入力し、データの最後の行までフィルハンドルをドラッグしてコピーしておけば、オートフィルタで表示されたデータのみを対象として、1から順に番号が表示されます。

集計方法の3は、COUNTA（データの個数）の意味です。

上記の操作で、例えばA5のセルには「=SUBTOTAL(3,$C$2:C5)」がセットされていますので、C2～C4にすべて何らかのデータがあれば、4が表示されることになります。

オートフィルタとSUBTOTALの関連についての詳細は、「オートフィルタ」のページを参照ください。