Excel。小計を含まずに、大量のデータから、最大値を見つける方法
＜SUBTOTAL関数＞
Excelにおいて、それほど大きくない表ならば、途中にある小計を除いて範囲選択して、
MAX関数を使うか、あるいは、MIN関数を使えば、
最大値や最小値を求めることができますが、データ量が多い表の場合、
その途中途中にある、小計を外して、範囲選択をして算出するのは、
結構面倒な作業となりますよね。
小計を含めて、範囲選択をしても、算出してくれるのが、一番楽なのですが、
残念ながらMAX関数では、当然小計の値を持ってきてしまいます。
そこで、どうしたら効率よく算出することが出来るのか？
というのが、今回のテーマなのです。
まずは、下記の表をご覧ください。
このような表があります。それぞれの地域で小計があります。
B9の数式は、
=SUM(B2:B8)
というように、それぞれの小計はSUM関数を使って算出してあります。
そこで、確認のため、E3にMAX関数で、B2:B25までを範囲選択して算出してみましょう。
当然、小計も含めてしまいますので、このような結果が算出されますね。
なので、この小計を除きたいとした場合、
MAX関数を使用した場合には、範囲選択で小計を除かないといけないわけです。
しかしながら、データが大量の場合は大変な作業となってしまいます。
そこで、小計をSUM関数ではなくて、
【SUBTOTAL関数】
を使って算出すると、この問題を解決してくれるのです。
では、先程の表にC列を加えてみました。
C列の小計もB列と同じになっていますね。
C9の数式は、
=SUBTOTAL(109,C2:C8)
なお、このSUBTOTAL関数は手入力で作成するのがオススメの関数ですね。
ダイアログボックスでもいいのですが、集計方法の番号がわかりません。
手入力だとこのようになります。
=subtotal と入力した後に、集計方法の番号一覧が登場しますので、
この一覧から選ぶほうが楽ですね。
ちなみにSUMはSUMでも、9番ではなくて109番のSUMを選択しております。
これは、非表示にしても、可視情報のみで算出してくれますので、実務向きですね。
そして、先程は、MAX関数を使いましたが、こちらもSUBTOTAL関数を使って算出します。
MAX関数では変わりません。
E6にSUBTOTAL関数を使って算出させていきましょう。
E6の数式は、
=SUBTOTAL(104,C2:C25)
範囲選択は、C2:C25と小計を含んでいますよね。
つまり、SUM関数の代わりにSUBTOTAL関数で小計を算出させて、
MAX関数の代わりに、SUBTOTAL関数を使うことによって、
作業効率がアップすることが出来ますので、大きなデータで、
小計を含むようなデータの場合は、SUBTOTAL関数を知っているといいかもしれませんね。
