Excelで上位だけ表示

質問

過去質問にもあったのですが、他の方法はないのかと思いまして質問させてください…。今日昨日はじめたばっかりのド素人なので過去質問読みましたが理解できませんでした；；

まず、表で、
出席番号・名前・点数・順位（1　山田太郎　435　1など）
を表示して、その下に上位３人のデータを表示するというものです。
自分でVLOOKUPを使ってどうにかしようとしたのですが、色々やってみたものの失敗続きでして...
VLOOKUPとLARGEあたりでできますでしょうか？
順位はRANKで表示しています

回答No.4

AloneAgain · ベストアンサー

No.3です。

>とりあえず今扱っているサンプルデータではかぶってません
「たまたま」サンプルでは重複する順位がないだけで、今後のデータによっては同じ順位が出る可能性があるのなら、No.3 の2番目の方法（同じ順位がありうる場合）を使ってくださいね。
サンプルデータで試してみればおわかりになると思いますが、点数が同じで順位が重複する場合、1番目の数式では対応できません。
*
ここで No.3 の訂正です。
No.3 の2番目の方法、作業列の Ｅ2に入れる数式のあとに書いているコメントは、次のように訂正します。
「以下、データのある行まで下にオートフィルでコピー」
　　↓
「以下、データの最終行までオートフィルでコピー」
*
*
>ROW(A1)、COLUMN(A1)はどういう役割をしているのかよくわからない
◆ROW関数・・指定したセルの行番号が返ります。
◆COLUMN関数・・指定したセルの列番号が返ります。

ためしに、どのセルでもいいので =ROW(A1) と入れてみてください。
入力したセルがどこであっても、A1セルの行番号ですから 1 と表示されるはずです。
では、その数式をオートフィルを使って下方向へいくつかコピーしてください。
数式は、=ROW(A2)、=ROW(A3)、=ROW(A4)・・と変わり、表示される値も 2、3、4 ・・になると思います。
A2の行番号は 2、A3の行番号は 3 ・・だからですね。
次に、最初の =ROW(A1) の数式を右方向にフィルコピーしてください。
数式は、=ROW(B1)、=ROW(C1)、=ROW(D1)・・と変わりますが、表示される値はどこまでいっても 1 のままです。
B1 も C1 も行番号は 1 ですから当然です。

次です。どのセルでもいいので =COLUMN(A1) と入れてみてください。
A1セルはもちろん A列ですから、列番号 1 が表示されます。
これを先ほどと同じように下方向へフィルコピーするとどうなるでしょうか？
数式は、=COLUMN(A2)、=COLUMN(A3)・・と変わりますが、A列には変わりないのでどこまでいっても 1 のままです。
では =COLUMN(A1) の数式を右方向にフィルコピーしてください。
数式は =COLUMN(B1)、=COLUMN(C1)・・と変わり、値も 2、3、4 ・・になります。
B列の列番号は 2、C列の列番号は 3 だからです。

要は、=ROW(A1)を下方向にコピーすれば、値が 1、2、3 ・・と変わり、
=COLUMN(A1) を右方向にコピーすれば、値が 1、2、3 ・・ に変わる、ということです。
われながらクドい説明だと思いつつ・・・ここまではよろしいでしょうか？
*
これを利用すれば次のようなことができます。
たとえば A2：A30 のセルに、重複しない数値がランダムに入っているとします。
その中の 1～3番目に大きい数値は LARGE関数を使えば求めることができますよね？
C2：C4 に数式を入れる場合、
C2 に =LARGE($A$2:$A$30,1) と入力すれば 1番大きな数値が表示されます。
これだけならいいのですが、この数式を C4 まで下にコピーしても、数式はまったく変わらず、最後の 1 は 1 のままですから、C3 にも C4 にも 1番大きな数値が表示されてしまいます。
数式をコピーしても 1 は勝手に 2、3・・ とは変わってくれないのです。
では、数式をいったんコピーしたあと、C3 の 1 は 2 に、C4 の 1 は 3 に、いちいち修正しなければいけないのでしょうか？

もうおわかりだと思いますが、そんな必要はありません。
このようなときに ROW関数が威力を発揮するのです。
なんだか、やっと本題に近づいてきたような気がしてきました(^o^)>
C2 に =LARGE($A$2:$A$30,ROW(A1)) と入れ、下にコピーすれば、数式には何も手を加えることなく C3 には 2番目に大きな数値が、C4 には3番目に大きな数値が表示されるというわけです。理由はわかりますよね？
C2：E2 のように横方向に結果を表示させたければ、
C2 に =LARGE($A$2:$A$30,COLUMN(A1)) を入れて、右にコピーすればいいのです。

まとめ：
数式を下方向や右方向にコピーするだけで、数式の中の引数などを 1、2、3 ・・と変えたいときに、ROW関数や COLUMN関数を使います。

ついでに：
>これをするにはVLOOKUPを使うといいと聞いたので、
>てっきり使うものなのかと思ってました
VLOOKUP関数の検索値は必ず表の左はしに（A列から始まる表ならA列に）なければいけません。
ですから、「順位」に絶対に重複がなければ 「順位」を表の左はしに、
重複が出る可能性があれば、No.3 の 2番目の方法で使っている「作業列」を表の左はしにもってくるというように、表のレイアウトを変更さえすれば VLOOKUP関数でもご質問の上位3位を表示させることは可能です。

お礼

