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

2018/02/03

top5

さて、前回の Part 1 ではクラスの数学の得点ベスト5を抽出した順位表を作りました。

ポイントは、「同点の場合は同順位とする」という点でした。
しかし、出来上がった順位表はベスト5の得点のみを表示したもので、「誰の」得点かが分からないものでした。

そこで、Part 2 の今回は「氏名(項目)」と「得点(数値)」両方を表示する順位表の作り方を解説します。
関数がいろいろ登場しますがステップ・バイ・ステップで図を入れながら説明するので頑張ってついてきてください。

COUNTIF関数 と RANK.EQ関数

ここでは Part 1 で作った順位表を引き続き使用します。

ranking6▲Part 1 でここまで作りました。
この記事から読み始めた方は、ぜひ Part 1 をご覧ください。
では始めましょう。



RANK.EQ関数

RANK.EQ関数は、例えばクラスの得点一覧の中でそれぞれの得点が全体の何番目になるか(順位)を表します。
※RANK関数のバリエーションについては、Part 1 参照。

まずは準備作業として、Part 1で作った順位表のD列に、40名それぞれの得点の順位を表示させます。
ここでは、セルD4に入る数式を;
=RANK.EQ(C4, $C$4:$C$43)+ COUNTIF($C$4:C4, C4)-1
とします。
ranking7▲セルD4に「38(位)」が表示された
※D列の見た目が良くないので、最終的には仕上げにフォントの色を白にして見えなくします。

COUNTIF関数

上のセルD4の数式の COUNTIF関数 の部分に「???」となった方もいるかもしれませんね。

PointPart1 の流れではセルD4の数式を「=RANK.EQ(C4, $C$4:$C$43)」としてしまいそうですが、これでは「同点の場合は同順位」になってしまうため、今回の準備作業には適していません。
今回の準備作業では個人を識別するためにインデックスとして使用したいので、便宜的に「同点の場合も行が上の人を上位」にします。

セルD4の数式の COUNTIF関数 の部分では範囲の「始点」は絶対参照 ($C$4)、「終点」は相対参照 (C4)として得点の登場回数によって順位を「+1」することで同順位になることを回避します。

 
そして、セルD4の数式を40名分下の行にコピーして、各々の順位を表示させます。

INDEX関数 と MATCH関数

つづいて、2つめの準備作業として、G列に順位表の氏名欄を追加設置します。
ranking9▲G列に氏名欄を追加しました

ここからが本題です。
各ランキングの順位に対応した「氏名」をG列に表示させるには、2つの関数のコンビネーションが必要です。
セルG4にはこのような数式が入ります;

=INDEX($B$4:$B$43, MATCH(A4, $D$4:$D$43, 0)

MATCH関数 を補助的につかって INDEX関数 で正しい「氏名」を導き出します。
ranking10▲セルG4には第1位の「吉田」さんの名前が表示されました。

MATCH関数

まず、補助的に使われた MATCH関数 から説明します。
MATCH関数は「指定したデータが表の何番目にあるか」を調べるときに使います;

=MATCH(検査値, 検査範囲, [照合の型] )

こんな感じで表されます。

セルG4での MATCH関数 の役割は「1位の人(=D列が“1”の人)が得点一覧表の何番目に位置しているか?」をつきとめることです。
実際に、セルG4に「=MATCH(A4, $D$4:$D$43, 0)」だけを入れてみたところ、答えは「37」が表示されました。
つまり、1位の人は一覧表の37番目にいる、ってことです。


INDEX関数

つぎに、INDEX関数 を説明します。
INDEX関数は「指定したセル範囲の中から〇行(△列)目にあるデータを抽出」するときに使います。
数式としては;

=INDEX(参照, 行番号, [列番号] , [領域番号])

のように表されます。

今回セルG4に入力した INDEX関数 においては、[列番号] 以降を使用していません。
「参照」の部分が得点一覧の氏名欄で、「行番号」が MATCH関数 となっています。
セルG4においては、MATCH関数の部分の答えは「37」ということがわかっているので、つまり数式を翻訳すると;
得点一覧表の氏名欄の中で 37番目の氏名(つまり1位の人)は?」と言い換えることができます。

では、セルG4の数式をG8までコピーします。
ranking11
これで、得点と氏名の両方を抽出した順位表の完成です。
最後にD列のフォントカラーを白にして仕上げです。
ranking12▲D列がきれいになりました



検証

試しに、一覧表の名簿No.4の江口さんの得点(セルC7)を98点に変更すると、江口さんがランキングの3位に自動で浮上してきました。
ranking13
以上で「項目と数値を抽出した順位表の作り方」の解説は終了です。

難しい関数が複数登場しましたが、ゆっくりやれば大丈夫です。
ぜひトライしてみてください。

関連記事



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

EXCELでベスト5の順位表を作る方法 Part 1
LARGE関数・RANK関数
学校の成績ベスト5、〇〇の都道府県別ベスト10。。。 日々データ分析に携わっていると、仕事でも何かと「順位」「ベスト〇位」を求められることがしばしばです。

EXCELの行や列を折りたたむ「グループ化」をマスターしよう
グループ化
行や列を折りたたむ「グループ化」の機能について解説。たたむ・展開するの切替えはワンタッチで簡単!この グループ化 で見た目もすっきり、そして機能的な作表に挑戦しましょう。

株価チャートの作り方|Excelで株価のグラフを作る
Excelグラフ
株価チャートの作り方と見方を図解します。株価のグラフがうまく作成できないという方は「表の並び」が良くないかもしれません。ローソク足の見方にも触れていますので、ぜひ当サイトをご一読ください。

オートフィルオプションの表示・非表示の切り替え|Excelのキホン
オートフィル
「オートフィルオプション」、使いこなしていますか?Excelを使っていると、セルの値や数式を下方向や横方向にコピー(オートフィル)する機会が頻繁に訪れますが、こんな時に役立つのが「オートフィルオプション」です。

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

-EXCEL&VBA
-, , , ,

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