EXCELで、ある値で検索後、最大値を求める方法について

質問

EXCELで、ある値での最大値を求める方法がありますでしょうか？数日考えておりましたが結論はでませんでした。
どなたかヒントだけでも結構ですのでご教授頂けないでしょうか？

下記の場合、

　　A　　B　　C　　D　　E　　F　　G
1 　　　　　　あ　　い　　　　　　　　
2　　　　1　　2　　2.1　　　　9.6
3　　　　2　　3　　5.4　　　　5.6　　　
4　　　　3　　4　　8.2　　　　5.4　　　
5　　　　4　　5　　1.9　　　　8.2　　　
6　　　　5　　1　　7.8　　　　4.5　　　
7　　　　6　　1　　9.6　　　　 .　　　
8　　　　7　　2　　5.6　　　　 .　　　
9　　　　8　　5　　4.5　　　　 .　　　
10　　　 9　　3　　4.8　　　　　　　
.　　　　.　　.　　 .　　　　　　　　
.　　　　.　　.　　 .　　　　　　　　
.　　　　.　　.　　 .　　　　　　　　
3000以上

Ｂ列の数値をもとにＣ列の中で検索させ、いくつか該当するとき、その右側のＤ列の最大値を求めることができるかどうか。

たとえば、Ｂ列の1の場合、Ｃ列の中で１が２箇所あり、その右側であるＤ列の値は7.8と9.6となります。そのうちの最大値は9.6ですので、Ｆ列の同行に答えとなる値が入ります。そのようなＦ列の関数等は無いでしょうか？

当方でもいろいろ調べ、ＤＭＡＸ関数が使えそうですが、Ｂ列の値が縦（列）となってる場合はうまくいきませんでした。もしＢ列の値が、縦（列）ではなく横（行）である場合は、OFFSET関数などと併せてうまくいきますが、最大２５６列（ＥＸＣＥＬの制限）までしか扱えません。

何か方法があればヒントだけでも結構ですのでよろしくお願いします。

回答No.1

maruru01 · ベストアンサー

こんにちは。maruru01です。

とりあえず、配列数式の方法です。
F2に、

=MAX(IF($C$2:$C$10=B2,$D$2:$D$10))

と入力して、[Ctrl]と[Shift]を押しながら[Enter]を押します。
（数式の両端に「{}」が付きます。）
これを下の行へコピーします。
データ範囲は適宜変更して下さい。

この配列数式だと、データ行数が多いと処理が重くなります。
マシンスペックにもよりますが、3000行は微妙です。

そこで、B・C・D列が数値であるという条件で、作業列を1列使っての別の方法です。
ただし、D列の値がどんなに大きくても100以下という前提です。
作業列をE列とします。
E2に、

=C2*1000+D2

と入力して、下の行へコピーします。
次に、F2に、

=SMALL(E:E,COUNTIF(C:C,"<="&B2))-B2*1000

と入力して、下の行へコピーします。

お礼

目から鱗です。「配列数式」なるものが存在することをはじめて知りました。数日間悩んでいたものですから、涙が出るほどうれしかったです。
本当にありがとうございました。

回答No.2

noname#29107

=MAX((A2=$B$2:$B$100)*($C$2:$C$100))
配列数式にするために、上記式入力後Ctrl+Shift+Enterを押下

{=MAX((A2=$B$2:$B$100)*($C$2:$C$100))}
のように表示されていればOK

お礼

目から鱗です。「配列数式」なるものが存在することをはじめて知りました。数日間悩んでいたものですから、涙が出るほどうれしかったです。
本当にありがとうございました。