1. Home » 
2. エクセル練習問題：目次 » 
3. 順位の計算

順位の計算：エクセル練習問題

更新:2025/9/16;作成:2012/8/9

問題1の解答例　　　　topへ

• 問題1：得点の高い方から順位をつけなさい。同得点は同順位とします。
• 解答
  

  	B	C	D	E
  2	名前	性別	得点	順位
  3	相沢一郎	男	52	7
  4	井上浩二	男	74	5
  5	上野有紀	女	84	1
  6	内野武	男	77	2
  7	上田祥子	女	68	6
  8	江田早苗	女	77	2
  9	榎本高貴	男	77	2
  10	小田和樹	男	47	8

順位はRANK関数で求めることができます

• RANK関数の構文：=RANK(数値,範囲,[順序])
  順序に0を入れるか、省略すると降順での順位になります。
  0以外の数値を入れると昇順での順位になります。
• RANK関数　に説明がありますので参照してください。
• RANK関数の「範囲」は不動ですので絶対参照にしておいて、E3の数式を下方向へコピーします。
  E3セルは　=RANK(D3,$D$3:$D$10)　と入力して、下方向へ数式をコピーします。
  または、=RANK(D3,$D$3:$D$10,0)　でもOKです。
  
• Excel for Excel365,Excel2021以降のスピルが利用できるバージョンでは、
  =RANK(D3:D10,D3:D10,0)　で計算できます。
  

RANK.EQ関数を使って求める

• Excel2010ではRANK.EQ関数が使用できます。 RANK関数と同じ結果が得られます。
  RANK.EQ関数の構文：RANK.EQ(数値,範囲,[順序])
• E3セルは　=RANK.EQ(D3:D10,D3:D10)　とします。
  または、=RANK.EQ(D3,$D$3:$D$10)　として、オートフィルなどで下方向へ数式をコピーします。
  

COUNTIF関数を使って求める

• 構文：=COUNTIF(範囲,検索条件)
  COUNTIF関数　に説明がありますので参照してください。
• 求めたい得点よりも大きな値が何個あるか調べれば、求めたい得点の順位はその個数+1となります。
  よって　
• COUNTIF関数の検索条件で比較演算子とセル参照を組み合わせて使う時は比較演算子とセル参照を&でつなぎます。
  • Spillが使えるExcel for Excel365,Excel2021以降のバージョン場合は
    =COUNTIF(D3:D10,">"&D3:D10)+1　と入力します。
  • Excel2019以前のバージョンでは
    =COUNTIF($D$3:$D$10,">"&D3)+1　と入力して、オートフィルなどで下方向へ数式をコピーします。
  

問題2の解答例　　　　topへ

• 問題2：得点の高い方から順位をつけなさい。
  ただし、同得点は上の行の方を上位とし、同順位はないようにしなさい。
• 解答
  

  	B	C	D	E
  2	名前	性別	得点	順位
  3	相沢一郎	男	52	7
  4	井上浩二	男	74	5
  5	上野有紀	女	84	1
  6	内野武	男	77	2
  7	上田祥子	女	68	6
  8	江田早苗	女	77	3
  9	榎本高貴	男	77	4
  10	小田和樹	男	47	8

Filter関数、SORT関数、XMATCH関数を使って求める

• Excel for Microsoft365、Excel2021以降ではFilter関数、SORT関数、XMATCH関数が使えます。
  無理やり感がありますが・・・名前を得点の高い順に並べ替えて、その並べ替えた配列で各行の名前が何番目に有るかを調べています。
• E3セルに　=XMATCH(B3:B10,FILTER(SORT(B3:D10,3,-1),{1,0,0}))　と入力しています。
  

COUNTIF関数を使って求める

• 構文：=COUNTIF(範囲,検索条件)
  COUNTIF関数　に説明がありますので参照してください。
• 同点が上のデータにあるか否かをCOUNTIF関数で調べます。
  同点が2個あれば、2つ目の同点者はRANK関数で求めた順位+1　となりますので、+COUNTIF($D$3:D3,D3)-1　と補正します。
  この問題では絶対参照の使い方がポイントと考えます。
• E3セルに　=RANK(D3,$D$3:$D$10)+COUNTIF($D$3:D3,D3)-1
  と入力して、下方向へコピーします。
  

作業列を使って求める

• 作業列を使ってもよいケースでは行番号を補正値に利用した作業列を設けます。
• E3セルに　=D3-ROW()/100　と入力し、E10セルまでコピーしました。
  • 行番号が小さいほど大きな値になるように行番号の 1/100 の値を得点から引いて補正しました。
    行番数が99までは有効ですが、100〜999のデータのときは1/1000など得点の最小値1に影響を与えないようにする必要があります。
    • 構文ROW([範囲])
      範囲を省略すると、ROW 関数が入力されているセルの行番号が返されます。
      ROW・ROWS関数　に説明がありますので参照してください。
