同順の場合の順位について（2回目）

質問

A　　　　B　　　　C　　　 D　　　E　　　　F　　　　G
1　　　社長　　　2　　　　60（点） -　　　1（位）次長　　　75（点）
2　　　次長　　　1　　　　75　　　 -　　　2　　　社長　　　60
3　　　部長　　　2　　　　60　　 　-　　　2　　　部長　　　60　
4　　　係長　　　4　　　　55　 　　-　　　4　　　係長　　　55
5　　　　-　　　　-　　　　 -　 　 　-　　　 -　 　　　-　　　　-
6　　　 1 (位）　エラー 　 75（点）-　　　-　　　　-　　 　 -
7　　　 2 　　　エラー 　　　60　　　-　　　-　　　　-　　 　-
8　　　 2 　　　エラー 　　　60　　　-　　　-　　　　-　　　 -
9　　　 4 　　　エラー 　　　55　　　-　　　-　　　　-　　　 -

E1=RANK(LARGE($C$1:$C$4,ROW(A1)),$C$1:$C$4)
G1=LARGE($C$1:$C$4,ROW(A1))
F1=IF(E1="","",INDEX($A$1:$A$4,LARGE(INDEX((C$1:C$4=G1)*ROW($A$1:$A$4),),COUNTIF($E$1:$E$4,E1)-COUNTIF($E$1:E1,E1)+1)))
上記なら、きちんと順位、順位の名前、点数が入ります。

A1=RANK(LARGE($C$1:$C$4,ROW(A1)),$C$1:$C$4)
C1=LARGE($C$1:$C$4,ROW(A1))

B6、B7、B8にどういう関数が入りますか？
教えて頂けますか。
他にも何か良い方法がないでしょうか。
1位から100位までありますので、やり方が分からず焦ってます。

回答No.2

golgo1013 · ベストアンサー

＞1位から100位までありますので、
100件のデータがあるという事ですので、実際はA6から表示する事はないのですよね。
質問のようにA6からの表示では同じ列に異なる形式のデータが存在し、また行数とデータ件数に差（オフセット）がありROW関数を使っているので複雑になります。
A6からA9には
A6=RANK(LARGE($C$1:$C$4,ROW(A1)),$C$1:$C$4)
A7=RANK(LARGE($C$1:$C$4,ROW(A2)),$C$1:$C$4)
A8=RANK(LARGE($C$1:$C$4,ROW(A3)),$C$1:$C$4)
A9=RANK(LARGE($C$1:$C$4,ROW(A4)),$C$1:$C$4)
C6からC9には
C6=LARGE($C$1:$C$4,ROW(A1))
C7=LARGE($C$1:$C$4,ROW(A2))
C8=LARGE($C$1:$C$4,ROW(A3))
C9=LARGE($C$1:$C$4,ROW(A4))
B6からB9には
B6=IF(A6="","",INDEX($A$1:$A$4,LARGE(INDEX((C$1:C$4=C6)*ROW($A$1:$A$4),),COUNTIF($A$6:$A$9,A6)-COUNTIF($A$6:A6,A6)+1)))
B7=IF(A7="","",INDEX($A$1:$A$4,LARGE(INDEX((C$1:C$4=C7)*ROW($A$1:$A$4),),COUNTIF($A$6:$A$9,A7)-COUNTIF($A$6:A7,A7)+1)))
B8=IF(A8="","",INDEX($A$1:$A$4,LARGE(INDEX((C$1:C$4=C8)*ROW($A$1:$A$4),),COUNTIF($A$6:$A$9,A8)-COUNTIF($A$6:A8,A8)+1)))
B9=IF(A9="","",INDEX($A$1:$A$4,LARGE(INDEX((C$1:C$4=C9)*ROW($A$1:$A$4),),COUNTIF($A$6:$A$9,A9)-COUNTIF($A$6:A9,A9)+1)))
になります。
しかし100件もあるのでスマートに考えるとデータのシートと順位を表示するシートを別にすれば良いと思います。
Sheet1に入力データ、Sheet2に順位データとし、件数を100とすると、
(1)Sheet1
A列→名前（入力）
B列→点数（入力）
C列→順位（計算）
下記の式を件数分オートフィルします。
C1=IF(ISBLANK(A1),"",RANK(B1,B:B))
(2)Sheet2
A列→順位（計算）
下記の式を件数分オートフィルします。
A1=IF(ISBLANK(Sheet1!A1),"",RANK(LARGE(Sheet1!$B:$B,ROW(A1)),Sheet1!$B:$B))
B列→名前（計算）
下記の式を100件分オートフィルします。（※）
B1=IF(A1="","",INDEX(Sheet1!$A:$A,LARGE(INDEX((Sheet1!$B$1:$B$100=C1)*ROW(Sheet1!$A$1:$A$100),),COUNTIF($A:$A,A1)-COUNTIF($A$1:A1,A1)+1)))
C列→点数（計算）
下記の式を件数分オートフィルします。
C1=IF(A1="","",LARGE(Sheet1!$B:$B,ROW(A1)))
※B列だけ件数に依存した数式になっています。
　依存しないように考えたのですが、まだうまくいっていません。（実力不足）

回答No.1

kigoshi

なかなか回答がつきませんね。

そもそもRANK関数を使わなければならないのでしょうか。
点数順に降順ソートして順位をあとで付けると言う方法ではダメなのでしょうか。
その際、同点の場合の順位の付け方さえうまくできるのなら解決しますか？

とたえば、表題行が１行目、順位をＡ列、点数がＣ列、とした場合。
A2の式　=IF(C2=C1,A1,ROW()-1)
でA2を下までコピー、で目的の表が作成できるように考えますが。

表題行などが何行かあって、１位の行がもっと下に下がるときは
ROW()-1　の１を適宜変更して下さい。

いかがでしょうか。ご参考になれば。

お礼

回答ありがとうございます。
私も最初に降順ソートして、順位を後でつけていたのですが、1位から100位の間に同順位が結構あり、関数を使えればいいなと思ってました。　なるべく簡単な方法でやってみます。
参考にしますので、ありがとうございました。