こんにちは。ユージーン(@Eugene_no2)です！
このサイトでは、仕事を通じて身につけたExcelやPPTの便利技の紹介もしています。
今回は、ExcelのRANK関数で、同じ順位が出てこないようにする方法です。
スポンサーリンク
RANK関数で同じ順位が出るのを防ぎたい
RANK関数はその名の通り、範囲内の数値を大きい順にランキングしてくれる、非常に便利な関数です。
例えば、学校の試験の点数を、得点が高かった学生から順番に並べる、なんてことが簡単にできるようになります。
ところが、もし同じ得点の学生がいた場合、RANK関数を使うと、その２人には同じ順位が与えられるという特徴があります。
同じ得点なんだから、もちろん同じ順位でも構わないのですが、同じ順位が存在することによる不都合もあります。
例えば、RANK関数を使って判定した順位をもとに、VLOOKUP関数を使って、別なランキング表を作ろうとした場合に、困ったことが起こるのです。
先ほどの表では、小池さんと増田さんが同じ得点なので、RANK関数で出てくる結果は、どちらも「３」となります。
では、この順位を使って、そのとなりにあるランキング表を完成させてみましょう。
VLOOKUP関数を使い、順位を検索値にして、名前と得点を引っ張ってくることが出来ます。
今回の例では、１行目に列番号用の数値を打った上で、G3列に次のように関数を入力します。
=VLOOKUP($F3,$B:$D,G$1,FALSE)
ちなみに、このVLOOKUP関数の書き方に疑問を持たれた方は「vlookup関数で複数列のデータを一気に取得する方法」で詳しく解説しているので、合わせてどうぞ。
さて、VLOOKUP関数で数字を飛ばした結果を見ると、元のデータに４位と６位が存在していないので「#N/A」が出てきてしまうのです。
RANK関数で判定した範囲の中に、同じ順位が入っていると、その次の順位がブランクになるために、必ずこのようなことが起こってしまいます。
なので、たとえ同点がいた場合でも、同じ順位が入らないようにする必要があります。
そんなこと出来るの？って思うかも知れませんが、関数を工夫すれば出来るんです！
ちなみに、手入力で順位を修正するのは、マジでオススメできません。
面倒ですし、次回同じファイルを使って別のランキング表を作ろうとすれば、かなりの確率でミスが起こりますから。
スポンサーリンク
RANK関数で同順位を出さない裏技
では、ここからはRANK関数で、範囲内に同点のセルがあっても、同じ順位を出来ないようにする方法を紹介します。
まず、先に正解の関数を書きます。B3セルに入れるべき関数は、次の通り。
=RANK(D3,$D$3:$D$10)+COUNTIF(D3:$D$10,D3)-1
意味が分からなくても大丈夫。きちんと解説します。
このように関数を書けば、同じ得点でも順位が被ることなく、１位～最下位まで順位を振れるので、知っておくと便利な方法です。
この関数で同じ順位が出てこなくなる理由
ではまず、このちょっと複雑に見える関数が、いったい何をやろうとしているのかについて、分かりやすく説明を試みます。
まず、前半部分。
RANK(D3,$D$3:$D$10)
この部分は、先ほどと何も変わらない、普通のRANK関数です。ただ、これだと同じ順位が出てきてしまいます。
そこで大事なのが、後半部分。
+COUNTIF(D3:$D$10,D3)-1
これは何かというと、「そのセルから下に、同じ数字が何個あるかを数えて、その数を順位に足せ！」と命令しているのです。
例えば、B3セルに入力する関数では、D3からD10セルまでの間に、64という数字がいくつあるかを数えるのです。
この場合は見ての通り、64は２個ありますね。
関数は最後に「－１」をしているので、結局のところ、D3セルには、普通のRANK関数で出てくる数字＋１が入ることになります。
つまり、３行目の小池さんの順位は、普通のRANK関数なら「３」が入るところですが、この関数では「４」が入るということ。
この関数、やってみると分かりますが、４行目の増田さんの順位は、どちらの関数でも同じ「３」が入るんです。
なぜなら、増田さんより下には、64という数字は無いので、「COUNTIF(省略)-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
ということで今回は、ExcelのRANK関数で同じ順位が存在しないようにする方法の解説でした。
他にもExcelやPPTの便利技について、書いてますのでもう１ページどうぞ！
| 合わせて読みたい |
Excelの便利な活用術・実践的な工夫・小技まとめ 当ブログではたびたび、他のサイトには載っていないような、Excelの便利な活用方法や、実践的な工夫・小技などを紹介しています。 |