• Spillが使えるExcel for Excel365,Excel2021以降のバージョン場合は
  E3セルは　=D3:D10-ROW()/100　
  F3セルは　=RANK(E3:E10,E3:E10)　と入力します。
  なお、スピルの結果のセル範囲を参照すると、自動的に「#」の付いたセル参照に切り替わります。
  =RANK(E3#,E3#)　となります。
  「＃」は「スピル範囲演算子」と呼ばれます。
• Excel2019以前のバージョンでは
  E3セルは　=D3-ROW()/100
  F3セルは　=RANK(E3,$E$3:$E$10)　と入力して、オートフィルなどで下方向へ数式をコピーします。
• 

問題3の解答例　　　　topへ

• 問題3：得点の高い方から順位を付けました。ただし、同得点の場合は上の行を上位としました。
  このデータ表から、上位3位までの得点の順位と名前を書き出しなさい。
  この例では3位の得点は77ですが4位も同点なので書き出す必要があります。

GROUPBY関数,CHOOSECOLS関数,MAX関数,LARGE関数を使って求めてみました

• Excel for Microsoft365で使用できるGROUPBY関数
  Excel2024以降で使用できるHSTCK関数、CHOOSECOLS関数
  などを使ってデータを抽出することができます。
• G3セルには以下の数式を入力しています。
  =CHOOSECOLS(
  GROUPBY(HSTACK((E3:E10)&"位",B3:B10),D3:D10,MAX,,0,,D3:D10>=LARGE(D3:D10,3)),
  {1,2})
  • 取り出したいデータを　HSTACK((E3:E10)&"位",B3:B10)　として、GROUPBY関数で取り出します。
    関数はMAX、抽出条件は上位3位以上なので引数のフィルターは　D3:D10>=LARGE(D3:D10,3)　としています。
  • GROUPBY関数取り出したデータの配列は3列(順位、名前、得点)になります。
  • CHOOSECOLS関数で3列の配列から最初の2つの列を取り出します。
  

SORTBY関数,Filter関数を使って求めてみました

• Microsoft365,Excel2021以降ではFilter関数やSORT関数やSORTBY関数が使えます。
  • FILTER関数でデータを抽出する:Excel関数
    SORT関数でデータを並べ替える:Excel関数
    SORTBY関数で指定した範囲または配列でデータを並べ替える:Excel関数
• G3セルに　=SORT(FILTER(E3:E10,D3:D10>=LARGE(D3:D10,3)))&"位"　と入力します。
  「3番目に大きい得点以上」の順位をFILTER関数で抽出して、SORT関数で並べ替えています。
• H3セルに
  =SORTBY(FILTER(B3:B10,D3:D10>=LARGE(D3:D10,3)),
  FILTER(E3:E10,D3:D10>=LARGE(D3:D10,3)))
  と入力します。
  • FILTER(B3:B10,D3:D10>=LARGE(D3:D10,3))　「3番目に大きい得点以上」の名前をFILTER関数で抽出
    これを　SORTBY関数を使って、↓の順位で並べ替えています。
    FILTER(E3:E10,D3:D10>=LARGE(D3:D10,3))　「3番目に大きい得点以上」の順位をFILTER関数で抽出
• 

INDEX関数,MATCH関数,LARGE関数を使って求めてみました

• 解答
  

  • 	B	C	D	E	F	G	H
    2	名前	性別	得点	順位		順位	名前
    3	相沢一郎	男	52	7		1位	上野有紀
    4	井上浩二	男	74	5		2位	内野武
    5	上野有紀	女	84	1		3位	江田早苗
    6	内野武	男	77	2		4位	榎本高貴
    7	上田祥子	女	68	6
    8	江田早苗	女	77	3
    9	榎本高貴	男	77	4
    10	小田和樹	男	47	8

• 数式の例：この解答例では1,2,3・・・という数値をROW(A1)で代用しています。
  • 3位の得点は　LARGE($D$3:$D$10,3)　で求めることができます。
    • 構文：=LARGE(範囲,順位)
      LARGE関数　に説明がありますので参照してください。
  • 3位の得点以上は何名いるかを調べます。
    COUNTIF($D$3:$D$10,">="&LARGE($D$3:$D$10,3)
  • これで表示する対象者の数がわかりますので、
    G3セル以降に=IF((COUNTIF($D$3:$D$10,">="&LARGE($D$3:$D$10,3))>=ROW(A1)),ROW(A1)&"位","")
    として、このケースでは1位〜4位と順位を表示します。
  • H列の名前はE列の順位と一致する行の名前を書き出すのでINDEX関数で求めました。
    • 構文：=INDEX(範囲,行番号,[列番号],[領域番号])
      INDEX関数　に説明がありますので参照してください。
    • 構文：=MATCH(検査値,検査範囲,[照合の型])
      MATCH関数　に説明がありますので参照してください。
    =IF(G3="","",INDEX($B$3:$B$10,MATCH(ROW(A1),$E$3:$E$10,0)))
  • G3セルに
    =IF((COUNTIF($D$3:$D$10,
    ">="&LARGE($D$3:$D$10,3))>=ROW(A1)),
    ROW(A1)&"位","")
    と入力して、G8セルまでオートフィルなどで数式をコピーします。
  • H3セルに
    =IF(G3="","",
    INDEX($B$3:$B$10,MATCH(ROW(A1),$E$3:$E$10,0)))
    と入力して、H8セルまでオートフィルなどで数式をコピーします。
  • 
• 3位との同点までが5人いたら下図のように5位まで表示されます。
  この例では赤丸の得点を変更しています。
  
• 同点者がいないときは3位までが表示されます。
  この例では赤丸の得点を変更しています。
  

スポンサードリンク

PageViewCounter

Since2006/2/27