EXCELでベスト5の順位表を作る方法 Part 1

2018/08/02





ranking
学校の成績ベスト5、○○の都道府県別ベスト10。。。
人はこの世に生を受けたときから競うことを好み、順位付けをし、そのトップ、或いは上位にいることに喜びを感じ、周囲の者もその順位に関心を示してきました。

ちょっとおかしな調子で始まりましたが、日々データ分析に携わっていると、仕事でもなにかと「順位」「ベスト○位」を求められることがしばしばです。

というわけで、今回の h1r0-style.net では「Excelでベスト5をカンタンに抽出する方法」を2回にわたってピックアップします。
同点の場合の処理方法なども解説しますので是非読んでみてください。

エクセルで順位表を作ってみる

LARGE関数

まずは単にベスト5の数値を抽出する方法から説明します。

こんなときに使用するのが「LARGE関数」なのです。




LARGE関数とは

LARGE関数 とは「データの中で、大きい方から数えて○番目の値」を抽出してくれる便利な関数で;

=LARGE(範囲, 順位)

で表されます。

サンプルとして学校のテストの点数の一覧からベスト5の得点(数値)を抽出します。
下の図は、クラスの数学のテストの得点表です。

ranking1▲左側が名簿順に並んだ得点一覧表、右側がこれから作成するベスト5の順位表です。

得点一覧は40名分の得点が名簿順に並んでいるため、一見しただけではベスト5は分かりません。

そこでまず、順位表の1位のセル(F4)に LARGE関数 を入力します。
前述の例に習って;

=LARGE($C$4:$C$43, A4

と入力すると、セルF4の値は「100」と表示されました。

ranking2
つまり、40名中トップの得点は100点満点ということです。

Point2位以下のセルに数式をコピーする事を前提に、得点一覧の「得点」欄のセル範囲(青字部分)はズレないように“絶対参照”にしておくのがポイントです。
また、関数の中で順位を指定する部分(赤字部分)は、A列の番号を使用します。
順位表のE列に順位を表す数字を入れるとよいのですが、次に説明する「同点の生徒がいれば同順位とする」ためには最初から数字を振ることは不可能だからです。(この問題は関数でこのあと解決します!)

 
続いて、このセルF4の数式をセルF8までコピーします。

ranking3
すると簡単にベスト5の得点を抽出できました。
お気づきの方もいると思いますが、第1位の点数を求めるのは、MAX関数でも対応できます。
※2位以下には適用できません。

さて、なんとか順位表が出来たのですが、この順位表では上から3番目と4番目が97点で同点です。
同点の場合は同じ順位とする」ためにはどうすればいいのか?

次は順位の自動割振りを紹介します。


RANK.EQ関数 / RANK関数

RANK.EQ関数・RANK関数の使い方

順位表の中に同点が発生している場合、同じ順位にするにはどうすればいいか?

そこで使用するのが、RANK.EQ関数、そして、Excelのバージョンが2007以前の場合は RANK関数 です。

RANK.EQ関数 または RANK関数は、いずれも指定範囲中における任意の数値の順位(ランク)を返す関数で;

=RANK.EQ(数値, 範囲)
=RANK(数値, 範囲)

で表されます。

これを順位表の「順位」の列(E列)に入れていきます。
セルE4に入る数式としては、=RANK.EQ(F4, $F$4:$F$8)となります。
入力が完了すると、セルE4には首位を表す「1」が表示されました。

ranking4
※今度も 2位以下のセルに数式をコピーする事を前提に、「得点」欄のセル範囲(F4からF8)はズレないように 絶対参照 にしておくのがポイントです。

参考までに、RANK関数のバリエーションは以下のとおりです;

RANK.EQ : 複数の数値が同じ順位にある場合は、その値の中の最上位を返します
RANK.AVG: 複数の数値が同じ順位にある場合は、順位の平均値を返します
RANK  : Excel2007以前のバージョンに対応して、動きは RANK.EQ と同じ

 
ではこれらの情報を踏まえて、セルE4の数式をE8までコピーします。

ranking5
すると、3番目と4番目はどちらも「3」となり、同点のため同順位となります。
ちなみに、関数に RANK.AVG を使用すると、3と4の平均となり「3.5」と表示されます。

以上で同点を含む順位表の完成です。
次回、Part 2 では、順位表に「氏名」も表示するための極上テクニックを紹介します。
おたのしみに。

関連記事



併せて読んでおくとさらに便利

EXCELでベスト5の順位表を作る方法 Part 2
MATCH関数・INDEX関数
Part 2 の今回は「氏名(項目)」と「得点(数値)」両方を表示する順位表の作り方を解説します。関数がいろいろ登場しますがステップ・バイ・ステップで図を入れながら解説します。


HLOOKUP関数とVLOOKUP関数の違いと使い方
HLOOKUP関数・VLOOKUP関数
「HLOOKUP関数」をご存知ですか? HLOOKUPとVLOOKUPとの違いについて解説します。HLOOKUPを使った複数条件に合致するデータの抽出方法や、HLOOKUPの[#N/A]エラーを非表示にする方法などなど。

エクセルで見やすいグラフ・きれいなグラフはこうして作る
エクセルグラフ
「見やすいシリーズ」第二弾!エクセルで見やすいグラフ・きれいなグラフの作り方。グラフがあることで、数値の差や変化などが視覚的に伝わることで資料を見る人が理解しやすくなります。

EXCELで 5段階評価(0.5刻み) を☆マークを使って表す方法
条件付き書式・REPT関数
「星みっつです!」なんて某料理番組のような評価をエクセルで表したい。しかも、0.5刻みでひとつ「半」ふたつ「半」も表してみたい。エクセルで簡単に☆マークの評価表が作れます。

EXCELで点数に応じて相対評価 「上から〇%はA評価」をやってみる
ABC分析・PERCENTRANK関数・VLOOKUP関数
Excelを使って点数に応じた評価(相対評価)をする方法について解説。5段階評価もラクラク!PERCENTRANK関数とVLOOKUP関数を使って相対評価を成功させます。学校の成績表など「相対評価」で評価を...

※その他、関連記事はこの記事の下の方にもあります。

-EXCEL&VBA
-, , ,

シェアしていただけると助かります。