Excel 特定条件でのMAX値を抽出

質問

こんにちは。

エクセルである特定のMAX値を抽出したいのですがうまく抽出出来ません。具体的には、下のようになっています

A列　　　　　　　B列
１　　　　　　　２
７　　　　　　　３
５　　　　　　　６
４　　　　　　　６
３　　　　　　　６
７　　　　　　　６

上のような並びがあるとして、B列の最大値を満たしている数値（この場合は複数の６）に関して、その隣のA列の数値の中で、最大になる数値（この場合は７）を抽出するような関数を作りたいのです。
そのまま読めばいいのかもしれませんが、エクセルを学びたいという気持ちもありますので、どうか分かる方は教えて下さい。

回答No.5

MackyNo1 · ベストアンサー

＞そのままMINに変えるだけではうまくいかず、０が抽出されました。
＞ちなみにA列の中にはマイナスの値もあるのですがこれは関係ありますか？

=MAX(INDEX((MAX($B$1:$B$10)=$B$1:$B$10)*$A$1:$A$10,))

上記の式の意味は、「(MAX($B$1:$B$10)=$B$1:$B$10)」の部分は、B列のセルがB1:B10セルの中の最大値と等しい場合には「TRUE」異なる場合には「FALSE」が返ります。
これにA列の対応するセルを掛け算するとTRUEの部分は「1」「FALSE」は「0」として計算されるので、最大値の行はA列の値、それ以外の行は0が返ることになります。
この配列の中からMAX関数で最大値を検索していることになります。
ちなみにINDEX関数の部分は、その配列を範囲に変換している（Ctrl+Shift+Enterで確定しなくても演算できるようにしている）数式です。

同様に、最小値を検索する場合は、B列が最小値以外の場合は「0」、最小値の場合はA列の値となる配列が得られます。

このとき注意することは、実際のA列の最小値が正の値である場合は、B列が最小値以外の場合の「0」の値が配列の中で最小となるためご質問のような状況が発生します（マイナスの値が該当データの場合は問題ありません）。

同じことは最大値の場合にも言えることで、例えばA列の最大値がマイナスの値の場合は、最大値が「0」となることになります。

これを避けるには、NO3の回答のように、IF関数でB列の最大値あるいは最小値でない場合は「０」ではなく「FALSE」を返る数式にして（この場合は演算していないのでFALSEは数字として認識されない）Ctrl+Shift+Enterで確定するのが簡単ですが、この操作なしで計算したいなら以下のような数式にする必要があります。

=MAX(INDEX(-(MAX($B$1:$B$10)<>$B$1:$B$10)*10^10+$A$1:$A$10,))

=MIN(INDEX((MIN($B$1:$B$10)<>$B$1:$B$10)*10^10+$A$1:$A$10,))

ちなみに、数式がどのような配列を返しているのか調べたいときは、数式バーの上で計算したい数式部分（例えば「(MAX($B$1:$B$10)=$B$1:$B$10)」の部分）をドラッグして選択してF9キーを押してみてください（解除はEscキー）。

お礼

大変詳しく丁寧な説明をありがとうございます！本当に助かりました。おかげでデータの解析が順調になりました！
数式の確認方法も了解しました。今後の参考にさせていただきます。

回答No.4

keithin

＞エクセルを学びたいという気持ちもありますので、どうか分かる方は教えて下さい

ごくふつーに、次のように計算します。

Ｃ１に
=IF(B1=MIN(B:B),A1,"")
と記入、Ｃ６までコピー。

=MIN(C:C)
と記入し、欲しかった答えを得る。

「学びたい」のとは違うただのゲテモノのアプローチでは、次のようにします。

=MIN((B1:B7<>"")*(B1:B7=MIN(B1:B6))*A1:A7)
と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力する。

＃思ったのならどうしてやってみないのですか？

お礼

回答ありがとうございます。
ご指摘はごもっともです。実は回答された方のすべての方法でMAXのみをMINに変えてやってみたんですが結果はすべて０だったので頭を抱えていました。大変申し訳ないのですが、今回keithin様にご提示頂いた方法も数値が0で抽出されてしまいます。。。

ちなみにデータ数は2000近くあり、最小値で抽出されるべき値はマイナスです。
良かったら回答お願いします。手間をかけさせてしまってすみません。

回答No.3

tom04

こんばんは！
すでに回答は出ていますので、参考程度で・・・

表示したいセルに
=MAX(IF(B1:B100=MAX(B1:B100),A1:A100))

配列数式になりますので、Shift+Ctrl+Enterキーで確定します。

この画面からコピー＆ペーストする場合はセルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定です。
数式の前後に｛　｝マークが入り配列数式になります。

とりあえず100行まで対応できる数式です。m(_ _)m

補足

ありがとうございます。
ちなみに、これの最小値を抽出するバージョン（B列最小値の中からA列最小値を見つける）の関数はどのようにつくればいいですか？
そのままMINに変えるだけなんでしょうか？

回答No.2

MackyNo1

以下のような数式でB列の最大値のセルの中でA列の最大値を求めることができます。

=MAX(INDEX((MAX($B$1:$B$10)=$B$1:$B$10)*$A$1:$A$10,))

補足

ありがとうございます。
今のところこちらを張り付けまして順調です。ちなみに、これの最小値を抽出するバージョン（B列最小値の中からA列最小値を見つける）の関数はどのようにつくればいいですか？
そのままMINに変えるだけではうまくいかず、０が抽出されました。
ちなみにA列の中にはマイナスの値もあるのですがこれは関係ありますか？

回答No.1

keithin

＞エクセルを学びたいという気持ちもありますので、どうか分かる方は教えて下さい

ごくふつーに、次のように計算します。

Ｃ１に
=IF(B1=MAX(B:B),A1,"")
と記入、Ｃ６までコピー。

=MAX(C:C)
と記入し、欲しかった答えを得る。

「学びたい」のとは違うただのゲテモノのアプローチでは、次のようにします。

=MAX((B1:B6=MAX(B1:B6))*A1:A6)
と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力する。

補足

ありがとうございます！上の質問者の方にも捕捉しましたが、最小値の場合のやり方もぜひ教えていただけると助かります。