エクセルで順位付けする方法を教えて下さい

質問

エクセルで順位付けする方法を教えて下さい。
（A列)にクラス、（B列）に学籍番号、（C列）に得点が記載された1つの表があるとします。表にはクラス、学籍番号、得点、順に並んでいないところを、関数で順位付けしたいと考えています。表示結果として、クラス毎で得点の低い順に表示したいと考えています。

具体的には、
元々の表
（A列）　（B列)　（C列）
　A組　　　８　　　５７
　B組　　　４　　　４１
　A組　　　６　　　４２
　C組　　　３　　　８３
　C組　　　６　　　７３
　B組　　　２　　　８３

結果の表
（A列）　（B列)　（C列）
　A組　　　６　　　４２
　A組　　　８　　　５７
　B組　　　４　　　４１
　B組　　　２　　　８３
　C組　　　６　　　７３
　C組　　　３　　　８３

非常に煩雑な関数になりそうですが、お力をお貸しいただきたく宜しくお願いいたします。

回答No.14

layy

追記。

A 8 57 → A 6 42 1
B 4 41 → A 8 57 2
A 6 42 → B 4 41 1
C 3 83 → B 2 83 2
C 6 73 → B 2 83 2
B 2 83 → B 3 83 2
B 2 83 → C 6 73 1
B 3 83 → C 3 83 2

提示分ですが、
一応確認として、
万が一、入力誤りがあって
同じクラス、番号、点の重複データがあったとしても対応してました。

ここの回答例をベースにして、今回の対応が終わるとして、
以後ちょっと条件が変わっても保守できるのか、と
いうのが気になります。
他の用途でも順番付け（グループごとに並べ替え、連番を振る）というのは
よくある話なので、活用できたらと思います。
今回使われている関数、ＶＢＡを学習しておくことをお勧めします。

回答No.13

maron--5

◆Sheet2の式
A1=INDEX(Sheet1!A:A,MATCH(SMALL(INDEX((CODE(Sheet1!$A$1:$A$6)&TEXT(Sheet1!$C$1:$C$6,"000")&TEXT(Sheet1!$B$1:$B$6,"000"))*1,),ROW(A1)),INDEX((CODE(Sheet1!$A$1:$A$6)&TEXT(Sheet1!$C$1:$C$6,"000")&TEXT(Sheet1!$B$1:$B$6,"000"))*1,),0))
★右と下にコピー

回答No.12

KURUMITO

関数でということでしたら計算が重くならずに対応するためには作業列を設けて行うことでしょう。
シート1にお示しのデータがあるとしてＤ1セルには次の式を入力して下方いオートフィルドラッグします。

=IF(A1="","",CODE(A1)*1000+C1)

シート2にはお求めの表を表示させるとしてＡ1セルには次の式を入力してＣ1セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNTA(Sheet1!$A:$A),"",INDEX(Sheet1!$A:$C,MATCH(SMALL(Sheet1!$D:$D,ROW(A1)),Sheet1!$D:$D,0),COLUMN(A1)))

同じクラスで同じ点数がある場合でも対応できます。

回答No.11

layy

Sub RANKING()
Dim I As Long
Dim J As Long
Dim K As Long
Dim M As Long '算出行数

Dim XX As String
Dim YY As String
Dim ZZ As String

Dim X(10) As String 'Ａ列
Dim Y(10) As String 'Ｂ列
Dim Z(10) As String 'Ｃ列
Dim R(10) As String '順位

Range("A1").Select
M = 0
'とりあえず１０行までループ、値があるものはワークへ退避
For I = 1 To 10
If Len(Cells(I, 1)) > 0 Then
M = M + 1
X(M) = Cells(I, 1)
Y(M) = Cells(I, 2)
Z(M) = Cells(I, 3)
R(M) = 0
End If
Next I

'ワークへ退避したものを並べ替え（Ａ列違えば相互入替、Ｃ列違えば相互入替）
For I = 1 To M - 1
For J = I + 1 To M
If X(I) > X(J) Then
XX = X(J)
X(J) = X(I)
X(I) = XX

YY = Y(J)
Y(J) = Y(I)
Y(I) = YY

ZZ = Z(J)
Z(J) = Z(I)
Z(I) = ZZ
Else
If X(I) < X(J) Then
Else
If Z(I) > Z(J) Then
XX = X(J)
X(J) = X(I)
X(I) = XX

YY = Y(J)
Y(J) = Y(I)
Y(I) = YY

ZZ = Z(J)
Z(J) = Z(I)
Z(I) = ZZ
End If
End If
End If
Next J
Next I

'並べ替えしたものに順位付け
K = 1
R(1) = 1
For I = 1 To M
If X(I - 1) <> X(I) Then
K = 1
Else
If Z(I - 1) <> Z(I) Then
K = K + 1
End If
End If
R(I) = K
Next I
'順位付けしたものをセルへ貼り付け
For I = 1 To M
Cells(I, 5).Value = X(I)
Cells(I, 6).Value = Y(I)
Cells(I, 7).Value = Z(I)
Cells(I, 8).Value = R(I)
Next I
MsgBox ("終了")

End Sub
ざっと作成してみたので参考。
あとはデバッグして調整してください

回答No.10

kagakusuki

