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

質問

得点表を元に順位(ＲＡＮＫ関数)を出し名前順の表にして、
その順位表を元にＶＬＯＯＫＵＰ関数で順位順の表を作ってます。
この場合、順位順の表では同順位の人がいた場合、同順位２人目以降の人の名前が表示されません(#Ｎ／Ａと表示されます)
同位の人も名前と得点をそのまま表示させたいのですが、何か方法は有りませんか？

現在↓

１位　Ａさん　１０点
２位　Ｂさん　　９点
３位　 #N/A　　#N/A
４位　Ｄさん　　８点
　　　　・
　　　　・
となります。それを

１位　Ａさん　１０点
２位　Ｂさん　　９点
３位　Ｃさん　　９点
４位　Ｄさん　　８点

としたいです。
左の順位表記は書きこんでいるだけなので変化しません。もちろん順位に連動して変化してほしいですが、このままでも構いません。

Ａさん・Ｂさんの各セルに＝ＶＬＯＯＫＵＰ(Q5,$U$4:$V$21,2,FALSE)のように設定しています。
ずいぶん前に本を見ながら設定したもので、理屈は忘れてます・・エクセル２０００です。

回答No.10

Wendy02 · ベストアンサー

こんにちは。
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)

これは、値コピーして、並べ替えすれば、式はいらないはずですよね。私自身は、あまり難しい式などは使わないのです。手作業で出来る部分は、そのまま手作業で作ります。

お礼

度々ありがとうございます。くわしくレクチャーいただき大変勉強なります。(完全に理解できる訳では無いですが)、理解できない者に教えるのは大変ですよね・・
「ユニークなデータ」や規則性データの抽出は参考にして何とか克服したいです。

ちなみに、＊＊＊以下で思った通り表示しますので、個人的には満足です。
お時間とらせて申しわけ有りませんでした。
ありがとうございました。

回答No.9

shiotan99

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でまちがいないですか？
・ランク表は、どの列の何行目から表示させたいのですか？
・実際に入力した数式を、そのままコピーして提示してください。

補足

再度、ご回答ありがとうございます。
上記の通りやりましたら問題なく表示できました。

>３行ほど余分に設定しないとランク表・・
>この↑意味もさっぱりわかりません。
の部分は自分でもどう説明したものか、わかりませんでした。すみません。

> Ｄ列の設定はＴ列２２以下にしたのですが、
これは、Q22：S38にランク表を作ったため、
その横のT列22以下に、No.7の回答にあった
>D1に
>=A1+ROW()/100
>と入れて、下にフィルコピー
を設定すればよいのかと思った為です。

17人と18人の違いは、質問当初は元々あった表を改良するつもりでした。その表が、余分も含めて18人分の表だったのです。たくさん回答いただいたためすべて新規に作ってみようと思い、今現在、実際に必要な17人分で作ってます。そのため混同したようです。すみません。

いたらない点察していただき、適切な回答いただきありがとうございました。

回答No.8

Wendy02

こんにちは。

#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)

お礼

お礼欄ですが再度補足です。
元データはQ1:S18に、一番上は見出しです。
ランク表はQ21:S38に表示してます。
　　Q　　R　　 S
1,順位　名前　得点
2,１位　Aさん 10点
　　　　　・
　　　　　・
21,順位　名前　 得点
22,#N/A　#REF! #N/A
　　　　　・
　　　　　・
38,#N/A #NUM! #N/A

と言った状況です。
この場合R:38では=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($S$2:$S$18+ROW($Q$22:$Q$38)/100,,),ROW(Q18)),1)*100-1,1)となっています。
・・・ROW(Q18)),・・・を
ROW(A1)),やROW(A18)),にしてみますがダメです。表記は#RUF!になったりします。

B1:O38には変動する得点計算の為の一人づつの別の表があります。
この上の補足にある
>D12の部分が変わります。→F12,H12,J12・・J38,L38
と言うのはその合計点が表示されているセルです。

No.9さんの方法で上手く表示できましたが、
Wendy02さんの方法も完成させたいなと言う気持ちもあり、お時間ございましたら教えていただけるとうれしいです。よろしくお願いします。

関数の意味が理解できず、説明も下手で、すみません。

補足

度々ありがとうございます。
せっかくだから新しく作り直しているのですが、１７人分の元データ表とランク表をＱ(順位)・Ｒ(名前)・Ｓ(得点)各列にたてに並べて設置したいのですが、ランク表全体が＃Ｎ/Ａとなります。参照セルの書き換えが間違っているのでしょうね。
Ｑ2～18に
=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

Ｒ22以下
=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($S$2:$S$18+ROW($Q$23:$Q$41)/100,,),ROW(Q1)),1)*100-1,1)

お時間ございましたらよろしくお願いします。

回答No.7

shiotan99

こんにちは～

> 同点の場合、
> １位　Ａさん　１０点
> ２位　Ｃさん　　９点
> ２位　Ｄさん　　９点
> ４位　Ｂさん　　８点
> と言う表記になるようには出来ますでしょうか？

