【Excel】 INDEX ，MATCH でいいのか。該当が複数ある場合

質問

項目の検索で行き詰まっています。

A1 から A30 に名前
B1 から B30 に得点
が入力されています。
B31 に最高点を表示するのは、MAX 関数でできました。

B32 にその最高点をとった人物が誰かを表示するのに
=INDEX($A$1:$A$30,MATCH($B$31,$B$1:$B$30),1)
と入力したら、
該当者が１人しかいない時は問題ないのですが、複数いる場合でも１人しか表示されません。
該当が複数ある場合の出し方は他に方法があるのでしょうか？

どなたか力を貸してください。
よろしくお願いします。

回答No.3

noname#52504 · ベストアンサー

誰が最高点なのか確かめるだけであれば、
#１さんのご提案されたオートフィルタが恐らく最短ですね。

ただ、ご質問の例をみますと、
やはり特定のセルに値を返す必要があるのではないかとも思われます。
その場合、#2さんがご提案されたように作業列を使って引くのが順当な手順かと思います。

さて。
たまたまつい先日似た質問にお答えしたので、
正直あまりお奨めはしないのですが、「一発関数回答」を。

B32セル：
　=IF(ROW()-ROW(B$31)>COUNTIF($B$1:$B$30,B$31),"",INDEX($A$1:$A$30,MATCH(LARGE(($B$1:$B$30=B$31)/ROW($B$1:$B$30),ROW()-ROW(B$31)),1/ROW($B$1:$B$30),0)))

を配列数式として入力し下方に十分な数だけフィル。(Excel2003で動作確認済)
※通常の数式は、数式を入力した後Enterキーで確定しますが、
　これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

数式中に、B$31，$A$1:$A$30，$B$1:$B$30の３種類の参照が複数回登場しますが、
いずれも質問文の数式と同様のニュアンスで考えていただいて結構です。
位置が変わる場合はそれぞれ置き換えてください。
（ただし、最高点のセルの直下に、縦に並べて名前を表示することを想定しています)

数式の意味内容については、やや手前味噌の感もありますが、
先日した回答のURLを挙げておきますのでご参考ください。
http://oshiete1.goo.ne.jp/qa3328660.html
（このケースでは行列(縦横)が逆になっています）

お礼

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

ご回答の式をそのままコピーして貼り付けたら一発で表示されました。
配列数式はまだよくわからないのでこれから勉強します。
当面の問題は解決しました。
重ねてありがとうございました。

回答No.5

imogasi

＃４です。
＞どうかじらさずに、ストレート・・
邪推しないでください。再々同類質問があるものだからヒントだけにしたもの。
こういうタイプ別に、どういうものがあるかというのも参考になると思ったから挙げた。
GOOGLEでいま「imogasi方式」で照会すると、５４６件あるということらしい。
（また良ければOKWAVEででも照会してください。）
最近の質問には「照会してください」が多いが、実際内容を回答したもので過去のものは出にくくなっているが
http://dospara.okwave.jp/qa3292030.html
http://virus.okwave.jp/qa3295728.html
など
ーー
要は
（１）条件に該当する（この質問では最大値）に当たる値が入っている行に、上の行から連番を振る（このために作業列を使う）
理由があって、第１行目に行挿入する.
作業列をE列とするとE2に
=IF(B2=$B$32,MAX($E$1:E1)+1,"")と入れてE3１まで式を複写する。
（２）その１，２，３・・の番号を、行番号の３３，３４，３５・・
とを関連付けて、３３行には、作業列がrow()-32（すなわち１）の
行をMATCH関数で探して、INDEX関数で採ってくる。
１行行挿入したので、３３行に
=INDEX($A$2:$A$31,MATCH(ROW()-32,$E$2:$E$31,0),1)
これを、B３２と同じ値の行数だけ式を複写する。
実際は余分行に式を複写しても、#N/Aを出さないように、IF関数を用いて、B32の値のB2:B31までのCOUNTIF数より大なら空白というのを、式に組み込むが、＃３のご回答にも出ているので略。

回答No.4

imogasi

これはむつかしいのです。
これが簡単にできれば、エクセルの表での抜き出し問題も、もう少し簡単になるのですが。
（１）作業列を使う方式
例えば自称「imogasi方式」でWEB照会してくれれば、過去の類似問題がたくさん出ます。そこには他の方の（３）の回答実例なども出ます。
（２）配列数式
（３）複雑な式
（４）VBAでユーザー関数（同じ値でｎ番目の該当行番号を返す関数）
を作る
など過去に見たり、回答したりしました。
理解しやすく、納得しやすいのは（１）でしょう。

お礼

ご回答ありがとうございます。
私の方針として質問を丸投げせず試行錯誤した結果、ここに質問をしようと心がけています。
今回の回答ではWEBが検索できませんでした。
どうかじらさずに、ストレートに回答を記載するか。URLを貼り付けてください。

回答No.2

kaisendon

オートフィルタに一票　(o‥o)/

なお、関数で
> 該当が複数ある場合の出し方
は、いろいろあると思いますが一例です。

複数人の名前をどこに表示したらよいのか分かりませんので、
例えば全員が同点ならば、（全員が最高得点になるので）
B32からB61までひとつのセルに一人ずつ表示させるようにしました(^^ゞ

B31には　=MAX(B1:B30)　←この式が入っているとします。

どこでもいいですが、仮にC列、D列を作業列にして、
C1に　=IF(B1=$B$31,B1+ROW()*0.01,"")　
（C30までフィルコピー）
D1に　=IF(COUNT($C$1:$C$30)<ROW(D1),"",SMALL($C$1:$C$30,ROW(D1)))
（D30までフィルコピー）

B32に　=IF(D1="","",INDEX($A$1:$A$30,MATCH(SMALL($D$1:$D$30,ROW(B1)),$C$1:$C$30,0)))
（B61までフィルコピー）

これでご希望の結果が返るかと思います。
（但し、B列（得点の入力欄）に何も入力が無い場合は全員の名前が表示されます）

ご覧のように、ご希望の事を関数でやるのは結構めんどいです(^^ゞ
なので、やっぱり「オートフィルタ」をお勧めしますｗ

蛇足：
> B32 にその最高点をとった人物が誰かを表示するのに
> =INDEX($A$1:$A$30,MATCH($B$31,$B$1:$B$30),1)
> と入力したら、
> 該当者が１人しかいない時は問題ないのですが、
↑この式は、
=INDEX($A$1:$A$30,MATCH($B$31,$B$1:$B$30,0))
↑こうした方がいいのではないかと(^^ゞ

お礼

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

NO1の方への回答にも書きましたが、実際の作業は、人数が600名、項目が10項目以上になるため、あまり作業列を増やしたくないというのが本音です。それでも人数や項目が少ないときは使えそうなのでストックしておきます。
また、最高点該当者の出し方の関数もご指摘もありがとうございました。

回答No.1

papayuka

全員同点ならば、最大で30名になるって事ですよね？

C1 に =IF(RANK(B1,$B$1:$B$30)=1,"☆","")
のような感じで関数を入れて C30 までコピーすれば、誰が1位で最高得点だと解ります。
必要に応じてこれをオートフィルタで抽出すれば良いと思いますが、如何でしょう？

お礼

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

papayukaさんの方法や条件付書式設定後、該当をコピペしたりしました。
実際の作業は、人数が約600名、得点項目が10項目以上となるため手間や間違いを防ぎたいのでここに質問したしだいです。

それでも一番の回答ありがとうございました。
これｋらもよろしくお願いします。