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

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

更新:2024/12/15;作成:2012/8/9

問題1の解答例　　　　topへ

• 解答
  

  	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.EQ関数を使って求める

• Excel2010ではRANK.EQ関数が使用できます。 RANK関数と同じ結果が得られます。
• E3セルは　=RANK.EQ(D3,$D$3:$D$10)　となります。
• RANK.EQ関数の構文：RANK.EQ(数値,範囲,[順序])

Spill(スピル)が使える場合(Microsoft365,Excl2021以降)

• E3セルに　=RANK(D3:D10,D3:D10)　と入力します。
  引数のセル範囲は相対参照でOKです。
  

COUNTIF関数を使って求める

• 構文：=COUNTIF(範囲,検索条件)
  COUNTIF関数　に説明がありますので参照してください。
• 求めたい得点よりも大きな値が何個あるか調べれば、求めたい得点の順位はその個数+1となります。
  よって　=COUNTIF($D$3:$D$10,">"&D3)+1　でも求めることができます。
• COUNTIF関数の検索条件で比較演算子とセル参照を組み合わせて使う時は比較演算子とセル参照を&でつなぎます。
  • Spillが使える場合は　=COUNTIF(D3:D10,">"&D3:D10)+1　でOKです。
  
  

問題2の解答例　　　　topへ

• 解答
  

  	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

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関数　に説明がありますので参照してください。
• F3セルに　=RANK(E3,$E$3:$E$10)　と入力し、下方向へ数式をコピーします。
  

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

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

問題3の解答例　　　　topへ

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

• Excel for Microsoft365で使用できるGROUPBY関数
  Excel2024以降で使用できるHSTCK関数、CHOOSECOLS関数
  などを使ってデータを抽出することができます。
• G3セルには以下の数式を入力しています。
  =CHOOSECOLS(
  GROUPBY(HSTACK((E3:E10)&"位",B3:B10),D3:D10,MAX,,,,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)&"位","")
    H3セルに
    =IF(G3="","",
    INDEX($B$3:$B$10,MATCH(ROW(A1),$E$3:$E$10,0)))
  • 
• 3位との同点までが5人いたら下図のように5位まで表示されます。
  この例では赤丸の得点を変更しています。
  
• 同点者がいないときは3位までが表示されます。
  この例では赤丸の得点を変更しています。
  

スポンサードリンク

PageViewCounter

Since2006/2/27