Excel。途中に集計行がある表。集計を除いた最大値を算出したい。
＜SUBTOTAL関数＞
四半期集計などが表の途中にある表で、最大値を算出したい場合、SUM関数とMAX関数だと上手く算出することができません。
表を用意しましたので、確認していきます。
日々の集計である小計が算出されている表です。
C列の場合からみていきましょう。
小計は、SUM関数とつかって、算出しています。
そして、最大値を算出したいわけですから、MAX関数と考えますが、範囲選択が面倒です。
どうしてかというと、小計を含めて範囲選択すれば、当然、小計の値の方が日々のデータよりも大きくなるからです。
そのため、小計を含めて最大値を算出すると、最大値が算出できないので、小計を除いて範囲選択する必要があります。
ただ、イチイチ、小計を除いて範囲選択するのは、面倒です。
実は、SUM関数やMAX関数とつかって、小計を除いた最大値を算出するよりも、別の関数をつかうと、手早く算出することができます。
先程の表を数式で表示してみます。
C列の小計はSUM関数。そして、最大値はMAX関数をつかっています。
D列は、小計も最大値もSUBTOTAL関数をつかって算出しています。
なお、SUBTOTAL関数ではなくて、AGGREGATE関数をつかってもOKです。
ただ、AGGREGATE関数は引数が、SUBTOTAL関数よりも、多いので、今回は、SUBTOTAL関数をつかって説明しております。
D10のSUBTOTAL関数をつかった最大値の数式ですが、範囲選択は、D2:D9で設定しています。
SUBTOTAL関数は、範囲選択にあるSUBTOTAL関数の結果は除外するようになっています。
そのため、範囲選択を必要なデータのみにする必要はないというわけです。
それでは、小計のD5に設定したSUBTOTAL関数を確認します。
最初の引数は、集計方法です。109は非表示にも対応した合計値を求めることができる番号です。
最大値は、104と割り振られています。
2つ目の引数は、参照なので、範囲選択ということですから、それぞれのデータを設定します。
今回のように、途中に小計とかの集計行が含まれている場合、SUM関数とMAX関数で算出よりも、SUBTOTAL関数をつかうことで、手早く最大値を算出することができます。