Excel(エクセル)基本講座：順位の関数

指定した数値の順位を求める　　　topへ
　ランク
=RANK(数値,範囲,順序)
　　　順序：「0」は大きいほうからの順(降順)：省略可
　　　順序：「1」は小さいほうからの順(昇順)：省略不可

【問題1】

• 合計得点の大きい方から順位を付けなさい。
  

  	B	C	D	E	F	G
  2	名前	国語	数学	英語	合計	順位
  3	井上	80	85	88	253	2
  4	会田	70	65	58	193	5
  5	上野	90	88	76	254	1
  6	岡田	55	62	47	164	6
  7	釜元	77	64	71	212	3
  8	木下	64	75	69	208	4

【解答1例】

• 順位はRANK関数で求めます。　大きい順ですので、降順の「0」を指定します。
  なお、範囲は同じ範囲を指定しますので絶対参照とします。絶対参照は[F4]キーを使うと便利です。
• なお、この「0」は省略可ですので　=RANK(F3,$F$3:$F$8)　としてもOKです。

• 	B	C	D	E	F	G
  2	名前	国語	数学	英語	合計	順位
  3	井上	80	85	88	253	=RANK(F3,$F$3:$F$8,0)
  4	会田	70	65	58	193	=RANK(F4,$F$3:$F$8,0)
  5	上野	90	88	76	254	=RANK(F5,$F$3:$F$8,0)
  6	岡田	55	62	47	164	=RANK(F6,$F$3:$F$8,0)
  7	釜元	77	64	71	212	=RANK(F7,$F$3:$F$8,0)
  8	木下	64	75	69	208	=RANK(F8,$F$3:$F$8,0)

【問題2】

• 総得点が同点の場合、G列のように順位が飛び飛びになります。
• H列のように同点の場合は表示順の行番号が若い方を上にするにはどうしたらいいでしょう?

• 	B	C	D	E	F	G	H	I
  2	名前	国語	数学	英語	合計	順位A	順位B	作業列
  3	井上	81	85	88	254	1	1
  4	会田	70	65	58	193	5	5
  5	上野	90	88	76	254	1	2
  6	岡田	55	62	47	164	6	6
  7	釜元	77	64	71	212	3	3
  8	木下	68	75	69	212	3	4

【解答2例】

• 作業列としてI列を使用します。
• I3セルに『=F3-ROW()/1000』　として、1点に満たない数値(行番号の千分の一)を差し引き、同得点間に差を付けてやります。
  • 逆に『=F3+ROW()/1000』とすると、行番号が大きい方が上位になります。
• 順位はI列を基準にRANK関数で出します。
  

  	B	C	D	E	F	G	H	I
  2	名前	国語	数学	英語	合計	順位A	順位B	作業列
  3	井上	81	85	88	254	=RANK(F3,$F$3:$F$8,0)	=RANK(I3,$I$3:$I$8,0)	=F3-ROW()/1000
  4	会田	70	65	58	193	=RANK(F4,$F$3:$F$8,0)	=RANK(I4,$I$3:$I$8,0)	=F4-ROW()/1000
  5	上野	90	88	76	254	=RANK(F5,$F$3:$F$8,0)	=RANK(I5,$I$3:$I$8,0)	=F5-ROW()/1000
  6	岡田	55	62	47	164	=RANK(F6,$F$3:$F$8,0)	=RANK(I6,$I$3:$I$8,0)	=F6-ROW()/1000
  7	釜元	77	64	71	212	=RANK(F7,$F$3:$F$8,0)	=RANK(I7,$I$3:$I$8,0)	=F7-ROW()/1000
  8	木下	68	75	69	212	=RANK(F8,$F$3:$F$8,0)	=RANK(I8,$I$3:$I$8,0)	=F8-ROW()/1000

• COUNTIF関数で順位Bをだすには
  =COUNTIF($F$3:$F$8,">"&F3)+COUNTIF($F$3:F3,F3)
  とすれば、作業列は不要になります。

【問題3】

• 順位をJ2セルの入力して、その順位の名前をJ3セル以降に取出してください。
  なお、G列で同順位の場合、行番号が若い方を上にします。

• 	B	C	D	E	F	G	H	I	J
  2	名前	国語	数学	英語	合計	順位	作業列	順位	3
  3	井上	81	85	88	254	1		名前	釜元
  4	会田	70	65	58	193	5			木下
  5	上野	90	88	76	254	1
  6	岡田	55	62	47	164	6
  7	釜元	77	64	71	212	3
  8	木下	68	75	69	212	3

【解答3例】

• 作業列としてH列を使用します。
• H3セルに『=G3&"_"&COUNTIF($G$3:G3,G3)』としてH8セルまでコピーします。
• J3セルに『=IF(COUNTIF($H$3:$H$8,$J$2&"_"&ROW(A1))=0,"",
  INDEX($B$3:$B$8,MATCH($J$2&"_"&ROW(A1),$H$3:$H$8,0)))』としてJ8セルまでコピーします。
