【Excel関数】自動計算で成績順に並び替わるランキング表を作る

こんにちは、ふみです。みなさんは Microsoft Excel の関数を使って成績順に名簿を並び替えようと思ったことはありますか？

Excel は「ホーム」タブの「並び替えとフィルター」から並べる順番を指定すると簡単に並び替えができます。しかし、元のデータを崩せない時は、コピー＆ペーストしてから並び替えしなければならないので、面倒な上、手作業によるミスでデータの抜けが発生する可能性もあります。

今回は重複する成績を含むデータを Excel関数で並び替えて、ランキング表を表示させる方法を紹介します。

成績上位10名まで表示されるランキング表をつくる（10位は1人のみ）

例として、ある小学校の50m走の計測結果がB～C列に記載されている Excel データがあったとします。
ここではD列に途中計算の値としてランキング表の掲載順位を表示させます。

このデータを元にして成績上位10名が自動計算で表示されるランキング表をつくる手順は次のとおりです。

１．
セルD3 に次の数式を仮に設定する。（RANK関数の3番目の値は昇順の「1」で設定）

=RANK($C3,$C:$C,1)

これでセルD3 にセルC3 のタイムの順位が表示されました。
しかし、この数式で下までコピーした場合、同一順位のタイムは同じ順位が表示されてしまいます。したがって、この数値ではランキング表の掲載順位にすることができません。

２．
途中計算の値をタイムの順位からランキング表の掲載順位に変更する為、セルD3 に数式を追加して、次のように変更する。（COUNTIF関数の$の位置に注意）

=RANK($C3,$C:$C,1)+COUNTIF($C$3:$C3,$C3)-1

これでセルD4 に掲載順位が表示されました。数式の「COUNTIF($C$3:$C3,$C3)-1」はC列の3行目から同じ行までの間に重複するタイムがないときは 0 を、3行目までに同じ行以外に重複するタイムがあるときはその数を返すので、タイムが重複する毎に順位が1加算されます。
しかし、数式を変更すると上の画面のように表示形式が自動で変更されてしまう場合があります。
この場合は、マウスの右クリックから 「セルの書式設定」 > 「表示形式」タブを選択後、分類(C):を「標準」に変更して「OK」をクリックすると表示形式が元に戻ります。

３．
セルD3 をドラッグ＆ドロップで下までコピーする。

これでD列に全員のランキング表の掲載順位が表示されました。掲載順位はタイムの順位ではないので注意しましょう。

４．
F～H列に成績上位10名が表示される表枠を作成後、セルF3 に数値の「1」を入力する。（この数値はランキング表の掲載位置の計算に使用する）

５．
セルG3 に次の数式を設定する。（参照先のセルF3は絶対参照）

=INDIRECT("B"&MATCH($F$3+ROW()-3,$D:$D,0))

これでセルG3 にタイム1位の名前が表示されました。
数式の「$F$3+ROW()-3」は掲載順位を計算しています。「-3」は1位を掲載する列番号の負の数です。この掲載順位を検索値としてD列を検索してB列を戻り値にするのですが、検察値が戻り値よりも右側にある場合、VLOOKUP関数が使えません。その代わりとして、INDIRECT関数とMATCH関数の組み合わせを使います。

６．
セルG3 をドラッグ＆ドロップでセルH3 にコピーする。

７．
セルH3 の数式の記述を “B” から “C” に変更する。変更後の数式は次のとおり。

=INDIRECT("C"&MATCH($F$3+ROW()-3,$D:$D,0))

これで、D列を検索した戻り値の列がC列に変わったので、セルH3 に1位のタイムが表示されました。

８．
セルG3～セルH3をドラッグ＆ドロップで下までコピーする。

これで10位までの名前とタイムが表示されました。（10位は1人まで）
この時、セルの表示が元データと合っていなかった場合、マウスの右クリックから 「セルの書式設定」でセルの表示形式を変更しておきます。

９．
セルF4 に次の数式を設定する。

=RANK($H4,$C:$C,1)

これでセルF4 に掲載2位のタイムの順位が表示されました。
1位が2人いる場合、セルF4 は「1」が表示されます。

10.
セルF4 をドラッグ＆ドロップで下までコピーする。

これで成績上位10名まで表示されるランキング表が完成しました。
同タイムの人がいる場合は同じ順位が複数表示されます。
ただし、このままでは10位が1人までしか表示されません。10位が2人以上いるときは見落とさないように注意しましょう。ランキング表を手作業で下の方へコピーして拡張すると、2人目の10位の存在を確認できます。

成績10位が2人以上の時に自動表示される高度なランキング表にする

10位が2人以上いるときにランキング表に自動で表示させることもできますが、順位判定で非表示にする条件式を追加する必要がある為、数式はより複雑になります。

10位が2人以上いるときにランキング表に自動で表示させる手順は次のとおりです。

１．
本記事で紹介している「成績上位10名まで表示されるランキング表をつくる（10位は1人のみ）」の表を完成させる。

２．
セルG3 の数式を次のように変更する。（「<=10」はランキングに表示させる順位の条件）

=IF(RANK(INDIRECT("C"&MATCH($F$3+ROW()-3,$D:$D,0)),C:C,1)<=10,INDIRECT("B"&MATCH($F$3+ROW()-3,$D:$D,0)),"")

３．
セルH3 の数式を次のように変更する。

=IF($G3="","",INDIRECT("C"&MATCH($F$3+ROW()-3,$D:$D,0)))

４．
セルF4 の数式を次のように変更する。

=IF($G4="","",RANK($H4,$C:$C,1))

５．
セルG3～H3、セルF4の数式をそれぞれドラッグ＆ドロップで下までコピーする。

これで成績10位が2人以上の時に自動表示されるランキング表が完成しました。

上の画面では成績10位の表示は2名までですが、ランキング表を下の方へコピーで拡張すると、成績10位の表示数を増やすことができます。
ちなみに、次点さんのタイムを10.07に変更すると、9位が3人になり、次点さんもランキング表に表示されるようになります。

まとめ

• ランキング表をつくるには途中計算の値としてランキング表の掲載順位を表示させる
• ランキング表の掲載順位は RANK関数と COUNTIF関数を組み合わせる
• 10位が2人以上いるときは見落とさないように注意する
• 10位が2人以上いるときにランキング表に自動で表示させる場合、順位判定で非表示にする条件式を追加する必要がある

ありがとうございました。