Excel。MAXIFS関数はないけれど、条件付き最大値を算出してみたい
＜AGGREGATE関数＞
Office365版のExcel2016に加わった関数に、MAXIFS関数という条件付き最大値を算出できる関数が登場したそうですが、
それ以外のバージョンのExcelを使っていても、
条件付き最大値を算出できるので、その方法をご紹介していきます。
ただ、Excel2010以降ということで紹介していきます。
Excel2010にAGGREGATE関数というのが加わり、
その関数を使うことで条件付き最大値を算出することが出来ます。
AGGREGATE関数は、SUBTOTAL関数に似ている関数ですが、
計算種類が増えましたので、
条件付き最大値を算出することが出来るようになりました。
では、次の表があります。
A1からの表がありまして、
支店名が東京の売上金額の最高値をJ2に算出していきます。
J2をクリックして、
AGGREGATE関数ダイアログボックスを表示したいところですが、
計算種類を選択する都合、手入力をお勧めします。
=AGGREGATEと手入力していきます。
続いて、計算種類が聞かれますので、14のLARGEを選択します。
「,」カンマを入力すると、今度はオプションを選択します。
今回は、4の「すべてを検索対象とします」を選んて、
「,」カンマを入力すると、配列。つまり範囲選択をしていき、
最後の順位は、最高値なので、1を設定して、
最終的に、次の数式を完成させます。
=AGGREGATE(14,4,($C$2:$C$128=I2)*$G$2:$G$128,1)
これで完成しました。
数式の説明は後にして、本当に東京の最高売上金額があっているのかを、
表を並び替えして確認してみましょう。
A1をクリックして、データタブの並び替えをクリックします。
最優先されるキーは、支店で順序は昇順。
次に優先されるキーは、売上金額で順序は降順。
設定したら、OKボタンをクリックしましょう。
並び替えを行うと、東京の売上金額の最高値は、
算出した値と同じになっているのが確認できますね。
表をNoの昇順に戻しておきましょう。
では、数式を改めて確認してみましょう。
=AGGREGATE(14,4,($C$2:$C$128=I2)*$G$2:$G$128,1)
引数の($C$2:$C$128=I2)*$G$2:$G$128が何なのか？を説明していきます。
H2に
=C2="東京"
という数式を作成して、オートフィルで数式をコピーしてみましょう。
東京ならば、TRUE。そうでなければ、FALSEが算出されました。
Excelでは、TRUEが1でFALSEが0と設定されていることを使っていきます。
要するに次のようになっているわけです。
I2には、
=H2*G2
という計算式を作ることで、
TRUEだったら、売上金額を表示という結果を求めることが出来ました。
TRUE。
すなわち、これで東京のみの売上金額を【抽出】することが出来たわけです。
あとは、このI列の最高値を算出すれば、
東京都の売上金額の最高値を算出することが出来るわけです。
DMAX関数を使用する方法もありますが、
事前にセルに条件枠を準備する必要があるので、
AGGREGATE関数を使った条件付き最大値を算出する方法をご紹介しました。