万が一、他のクラスに同じ得点の人がいる場合、特に別クラスで学籍番号と得点が共に同じ人がいる場合にも対応する方法です。
　今仮に、クラスがA組からJ組までの10クラスまであるものとします。
　又、元々の表はSheet1に存在し、結果の表はSheet2に作成し、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA1セルに、次の数式を入力して下さい。

=IF(ROW(1:1)>MATCH(9^9,Sheet1!$C:$C),"",Sheet1!$C1+(MATCH(Sheet1!$A1,{"A組","B組","C組","D組","E組","F組","G組","H組","I組","J組"},0)+Sheet1!$B1/MAX(Sheet1!$B:$B))/10)

　尚、この数式中の"A組","B組","C組","D組","E組","F組","G組","H組","I組","J組"の部分は、実際のクラス名に合わせて、適時修正して下さい。
　又、クラスの総数が10クラスを超えている場合には、数式の末尾にある

/10)

の部分の10を、クラス数に等しい数に変更して下さい。（クラス数の方が少ない分には問題ありません）
　次に、Sheet3のA1セルをコピーして、A2以下に貼り付けて下さい。
　次に、Sheet2のA1セルに、次の数式を入力して下さい。

=IF(ROW(1:1)>COUNT(Sheet1!$C:$C),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet3!$A:$A,ROW(1:1)),Sheet3!$A:$A,0)))

　次に、Sheet2のA1セルをコピーして、B1セルとC1セルに貼り付けて下さい。
　次に、Sheet2のA1～C1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　以上です。

回答No.9

MackyNo1

私の回答で同じ組に同点の人がいた場合に対応するなら、複雑な数式になりますが以下のような数式をF2セルに入力してください。

=IF(E2="","",INDEX(B:B,LARGE(INDEX(($A$2:$A$10=E2)*($C$2:$C$10=G2)*ROW($A$2:$A$10),),IF(SUMPRODUCT(($A$2:$A$10=E2)*($C$2:$C$10=G2))=1,1,SUMPRODUCT(($A$2:$A$10=E2)*($C$2:$C$10=G2))+1-COUNTIF($E$2:E2,E2)))))

ちなみに上記の数式はE列とG列の値を参照していますので、G列の点数データが表示されていないと（数式を入力しないと）正しい値を表示しません。

回答No.8

某HN クロメート(Chromate)（@CoalTar）

＃３、＃７です。同一シートで、遊びの配列数式。
=IF(COUNT(B:B)<ROW(A1),"",
INDEX(A$2:A$7,MATCH(SMALL(CODE($A$2:$A$7)*100000-$C$2:$C$7*100+$B$2:$B$7,ROW(A1)),
CODE($A$2:$A$7)*100000-$C$2:$C$7*100+$B$2:$B$7,0)))
[Ctrl]+[Shift] ＋[Enter] で確定({}で囲まれる)
右へ下へオートフィル
切り取り 別シートへ貼り付け

先の回答の応用にはなるのですが、お遊びなので解説しません。
失礼しました。m(_ _"m)ペコリ

回答No.7

某HN クロメート(Chromate)（@CoalTar）

＃3です
MackyNo1 さんの回答を参考にさせて頂いていたところ
重複の問題に気づかされました。もう一度書き直します

作業列を使って丁寧に作っていくとして
D2セル クラス、点数、番号を順位に組み込む
=CODE(A2)*100000-C2*100+B2
下へオートフィル

E2セル 順番を出す
=RANK(D2,$D$2:$D$7,1)
下へオートフィル

G2セル 単に連番
H2セル 位置を探す
=MATCH($G2,$E$2:$E$7,0)
下へオートフィル

I2セル 値を返す
=INDEX(A$2:A$7,$H2)
下へ右へオートフィル

回答No.6

tom04

No.5です！
たびたびごめんなさい。

前回の投稿でSheet2の数式が間違っていました。

Sheet2のA2セルは
=IF(COUNT(Sheet1!$E:$E)<ROW(A1),"",INDEX(Sheet1!A$2:A$1000,MATCH(ROW(A1),Sheet1!$E$2:$E$1000,0)))
に訂正してください。

前回の式だと1行ずれてしまいます。

何度も失礼しました。m(__)m

回答No.5

tom04

こんばんは！
単純にRANK関数で対応できると思いますが、万一同じクラス内に同点の人がいた場合に対応できるようにしてみました。

↓の画像のように作業用の列を2列設けています。
そして、Sheet1に別表（G・H列）を作ってみました。これは単純にクラスを小さい順に並べるための表です。

Sheet1の作業列1D2セルに
=IF(A2="","",VLOOKUP(A2,$G$2:$H$5,2,0)+RANK(C2,C:C,1))
作業列2のE2セルに
=IF(D2="","",COUNTIF(D:D,"<"&D2)+COUNTIF($D$2:D2,D2))
という数式を入れ、C2・D2セルを範囲指定しD2セルのフィルハンドルで下へオートフィルでずぃ～～～！っとコピーします。

そして結果のSheet2のA2セルに
=IF(COUNT(Sheet1!$E:$E)<ROW(A2),"",INDEX(Sheet1!A$2:A$1000,MATCH(ROW(A2),Sheet1!$E$2:$E$1000,0)))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
これで同じクラスに同点（同順位）の人がいてもちゃんと表示されると思います。
尚、同点の場合は上側の行の順位が先に表示されます。

以上、参考になれば良いのですが・・・m(__)m