エクセルでスコアランキング表作成について

質問

エクセルに関する質問です。
エクセルでスコアランキングを作成したいと考えております。
ゲームのスコアを入力していき、自動で順位順に並び替え、
パワーポイントにリンクで自動表示という流れを作りたいと考えております。

(1)入力sheet
A列：順位 RANK関数(D列のスコアで順位) B列:参加者NO(固定001～) C列:ニックネーム(任意) D列：スコア 3-4桁の数値

(2)順位表sheet
A列：順位 1～50 B列～D列→A列と入力sheetのA列を検索値として、参加者NO、ニックネーム、スコアを順位順に表示

という二つのsheetをつくっております。
スコアが同値で同じ順位が複数発生した際に、順位表sheetでN/Aになってしまいます。

同じスコアだった場合に、順位表に ４位、5位、5位、7位というように表示し、
同順位の人を表示する方法(vlookupですと、上の行しか読み込まれず・・・)が
ありましたらご教示いただけないでしょうか？

御確認の程、よろしくお願いします。

回答No.7

imogasi

#3他です。別方法を考えたので。
当大会での記録点成績（レコード）として何番目の記録かというのを出す課題だとして、
データ例　Ａ列　Ｂ列　Ｃ列
Ａ列は元データ、Ｂ列は「配列数式による、重複なしデータ」、Ｃは求める順位。
数 重複無し数 ランク
10 10 8
12 12 6
15 15 4
20 20 1
18 18 2
20 - 1 　＜Ｂ列の―は空白セルの説明上の印
8 8 9
15 - 4
18 - 2
11 11 7
17 17 3
14 14 5
6 6 10
20 - 1
Ｂ２の式は、Ｂ２：Ｂ１５を範囲指定しておいて、Ｂ２をアクチブにしておいて、
=IF(COUNTIF($A$2:A2,A2:A15)=1,A2:A15,"")
と式を入れて、Ｓｈｉｔｆ＋Ｃｔｒｌ＋Ｅｎｔｅｒの３つのキーを同時押しする（配列数式）
Ｃ２の式は
=RANK(A2,$B$2:$B$15)
下方向に式を複写。
Ｂ列は中間データ列として必要で、無くせてないが。

回答No.6

nishi6

#1です。列の挿入は質問の主旨に合っていなかったですかね。入力シートの順位をコピーすれば済むと思うんですが。

今回は、入力シートに計算列を２列設定して、順位表シートは算式をセットするのみとしてみました。順位表シートでは何もしません。説明は添付図のようなカタチを想定しています。

入力シートのE列はF列の順位（表示用に抽出する順位）
　E2:=IF(A2<>"",RANK(F2,$F:$F),"")
入力シートのF列は質問の表示したい順位計算用の値
　F2:=IF(A2<>"",(D2&"."&RIGHT("00"&(100-ROW()),2))*1,"")
これを下方向に多めにコピーしておきます。同じ順位なら、上にあるデータを先に表示します。

順位表シートの
　A2：=IFERROR(INDEX(入力!$A:$D,MATCH(ROW()-1,入力!$E:$E,0),1),"")
　B2：=IFERROR(INDEX(入力!$A:$D,MATCH(ROW()-1,入力!$E:$E,0),2),"")
　C2：=IFERROR(INDEX(入力!$A:$D,MATCH(ROW()-1,入力!$E:$E,0),3),"")
　D2：=IFERROR(INDEX(入力!$A:$D,MATCH(ROW()-1,入力!$E:$E,0),4),"")
もしくは、
A列から始まっていれば、
　A2：=IFERROR(INDEX(入力!$A:$D,MATCH(ROW()-1,入力!$E:$E,0),column()),"")
として全体にコピーします。
いずれも多めにコピーしていた方がいいでしょう。

入力シートの順位は。例えば、A2セル、
　A2:=IFERROR(RANK(D2,$D:$D),"")
のように、エラーが出ないようにしてください。（多目に算式をセットしておいてもいいです）

当然、VBAでも対応できますが、初期化、データ範囲の設定、エラー対応、実行して更新するタイミングなどを考えると、多目に算式をセットしておき、入力シートに入力すれば、順位表シートに結果が自動的に表示できる方がいいでしょう。

回答No.5

chayamati

＞同順位の人を表示する方法(vlookupですと、上の行しか読み込まれず・・・)
★そうですねVlookupではダメですね
　A1~D51をテーブルとして設定すると、各列の1行目に▼マークが付き
　　並び替え、フィルター、抽出が可能です
　　また任意のセルに式を設定すると同一セルの他の行にも適用されます
　入力シートに、データを入力したのち、順位列に並び換えで如何ですか
　セルA2の式は　=RANK.EQ([@スコア],D:D)
順位表シートは不要です

