Excel

エクセルで条件付きの最小値を求める【#Excel #数式 #上級者】

あなたはエクセルで、

条件付きの
最小値を
求めたい

と思ったことは
ありますか。

条件付きの平均値を
求める方法が
あるので、

きっと最小値も
同じように
条件付きの関数が
あるのだろう

と思いきや、
なくてがっかり
していませんか。

条件付きの最小値を
求めるには、

かなり回りくどい
数式を組まないと
いけません。

また、いつでも
同じように
数式を組めば
求められる

というものでも
ありません。

そういう意味で、
上級者向けの方法
と言えますね。

ですが、
この数式が
理解できるなら、

あなたは
自らの力で
新たに数式を
組み上げていける。

上級者であると
言っていいでしょう。

そのための
ヒントになれば
幸いです。

方法:複雑で見出しには書けない

=MIN(INDEX((条件の範囲=条件)*値の範囲+(条件の範囲<>条件)*AVERAGEIF(条件の範囲,条件,値の範囲),0))

一見してわかるとおり、かなり複雑で回りくどくて面倒くさい数式です。

段階を踏んで少しずつ説明します。

条件を満たす場合の処理

=MIN(INDEX((条件の範囲=条件)*値の範囲+(条件の範囲<>条件)*AVERAGEIF(条件の範囲,条件,値の範囲),0))

条件の範囲=条件とすることで、条件を満たすセルの値をTRUEに、条件を満たさないセルの値をFALSEに変換します。なお、TRUEとFALSEは内部ではそれぞれ1と0として扱われているので、四則演算することができます。

=MIN(INDEX((条件の範囲=条件)*値の範囲+(条件の範囲<>条件)*AVERAGEIF(条件の範囲,条件,値の範囲),0))

TRUEもしくはFALSEになった条件の範囲に値の範囲をかけることで、条件がTRUEになる場合はそのままの値に、FALSEになる場合は0に変換します。これにより、条件を満たす場合の値だけを取り出すことができます。

ここまでで、条件を満たす場合の処理が完了しました。

条件を満たさない場合の処理

条件付きの最小値を求める場合の肝は、条件を満たさない場合の処理にあります。

=MIN(INDEX((条件の範囲=条件)*値の範囲+(条件の範囲<>条件)*AVERAGEIF(条件の範囲,条件,値の範囲),0))

条件を満たす場合の逆の処理です。条件の範囲<>条件とすることで、条件を満たしている場合をFALSEに、満たしていない場合をTRUEに変換します。

=MIN(INDEX((条件の範囲=条件)*値の範囲+(条件の範囲<>条件)*AVERAGEIF(条件の範囲,条件,値の範囲),0))

この処理が肝です。

条件を満たさない場合の値を、条件を満たす場合の平均値に変換します。
え、よくわからない?
ではもう一度。
条件を満たさない場合の値を、(((条件を満たす場合)の平均値)に変換)します。

ちょっとは読みやすくなったでしょうか。

これをすることで、条件を満たさない場合でも、条件を満たす場合の最小値よりも大きく、条件を満たす場合の最大値よりも小さい値に変換されるので、最大値でも最小値でも、求めることができるようになります。また、条件を満たす場合の値は先にFALSEに変換してあるので、これは0に変換されます。

さて、最後の仕上げです。

条件付きの最小値を求める

=MIN(INDEX((条件の範囲=条件)*値の範囲+(条件の範囲<>条件)*AVERAGEIF(条件の範囲,条件,値の範囲),0))

太字部分は先に計算したものを足し合わせたものです。赤字部分は条件を満たさない場合が0、青字部分は条件を満たす場合が0になっているので、それぞれ足し合わせれば互いに影響を与えることなく合体させることができます。

=MIN(INDEX((条件の範囲=条件)*値の範囲+(条件の範囲<>条件)*AVERAGEIF(条件の範囲,条件,値の範囲),0))

最後にMIN関数で最小値を求めれば完了です。これで、条件付きの最小値を求めることができました。なお、MINをMAXに置き換えれば、すぐに条件付きの最大値を求めることができます。試しにやってみましょう。

ね。

ご覧の通りです。

ほかのサイトでは「最大値は求められるけど最小値は求められない」とか、「最大値はこの方法で、最小値はこの方法で」みたいに違う数式を組まないといけないものがほとんどです。わかりにくいですが便利なので、ぜひ後世に伝えていってください。

謎の数式

さて、この数式の中で説明を飛ばした部分があります。

=MIN(INDEX((条件の範囲=条件)*値の範囲+(条件の範囲<>条件)*AVERAGEIF(条件の範囲,条件,値の範囲),0))

さて、これは一体何でしょうか。

実は、INDEX関数の第2引数に0を与えると、第1引数に与えられた配列をそのまま返すという機能があります。

これを利用することで、配列数式を使うことなく配列計算を行うことができます。配列数式は便利な反面制約が多いので、INDEX関数で疑似的に配列数式を使っています。

これも、ちょっとしたときに使えるので、覚えておくとよいでしょう。

あとがき

さて、
条件付き最小値の
求め方は
わかりましたか。

かなり複雑な
方法でしか
求められないので、

説明も
長くなって
しまいまいした。

ですが、
ここまでの
説明で理解できた
あなたなら、

ここに出てきた
テクニックを
応用して、

さらに
いろいろなことを
解決していく
ことができる
でしょう。

あなたの
今後のひらめきに
期待しています。

では。