Excel。表中に小計や平均があるときは、SUBTOTAL関数・AGGREGATE関数の出番です
＜SUM・AVERAGE・SUBTOTAL・AGGREGATE関数＞
表中に小計行や平均行があれば、普通はSUM関数やAVERAGE関数を使って算出しますが、
総合計を算出しようとすると、範囲選択がとても、面倒なことになります。
どうしても、合計＝SUM関数や平均＝AVERAGE関数というのが、
しみ込んでいますが、
これをSUBTOTAL関数・AGGREGATE関数に変えるだけで、
作業効率が改善します。
では、次の表を使って確認してみましょう。
東京小計や関東小計はSUM関数を使って算出しています。
数式はそれぞれ、
B9には、=SUM(B3:B8)
B14には、=SUM(B11:B13)
東京平均と関東平均には、AVERAGE関数を使って算出しています。
数式はそれぞれ、
B10には、=AVERAGE(B3:B8)
B15には、=AVERAGE(B11:B13)
ここからが少し面倒になってきます。
B16の合計を算出する場合、B9とB14を範囲選択する必要があります。
B16の数式は、=SUM(B9,B14)
今回は、小計箇所が2か所なので、手で入力するとしても、
それほど面倒ではないかもしれませんが、この小計数が10か所もあれば、
とても面倒で厄介になることが想像できると思います。
さらに厄介なのが、平均。
間違っても、合計のように範囲を使ってはいけませんね。
B17に、
=AVERAGE(B10,B15)
という数式を作ったらダメですね。
平均の平均は、求めたい数値になりません。
なので、該当のデータを範囲選択する必要が出てきますので、
B17の数式は、
=AVERAGE(B3:B8,B11:B13)
としなければなりません。
ということは、データ量が多い場合は、
いちいち範囲選択から小計行を除きながら設定する必要が発生するわけですね。
【SUBTOTAL関数はSUBTOTAL関数を除外する】
そこで、SUM関数やAVERAGE関数で算出したところを、SUBTOTAL関数を使って算出してみることにします。
SUBTOTAL関数は手入力するほうが楽な関数ですので、
手入力で数式を作っていきます。
集計方法は、109を使います。
100番台は、行が非表示になると除外してくれます。
では、B9の東京小計の数式は、
=SUBTOTAL(109,B3:B8)
同じ要領で、
B10の東京平均には、=SUBTOTAL(101,B3:B8)
平均の集計方法は、101を使います。
B14の関東小計の数式は、
=SUBTOTAL(109,B11:B13)
B15の関東平均の数式は、
=SUBTOTAL(101,B11:B13)
さて、ここまでは、SUBTOTAL関数に変えただけのように感じますが、
B16の合計をSUBTOTAL関数で算出してみます。
B16の数式は、
=SUBTOTAL(109,B3:B15)
注目する点は、範囲選択の引数ですね。
B3:B15を小計も平均も関係なく範囲選択しています。
では結果を確認してみましょう。
なんと、合計値を算出してくれました。
では、B17の平均もSUBTOTAL関数で算出してみましょう。
数式は、
=SUBTOTAL(101,B3:B16)
今回も範囲選択に注目。
B16の合計も含めてしまっていますが、これで問題ありません。
では結果を確認してみましょう。
SUBTOTAL関数の特徴は、
範囲選択にSUBTOTAL関数で算出した結果がある場合、
それを除外して算出してくれるという『特長』があります。
なので、SUM関数やAVERAGE関数でという固定した考え方だと、
データが大きくなる場合、非常に面倒なことが発生してしまうのです。
そして、注意しなければいけない点があります。
SUBTOTAL関数は途中にある、
SUM関数やAVERAGE関数などの算出結果は除外してくれません。
【AGGREGATE関数でもOK】
AGGREGATE関数は、Excel2010から登場した関数ですが、SUBTOTAL関数と同じ動きをします。SUBTOTAL関数との違いは、
集計方法の種類が増えたことですね。
そして、引数に、オプションが追加されて、
どのように算出させるのかを決めることができます。
東京小計のC9の数式は、
=AGGREGATE(9,3,C3:C8)
それでは、SUBTOTAL関数と同じように、
それぞれの小計や平均を算出してみます。
同じ結果になりましたね。
このように、途中に小計や平均が算出されている表の場合は、
SUBTOTAL関数やAGGREGATE関数を使ってみると、
作業効率が改善することができるかもしれませんね。
