当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。【Excel複数条件】RANK関数で同じ順位の場合に別の条件で順位を決定したい2019-09-07【Excel複数条件】RANK関数で同じ順位の場合に別の条件で順位を決定したいExcelRANK関数は順位を付ける関数です。MOSではめったに出題されませんが、一般的なExcelの検定では3級レベルです。いうわけで、初心者向けのRANK関数の詳しい説明については他のサイトに任せることにしたいと思います。ところで、RANK関数については不便なことが1つだけありまして・・・RANK関数は1つの列について、その順位を付けることはできますが、複数の列(複数条件)で順位を付けることができないのです。したがって、同じ数値の場合は必ず同じ順位となります。そこで、今回は、同じ順位だった場合に別の列の数値を参照して異なる順位を決定する方法について解説します。問題A列に10人分の得点を入力した。降順で順位を求めなさい。順位は他の人との比較で決まるものであって、自分の点数だけで求めることはできません。順位を求めるためには、自分の点数のほかに他人の点数も必要です。RANK関数では少なくとも2つの引数が必要です。=RANK(自分の点数,他人の点数)第1引数は自分の点数であり、第2引数は自分を含めた点数の範囲全体を指します。オートフィルをすることを考慮してこの範囲は絶対参照にします。大きい得点から順に順位が付きます。これを降順の順位といいます。同点の場合は同じ順位となります。逆にゴルフのスコアや陸上競技のタイムのように、小さいほうが良い場合は昇順で順位を求めます。昇順の場合は第3引数を1にします。=RANK(自分の点数,他人の点数,1)2.順位に応じてポイントを付ける問題A列に10人分の得点を入力して順位を求めた。(1)1位の者を10点、2位の者を9点、・・・10位の者を1点として、点数を求めなさい。(2)1位の者を10点、2位の者を5点、3位の者を2点、4位以下を0点として、点数を求めなさい。(3)1位の者を10点、2位の者を5点、3位の者を2点、4~8位の者を1点、9位以下を0点として、点数を求めなさい。順位に応じて点数に換算することがありますが、最も簡単な方法は人数から順位を引くことです。11から順位を引けば1位の人が10点となります。人数が増えたら計算式を変えなければなりません。次に1~3位の人だけポイントを与えてそれ以外は0点とする場合、IFERRORとCHOOSEを使います。=IFERROR(CHOOSE(順位,1位のポイント,2位のポイント,3位のポイント),0)さらに、4~8位の者を1点とする場合、IFを使います。=IFERROR(CHOOSE(順位,1位のポイント,2位のポイント,3位のポイント),IF(順位>=8,1,0))3.RANK関数で複数条件の順位を求める問題B列に10人分の得点(整数)を入力したので、C列に降順の順位を求めなさい。同点の場合は、前回順位が上位だった者を上位とし、前回順位も同じ場合は今回も同じ順位とする。解説(1)ポイント制まず、前回の順位を10から引いてポイントに換算したとします。ウェイト(比重)を下げるため、100で割ります。今回の得点と順位点を足すことによって、整数部分は今回の得点で、小数部分が順位点となります。今回が同点だった場合、小数部分の順位点の差で順位を決めることができます。総得点=今回得点+(10-前回順位)/100RANKで順位を求めます。(2)減点法ポイント制の場合は順位点を加点しましたが、逆に、順位の低い者を減点するという方法もあります。同点だった場合は、小数部分の差で減点の少ないほうが上位になります。総得点=今回得点-前回順位/100RANKで順位を求めます。ポイント制や減点法の場合、人数が増えたら数式を見直す必要があります。そこで、人数が増えた場合に備えて別の場所に人数(=COUNT)を求めておいたとします。この場合は人数またはその10倍で割ればよいです。人数に関係なく対応する方法として逆数を用いる方法もあります。逆数を10で割ることによって、総得点の小数部分が前回順位の逆数となり逆の順番でポイントを与えたのと同じになります。この方法であれば人数が増えても対応できます。総得点=今回得点+1/前回順位/104.COUNTIF・COUNTIFS関数で順位を求める問題さきほどの問題について、COUNTIF関数を用いて今回の得点の順位を求めなさい。同じ得点の人で、前回順位が高い人が何人いるかを求めなさい。降順の順位は自分より高い点数の人をカウントしているのと同じです。この考え方は情報処理の国家試験等でも出題される重要な知識です。IF(他人>自分)自分の順位に1を加算COUNTIF関数を用いてカウントします。1を足せば順位になります。同じ点数の人は同じ順位になります。これはRANK関数と同じです。=COUNTIF(他人の点数,">"&自分の点数)+1次に、同じ得点の人をカウントするのもCOUNTIFですが、自分より前回順位が高い(=順位の数値が小さい)だけカウントするのでCOUNTIFSを使います。=COUNTIFS(他人の点数,自分の点数,他人の順位,"<"&自分の順位)これらを足すことによって、前回順位を加味した順位を求めることができます。5.SUMPRODUCT・MMULT関数で順位を求めるRANKの考え方とCOUNTIFの考え方を合わせて考えたら、SUMPRODUCT関数などで求めることも可能です。配列を用いて全員のポイントを求めたうえで、自分のポイントより高い人をカウントします。大きい人を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解説は以上です。わえなびチャンネルhttp://www.youtube.com/c/waenavijpチャンネル登録をすると最新の動画をチェックできます。初心者向けの動画も大量にあります。平成三十年八月]スターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。