再び丁寧な回答ありがとうございます
ROW(A1)、COLUMN(A1)の役割もよくわかりました！手間を省くためのものだったのですねー！
VLOOKUPが、左端からしか検索できないことは知っていたのですが、妙にこだわってしまってそこばかり考えていたのでした；並び替えればできないことはないのですね
ありがとうございました！

回答No.3

AloneAgain

こんばんは。

関数を使う方法ですが・・
>過去質問にもあったのですが、他の方法はないのかと思いまして
同じ方法でしたらすみません。
*
出席番号・名前・点数・順位が A列～D列にあり、
1行目が見出し、データ範囲が A2：D30 だとします。
*
ところで・・
1位から3位までに同じ順位はないのでしょうか（3位が2人いるとか）？

▼1位から3位までに同じ順位はない場合
表示させたい最初のセルに↓
-------------------------------------------
=INDEX($A$2:$D$30,MATCH(SMALL($D$2:$D$30,ROW(A1)),$D$2:$D$30,0),COLUMN(A1))
-------------------------------------------
そのセルが A32 だとすれば、D32 まで（計4列分）右にオートフィルでコピー
そのまま下に3行分コピー
*
ROW(A1)、COLUMN(A1) の A1 はそのまま A1 のままにしておいてください。
列記号、データ範囲は適宜変更してください。
*
▼1位から3位までに同じ順位がありうる場合
方法はいろいろあると思いますが、とりあえず作業列を使う方法です。
E列を作業列とします。
E2に↓（順位が D列だとして）
------------------------
=IF(D2>3,"",D2*1000+A2)
------------------------
以下、データのある行まで下にオートフィルでコピー

表示させたい最初のセルに↓
----------------------------------------
=IF(COUNT($E$2:$E$30)<ROW(A1),"",INDEX($A$2:$D$30,MATCH(SMALL($E$2:$E$30,ROW(A1)),$E$2:$E$30,0),COLUMN(A1)))
----------------------------------------
そのセルが A32 だとすれば、D32 まで（計4列分）右にオートフィルでコピー
そのまま下に適当に（10行分ほど）コピー
10行分数式を入れても、4位以下は表示されません。
*
最初の数式に同じく、ROW(A1)、COLUMN(A1) の A1 はそのまま A1 のままにしておいてください。
列記号、データ範囲は適宜変更してください。
作業列が目障りなら非表示にしてください。

お礼

回答ありがとうございます
同じ順位はたぶん無い…とおもいます。とりあえず今扱っているサンプルデータではかぶってません
とりあえず教えてくださった方法をそのまま試してみたところ、できました！
でも、ROW(A1)、COLUMN(A1)はどういう役割をしているのかよくわからないので、もしよければ教えていただけると嬉しいです

回答No.2

rio_d

こんばんは。

Lookup系関数やMatch関数など、表からキーワードで検索するという検索系関数は、検索するデータが昇順に並んでいなければいけないとか、同じデータが2個以上あると正しい結果を返さないとか、色々と制約があるため、順位などの検索は結構手間がかかります。

すごく卑怯な手を使っていいのであれば、下記のような解答もあるということで。

【条件】
まず元データとして、
　　A列は出席番号, B列は名前, C列は点数, D列は順位を入力する
　　データは1行目～40行目まで入っている
という条件にしますね。

【やり方】
(1)　A1～C40まで、データを入れます。
(2)　D1セルに、下記を入れます。
　　　=RANK($C1,$C$1:$C$40)+A1*0.001
(3)　D1セルの内容を、D2～D40にコピーします。
(4)　このままだと小数点で一杯なので、セルの書式設定を「数値」にし、小数点以下を表示させないようにします。

-----これでランク確定-----

(5)　A42セルに、=SMALL($D$1:$D$40,1)　と入力します。
(6)　A43セルに、=SMALL($D$1:$D$40,2)　と入力します。
(7)　以下、A44～A61セルまで、上記SMALL関数を入れていきます。
　　　ただし、２番目の引数は+1ずつしていってください。
(8)　A42～A61セルの見栄えも小数点で一杯なので、(4)と同様にセルの書式設定を行います。
(9)　B42セルに、=INDEX($B$1:$B$40,MATCH(A42,$D$1:$D$40,0))　と入力します。
(10) B42セルの内容を、B43～B61セルにコピーします。
(11) C42セルに、=INDEX($C$1:$C$40,MATCH(A42,$D$1:$D$40,0))　と入力します。
(12) C42セルの内容を、C43～C61セルにコピーします。

-----一応完成-----

【卑怯な手の解説】
(2)でA1*0.001を足しているのは、同ランクを排除するためです。
ここで同ランクのものがあると、B42セルなどに入れるMATCH関数で、正しい場所を拾ってこなくなるため、わざわざ同ランクを排除する行為が必要となります。
と、ここだけがキモです。他は関数をいじり倒していれば上記のようにしなくても何とかなると思います。

それぞれの関数の意味は、申し訳ないんですがご自身でお調べ下さいませ。

お礼

回答ありがとうございます
LOOKUP系はランクには不向きなんですね。これをするにはVLOOKUPを使うといいと聞いたので、てっきり使うものなのかと思ってました
なんだかとっても奥が深そうでおもしろいですね

回答No.1

ken1low

こんにちは。
オートフィルタを使うのはだめでしょうか？

お礼

早速ありがとうございます
できればオートフィルタじゃなく、ぱっと見が普通の表っぽいほうがいいので...
というか実はオートフィルタもまだ仕組みがよくわかってないのです；すみません；