Excel。小計を除いて最大値を求めたいのに範囲選択が面倒なのでどうにかしたい
＜SUBTOTAL関数＞
帳票と同じようにExcelで表を作ってしまうと、意外と面倒な処理が発生することがあります。
例えば次のような表。
小計が含まれている帳票ですね。
最大値を算出しているB14。
おかしいですよね。
合計値の値を算出しています。
範囲選択をB3:B11までにすると、今度は、1782という小計値を算出してしまいます。
要するに、範囲選択内の最大値を算出してしまうわけです。
となると、範囲選択は、「B3:B5,B7:B8,B10:B11」というように、何度も区切らなければいけません。
これでは、件数が増えた場合、かなり面倒な作業をしないと、数式を作ることができないということになってしまいます。
では、どうしたらいいのでしょうか？
まずは、合計値の場合からみてみましょう。
B6・B9・B12には、SUM関数をつかった数式で小計値を算出しています。
B13もSUM関数をつかっていますが、オートSUMボタンをつかって、合計をつかうと、その範囲選択内にあるデータ内でSUM関数の算出結果のみを合算してくれます。
B13の数式は、自動的に、
=SUM(B12,B9,B6)
と生成してくれます。
SUM関数をつかうと、B3:B12をドラッグしても、SUM関数のところだけをつかってくれるので、便利です。
では、最大値はどうなのでしょうか？
オートSUMボタンにある、最大値をつかって、B3:B13までを範囲選択してみると、データであろうが、小計値であろうが、合計値だろうが、関係なく範囲選択内の、最大値を算出してしまいます。
SUM関数をつかったところを除外して算出してくれることはありません。
実は、SUM関数やMAX関数だと、このような帳票には不向きな関数なのです。
今回のような場合は、SUBTOTAL関数かAGGREGATE関数をつかうことで、一気に問題を解決することができ、合理的に数式を作ることができます。
SUBTOTAL関数をつかってみた場合をみてみましょう。
最大値のC14は、小計や合計の値ではなく、店舗の最大値が表示されています。
まずは、小計の数式を確認してみます。
C6の数式は、
=SUBTOTAL(109,C3:C5)
と設定しています。
引数の109をつかうことで、合計を算出することができます。
残りも同じようにSUBTOTAL関数をつかっています。
C13の数式は、
=SUBTOTAL(109,C3:C12)
と設定しています。
範囲選択も小計のセルのみを選択するわけではなく、全体を選択してもSUM関数どうように、SUBTOTAL関数で算出した値のところだけを合算してくれいます。
問題の最大値を算出しているC14の数式は、
=SUBTOTAL(104,C3:C13)
引数の104は、最大値を算出する番号です。
範囲選択は、C3:C13とデータも小計値も合計値も関係なく範囲選択していますが、最大値がきちんと算出されています。
このように、途中に小計や合計が含まれる帳票のような表の場合には、SUM関数やMAX関数ではなくて、SUBTOTAL関数やAGGREGATE関数をつかうことで、結果的に効率よく数式をつくることができます。
ケースバイケースで、どの関数をつかうのが効率的なのかを考えながら帳票をつくるといいかもしれませんね。
