エクセルではRANK関数を使って順位を求めることができますが、同じ数値がある場合は同じ順位となってしまいます。
そこで重複のない順位を出す方法については以前ご紹介しましたが、その方法は作業セルを使う方法でした。
作業セルを使わずに重複なし順位を求めることができれば、スマートだし、セルも汚さず、1セルで完結できるのになと思いませんか。
ということで、このページではエクセルで作業セルを使わずに重複しない順位を出す方法についてご紹介します。
スグにためセル！ – ここを読めばすぐ使える
エクセルで同じ数値がある場合でも重複しない順位を出すには、RANK関数とCOUNTIF関数を組み合わせることで、作業セルを使わずに、順位を求めることができます。
順位を求めたいセルに次の計算式を入力する。
=RANK(順位を求めたい数値,順位を求めるセルの範囲全体,順序)+COUNTIF(対象の範囲の一番上のセルから順位を求めたい数値のセルまでの範囲,順位を求めたい数値)-1
※順序は、最も大きい数字を1位としたい場合は「0」、最も小さい数字を1位としたい場合は「1」と入力する
※ より詳しい解説はここから下に続きます。
RANK関数だけだと順位が重複してしまう
そもそもRANK関数で順位を求めると、同じ数値の場合は同じ順位になってしまいます。そして、その次の数値は、同じ順位になった数値の数だけ飛ばして、順位がつけられます。
つまり、100点、50点、50点、50点、30点とある場合は、１位の次は、２位、２位、２位と同じ順位が続いて、その次は３位ではなく、５位となるわけです。
ここで、上の例の点数を取った人の名前をそれぞれAさんが50点、Bさんが100点、Cさんが50点、Dさんが30点、Eさんが50点としておきましょう。
そして、Aさん、Bさん・・・Eさんに、点数の順に並んでもらいます。
すると、Ｂさんが100点で１番目となります。
次は、50点ですが、50点はＡさん、Ｃさん、Ｅさんと３人いますので、先に来た順（名前が上に書かれている順）に並べるとしましょう。
ということで、先に来たのはＡさんです。Ａさんはそのまま2番目の位置に並べます。
次に来たのがCさんです。Cさんも50点で２番目に並びたいところですが、すでにＡさんがいますので、３番目となり、次に来たＥさんも50点なので2番目に並びたいところですが、ＡさんとＣさんが既に並んでいるため、４番目となります。最後にＤさんは30点なので、Ａ、C、Ｅさんの後ろの5番目ということになります。
重複のない順位ということは、この並んだ順番がそのまま順位となればいいわけです。
ここで、Ｃさんに着目しましょう。Ｃさんは50点で２位なので、２番目に並ぼうとしました。しかし、すでにＡさんがいて、このとき同じ点数の人がすでに１人いたために、１つ下の３番目となりました。つまり、２番目に＋１したということです。
次にＥさんに着目すると、Ｅさんも50点で２位ですが、すでに同じ点数のＡさん、Cさんの２人がいたため、２番目にの２つ下の４番目となりました。つまり、２番目に＋２したということです。
このように、同じ点数の人が先にいる場合は、その人数を順位に足してあげればいいのです。
ここで、エクセルに話を戻すと、上で説明したことは、RANK関数で順位を求めて、同じ点数（数値）の人数を数えて、それを加えればいいのです。
ちなみに、ある特定の数値を数える関数、つまり条件付きで数える関数といえば・・・
そう！COUNTIF関数ですね。
COUNTIF関数を用いて同じ数値の数を加えればいい
先ほどのＡさんたちの例では、Ｃさんが並ぼうとしたときに既に並んでいたＢさんとAさんを対象として、Ｅさんが並ぼうとしたときはＢさんとAさんとＣさんを対象として、自分と同じ点数の人数を数えました。つまり、数える時点までに並んでいる人（順位が確定している人）を対象に同じ点数の人数を確認するのです。
ということで、COUNTIF関数を使う場合でも、数える範囲の最初はＢさんで固定ですが、範囲の最後は自分よりひとつ前のセルである必要があり、入力するセルによって変動するということです。
そのため、COUNTIFの最初のセルには「$」をつけて絶対参照とし、最後のセルには「＄」をつけずに相対参照にする必要があります。
エクセル関数として書くと次のような感じです。
=COUNTIF($範囲の最初:範囲の最後,人数を数える点数）
先頭の人にとって、ひとつ前の人って・・・誰？
ただ・・・、ここで問題があります。
１位のＢさんに着目してみてみましょう。Ｂさんが並ぼうとしたとき、まだ誰も並んでいません。そのため、同じ点数かどうかを確認する相手がいないのです。
COUNTIFで考えれば、確認する相手がいないということは、指定する範囲がないということです。
これでは困ります。
ということで、確認する相手に自分を含めましょう。そうすれば、最低でも確認する相手ができます。COUNTIFで考えると、その範囲はひとつ前のセルまでではなく、順位を確認したい数値のセルまで含めるということです。
ちなみに、自分は自分と必ず同じ点数になるわけですから、同じ点数の人が最低でも1人になってしまいます。そのため、あとで１を引く必要がありますので、注意しましょう。
実際にタメしてみましょう
それでは、実際にエクセル上で試してみましょう。
下の図のようにA列に先ほどの例のように50、100、50、30、50と数字を入力しています。
B列に重複しない順位を求めてみます。
B2セルに次のように入力します。
=RANK(A2,$A$2:$A$6,0)+COUNTIF($A$2:A2,A2)-1
これまで説明したことと同じです。
まずはRANK関数で通常のように順位を求めます。
そして、COUNTIF関数でこのセルと同じ数値がいくつ出ているのかを数えます。
数える範囲は変動させる必要がありますが、先頭のセルは固定にしたいので「$」をつけています。最後のセルは動いてほしいので「$」はつけていません。また、数える範囲が最低でも1セル以上となるよう、自分のセルも含めていますが、この時点では開始のセルも終了のセルもA2なので、「$A$2:A2」という形になっています。
そして、最後に自分を除くために、-1するのをお忘れなく。
これを下までコピーすると・・・
きちんと重複しない順位が表示されましたね。
計算過程をチェックしてみよう
B4セルは、点数が50点に対して３位と順位が導かれました。念のため、このB4セルについて、計算過程をチェックしてみましょう。
このセルの計算式は次のようになっています。
=RANK(A4,$A$2:$A$6,0)+COUNTIF($A$2:A4,A4)-1
前半のRANK関数の部分ですが、これは$A$2:$A$6の範囲内でA4である「50」の順位を計算します。その結果は２位となります。ということで、この時点で、計算式は次のようになります。
=2+COUNTIF($A$2:A4,A4)-1
そして、後半のCOUNTIFの部分ですが、数える範囲は狙い通りちゃんと動いています。B2セルに入力したときは、A2からA2となっていましたが、B4セルではA2からA４となっていますね。
ということで、A2からA4の中でA4と同じ50点となっているものを数えると2となります。この時点で、計算式は次のようになります。
=2+2-1
その結果、B4の答えは３となり、重複しない順位を求めることができました。
COUNTIFは遅くなることがある・・・
冒頭にも書いたように、重複しない順位を求める方法としては、作業セルを使う方法もご紹介しました。でも、このページのように作業セルを用いなくても重複なし順位が求められるのであれば、作業セルを用いた方法はいらないのでは？と思いますよね。
しかし、作業セルを用いた方法にもメリットがあるのです。
というのも、COUNTIF関数を多用するとエクセルが遅くなることがあるのです。
ま、多用しなければ問題はありませんので、その場合はこのページでご紹介した作業セルを使わない方法で重複なし順位を求めればよいです。その方がスマートで、セルを汚すこともなく、1セルで完結させることができるからです。
ただ、エクセルが重いなと感じたときは、作業用セルを使う方法を採用すればいいと思います。
まとめ
作業用セルを用いずに重複なし順位を求めるには、RANK関数で通常の順位を求め、COUNTIF関数でその時点で同じ数値の数を数えて順位に加えてあげればよいということがわかったかと思います。
また、作業用セルを用いた方法とそうでない場合のメリット、デメリットもご紹介しました。
重複なし順位を求めることは、あまりないかもしれませんが、意外に使うことがあるのです。
ということで、このページではエクセルで作業セルを使わずに重複しない順位を出す方法をご紹介しました。
