Excel。範囲内に集計値がある場合、楽に最大値を算出するにはどうしたらいいの
＜SUBTOTAL関数＞
四半期集計がある表の場合、最大値を算出するとなると、四半期集計を除いて範囲選択しないと算出することができません。
B11の数式は、最大値を算出したいので、MAX関数をつかっています。
=MAX(B2:B9)
ただ、B5やB9に四半期集計があるので、これら集計行を除かないと、当然集計値の方が大きいため、きちんとした最大値を算出することができません。
当然、範囲選択で集計行を除けばいいわけですが、集計行が増えれば増えるほど、面倒になります。
このような集計表を作る場合、実は、四半期集計などの途中集計や、最大値を算出するにあたり、「SUBTOTAL関数かAGGREGATE関数」をつかうことで、範囲選択が面倒にならず、手早く算出することができます。
まず、B5とB9の四半期集計をSUM関数からSUBTOTAL関数に変更します。
B5の数式は、
=SUBTOTAL(9,B2:B4)
B9の数式は、
=SUBTOTAL(9,B6:B8)
このように、数式を設定します。
なお、SUBTOTAL関数は集計をする関数です。
最初の引数は「集計方法」です。
「9」は合計を算出する番号です。
今回は、行の非表示がないので、「109」の100番台はつかわなくて大丈夫です。
B11の最大値もMAX関数ではなくて、SUBTOTAL関数で数式をつくります。
=SUBTOTAL(4,B2:B9)
計算方法「4」番は、最大値を設定する番号です。
そして、2つ目の引数は、範囲ですが、集計行を除く必要はありません。
すると、集計行を除いて、最大値を算出することができます。
SUBTOTAL関数は、範囲選択に、SUBTOTAL関数をつかった算出結果がある場合には、それを除外して、算出してくれるという、便利な機能があります。
それにより四半期集計を除いた最大値を算出することができたというわけです。
ちょっとした関数の違いですが、手早く算出できるかもしれませんので、調べてみるといいかもしれませんね。
