Excel。範囲内に小計があると最大値を算出するのが面倒なので、どうにかしたい
＜SUBTOTAL関数・AGGREGATE関数＞
簡単そうな処理でも、ちょっと表の条件が変わると面倒になることが多いのもExcelの特徴といえば特徴ですが、次の表もそのパターン。
四半期ごとの集計が途中に表示されている表なのですが、半期の売上高の最高金額を算出したい場合、意外と面倒なことが発生します。
何が面倒なのかというと、B10の数式は、
=MAX(B2:B4,B6:B8)
というように、小計値を含めてしまうと、合算値なので、一番大きな数値なのは決まっていますから、いちいち範囲選択をわけて設定しないといけないわけです。
当然、第2四半期までですが、これが、第3・第4というように増えるとさらに範囲選択を繰り返す必要が発生します。
まして、四半期ではなく、さらに大きなデータの場合は、面倒です。
できれば、範囲選択を一度で済ませたい。
なんで、そうなってしまうのかというと、小計の数式に問題があるのです。
B5の数式を確認してみると、
=SUM(B2:B4)
別に何の問題もない、SUM関数で合計値を算出しています。
B9も同じようにSUM関数を使っています。
このSUM関数をある関数に変更するだけで、そして、最大値を算出するのもMAX関数ではなくて、ある関数に変更するだけで、範囲選択に小計を含めても最大値を算出することができます。
【SUBTOTAL関数かAGGREGATE関数で小計を算出】
Excelには、小計を算出するための『SUBTOTAL関数』というのが用意されています。B5にSUBTOTAL関数をつかって小計を算出していきます。
SUBTOTAL関数は手入力で設定するほうが引数の設定が楽です。
最初の引数、集計方法は、109のSUMを選択します。先頭に”1”が付いている集計方法は、行の非表示にも対応しています。
今回非表示にすることはありませんが、109のSUMで設定してきます。
次の参照1には、B2:B4を範囲選択します。
B5の数式は、
=SUBTOTAL(109,B2:B4)
と設定しております。
同じように、第2四半期のB9も設定します。
算出結果を確認すると、先程のSUM関数と同じ結果になっていますね。
そして、ここからが本題。
最大値をMAX関数ではなくて、SUBTOTAL関数をつかって算出していきます。
B10にSUBTOTAL関数で最大値を算出します。
今回は、104の最大値を使用します。
そして、参照の範囲は、B2:B9と小計を含めて範囲選択を設定します。
B10の数式は、
=SUBTOTAL(104,B2:B9)
では、確認してみましょう。
MAX関数の時のように、わざわざ範囲選択を分けて設定する必要はなく、小計を含めて範囲選択して算出することができました。
このように、SUM関数だけでなく、SUBTOTAL関数を使うことで、他の作業を追加していくときに利便性が向上します。
また、SUBTOTAL関数ではなく「AGGREGATE関数」を使っても同じように最大値を求めることができます。
