関数 EXCEL&VBA

エクセルでベスト5の順位表の作り方　Part 2

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

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

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



COUNTIF関数 と RANK.EQ関数

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


▲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

とします。


▲セルD4に「38(位)」が表示された
※D列の見た目が良くないので、最終的にはフォントの色を白にして隠します。

COUNTIF関数

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

PointPart1 の流れではセルD4の数式を「=RANK.EQ（C4, $C$4:$C$43）」としてしまいそうですが、これでは「同点の場合は同順位」になってしまうため、今回の準備作業には適していません。
今回の準備作業では個人を識別するためにインデックスとして使用したいので、便宜的に「同点の場合も行が上の人を上位」にします。セルD4の数式の COUNTIF関数 の部分では範囲の「始点」は絶対参照 ($C$4)、「終点」は相対参照 (C4)として得点の登場回数によって順位を「+1」することで同順位になることを回避します。

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


▲D列に40名分の順位が表示された

INDEX関数 と MATCH関数

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


▲G列に氏名欄を追加しました

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

=INDEX（$B$4:$B$43, MATCH（A4, $D$4:$D$43, 0））

MATCH関数 を補助的につかって INDEX関数 で正しい「氏名」を導き出します。


▲セルG4には第1位の「吉田」さんの 名前が表示されました。

MATCH関数

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

=MATCH（検査値, 検査範囲, [照合の型] ）

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

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

INDEX関数

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

=INDEX（参照, 行番号, [列番号] , [領域番号]）

のように表されます。

今回セルG4に入力した INDEX関数 においては、[列番号] 以降を使用していません。
「参照」の部分が得点一覧の氏名欄で、「行番号」が MATCH関数 となっています。

セルG4においては、MATCH関数の部分の答えは「37」ということがわかっているので、つまり数式を翻訳すると；
「得点一覧表の氏名欄の中で 37番目の氏名（つまり1位の人）は？」と言い換えることができます。

では、セルG4の数式をG8までコピーします。

これで、得点と氏名の両方を抽出した順位表の完成です。
最後に、D列のフォントカラーを「白」にして仕上げでます。


▲D列がきれいになりました

検証

試しに、一覧表の名簿No.4の江口さんの得点（セルC7）を98点に変更すると、江口さんがランキングの3位に自動で浮上してきました。


▲江口さん、急浮上！

以上で「エクセルでベスト5の順位表の作り方」の解説は終了です。

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

関連記事・広告



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

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

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




-関数, EXCEL&VBA
-, , , ,