RANK関数は順位を付ける関数です。MOSではめったに出題されませんが、一般的なExcelの検定では3級レベルです。というわけで、初心者向けのRANK関数の詳しい説明については他のサイトに任せることにしたいと思います。
ところで、RANK関数については不便なことが１つだけありまして・・・
RANK関数は1つの列について、その順位を付けることはできますが、複数の列（複数条件）で順位を付けることができないのです。したがって、同じ数値の場合は必ず同じ順位となります。
そこで、今回は、同じ順位だった場合に別の列の数値を参照して異なる順位を決定する方法について解説します。
目次
- １．RANK関数の復習
- ２．順位に応じてポイントを付ける
- ３．RANK関数で複数条件の順位を求める
- ４．COUNTIF・COUNTIFS関数で順位を求める
- ５．SUMPRODUCT・MMULT関数で順位を求める
１．RANK関数の復習
問題
A列に10人分の得点を入力した。降順で順位を求めなさい。
解説
順位は他の人との比較で決まるものであって、自分の点数だけで求めることはできません。順位を求めるためには、自分の点数のほかに他人の点数も必要です。RANK関数では少なくとも2つの引数が必要です。
- =RANK(自分の点数,他人の点数)
第1引数は自分の点数であり、第2引数は自分を含めた点数の範囲全体を指します。ただし、オートフィルをすることを考慮してこの範囲は絶対参照にします。
大きい得点から順に順位が付きます。これを降順の順位といいます。同点の場合は同じ順位となります。
逆にゴルフのスコアや陸上競技のタイムのように、小さいほうが良い場合は昇順で順位を求めます。昇順の場合は第3引数を1にします。
- =RANK(自分の点数,他人の点数,1)
２．順位に応じてポイントを付ける
問題
A列に10人分の得点を入力して順位を求めた。
（１）1位の者を10点、2位の者を9点、・・・10位の者を1点として、点数を求めなさい。
（２）1位の者を10点、2位の者を5点、3位の者を2点、4位以下を0点として、点数を求めなさい。
（３）1位の者を10点、2位の者を5点、3位の者を2点、4～8位の者を1点、9位以下を0点として、点数を求めなさい。
解説
順位に応じて点数に換算することがありますが、最も簡単な方法は人数から順位を引くことです。例えば、11から順位を引けば1位の人が10点となります。ただし、人数が増えたら計算式を変えなければなりません。
- =11-順位
次に１～３位の人だけポイントを与えてそれ以外は0点とする場合、IFERRORとCHOOSEを使います。
- =IFERROR(CHOOSE(順位,1位のポイント,2位のポイント,3位のポイント),0)
さらに、4～8位の者を1点とする場合、IFを使います。
- =IFERROR(CHOOSE(順位,1位のポイント,2位のポイント,3位のポイント),IF(順位>=8,1,0))
３．RANK関数で複数条件の順位を求める
問題
B列に10人分の得点（整数）を入力したので、C列に降順の順位を求めなさい。ただし、同点の場合は、前回順位が上位だった者を上位とし、前回順位も同じ場合は今回も同じ順位とする。
解説
（１）ポイント制
まず、前回の順位を10から引いてポイントに換算したとします。さらに、ウェイト（比重）を下げるため、100で割ります。
今回の得点と順位点を足すことによって、整数部分は今回の得点で、小数部分が順位点となります。今回が同点だった場合、小数部分の順位点の差で順位を決めることができます。
- 総得点＝今回得点＋（10－前回順位）／100
RANKで順位を求めます。
（２）減点法
ポイント制の場合は順位点を加点しましたが、逆に、順位の低い者を減点するという方法もあります。同点だった場合は、小数部分の差で減点の少ないほうが上位になります。
- 総得点＝今回得点－前回順位／100
RANKで順位を求めます。
ポイント制や減点法の場合、人数が増えたら数式を見直す必要があります。そこで、人数が増えた場合に備えて別の場所に人数（=COUNT）を求めておいたとします。
この場合は人数またはその10倍で割ればよいです。
- 総得点＝今回得点＋（人数－前回順位）／人数／10
- 総得点＝今回得点－前回順位／人数／10
（３）逆数
人数に関係なく対応する方法として逆数を用いる方法もあります。逆数を10で割ることによって、総得点の小数部分が前回順位の逆数となり逆の順番でポイントを与えたのと同じになります。この方法であれば人数が増えても対応できます。
- 総得点＝今回得点＋1／前回順位／10
４．COUNTIF・COUNTIFS関数で順位を求める
問題
さきほどの問題について、COUNTIF関数を用いて今回の得点の順位を求めなさい。また、同じ得点の人で、前回順位が高い人が何人いるかを求めなさい。
解説
降順の順位は自分より高い点数の人をカウントしているのと同じです。この考え方は情報処理の国家試験等でも出題される重要な知識です。
- IF(他人＞自分）自分の順位に1を加算
COUNTIF関数を用いてカウントします。1を足せば順位になります。同じ点数の人は同じ順位になります。これはRANK関数と同じです。
- =COUNTIF(他人の点数,">"&自分の点数)+1
次に、同じ得点の人をカウントするのもCOUNTIFですが、自分より前回順位が高い（＝順位の数値が小さい）だけカウントするのでCOUNTIFSを使います。
- =COUNTIFS(他人の点数,自分の点数,他人の順位,"<"&自分の順位)
これらを足すことによって、前回順位を加味した順位を求めることができます。
５．SUMPRODUCT・MMULT関数で順位を求める
RANKの考え方とCOUNTIFの考え方を合わせて考えたら、SUMPRODUCT関数などで求めることも可能です。つまり、配列を用いて全員のポイントを求めたうえで、自分のポイントより高い人をカウントします。
- IF(他人のポイント＞自分のポイント）自分の順位に1を加算
例えば、減点法でポイントの総得点を求める場合は、配列数式で {=B$2:B$11-A$2:A$11*1%} となります。
よって、自分の点数 B2-A2*1% より大きい人をSUMPRODUCTで求めたらよいです。
- =SUMPRODUCT((他人>自分)*1)+1
- =SUMPRODUCT((B$2:B$11-A$2:A$11*1%>B2-A2*1%)*1)+1
この式をMMULTで簡単にすることもできます。
- =SUM((MMULT(A$2:B$11-A2:B2,{-0.01;1})>0)*1)+1
解説は以上です。