別解です。
参照している元表のレイアウトがわかりませんので、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点としてください。

補足

ありがとうございます。
何とか表示は出来るのですが、ランク表に項目「順位」「名前」「得点」を上に書くと表全体が#ＶＡＬＵＥ！になってしまいます。
元の表・ランク表をＱ・Ｒ・Ｓ列に縦に１８人分の表として並べて作ってます。Ｄ列の設定ははＴ列２２以下にしたのですが、３行ほど余分に設定しないとランク表、上の３行分が＃＃＃＃状態になります。その３行を消しても表示してくれるので現状でもいいのですが。。

回答No.6

imogasi

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

と並びます。
点数はＢ１１に
=OFFSET($A$1,MATCH(SMALL($D$1:$D$6,ROW(B11)-10),$D$1:$D$6,0)-1,1)
で出ます。Ｂ１２：Ｂ１６に式を複写する。（結果上記のとおり）
こういうことでよいでしょうか。

補足

>得点表を元に順位(ＲＡＮＫ関数)を出し
>名前順の表にして
>この段階で何（の項目）をＶＬＯＯＫＵＰでとってくるのでしょうか？
>順位の順に（ソートをつかわず、関数で）並べ替えたいということですか。

実は、
１、最初に一人一人に誰が何点取ったか合計を出すだけの14ｘ14マスの表を18人分程Ａ列～Ｎ列・１行～８８行目ぐらいの間に作る（とりあえず満足)
２、順位を出すためＷ列に得点一覧の表を作り一人一人の表の合計点の下のセルにランクが出るようにする。例=RANK($N$13,$W$4:$W$21)、Ｕ・Ｖ列にはセル参照で順位と名前を表示（とりあえず満足)
３、Ｗ列を参照してグラフを作る（とりあえず満足)
４、一目で順位・得点がわかる表がほしい、Ｕ・Ｖ・Ｗ列を利用してＶＬＯＯＫＵＰの表を作る（#N/Aが出るがとりあえず満足)
５、得点入力を簡単にするための表を作る・・等々

と言った具合にセル参照で他の表にも反映する様、年月をかけ継ぎ足していったものですから
レイアウトはバラバラ、あっちへ行きこっちへ行きという具合なのです。
今は(５)の表がメインです。

・・余計にわかりにくい説明ですね。

回答No.5

shkwta

No.4です。
何度も訂正して申し訳ありません。

（誤）B1に　=C1*100000*ROW(C1)
（正）B1に　=C1*100000+ROW(C1)

補足

何度もありがとうございます。m(__)m
順番通り並びました。
いけそうですね！？後は私の工夫次第だと思います。

回答No.4

shkwta

No.1です。
＞また、順位順(上から１位・名前、２位・名前、・・)に名前が並ぶ列が無いのですが・・

申し訳ありません。まちがえました。
（誤）B1に　=C1*100000*ROW(C1)
（正）B1に　=C1+100000*ROW(C1)

回答No.3

imogasi

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

お礼

ありがとうございます。
Ａ列に１、Ｂ列に５、Ｃ列に５０１で、設定せよと言うことでしょうか？
う～ん、理解できなくてすみません（質問の仕方が悪いんでしょうね？）

多くても２０人前後の話なんですが・・

補足

ご回答ありがとうございます。
なにせ関数の意味を把握してるわけでは無いので、試して検証するのにも時間がかかりそうです。。
気長にお願いできれば幸いです。m(__)m

回答No.2

Wendy02

こんばんは。

ご質問のデータ位置などが、ほとんど分かりませんが、こういうことでしょうか？

元のデータが以下のような状態であるとします。

例:
　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)

とすればよいと思います。範囲は、適宜変えてください。

ただ、同点で、順位に差がつくのは、ちょっと疑問に思いました。

お礼

ありがとうございます。
多分これでいいのだと思いますが、同点の場合、
１位　Ａさん　１０点
２位　Ｃさん　　９点
２位　Ｄさん　　９点
４位　Ｂさん　　８点
と言う表記になるようには出来ますでしょうか？

補足

ご回答ありがとうございます。
まだ試せてませんが、時間がかかりそうなので・・お待ちくだされば幸いです。

>同点で、順位に差がつくのは、ちょっと・・
それも何とかしたいのですが。

回答No.1

shkwta

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

まず、得点表側です。
たとえば、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以下コピー。これで得点を取り出せます。

補足

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

とりあえず教えていただいた通りの設定をしてみたのですが、
Ｅ列の得点を適当に変え、同じ得点者を作ると やはり、Ｈ，Ｊ，Ｋ列に＃Ｎ／Ａが表示されたり、されなかったりです。また、順位順(上から１位・名前、２位・名前、・・)に名前が並ぶ列が無いのですが・・
何が悪いのか検証するのに、もう少し時間がかかりそうです。