エクセルで同率の順位の人が表示されない

Question

得点表を元に順位(ＲＡＮＫ関数)を出し名前順の表にして、
その順位表を元にＶＬＯＯＫＵＰ関数で順位順の表を作ってます。
この場合、順位順の表では同順位の人がいた場合、同順位２人目以降の人の名前が表示されません(#Ｎ／Ａと表示されます)
同位の人も名前と得点をそのまま表示させたいのですが、何か方法は有りませんか？

現在↓

１位　Ａさん　１０点
２位　Ｂさん　　９点
３位　 #N/A　　#N/A
４位　Ｄさん　　８点
　　　　・
　　　　・
となります。それを

１位　Ａさん　１０点
２位　Ｂさん　　９点
３位　Ｃさん　　９点
４位　Ｄさん　　８点

としたいです。
左の順位表記は書きこんでいるだけなので変化しません。もちろん順位に連動して変化してほしいですが、このままでも構いません。

Ａさん・Ｂさんの各セルに＝ＶＬＯＯＫＵＰ(Q5,$U$4:$V$21,2,FALSE)のように設定しています。
ずいぶん前に本を見ながら設定したもので、理屈は忘れてます・・エクセル２０００です。

Wendy02 · Accepted Answer

こんにちは。
Wendy02 です。No.8 に対するコメントです。

他の方の回答を見てはいませんが、どうやら、私のほうは、振り出しです。

その数式をみた限りでは、もう、解答自体はよりも、失礼ですが、これは、基本的な問題ではないかと思いました。直接の解答というよりも、経験者としての回答をさせていただきます。

最初のご質問のようにVLOOKUP　を使うことは、間違いないと思います。しかし、その関数を使用する場合は、検索に必要なもの／一意な（ユニークな）データを左端の格子状の1列目に持ってくるということは大前提です。

（もちろん、掲示板レベルで、他のワザをご披露する方もいるようですが、それはそういうテクニックであり、質問本位・データ本位の解答です。しかし、最初から、そういう作りにするのは、通常はありえないのです。）

順位を左端に以って表示するということ自体は問題ではなく、選び出すデータとして必要だということです。誤解なさらないでください。

つまり、以下の場合は、

　　Q　　R　　　 S
1,名前　　得点　順位　←データの母体（データベース）
2,Aさん　10点　　１位　

　変動しやすいもの、計算値は、右側に置きます。
　特定化する（ユニークなもの）は、左端になります。できない場合は、増加する数字でも可能です。

'---------------------------
21,順位　名前　 得点　　←表示側は、自由です。
22,１位　Aさん 10点
　　・
　　・
　　・
'-------------------------------

私の前回の式で、VLOOKUPを使い、ユニークなものとして、「名前」を使っていたので、一部の式が完全に使えません。（INDEX関数とMATCH関数を使ってだせますが、最初からは作りません。）

次に、また基本的なことですが、

＞=IF(RANK(D12,$S$2:$S$18)=COUNTIF($S$2:$S$18,">=" &D12),RANK(D12,$S$2:$S$18),COUNTIF($S$2:$S$18,">" & D12)+1)
＞D12の部分が変わります。→F12,H12,J12・・J38,L38

>D12の部分が変わります。→F12,H12,J12・・J38,L38

　=RANK(数値,範囲)

$S$2:$S$18　の範囲の数値をランキングで出すのが基本です。それを別のところのデータを使うと、万が一には、そのランキングはめちゃめちゃにならないかと思います。

　=RANK(S2,$S$2:$S$18)

あくまでも、範囲の中の数値である必要があると思います。以下の式は、ROW のカッコの中の範囲を変えればよいのですが。

=IF(RANK(S2,$S$2:$S$18)=COUNTIF($S$2:$S$18,">=" &S2),RANK(S2,$S$2:$S$18),COUNTIF($S$2:$S$18,">" & S2)+1)

もしも、元データが点在する場合でも、表に作る場合は、必ず規則性があるはずです。それを、OFFSET 関数やINDIRECT関数によって、ひとつにまとめ合わせることが可能なはずです。

例:
点数が点在している場合のまとめ方
（ただし、実際のデータに当てはめていません）
=INDIRECT(ADDRESS(18+INT(ROW(A1)/6)*10,4+MOD((ROW(A1)-1)*2,8)))

以上が、その元のならびに関するコメントです。
今回、私の回答自体は見送りで構わないのですが、基本的な作りは大事にしたほうがよいですね。

＊＊＊＊＊＊＊

エラー自体については、以下のようにすればよいはずです。ROW($A$2:$A$18)というのは、行データですから、それは、行があっていなくては、取り出せません。

順位　／Q22 ～で、

=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($Q$2:$Q$18+ROW($A$2:$A$18)/100,,),ROW(A1)),1)*100-1,1)

