今回は複数の条件の中で当てはまるデータのみ最大値をもとめる方法についてまとめています。
本来であれば、一番最適な関数は「MAXIFS関数」なのですが
MAXIFS関数はエクセルのバージョンが「365」「2019」「2016」のみ対応となっており
それ以前のバージョンでは使用することができません。
そこで今回はMAXIFS関数が使用できなくても複数条件に当てはまる
最大値をもとめる方法をご紹介します。
それでは見ていきましょう。
■こんなことができるようになる!
・MAXIFS関数を使用しなくても複数条件に当てはまる最大値を算出することができる。
■複数条件に当てはまる最大値をもとめる
今回、使用する関数はMAX関数とIF関数になります。
それぞれの詳細な内容について知りたい場合は下記リンクをご参照ください。
MAX関数はこちら☟
【MAX関数】エクセルで範囲を指定して、数値の最大値を求める方法について ► 独学エクセル塾 (dokugakuexcel.com)
IF関数はこちらから☟
【IF関数】エクセルで○○だったら△△を表示する～条件分岐を身に着ける～ ► 独学エクセル塾 (dokugakuexcel.com)
・1つの条件の最大値をもとめる
まず、手始めに1つの条件に当てはまる最大値をもとめてみましょう。
見本で下図のファイルを作成してみました。
B列には日付のデータが入っておりC列には収益額が入力されています。
今回はE3に入力してある「対象日以上」という条件のもと、最大値をもとめてみましょう。
通常であればMAX関数を使用しますが、「対象日以上」という条件が含まれています。
そこでIF関数と合わせることで条件を追加した最大値を求めることができます。
では関数の仕組みを見てみましょう。
{=MAX(IF(条件の範囲=条件,最大値をもとめたい範囲,””))}
関数の仕組みを見てみると数式の両端に「｛｝」がついてますよね。
こちらは配列関数と呼ばれる方法で「｛｝」を直接入力するのではありません。
数式を入力した後に「Ctrl」+「Shift」+「Enter」を同時に押すことで数式を確定することで配列関数となります。
では関数に当てはめてみましょう。
条件の範囲は日付で絞り込みたいので日付の範囲のB3:B12
条件はE3以上の日付を最大値したいので>=E3
最大値を求めたい範囲は収益なので収益の範囲のC3:C12となります。
数式はこのようになります。
=MAX(IF(B3:B12>=E3,C3:C12,””))
では入力してみましょう。
この状態で「Ctrl」+「Shift」+「Enter」を同時に押します。
結果を見てみましょう。
数式を確認して「｛｝」が入っていれば問題ありません。
これで条件の「5月27日」以上に当てはまる最大値は「874」ということがわかりました。
条件に当てはまらない「5月25日」の数値を「999」にしてみましょう。
参照範囲では「999」が最大値ですが、条件に当てはまらないので
最大値の出力の対象外となってます。
・2つの条件の最大値をもとめる
次に2つの条件に当てはまる最大値をもとめてみましょう。
下図の見本をご覧ください。
今回は開始時刻から終わりに時刻の間の最大値をもとめていきます。
つまり条件は2つあるということになりますね。
関数の仕組みはこのようになります
{=MAX(IF((条件の範囲1=条件1)*(条件の範囲2=条件2),最大値をもとめたい範囲,””))}
では関数に当てはめてみましょう。
条件の範囲1は日付で絞り込みたいので日付の範囲のB3:B12
条件1はE3以上の日付を最大値したいので>=E3
条件の範囲2も日付で絞り込みたいので日付の範囲のB3:B12
条件2はF3以下の日付を最大値したいので<=E6
最大値を求めたい範囲は収益なので収益の範囲のC3:C12となります。
数式はこのようになります。
=MAX(IF((B3:B12>=E3)*(B3:B12<=E6),C3:C12,””))
それでは入力してみましょう。
この状態で「Ctrl」+「Shift」+「Enter」を同時に押します。
結果を見てみましょう。
条件の「14：00から19：00」に当てはまる最大値を算出することができました。
・3つ以上の条件の最大値をもとめる
最後に3つ以上の条件に当てはまる最大値をもとめてみましょう。
下図の見本をご覧ください。
開始日と終わり日の2つの日付の間に加えて、対象に「○」がついてある条件に当てはまる最大値を求めたいと思います。
つまり3つの条件ということになりますね。
関数の仕組みはこのようになります。
{=MAX(IF((条件の範囲1=条件1)*(条件の範囲2=条件2)*(条件の範囲3=条件3),最大値をもとめたい範囲,””))}
関数を見てもらうとわかると思いますが
「*」をつなげていくと、条件を増やしていける仕組みになっています。
では2つの条件の際に入力した数式に対象の条件を加えてみましょう。
数式はこのようになります。
=MAX(IF((B3:B12>=F3)*(B3:B12<=F6)*(D3:D12=”○”),C3:C12,””))
この状態で「Ctrl」+「Shift」+「Enter」を同時に押します。
結果を見てみましょう。
すべての条件に当てはまる最大値は「578」ということがわかりました。
⬛︎よくあるエラーと対処法
・条件に当てはまらない数値も含まれる
こちらは配列関数になってない可能性があります。
関数を確定する際は「Ctrl」+「Shift」+「Enter」を同時に押してみてください。
☟通常に関数
☟配列関数
⬛︎まとめ
いかがだったでしょうか。
MAXIFS関数はバージョンが「365」「2019」「2016」のみ対応となっているため、それ以前のバージョンでは使用することが出来ません。
是非今回の内容を活用して
条件を絞ったの最大値を求めてみてください。
それでは次回の記事でお会いしましょう。
コメント