Excel SUBTOTAL関数の使い方｜合計や平均の集計方法

Excel（エクセル）のSUBTOTAL関数の使い方を紹介します。データを集計して合計や平均を求める時、SUM関数やAVERAGE関数を使ったことがあるでしょう。ここではそのような関数を1つの関数で代用する方法を解説します。

合計や平均の集計をするために、どのように引数を設定すればよいか丁寧に説明していますので参考にしてください。

ExcelのSUBTOTAL関数とは

Excel（エクセル）のSUBTOTAL関数とは、集計の種類を指定してさまざまな集計値を求めることができる関数のことです。SUM関数やAVERAGE関数を使わなくても、合計や平均など様々な統計量を求めることができます。

参照範囲にSUBTOTAL関数がある場合、その値は集計から除外してくれるので小計と全ての合計を素早く計算したい時に使い勝手のよい関数です。

ExcelのSUBTOTAL関数の書式

Excel（エクセル）のSUBTOTAL関数の書式は「=SUBTOTAL関数(集計方法,参照1,[参照2],…)」です。

引数は2つと少なく覚えやすいでしょう。それでは第1引数と第2引数の具体的な指定の仕方について見ていきます。

第1引数：集計方法

第1引数は集計方法です。SUBTOTAL関数は11個の関数を代替できるので、それぞれの関数に割り当てられた番号「1~11」を使って求める統計量を指定します。

またフィルターなどで非表示になっている値を含めるか含めないか設定することもできます。その場合はそれぞれの関数に割り当てられた番号「101~111」を使って指定します。

下の表に「同等の関数」と「集計の方法（非表示の値を含めるか含めないか）」に対応する引数をまとめましたので参考にしてください。例えば、ある範囲の平均を、非表示の値を含めないで求めるには「101」という番号を指定します。

第2引数：参照（集計したいセル範囲）

第2引数は集計するセルの範囲を選択する部分です。この引数に数値を直接指定することはできませんので注意しましょう。参照の範囲は最大254個まで設定することができます。

ExcelのSUBTOTAL関数の使い方【実例】

Excel（エクセル）のSUBTOTAL関数の使い方を実例を踏まえながら説明していきます。合計、最大、平均値の場合を紹介していますが、他の統計量に関しても同様の手順で求めることが可能です。

SUBTOTAL関数で合計値を求める

まずはSUBTOTAL関数で合計値を求める方法です。例では家計簿のデータを用いて4月の支出の合計を算出します。

SUBTOTAL関数で最大値を求める

次にSUBTOTAL関数で最大値を求める方法です。例では家計簿のデータを用いて4月の支出の最大値を算出します。

SUBTOTAL関数で平均値を求める

最後にSUBTOTAL関数で平均値を求める方法です。例では家計簿のデータを用いて4月の支出の平均値を算出します。

ExcelのSUBTOTAL関数とSUM関数の違いは？

Excel（エクセル）のSUBTOTAL関数を使えばSUM関数の代用ができることが分かりました。しかもSUBTOTAL関数には、SUM関数にない特徴があります。それは小計の取り扱いです。

SUBTOTAL関数を使うと、集計範囲の中にSUBTOTAL関数を使って求めた小計がある場合は自動で除外して集計してくれるのです。これはSUM関数にない特徴です。

具体例で見ていきましょう。まずはSUBTOTAL関数で各グループの小計を求めます。

次にグループ全体の合計をSUBTOTAL関数で求めます。この時参照範囲は、小計も含めた範囲を指定して問題ありません。なぜならSUBTOTAL関数で指定する範囲の中にSUBTOTAL関数を使った集計結果があれば、自動的にその値を除外してくれるからです。

このように、縦方向にいくつかのグループがありそれぞれの小計と全ての合計を表示する時はSUBTOTAL関数を利用するのが便利です。

SUBTOTAL関数の集計方法｜非表示の値を含める／含めない

Excel（エクセル）のSUBTOTAL関数の集計方法で、第1引数の設定を変えれば非表示の値を含めたり含めないことが可能であることを説明しました。ここでは非表示の値を含めた場合と含めなかった場合の差を分かりやすくするため、どちらもMAX関数の代替で比較してみましょう。

SUBTOTAL関数の集計方法で非表示の値を含める

SUBTOTAL関数の集計方法で非表示の値を含めるには第1引数に「1~11」の数字を指定します。例えば画像のように非表示の行があるデータを使って最大値を求めてみましょう。最大値を表示するセルに「=SUBTOTAL(4,C2:C11)」と入力します。

最大値は「10」となりますが、これは非表示となっているセルのデータです。第1引数に「1~11」を指定したので、非表示の行のデータも集計の対象となっていることが分かります。

SUBTOTAL関数の集計方法で非表示の値を含めない

次にSUBTOTAL関数の集計方法で非表示の値を含めない場合を見ていきましょう。第1引数には「101~111」を指定します。先ほどと同様に、非表示の行があるデータを使って最大値を求めてみましょう。最大値を表示するセルに「=SUBTOTAL(104,C2:C11)」と入力します。

今度は最大値は「9」となりました。これで非表示となっている「10」のデータが集計範囲の対象から除外されていることが分かります。

SUBTOTAL関数とフィルター機能を組み合わせる使い方

最後にSUBTOTAL関数とフィルター機能を組み合わせた使い方を紹介します。

ExcelのSUBTOTAL関数についておさらい

Excel（エクセル）のSUBTOTAL関数について、代用できる関数や引数の指定方法を紹介しました。SUBTOTAL関数は1つの関数で以下の11種類の統計量を求めることができます。引数は2つと数が少ないので初心者にも使いやすいでしょう。

SUBTOTAL関数は、非表示のセルや小計セルを除外して集計してくれる特徴がありますので、用いるデータによってはとても効率的にデータ整理ができることも覚えておきましょう。