Excel。表中の小計を除いて最大値を求めたいけど、小計を修正しないで算出したい。
＜ISFORMULA関数・MAXIFS関数＞
表中に小計が含まれている場合、データの最大値を算出したくても、小計の値が大きいため範囲選択をデータだけにする必要があります。
要するに、小計が邪魔な訳です。
C5とC9は、SUM関数をつかって小計を算出しています。
C10の最大値はMAX関数。
=MAX(C2:C9)
という数式で求めていますが、C2:C9と単純に範囲選択をすると、正しく最大値を算出できません。
そこで、よく、SUM関数やMAX関数ではなく、SUBTOTAL関数や、AGGREGATE関数をつかうことで、小計を含めた範囲選択で対応することができると説明があるわけです。
しかしながら、すでに表があり、しかも、小計の算出箇所が多い場合、SUM関数をSUBTOTAL関数で修正するには、大変です。
そこで、現在あるSUM関数を修正しないで、最大値を手早く算出する方法をご紹介します。
登場するのは、ISFORMULA関数です。
ISFORMULA関数は、セル内のデータが数式なのかを判断することができる関数です。
小計は、SUM関数という数式を使っていますので、数式でないと判断されたものだけの最大値ならばいいというわけです。
D2に設定した数式は、
=ISFORMULA(C2)
この数式をD9までオートフィルで数式をコピーします。
数式ならば、TRUE。
そうでなければ、FALSEを算出します。
その結果をつかい、D10には、次の数式を設定します。
=MAXIFS(C2:C9,D2:D9,FALSE)
条件付き最大値を算出できる、MAXIFS関数をつかうことで、数式以外を対象にした最大値を算出することができます。
最後に、MAXIFS関数の引数を確認しておきます。
最初は、最大範囲です。
最大値を算出したいデータがあるところなので、C2:C9と設定します。
2番目は、条件範囲1です。
条件の範囲ですから、D2:D9を設定します。
3番目は、条件1です。
FALSEと設定します。
真・偽なので、”FALSE”のように、「”（ダブルコーテーション）」で囲ってしまうと文字列になってしまうので、不要です。