名前 　／R22～

=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($Q$2:$Q$18+ROW($A$2:$A$18)/100,,),ROW(B1)),1)*100-1,2)

ここは、VLOOKUPでも可
点数　／S22 ～
=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($Q$2:$Q$18+ROW($A$2:$A$18)/100,,),ROW(C1)),1)*100-1,3)

これは、値コピーして、並べ替えすれば、式はいらないはずですよね。私自身は、あまり難しい式などは使わないのです。手作業で出来る部分は、そのまま手作業で作ります。

shiotan99 · Answer

No.7です。

補足を見ましたが、おっしゃっている意味がわかりません。
要は、元の表がQ列（順位）・R列（名前）・S列（得点）なんですよね。
No.7の補足では「18人分」、No.8の補足では「17人分」となっていますが、どちらなんでしょうか？
一応、17人分と考えて、1行目が見出し、2行目から18行目までデータが入っているとします。

Q2に

=RANK(S2,$S$2:$S$18)

と入れて、Q18までフィルコピー

T2に

=Q2+ROW()/100

と入れて、T18までフィルコピー

> Ｄ列の設定ははＴ列２２以下にしたのですが、

どういうことでしょうか？

> ３行ほど余分に設定しないとランク表、
> 上の３行分が＃＃＃＃状態になります。
> その３行を消しても表示してくれるので現状でもいいのですが。。

この↑意味もさっぱりわかりません。
こちらで検証しましたが、何の問題もありませんでした。

ランク表がどの列の何行目かに関係なく
順位を表示させる列の先頭行のセルに、

=INDEX(Q$2:Q$18,MATCH(SMALL($T$2:$T$18,ROW(A1)),$T$2:$T$18,0))

と入れて、右に計3列分フィルコピー
そのまま（3列が選択された状態で）下に計17行分フィルコピー

元の表がQ列・R列・S列で、データが2行目から18行目まで。
T列の2行目から18行目までに、上記2番目の数式が入っているのなら、
上の数式は一切（！）変更しないでください。そのままコピーして貼り付けてください。
ROW(A1) もそのまま ROW(A1)です。（←何行目かに関係なく）

それでもうまくいかないのならもっと具体的に補足してください。
・元の表はQ2：S18でまちがいないですか？
・ランク表は、どの列の何行目から表示させたいのですか？
・実際に入力した数式を、そのままコピーして提示してください。

Wendy02 · Answer

こんにちは。

#2 のWendy02 です。

＞１位　Ａさん　１０点
＞２位　Ｃさん　　９点
＞２位　Ｄさん　　９点
＞４位　Ｂさん　　８点
＞と言う表記になるようには出来ますでしょうか？

#2 の表を再び使います。

元のデータが以下のような状態であるとします。

例:
元データ
　G　　　H　　　I　
名前　　点数　　順位
Dさん　　8　　　5
Fさん　　9　　　2
Eさん　　4　　　8
Aさん　　10　　　1
Cさん　　9　　　3
Uさん　　5　　　7
Lさん　　6　　　6
Bさん　　9　　　4
Mさん　　4　　　9

'------------

ランク表

　A　　　B　　　 C
順位　　名前　　点数
　１位　　Aさん　　10
　２位　　Fさん　　9
　　・
　　・
'-----------------

I2:～
=IF(RANK(H2,$H$2:$H$10)=COUNTIF($H$2:$H$10,">=" &H2),RANK(H2,$H$2:$H$10),COUNTIF($H$2:$H$10,">" & H2)+1)


B2:～
=INDEX($G$2:$I$10,MOD(SMALL(INDEX($I$2:$I$10+ROW($A$2:$A$10)/100,,),ROW(A1)),1)*100-1,1)