• 計算結果は下表のようになります。
  

  	B	C	D	E	F	G	H	I	J
  2	名前	国語	数学	英語	合計	順位	作業列	順位	3
  3	井上	81	85	88	254	1	1_1	名前	釜元
  4	会田	70	65	58	193	5	5_1		木下
  5	上野	90	88	76	254	1	1_2
  6	岡田	55	62	47	164	6	6_1
  7	釜元	77	64	71	212	3	3_1
  8	木下	68	75	69	212	3	3_2

• 数式は以下のようになります。
  

  	B	C	D	E	F	G	H	I	J
  2	名前	国語	数学	英語	合計	順位	作業列	順位	3
  3	井上	81	85	88	254	=RANK(F3,$F$3:$F$8,0)	=G3&"_"&COUNTIF($G$3:G3,G3)	名前	=IF(COUNTIF($H$3:$H$8,$J$2&"_"&ROW(A1))=0,"", INDEX($B$3:$B$8,MATCH($J$2&"_"&ROW(A1),$H$3:$H$8,0)))
  4	会田	70	65	58	193	=RANK(F4,$F$3:$F$8,0)	=G4&"_"&COUNTIF($G$3:G4,G4)		=IF(COUNTIF($H$3:$H$8,$J$2&"_"&ROW(A2))=0,"", INDEX($B$3:$B$8,MATCH($J$2&"_"&ROW(A2),$H$3:$H$8,0)))
  5	上野	90	88	76	254	=RANK(F5,$F$3:$F$8,0)	=G5&"_"&COUNTIF($G$3:G5,G5)		=IF(COUNTIF($H$3:$H$8,$J$2&"_"&ROW(A3))=0,"", INDEX($B$3:$B$8,MATCH($J$2&"_"&ROW(A3),$H$3:$H$8,0)))
  6	岡田	55	62	47	164	=RANK(F6,$F$3:$F$8,0)	=G6&"_"&COUNTIF($G$3:G6,G6)		=IF(COUNTIF($H$3:$H$8,$J$2&"_"&ROW(A4))=0,"", INDEX($B$3:$B$8,MATCH($J$2&"_"&ROW(A4),$H$3:$H$8,0)))
  7	釜元	77	64	71	212	=RANK(F7,$F$3:$F$8,0)	=G7&"_"&COUNTIF($G$3:G7,G7)		=IF(COUNTIF($H$3:$H$8,$J$2&"_"&ROW(A5))=0,"", INDEX($B$3:$B$8,MATCH($J$2&"_"&ROW(A5),$H$3:$H$8,0)))
  8	木下	68	75	69	212	=RANK(F8,$F$3:$F$8,0)	=G8&"_"&COUNTIF($G$3:G8,G8)		=IF(COUNTIF($H$3:$H$8,$J$2&"_"&ROW(A6))=0,"", INDEX($B$3:$B$8,MATCH($J$2&"_"&ROW(A6),$H$3:$H$8,0)))

(余談1)　COUNTIF関数を使った順位の出し方

• COUNTIF関数で求める得点より高い得点の数を数えます。順位はその次になるので1を加えます。
• 合計欄が空欄や文字列であったらうまく計算できないので、
  =IF(ISNUMBER(F3),COUNTIF($F$3:$F$8,">"&F3)+1,"")
  のように、ISNUMBER関数の処理を加えてみました。
• 重複値は同順位となります。
  

  	B	C	D	E	F	G	H
  2	名前	国語	数学	英語	合計	順位	結果
  3	井上	80	85	88	253	=COUNTIF($F$3:$F$8,">"&F3)+1	2
  4	会田	70	65	73	208	=COUNTIF($F$3:$F$8,">"&F4)+1	4
  5	上野	90	88	76	254	=COUNTIF($F$3:$F$8,">"&F5)+1	1
  6	岡田	55	62	47	164	=COUNTIF($F$3:$F$8,">"&F6)+1	6
  7	釜元	77	64	71	212	=COUNTIF($F$3:$F$8,">"&F7)+1	3
  8	木下	64	75	69	208	=COUNTIF($F$3:$F$8,">"&F8)+1	4

(余談2)　SUMIF関数を使った順位の出し方

• 同順位が有る場合、以下の順位を繰り上げるには?
• 以下の表のような順位にしたい場合、作業列を使って処理してみました。

• 	B	C	D	E	F	G	H
  2	名前	国語	数学	英語	合計	順位	作業列
  3	井上	80	85	89	254	1	1
  4	会田	70	65	73	208	3	1
  5	上野	90	88	76	254	1
  6	岡田	55	62	47	164	4	1
  7	釜元	77	64	71	212	2	1
  8	木下	64	75	69	208	3

