SUMPRODUCT関数を用いた最小値

お世話になっております。

SUMPRODUCT関数の最小値の求め方で壁を越えられないため
皆様のお知恵をお貸しください。


下のような表があります。
　　A　B　C（セル列）
1　青　○　100
2　赤　○　95
3　青　×　75
4　青　○　200
5　赤　×　65
6　赤　×　80
7　青　○　105
8　青　×　85
9　赤　○　110
10　赤　×　70

青かつ○の最大値（200）は
=SUMPRODUCT(MAX((A1:A10="青")*(B1:B10="○")*(C1:C10)))

にて求められるのですが


青かつ○の最小値（100）は
=SUMPRODUCT(MIN((A1:A10="青")*(B1:B10="○")*(C1:C10)))

とすると、0が返ってしまいます。


googleで検索しては検証しを繰り返しましたが
どうにもうまくいきません。


Shift+Ctrl+Enterによる
{=MIN(IF((A1:A10="青")*(B1:B10="○"),C1:C10))}

では求められますが、出来れば配列でない方法を探しています。


宜しくお願い致します。

No.1 回答者： CoalTar 回答日時： 2014/02/05 16:56

SUMPRODUCT関数自体、配列を扱う関数です。

符号を逆にし、MIN関数を使わなければよいかと思います。
=MAX(C1:C10)-SUMPRODUCT(MAX((A1:A10="青")*(B1:B10="○")*(MAX(C1:C10)-C1:C10)))

この回答への補足

ご回答ありがとうございます。

重大なミスをしてしまいました。

7　青　○　105

ではなく、

7　青　○　空白

でした。

申し訳ございません。
宜しくお願い致します。

補足日時：2014/02/05 17:22

この回答へのお礼

>No.1=No.5=No.6さん
ありがとうございました。

お礼日時：2014/02/07 18:33

No.2 回答者： bunjii 回答日時： 2014/02/05 19:40

>青かつ○の最小値（100）は =SUMPRODUCT(MIN((A1:A10="青")*(B1:B10="○")*(C1:C10))) とすると、0が返ってしまいます。

論理的にそれで正しいことになります。
SUMPRODUCT関数内でMIN関数を使っていますがその引数の配列値に 0 が7個含まれていますので、その内の1つがMIN関数で返されます。
SMALL関数で8番目、またはLARGE関数で3番目が目的の値になります。

従って、次の式が適当と思います。

=SUMPRODUCT(LARGE((A1:A10="青")*(B1:B10="○")*(C1:C10),COUNTIFS(A1:A10,"青",B1:B10,"○")))

>出来れば配列でない方法を探しています。
SUMPRODUCT関数も配列値を扱っています。
只、Ctrl+Shift+Enterで確定しなくても良いだけです。

{=MIN(IF((A1:A10="青")*(B1:B10="○"),C1:C10))}

数式を確定するときにCtrl+Shift+Enterの操作で配列値が扱えるので、この手法の方がシンプルになると思います。

この回答へのお礼

ご回答ありがとうございます。

LARGE関数を使う方法もあるのですね。
なぜか私の環境では、#NAME?エラーとなってしまいます。

お礼日時：2014/02/07 18:40

No.3 回答者： MackyNo1 回答日時： 2014/02/05 19:42

Ctrl+Shift+Enterで確定しなくても計算できるようにしたいなら、以下のような数式にします。

MAX関数と同じようにSUMPRODUCT関数を利用する場合
=SUMPRODUCT(MIN(((A1:A10<>"青")+(B1:B10<>"○"))*10^10+C1:C10))

INDEX関数で配列を範囲に変換する方法
=MIN(INDEX(((A1:A10<>"青")+(B1:B10<>"○"))*10^10+C1:C10,))

この回答へのお礼

ありがとうございました。

お礼日時：2014/02/07 19:24

No.4 回答者： MackyNo1 回答日時： 2014/02/05 22:36

No3の回答の補足です。

C列に空白セルも混ざっているなら（空白セルを除外対象にしたいなら）、C列が空白以外という条件を追加することになります。

=MIN(INDEX(((A1:A10<>"青")+(B1:B10<>"○")+(C1:C10=""))*10^10+C1:C10,))

上記の数式の意味は、A列が青以外、B列が○以外あるいはC列が空白のいずれかの条件が成立する場合は10＾10という大きな数値を乗算した値（すべて成立しない場合はこの部分が0）と、C列の値を加算した値の中から最小値を調べています。

なお、INDEX関数で配列を範囲に変換する場合は、その配列を取得する場合にIF関数などを使用した数式は利用できません（配列の四則計算のみ使用可）。

この回答へのお礼

ご回答ありがとうございました。

MackyNo1さんご教示の数式はシンプルで正確に答えが求められましたのでベストアンサーとさせて頂きたいところでしたが
今回はSUMPRODUCTでのお題ということでNo.6さんにさせて頂きました。
INDEX関数につきましても勉強していきたいと思います。

ありがとうございました。

お礼日時：2014/02/07 19:24

No.5 回答者： CoalTar 回答日時： 2014/02/06 15:21

No.1です

＞Shift+Ctrl+Enterによる
＞{=MIN(IF((A1:A10="青")*(B1:B10="○"),C1:C10))}
＞では求められますが
と補足では矛盾します。補足の空白は、単純に未入力なセルってことで

未入力なセルも対象外なら条件を追加すればよい
=MAX(C1:C10)-SUMPRODUCT(MAX((A1:A10="青")*(B1:B10="○")*(C1:C10<>"")*(MAX(C1:C10)-C1:C10)))

この回答への補足

ご回答ありがとうございます。
また条件の矛盾点、申し訳ございませんでした。
最後に御教え頂きたいのですが
1,4,7行(青　○　)のデータがすべて未入力の場合
ご教示頂いた数式では、9行目の110が表示されるのですが
これを空白にすることは出来ますでしょうか？

補足日時：2014/02/07 10:02

この回答へのお礼

>No.1=No.5=No.6さん
ありがとうございました。

お礼日時：2014/02/07 18:32

No.6 ベストアンサー 回答者： CoalTar 回答日時： 2014/02/07 14:53

そのまんま

=IF(SUMPRODUCT((A1:A10="青")*(B1:B10="○")*(C1:C10<>"")*(MAX(C1:C10)))=0,"該当なし",
MAX(C1:C10)-SUMPRODUCT(MAX((A1:A10="青")*(B1:B10="○")*(C1:C10<>"")*(MAX(C1:C10)-C1:C10))))

この回答へのお礼

ご教示ありがとうございました。

思い通りの結果が出る数式を手に入れることができ
誠にありがとうございました。
SUMPRODUCT関数を用いての求め方でしたので
ベストアンサーとさせていただきました。

また、最初の質問の段階で条件がまとまりきれておらず
何度も手間をかけさせてしまい申し訳ございません。
以後、気をつけたいと思います。

ありがとうございました。

お礼日時：2014/02/07 18:28