【Excel】エクセルで順位を抽出して名前を表示する方法（上位データ・別シート・関数）

Excelで売上や得点の表を管理していると、上位の順位だけを抽出し、その順位に対応した名前を一覧で表示したい場面があります。

順位の数値だけならRANK関数で求められますが、名前まで自動表示するには、順位・数値・氏名の対応を崩さない関数の組み合わせが重要です。

本記事では、元データが1行目に見出しを持つ表を前提に、上位データの抽出、同順位への対応、別シートへの表示までを順番に解説します。

数式をコピーしても参照範囲がずれないよう、絶対参照と相対参照の違いも確認しておきましょう。

 

順位から名前を表示する基本手順

それではまず、順位を指定して該当する名前を表示する基本の数式について解説していきます。

A列	B列	C列
氏名	売上	順位
佐藤	98000	1
鈴木	84500	2
田中	71300	3

 

RANK.EQ関数による順位の作成

まずは元データに順位列を作成します。

たとえばA列に氏名、B列に売上があり、C列へ順位を表示する場合、C2セルに次の数式を入力します。

3番目の引数を0にすると、売上が大きい人ほど小さい順位になります。

B2は行ごとに変わる相対参照、$B$2:$B$10は比較範囲を固定する絶対参照です。

入力後、C2セル右下のフィルハンドルを下方向へドラッグすると、各人の順位をまとめて求められます。

売上の最大値が1位になり、2番目に大きい値が2位になるため、上位者の確認にも便利です。

 

INDEX関数とMATCH関数による氏名の検索

順位の入力欄をE2セルに用意し、たとえば1を入力したときに氏名をF2セルへ表示する方法を見ていきましょう。

F2セルには次の数式を入力します。

INDEX関数は指定された位置にある値を返し、MATCH関数は検索値が範囲内の何番目にあるかを返す関数です。

この式ではMATCH関数がC列の順位から位置を探し、その位置の氏名をINDEX関数がA列から取り出します。

E2に1を入力すれば1位の氏名、2を入力すれば2位の氏名が表示されます。

順位の列と氏名の列は同じ行数でそろえることが、正しく検索するための条件です。

 

上位順位を連続表示する数式のコピー

1位から5位までを縦に一覧化したい場合は、E2からE6に1、2、3、4、5と入力します。

F2セルの数式をF6セルまでコピーすれば、各順位に対応した名前が自動で表示されます。

順位を手入力せずに作成するなら、E2セルへ次の数式を入れて下へコピーする方法もあります。

ROWS関数は範囲に含まれる行数を数えるため、最初のセルでは1、次のセルでは2となります。

順位番号を数式で連番化しておくと、上位人数を増やす作業も簡単です。

 

上位データをLARGE関数で抽出する方法

続いては、順位列を作らずに売上などの上位データを取り出し、名前を表示する方法を確認していきます。

氏名	売上	抽出順位
佐藤	98000	1
鈴木	84500	2
田中	71300	3

 

LARGE関数による上位の数値取得

LARGE関数は、範囲内で何番目に大きい値かを返す関数です。

売上がB2からB10にある場合、E2セルに1位の売上を表示する数式は次のとおりです。

D2セルに1を入力すると最大値、2を入力すると2番目に大きい値が返されます。

順位番号をD2から下へ並べ、式もコピーすれば、上位5件や上位10件の売上を短時間で作成できます。

LARGE関数は元表の並び順に関係なく、数値の大きさだけで上位を判定します。

得点、販売数、アクセス数、評価点など、数値で比較できるデータに幅広く利用できます。

 

上位数値に対応する名前の表示

E2セルに抽出した上位売上があるなら、F2セルには次の数式を入力して氏名を表示します。

MATCH関数が上位売上と同じ数値をB列で検索し、INDEX関数が同じ位置にあるA列の氏名を返します。

この形ならC列に順位を用意しなくても、上位の数値と氏名を別の一覧として作成できます。

元データを並べ替えずにランキング表を作れる点が、この方法の大きな利点です。

ただし、同じ売上が複数ある場合は最初に見つかった1人の名前が繰り返し表示されるため、次の見出しで説明する対策が必要になります。

 

IFERROR関数によるエラー表示の整理

指定順位がデータ件数を超えると、LARGE関数はエラーを返します。

たとえばデータが8件しかないのに10位まで表示しようとすると、数式結果にエラーが出る仕組みです。

見た目を整えたいときは、次のようにIFERROR関数で式全体を囲みます。

該当するデータがないときは空白を返すため、ランキング枠をあらかじめ多めに作っても読みやすさを保てます。

IFERROR関数は原因そのものを直す関数ではなく、想定内のエラーを空白などへ置き換える関数です。

 

同順位と重複データへの対応