【計算例】

• 同得点は無視して、異なる得点のみから順位を求めます。
• 順位は、求めたい得点より大きい得点の数を求め「+1」することによって可能です。

• 	B	C	D	E	F	G	H
  2	名前	国語	数学	英語	合計	順位	作業列
  3	井上	80	85	89	254	=SUMIF($F$3:$F$8,">"&F3,$H$3:$H$8)+1	=IF(COUNTIF($F$3:F3,F3)>1,"",1)
  4	会田	70	65	73	208	=SUMIF($F$3:$F$8,">"&F4,$H$3:$H$8)+1	=IF(COUNTIF($F$3:F4,F4)>1,"",1)
  5	上野	90	88	76	254	=SUMIF($F$3:$F$8,">"&F5,$H$3:$H$8)+1	=IF(COUNTIF($F$3:F5,F5)>1,"",1)
  6	岡田	55	62	47	164	=SUMIF($F$3:$F$8,">"&F6,$H$3:$H$8)+1	=IF(COUNTIF($F$3:F6,F6)>1,"",1)
  7	釜元	77	64	71	212	=SUMIF($F$3:$F$8,">"&F7,$H$3:$H$8)+1	=IF(COUNTIF($F$3:F7,F7)>1,"",1)
  8	木下	64	75	69	208	=SUMIF($F$3:$F$8,">"&F8,$H$3:$H$8)+1	=IF(COUNTIF($F$3:F8,F8)>1,"",1)

指定した数値の順位を求める　　　topへ
　ランク　イコール
=RANK.EQ(数値,範囲,順序)　　
　　　順序：「0」は大きいほうからの順(降順)：省略可
　　　順序：「1」は小さいほうからの順(昇順)：省略不可
　　Excel2010で追加された関数です。

• 重複した数値は同じ順位と見なされ、それ以降の数値の順位がずれていきます。
  従来のRANK関数と同じ結果になります。

• 【問題1】

  • 合計得点の大きい方から順位を付けなさい。ただし、同得点のときは同順位とします。
    

    	B	C	D
    2	名前	合計得点	順位
    3	井上	253	1
    4	会田	193	5
    5	上野	253	1
    6	岡田	164	6
    7	釜元	208	3
    8	木下	208	3

  【解答1例】

  • 順位はRANK.EQ関数で求めます。　大きい順ですので、降順の「0」を指定します。
    なお、この「0」は省略可ですので　=RANK.EQ(C3,$C$3:$C$8)　としてもOKです。

    • 	B	C	D
      2	名前	合計得点	順位
      3	井上	253	=RANK.EQ(C3,$C$3:$C$8,0)
      4	会田	193	=RANK.EQ(C4,$C$3:$C$8,0)
      5	上野	253	=RANK.EQ(C5,$C$3:$C$8,0)
      6	岡田	164	=RANK.EQ(C6,$C$3:$C$8,0)
      7	釜元	208	=RANK.EQ(C7,$C$3:$C$8,0)
      8	木下	208	=RANK.EQ(C8,$C$3:$C$8,0)

指定した数値の平均順位を求める　　　topへ
　ランク　アベレージ
=RANK.AVG(数値,範囲,順序)　　
　　　順序：「0」は大きいほうからの順(降順)：省略可
　　　順序：「1」は小さいほうからの順(昇順)：省略不可
　　Excel2010で追加された関数です。

• 複数の値が同じ順位にあるときは、平均の順位が返されます。
  従来のRANK関数とは重複する値があると異なった結果になります。

• 【問題1】

  • 合計得点の大きい方から順位を付けなさい。ただし、同得点のときは順位の平均で求めなさい。
    

    	B	C	D
    2	名前	合計得点	順位
    3	井上	253	1.5
    4	会田	193	5
    5	上野	253	1.5
    6	岡田	164	6
    7	釜元	208	3.5
    8	木下	208	3.5

  【解答1例】

  • 順位はRANK.AVG関数で求めます。　大きい順ですので、降順の「0」を指定します。
    なお、この「0」は省略可ですので　=RANK.AVG(C3,$C$3:$C$8)　としてもOKです。
    • 井上と上野は同得点で1位、2位となるので、順位はその平均値で　(1+2)/2=1.5　となります。
      

      	B	C	D
      2	名前	合計得点	順位
      3	井上	253	=RANK.AVG(C3,$C$3:$C$8,0)
      4	会田	193	=RANK.AVG(C4,$C$3:$C$8,0)
      5	上野	253	=RANK.AVG(C5,$C$3:$C$8,0)
      6	岡田	164	=RANK.AVG(C6,$C$3:$C$8,0)
      7	釜元	208	=RANK.AVG(C7,$C$3:$C$8,0)
      8	木下	208	=RANK.AVG(C8,$C$3:$C$8,0)

PageViewCounter

Since2006/2/27