解説:

ROW(A1)),1)*100-1　の -1 は、タイトル行を差し引いています。
SMALLの中で、INDEX を使うのは、データを配列として扱うからです。そのままですと、+ROW($A$2:$A$10)　という式は、配列にさせないと、不活性のままなのです。

重み付けテクニック:

式の中で、/100　と100で割るのは、重み付けというワザ名で知られた方法です。元のデータと行データをいっしょに扱うときに、使う方法です。元のデータと行数をいっしょにして、SMALL で並べ替えをして、後で、分離させて、小数点以下を取り出し、×100 にしています。100行以内だから、÷　100 ですが、100を超えるデータの時は、÷　500　とか÷1000 とかして、データと交じり合わないようにするのがコツです。

A2:～
=VLOOKUP(B2,$G$1:$I$10,3,0)

C2:～
=VLOOKUP(B2,$G$1:$I$10,2,0)

shiotan99 · Answer

こんにちは～

> 同点の場合、
> １位　Ａさん　１０点
> ２位　Ｃさん　　９点
> ２位　Ｄさん　　９点
> ４位　Ｂさん　　８点
> と言う表記になるようには出来ますでしょうか？

別解です。
参照している元表のレイアウトがわかりませんので、A列が「順位」、B列が「名前」、C列が「点数」だとします（元の表です）。

A1に

=RANK(C1,$C$1:$C$20)

と入れて、下にフィルコピー

D1に

=A1+ROW()/100

と入れて、下にフィルコピー

ランク順の表を、F列からH列だとして、

F1に、

=INDEX(A$1:A$20,MATCH(SMALL($D$1:$D$20,ROW(A1)),$D$1:$D$20,0))

と入れ、右にH1までフィルコピー（←←右方向にコピーです！）
そのまま（F1からH1が選択された状態で）下に人数分フィルコピー（F列からH列まで同時に数式をコピー）