続いては、売上や得点が同じ人がいる場合に、名前を重複なく抽出する考え方を確認していきます。

氏名	売上	順位
佐藤	98000	1
鈴木	84500	2
高橋	84500	2

 

RANK.EQ関数における同順位の扱い

RANK.EQ関数では、同じ数値に同じ順位が付きます。

たとえば2人が84500で並んだ場合、両者は2位となり、その次の順位は4位です。

同順位を許容するランキングでは、3位が欠番になることはExcelの正常な計算結果です。

社内の評価表や競技の順位表では、この方式で問題ないケースも多いでしょう。

一方で、上位3人を必ず別々に表示したい場合、RANK.EQ関数だけで順位を検索すると同じ氏名を取得する可能性があります。

 

COUNTIF関数を加えた連番順位

同じ数値でも表示順を分けたいときは、RANK.EQ関数へCOUNTIF関数を加える方法があります。

C2セルに次の数式を入力し、下方向へコピーしてください。

RANK.EQ関数で基本順位を求め、COUNTIF関数で同一数値が何回出現したかを数えます。

最初の84500は2位、次に出現した84500は3位となるため、順位が一意になります。

COUNTIFの範囲は開始セルだけを固定し、終点側はコピーに合わせて広がる形にすることが重要です。

この順位列を使えば、INDEX関数とMATCH関数で同順位の氏名も個別に表示できます。

 

同順位をすべて表示するFILTER関数

Microsoft 365を利用している場合、同じ売上の人を全員表示したいときにはFILTER関数が役立ちます。

たとえばE2セルに確認したい売上額があり、その該当者をF2セルから表示する式は次のとおりです。

条件に一致する氏名が複数あると、結果が下のセルへ自動的に展開されます。

この機能はスピルと呼ばれ、あらかじめ結果のセルをコピーする必要がありません。

同順位者を漏れなく確認したい場合は、1件だけを返すMATCH関数よりFILTER関数が適しています。

 

別シートにランキングを表示する方法

続いては、元データ用のシートとは別に、見やすいランキング表を作成する手順を確認していきます。

元データシート	ランキングシート
A列 氏名	A列 順位
B列 売上	B列 氏名
C列 順位	C列 売上

 

シート名を含む参照式

元データのシート名を売上データ、ランキングを表示するシート名をランキングとします。

ランキングシートのC2セルで1位の売上を取得する場合は、次のように元シート名を付けて記述します。

シート名の後ろに感嘆符を付けると、そのシートのセル範囲を参照できます。

シート名に空白が含まれる場合は、シート名全体を単一引用符で囲みます。

別シート参照でも、データ範囲の絶対参照はそのまま必要です。

数式を作成するときは、手入力ではなく対象シートをクリックしてセル範囲を選択すると、参照ミスを減らせます。

 

別シートで氏名を返すINDEXとMATCH

ランキングシートのB2セルには、C2セルの売上に対応する氏名を返す数式を入れます。

INDEX関数の第1引数は氏名がある元データシートのA列、MATCH関数の検索範囲は売上があるB列です。

検索値であるC2はランキングシート上のセルなので、シート名を付ける必要はありません。

別シートで作る場合も、検索値と検索範囲のデータ形式をそろえることが大切です。

数値が文字列として保存されていると完全一致で見つからないことがあるため、表示形式だけでなく実際のデータ形式も確認しましょう。

 

オートフィルによるランキング表の完成

A2セルに1、A3セルに2を入力し、2セルを選択してフィルハンドルを下へドラッグすると順位の連番を作れます。

C2のLARGE関数とB2のINDEX関数も、表示したい順位までオートフィルします。

数式の開始セルだけを入力して下へコピーするため、上位10件へ増やすときも表を作り直す必要はありません。

元データが更新されると、ランキングシートの数値と名前も再計算によって自動更新されます。

報告用シートでは、順位・氏名・売上を表形式にして、桁区切りや条件付き書式を加えるとさらに読みやすくなります。

 

SORTBY関数とXLOOKUP関数の活用

続いては、Microsoft 365で利用できる新しい関数を使い、より短い式で順位と名前を抽出する方法を確認していきます。

氏名	売上	並べ替え後
佐藤	98000	佐藤 98000
鈴木	84500	鈴木 84500
田中	71300	田中 71300

 

SORTBY関数による氏名と売上の同時並べ替え

SORTBY関数は、指定した列を基準にして複数列のデータを並べ替える関数です。

氏名と売上をそのまま降順のランキングとして表示するなら、空いているセルに次の数式を入力します。

第1引数には表示したい範囲、第2引数には並べ替えの基準となる売上範囲、第3引数には降順を示すマイナス1を指定します。