お礼

chayamati様
ありがとうございます。
テーブル方法を使用する方法というのもあるのですね。
並び替えを行わず、リアルタイムに順位が出ればと思っております。
テーブルという方法も参考にさせていただき考えてみたいと思います。
関数のみと考えてましたが、VBA、テーブル等いろいろな方法がありそうですね。
大変勉強になります。ありがとうございます。

回答No.4

imogasi

#3です。回答において、重複のないデータ（＝点数）を出した後、降順で並べ替えをするステップの説明が漏れていました。例として挙げたものはソート後の状態ですが、それに至るには、１ステップ並べ替え操作が必要でした。
すみません。

回答No.3

imogasi

#2です
操作と関数でやってみました。作業列を使いますが。
データ例　Ａ１：Ｂ１５
Ａ列　Ｂ列＝下記関数の結果
数 本質問順位
10 8
12 6
15 4
20 1
18 2
20 1
8 9
15 4
18 2
11 7
17 3
14 5
6 10
20 1
Ａ列データをもとに、データーフィルター重複するレコードは無視するで
Ｄ列に出します。
Ｄ列にはＤ１：Ｄ１１に
ユニークな数
20
18
17
15
14
12
11
10
8
6
Ｂ２に関数式　=MATCH(A2,$D$2:$D$11,0)　と入れて下方向に式を複写します。
結果は上記のデータ例のＢ列の通り。

回答No.2

imogasi

この質問者は、暗黙の裡に、関数による回答を求めているのだろう。しかし、
エクセルの順位付けの関数は３あって
RANK.EQとRANKとRANK.AVG
です。
下記仮の簡単データで（シートで）やってみると
Ｄ列のデータを仮に
データ（ソート後から始める。ソート後でなくても結果は同じ）
12
15
34
123
134
134
134
として各関数でランクの数字を出すと下記のようになる。
Ａ－Ｄ列のデータです。ずれて見にくいですが、
=RANK.EQ(D2,$D$2:$D$8) =RANK(D2,$D$2:$D$8) =RANK.AVG(D2,$D$2:$D$8) データ
7 7 7 12
6 6 6 15
5 5 5 34
4 4 4 123
1 1 2 134
1 1 2 134
1 1 2 134
ーー
このどの関数でも、質問者は順位付け数字に、満足しないのでしょう。
だから下記のようにＶＢＡでソートしてＨ列に
データ
134
134
134
123
34
15
12
として、それに対し
標準モジュールに下記をコピペして実行。
Sub test01()
Dim dt As Variant
dt = Range("d1:D8")
Range("H1:H8") = dt
Range("H2:H8").Sort key1:=Range("H1"), Order1:=xlDescending
'----
k = 1
Cells(2, "j") = 1
For i = 3 To 8
If Cells(i, "H") = Cells(i - 1, "H") Then
Cells(i, "j") = k
Else
k = k + 1
Cells(i, "j") = k
End If
Next
End Sub
を実行すると。
H列　　Ｊ列
データ
134 1
134 1
134 1
123 2
34 3
15 4
12 5
となる。
ーーーー
データ
134 1
134 1
134 1
123 2
34 3
15 4
12 5
こういうのを望んでいるのではないか。
ーー
これをすべて関数（の組み合わせ）で行う（他の）回答が出るのを待ってください。
私ならユーザー関数で処理するとか考えるが、中間処理列を使ってしまう。
ーー
世の中にはこういう順位付け法もニーズがあるように思うが、単一関数では
出来ない（関数を組み合わせることになるが、複雑になる見込み）と思う。ましてＶＬＯＯＫＵＰ関数の利用はピント外れでしょう。

お礼

imogasi様
詳しい解説ありがとうございます。
VBAを使用する方法は、
まだ知識が追い付いておらず、恐縮ですが、
いただいた方法を使えば、さらにいろいろな複雑なことが出来そうですね。
いただいたものを参考に勉強させていただきます！ありがとうございます

回答No.1

nishi6

質問を読むと、添付図のB～E列のようになっていると思われます。
これに、左に補助列を挿入したらどうでしょうか。Vlookupの判定は、挿入したA列で行います。(添付図は挿入後です)

添付図では、A2セルは「=B2&"/"&C2」のようにしています。「/」はB2、C2セルを結合した時、同じキーにならないようするためです。
使う時には、A列を非表示にしておけば気にならないでしょう。