RANK関数で同じ順位が存在しないようにする方法

こんにちは。ユージーン(@Eugene_no2)です!

このサイトでは、仕事を通じて身につけたExcelやPPTの便利技の紹介もしています。

今回は、ExcelのRANK関数で、同じ順位が出てこないようにする方法です。

RANK関数で同じ順位が出るのを防ぎたい

RANK関数はその名の通り、範囲内の数値を大きい順にランキングしてくれる、非常に便利な関数です。

例えば、学校の試験の点数を、得点が高かった学生から順番に並べる、なんてことが簡単にできるようになります。

ところが、もし同じ得点の学生がいた場合、RANK関数を使うと、その2人には同じ順位が与えられるという特徴があります。

RANK関数では、同じ数字なら同じ順位

同じ得点なんだから、もちろん同じ順位でも構わないのですが、同じ順位が存在することによる不都合もあります。

例えば、RANK関数を使って判定した順位をもとに、VLOOKUP関数を使って、別なランキング表を作ろうとした場合に、困ったことが起こるのです。

先ほどの表では、小池さんと増田さんが同じ得点なので、RANK関数で出てくる結果は、どちらも「」となります。

では、この順位を使って、そのとなりにあるランキング表を完成させてみましょう。

VLOOKUP関数を使ってランキング表を完成させよう

VLOOKUP関数を使い、順位を検索値にして、名前と得点を引っ張ってくることが出来ます。

今回の例では、1行目に列番号用の数値を打った上で、G3列に次のように関数を入力します。

ランキング表の作成に失敗!

=VLOOKUP($F3,$B:$D,G$1,FALSE)

ちなみに、このVLOOKUP関数の書き方に疑問を持たれた方は「vlookup関数で複数列のデータを一気に取得する方法」で詳しく解説しているので、合わせてどうぞ。

さて、VLOOKUP関数で数字を飛ばした結果を見ると、元のデータに4位と6位が存在していないので「#N/A」が出てきてしまうのです。

RANK関数で判定した範囲の中に、同じ順位が入っていると、その次の順位がブランクになるために、必ずこのようなことが起こってしまいます。

なので、たとえ同点がいた場合でも、同じ順位が入らないようにする必要があります。

そんなこと出来るの?って思うかも知れませんが、関数を工夫すれば出来るんです!

ちなみに、手入力で順位を修正するのは、マジでオススメできません。

面倒ですし、次回同じファイルを使って別のランキング表を作ろうとすれば、かなりの確率でミスが起こりますから。

RANK関数で同順位を出さない裏技

では、ここからはRANK関数で、範囲内に同点のセルがあっても、同じ順位を出来ないようにする方法を紹介します。

まず、先に正解の関数を書きます。B3セルに入れるべき関数は、次の通り。

=RANK(D3,$D$3:$D$10)+COUNTIF(D3:$D$10,D3)-1

VLOOKUPとCOUNTIFを組み合わせた関数のサンプル

意味が分からなくても大丈夫。きちんと解説します。

このように関数を書けば、同じ得点でも順位が被ることなく、1位~最下位まで順位を振れるので、知っておくと便利な方法です。

この関数で同じ順位が出てこなくなる理由

ではまず、このちょっと複雑に見える関数が、いったい何をやろうとしているのかについて、分かりやすく説明を試みます。

まず、前半部分。

RANK(D3,$D$3:$D$10)

この部分は、先ほどと何も変わらない、普通のRANK関数です。ただ、これだと同じ順位が出てきてしまいます。

そこで大事なのが、後半部分。

+COUNTIF(D3:$D$10,D3)-1

これは何かというと、「そのセルから下に、同じ数字が何個あるかを数えて、その数を順位に足せ!」と命令しているのです。

COUNTIF関数の解説

例えば、B3セルに入力する関数では、D3からD10セルまでの間に、64という数字がいくつあるかを数えるのです。

この場合は見ての通り、64は2個ありますね。

関数は最後に「-1」をしているので、結局のところ、D3セルには、普通のRANK関数で出てくる数字+1が入ることになります。

つまり、3行目の小池さんの順位は、普通のRANK関数なら「」が入るところですが、この関数では「」が入るということ。

関数による違い

この関数、やってみると分かりますが、4行目の増田さんの順位は、どちらの関数でも同じ「」が入るんです。

関数の違いその2

なぜなら、増田さんより下には、64という数字は無いので、「COUNTIF(省略)-1」の部分は都合「」になり、何も足されないから。

このやり方を知ったときはわたしも感動しましたが、これなら同じ順位が入ることがないんです!

もちろん、同じ数字が3つ以上あったとしても、+2されるセル、+1されるセル、何も足されないセルに分かれるので、結局数字は被りません。

COUNTIF関数の参照範囲を工夫する

この関数でポイントとなるのが、COUNTIF関数の参照範囲です。COUNTIF関数は、

=COUNTIF(範囲, 検索条件)

で、指定した範囲内に、検索条件に当てはまるセルが何個あるかを数える便利な関数です。

そして、今回使用するCOUNTIF関数でのポイントは「そのセルより下に、同じ数がいくつあるか?」をカウントすることです。

それを実現するためには、

COUNTIF(D3:$D$10,D3)

というように、範囲の開始は「D3」というように絶対参照にせず、範囲の終了を「$D$10」というように絶対参照にしておくのです。

そうすると、セルを他の行にコピーしたときにも、「そのセルより下に、同じ数がいくつあるか?」を正しくカウントできますので。

さて、解説はここまでです。ご理解いただけましたか?

最後にもう一度、関数のサンプル(B3セル)を書いておきます。

=RANK(D3,$D$3:$D$10)+COUNTIF(D3:$D$10,D3)-1

RANK関数とCOUNTIF関数を駆使してランキング表完成!

ということで今回は、ExcelのRANK関数で同じ順位が存在しないようにする方法の解説でした。

他にもExcelやPPTの便利技について、書いてますのでもう1ページどうぞ!

 合わせて読みたい 


PPT・Excel : No.2宣言

Excelでセル内で改行する2つの方法/Excelグラフやセルで基準値以下の数字を非表示にする/1.2 − 1.1 は0.1 じゃない??/Excelで積み上げ縦棒グラフの合計値を表示させる方法…

では、今日も頑張らずに楽しんでいきましょう~!