SORTBY関数なら、名前と売上の対応を保ったまま一度に上位順へ並べ替えられます。

結果は複数行・複数列へ自動展開されるので、数式をコピーする必要がありません。

ただし、展開先に文字や数式が入力されているとエラーになるため、出力範囲は空けておきましょう。

 

TAKE関数による上位件数の限定

SORTBY関数で並べ替えた結果から、上位3件や上位10件だけを表示したい場合にはTAKE関数を組み合わせます。

上位5件の氏名と売上を抽出する式は次のとおりです。

SORTBY関数で売上順に整列した配列から、TAKE関数が先頭の5行だけを取り出します。

上位人数を変更するときは、式の最後にある5だけを変更すれば対応できます。

ランキング表に順位番号も必要なら、隣の列にSEQUENCE関数を入力して連番を表示する方法が便利です。

=SEQUENCE(5)と入力すると、1から5までの番号が縦方向に展開されます。

 

XLOOKUP関数による柔軟な名前検索

XLOOKUP関数は、検索値・検索範囲・戻り範囲を順に指定してデータを探せる関数です。

上位売上がE2セルにある場合、対応する氏名を取得する式は次の形になります。

INDEX関数とMATCH関数を1つにまとめたような書き方で、戻り値の範囲を直接指定できます。

XLOOKUP関数では、見つからないときに表示する文字も数式内で指定できます。

利用できるExcelのバージョンであれば、数式の意味を読み取りやすく、別シート参照にも使いやすい選択肢です。

 

数式エラーと抽出できない場合の確認項目

続いては、順位から名前が表示されない場合や意図しない結果になる場合の確認項目を解説していきます。

症状	主な原因	確認箇所
該当なし	数値形式の違い	検索値と売上列
同じ名前が続く	同じ数値	同順位の扱い
数式がずれる	参照の固定不足	ドル記号

 

検索値と数値形式の違い

MATCH関数やXLOOKUP関数で該当なしになるときは、見た目が同じでもデータ形式が異なる場合があります。

たとえば売上列が文字列の98000で、検索値が数値の98000である場合、完全一致として判定されないことがあります。

セル左上の緑色の三角や、数式バーの表示を確認してください。

数値としてそろえるには、エラー表示の変換機能、VALUE関数、または形式を選択して貼り付けの乗算などが使えます。

空白文字が氏名に混ざっている場合も検索結果に影響するため、TRIM関数で不要な空白を取り除く方法があります。

 

絶対参照の不足による範囲ずれ

最初のセルでは正しく表示されても、下へコピーした途端に順位や名前がおかしくなる場合は、参照範囲の固定を確認します。

比較する売上範囲がB2:B10のまま動いてほしい場合、$B$2:$B$10のように列と行の両方へドル記号を付けます。

絶対参照がないB2:B10の式を下へコピーすると、次の行ではB3:B11へずれてしまいます。

数式をコピーするランキング表では、一覧全体の範囲を固定する絶対参照が基本です。

F4キーを押すと参照形式を切り替えられるため、数式編集時に活用すると入力時間を短縮できます。

 

空白行と範囲拡張への備え

元データに空白行が多い場合や、毎月データが追加される場合は、範囲の設定を見直す必要があります。

固定範囲が$B$2:$B$10のままでは、11行目以降に追加したデータはランキングの対象になりません。

Excelのテーブル機能を使って元データをテーブル化すると、行を追加したときに参照範囲が自動拡張されます。

テーブル名を売上表とした場合、構造化参照を使った式へ変更する方法もあります。

更新頻度が高いデータでは、テーブル機能を使うと上位抽出のメンテナンスを減らせます。

 

まとめ エクセルで順位を抽出して名前を表示する方法

Excelで順位を抽出して名前を表示するには、順位を求める方法と、順位または上位数値に対応する氏名を検索する方法を分けて考えると整理しやすくなります。

基本的には、RANK.EQ関数で順位列を作り、INDEX関数とMATCH関数で氏名を返す方法が分かりやすい構成です。

順位列を作らずに上位データを抽出したい場合は、LARGE関数で売上や得点を求め、その値から名前を検索します。

同じ数値がある表では、同順位を許容するか、COUNTIF関数で順位を連番にするかを先に決めることが大切です。

別シートにランキングを表示するときは、シート名と感嘆符を含めた参照式を作り、元データ側の範囲を絶対参照で固定しましょう。

Microsoft 365ではSORTBY関数、TAKE関数、XLOOKUP関数を使うことで、より少ない数式で上位一覧を作成できます。

抽出できない場合は、数値が文字列になっていないか、検索範囲がずれていないか、データ追加分が参照範囲に含まれているかを確認してください。

目的に合う関数を選び、更新しても自動で反映されるランキング表を作っていきましょう。