=INDEX(A$1:A$20・・、のように A$1:A$20 を行だけ絶対参照にしておけば、列ごとに別々の数式を入れる必要はありません。
上の数式を右に下にコピーするだけです。

順位を「1位」、点数を「10点」のようにする場合は、
「セルの書式設定」のユーザー定義で、F列の表示形式を 0位、H列の表示形式を 0点としてください。

imogasi · Answer

#3です。＃３の例のＢ列は参考までにあげたものです。
Ｃ列のようにすれば、順位という意味で各人固有の番号を振れるといったまでです。部分的な参考にしてほしい解答でした。
そもそも質問のしたいことがよく分からない。
ＶＬＯＯＫＵＰ表の作り方で、今度はうまくいかないかもしれない。
＞得点表を元に順位(ＲＡＮＫ関数)を出し
得点で順位数字を出しでしょう。その場合氏名は同行にあるのでしょう。
Ａさん　７点　３（順位）
Ｂさん　８　　２
Ｃさん　７　　３
Ｄさん　６　　４
Ｅさん　９　　１
＞名前順の表にして
「順」とは名前文字コード順に並べるという意味ですが、別の意味で使ってませんか。これが質問が分かりにくい原因です。
この段階で何（の項目）をＶＬＯＯＫＵＰでとってくるのでしょうか？
順位の順に（ソートをつかわず、関数で）並べ替えたいということですか。
a15501
b23301
c31101
d15502
e23302
f31102
の場合
Ａ１１に
=OFFSET($A$1,MATCH(SMALL($D$1:$D$6,ROW(A11)-10),$D$1:$D$6,0)-1,0)
と入れてＡ１６まで複写すると
Ａ列　　Ｂ列（Ｂ列式は下記後述）
c3
f3
b2
e2
a1
d1

と並びます。
点数はＢ１１に
=OFFSET($A$1,MATCH(SMALL($D$1:$D$6,ROW(B11)-10),$D$1:$D$6,0)-1,1)
で出ます。Ｂ１２：Ｂ１６に式を複写する。（結果上記のとおり）
こういうことでよいでしょうか。

shkwta · Answer

No.4です。
何度も訂正して申し訳ありません。

（誤）B1に　=C1*100000*ROW(C1)
（正）B1に　=C1*100000+ROW(C1)

shkwta · Answer

No.1です。
＞また、順位順(上から１位・名前、２位・名前、・・)に名前が並ぶ列が無いのですが・・


申し訳ありません。まちがえました。
（誤）B1に　=C1*100000*ROW(C1)
（正）B1に　=C1+100000*ROW(C1)

imogasi · Answer

根本的にＶＬＯＯＫＵＰ関数で参照する表の一番左列の索引値は２つ同じものを入れても、最初のものしか引いてきません。そのことと関連してませんか。
ＲＡＮＫ関数が同点を同一順位で返す特徴があるためです。
ですからそのからを破らないといけません。
Ａ１：Ａ６に
15501
23301
31101
15502
23302
31102
とあるとき
Ｂ列には普通のランク関数ですが=RANK(A1,$A$1:$A$6)
Ｃ列に=RANK(A1,$A$1:$A$6)& TEXT(COUNTIF($A$1:A1,A1),"00")
と入れてＣ６まで複写します。
こういう数字を使います。
同一点数者を９９人以内と仮定できる場合です。

Wendy02 · Answer

こんばんは。

ご質問のデータ位置などが、ほとんど分かりませんが、こういうことでしょうか？

元のデータが以下のような状態であるとします。

例:
　G　　　H　　　I　
名前　　点数　　順位
Dさん　　8　　　5
Fさん　　9　　　2
Eさん　　4　　　8
Aさん　　10　　　1
Cさん　　9　　　3
Uさん　　5　　　7
Lさん　　6　　　6
Bさん　　9　　　4
Mさん　　4　　　9


別に、降順に並び換える必要はありません。

I2: ～
順位の出し方
=IF(RANK(H2,$H$2:$H$10)=COUNTIF($H$2:$H$10,">=" &H2),RANK(H2,$H$2:$H$10),COUNTIF($H$2:$H$10,">" & H2)+COUNTIF($H$2:H2,H2))

ランク表

　A　　　B　　　 C
順位　　名前　　点数
　１位　　Aさん　　10
　２位　　Fさん　　9
　　・
　　・


順位のA2:～

なぜか、ご質問では、全角を使っていたようなので、
書式－セル－ユーザー書式

[DBNum3]#,##0"位"

で、A2～下方に 
1
2
3
4
5
.

と入れれば、中身は、半角数値で、表示は、全角＋「位」がつきます。

B2:～
=INDEX($G$2:$H$10,MATCH(A2,$I$2:$I$10,0),1)

C2:～
=INDEX($G$2:$H$10,MATCH($A2,$I$2:$I$10,0),2)

とすればよいと思います。範囲は、適宜変えてください。

ただ、同点で、順位に差がつくのは、ちょっと疑問に思いました。

shkwta · Answer

こういう方法はどうでしょうか。

まず、得点表側です。
たとえば、D1からD20に人名、E1からE20に得点が入力されているとします。
C1に　=RANK(E1,$E$1:$E$20,0)
C2以下コピー。これで、まず順位を求めます。

B1に　=C1*100000*ROW(C1)
B2以下コピー。これで、異なる行に差をつけます。

A1に　=RANK(B1,$B$1:$B$20,1)
A2以下コピー。これは昇順の順位です。これで、重複しない順位が求められます。

次に順位表です。
どこかの列に1から20まで通し番号を打ちます。たとえば、G1～G20とします。

H1に　=VLOOKUP(G1,$A$1:$E$20,3,FALSE)&"位"
H2以下コピー。これで重複のある順位を取り出せます。
J1に　=VLOOKUP(G1,$A$1:$E$20,4,FALSE)
J2以下コピー。これで人名を取り出せます。
K1に　=VLOOKUP(G1,$A$1:$E$20,5,FALSE)&"点"
K2以下コピー。これで得点を取り出せます。

エクセルで同率の順位の人が表示されない

こんにちは。

No.7です。

この回答への補足

こんにちは。

この回答への補足

こんにちは～

この回答への補足

#3です。

この回答への補足

No.4です。

この回答への補足

No.1です。

根本的にＶＬＯＯＫＵＰ関数で参照する表の一番左列の索引値は２つ同じものを入れても、最初のものしか引いてきません。

この回答への補足

こんばんは。

この回答への補足

こういう方法はどうでしょうか。

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング