エクセルで作業セルを使わずに重複しない順位を出す方法

応用ワザ
この記事は約8分で読めます。

エクセルではRANK関数を使って順位を求めることができますが、同じ数値がある場合は同じ順位となってしまいます。

そこで重複のない順位を出す方法については以前ご紹介しましたが、その方法は作業セルを使う方法でした。

作業セルを使わずに重複なし順位を求めることができれば、スマートだし、セルも汚さず、1セルで完結できるのになと思いませんか。

ということで、このページではエクセルで作業セルを使わずに重複しない順位を出す方法についてご紹介します。

スポンサーリンク

スグにためセル! – ここを読めばすぐ使える

エクセルで同じ数値がある場合でも重複しない順位を出すには、RANK関数とCOUNTIF関数を組み合わせることで、作業セルを使わずに、順位を求めることができます。

順位を求めたいセルに次の計算式を入力する。

=RANK(順位を求めたい数値,順位を求めるセルの範囲全体,順序)+COUNTIF(対象の範囲の一番上のセルから順位を求めたい数値のセルまでの範囲,順位を求めたい数値)-1

※順序は、最も大きい数字を1位としたい場合は「0」、最も小さい数字を1位としたい場合は「1」と入力する

重複なく順位を出す計算式の書き方の図

※ より詳しい解説はここから下に続きます。

スポンサーリンク
スポンサーリンク

RANK関数だけだと順位が重複してしまう

そもそもRANK関数で順位を求めると、同じ数値の場合は同じ順位になってしまいます。そして、その次の数値は、同じ順位になった数値の数だけ飛ばして、順位がつけられます

つまり、100点、50点、50点、50点、30点とある場合は、1位の次は、2位、2位、2位と同じ順位が続いて、その次は3位ではなく、5位となるわけです。

2位が3回続いて、次は5位の図

ここで、上の例の点数を取った人の名前をそれぞれAさんが50点、Bさんが100点、Cさんが50点、Dさんが30点、Eさんが50点としておきましょう。

そして、Aさん、Bさん・・・Eさんに、点数の順に並んでもらいます。

すると、Bさんが100点で1番目となります。

次は、50点ですが、50点はAさん、Cさん、Eさんと3人いますので、先に来た順(名前が上に書かれている順)に並べるとしましょう。

ということで、先に来たのはAさんです。Aさんはそのまま2番目の位置に並べます。

次に来たのがCさんです。Cさんも50点で2番目に並びたいところですが、すでにAさんがいますので、3番目となり、次に来たEさんも50点なので2番目に並びたいところですが、AさんとCさんが既に並んでいるため、4番目となります。最後にDさんは30点なので、A、C、Eさんの後ろの5番目ということになります。

重複のない順位ということは、この並んだ順番がそのまま順位となればいいわけです。

ここで、Cさんに着目しましょう。Cさんは50点で2位なので、2番目に並ぼうとしました。しかし、すでにAさんがいて、このとき同じ点数の人がすでに1人いたために、1つ下の3番目となりました。つまり、2番目に+1したということです。

次にEさんに着目すると、Eさんも50点で2位ですが、すでに同じ点数のAさん、Cさんの2人がいたため、2番目にの2つ下の4番目となりました。つまり、2番目に+2したということです。

このように、同じ点数の人が先にいる場合は、その人数を順位に足してあげればいいのです。

ここで、エクセルに話を戻すと、上で説明したことは、RANK関数で順位を求めて、同じ点数(数値)の人数を数えて、それを加えればいいのです。

ちなみに、ある特定の数値を数える関数、つまり条件付きで数える関数といえば・・・

そう!COUNTIF関数ですね。

スポンサーリンク
スポンサーリンク

COUNTIF関数を用いて同じ数値の数を加えればいい

先ほどのAさんたちの例では、Cさんが並ぼうとしたときに既に並んでいたBさんとAさんを対象として、Eさんが並ぼうとしたときはBさんとAさんとCさんを対象として、自分と同じ点数の人数を数えました。つまり、数える時点までに並んでいる人(順位が確定している人)を対象に同じ点数の人数を確認するのです。

ということで、COUNTIF関数を使う場合でも、数える範囲の最初はBさんで固定ですが、範囲の最後は自分よりひとつ前のセルである必要があり、入力するセルによって変動するということです。

そのため、COUNTIFの最初のセルには「$」をつけて絶対参照とし、最後のセルには「$」をつけずに相対参照にする必要があります。

エクセル関数として書くと次のような感じです。

=COUNTIF($範囲の最初:範囲の最後,人数を数える点数

先頭の人にとって、ひとつ前の人って・・・誰?

ただ・・・、ここで問題があります。

1位のBさんに着目してみてみましょう。Bさんが並ぼうとしたとき、まだ誰も並んでいません。そのため、同じ点数かどうかを確認する相手がいないのです。

COUNTIFで考えれば、確認する相手がいないということは、指定する範囲がないということです。

これでは困ります。

ということで、確認する相手に自分を含めましょう。そうすれば、最低でも確認する相手ができます。COUNTIFで考えると、その範囲はひとつ前のセルまでではなく、順位を確認したい数値のセルまで含めるということです。

ちなみに、自分は自分と必ず同じ点数になるわけですから、同じ点数の人が最低でも1人になってしまいます。そのため、あとで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点に対して3位と順位が導かれました。念のため、このB4セルについて、計算過程をチェックしてみましょう。

このセルの計算式は次のようになっています。

=RANK(A4,$A$2:$A$6,0)+COUNTIF($A$2:A4,A4)-1

前半のRANK関数の部分ですが、これは$A$2:$A$6の範囲内でA4である「50」の順位を計算します。その結果は2位となります。ということで、この時点で、計算式は次のようになります。

=2+COUNTIF($A$2:A4,A4)-1

そして、後半のCOUNTIFの部分ですが、数える範囲は狙い通りちゃんと動いています。B2セルに入力したときは、A2からA2となっていましたが、B4セルではA2からA4となっていますね。

ということで、A2からA4の中でA4と同じ50点となっているものを数えると2となります。この時点で、計算式は次のようになります。

=2+2-1

その結果、B4の答えは3となり、重複しない順位を求めることができました。

スポンサーリンク
スポンサーリンク

COUNTIFは遅くなることがある・・・

冒頭にも書いたように、重複しない順位を求める方法としては、作業セルを使う方法もご紹介しました。でも、このページのように作業セルを用いなくても重複なし順位が求められるのであれば、作業セルを用いた方法はいらないのでは?と思いますよね。

しかし、作業セルを用いた方法にもメリットがあるのです。

というのも、COUNTIF関数を多用するとエクセルが遅くなることがあるのです。

ま、多用しなければ問題はありませんので、その場合はこのページでご紹介した作業セルを使わない方法で重複なし順位を求めればよいです。その方がスマートで、セルを汚すこともなく、1セルで完結させることができるからです。

ただ、エクセルが重いなと感じたときは、作業用セルを使う方法を採用すればいいと思います。

スポンサーリンク
スポンサーリンク

まとめ

作業用セルを用いずに重複なし順位を求めるには、RANK関数で通常の順位を求め、COUNTIF関数でその時点で同じ数値の数を数えて順位に加えてあげればよいということがわかったかと思います。

また、作業用セルを用いた方法とそうでない場合のメリット、デメリットもご紹介しました。

重複なし順位を求めることは、あまりないかもしれませんが、意外に使うことがあるのです。

ということで、このページではエクセルで作業セルを使わずに重複しない順位を出す方法をご紹介しました。

タイトルとURLをコピーしました