エクセルで売上やテストの成績をまとめているとき、元の表の並び順は変えずに、別の場所に順位から名前を抽出したい場面って結構ありますよね。とりあえずRANK関数で順位は出せたものの、いざ1位の最高点や最下位の人、あるいは上位のトップ3やトップ5、トップ10から下位までの名前を自動で抜き出そうとすると、手が止まってしまう方も多いかなと思います。
特に、普段使い慣れているVLOOKUP関数を使おうとして、氏名が左側の列にあるせいでエラーになり悩んでしまうケースは本当によくあります。そこでINDEXとMATCHの組み合わせやXLOOKUP関数、LARGEやSMALL関数を試してみるわけですが、今度は重複や同率で同じ点数の人が複数いるときに、同じ名前が2回出てしまったり、#N/Aや#SPILL!、#NUM!といったエラーが出たりして困ってしまうんですね。
さらに、SORTやFILTER、SORTBYといった比較的新しい関数でランキングを自動更新したい場合や、COUNTIFSを使って複数条件で部署別やグループ別に集計したいとき、別シートへ結果を飛ばしたいとき、あるいは関数を使わずにピボットテーブルやフィルターで手軽に絞りたいときなど、やりたいことによってベストなやり方は変わってきます。この記事では、エクセルでの順位や名前の抽出に関するあらゆる疑問を解消できるように、基本から応用まで分かりやすく整理してみました。
- 左側の列にある氏名を1位や特定の順位からエラーなく逆引きする基本の関数
- 同点や同率順位の人が複数いる場合に同じ名前が重複して出るのを防ぐ連番化の仕組み
- 最新関数による全自動ランキング表の作成や部署別などの条件付き抽出テクニック
- ピボットテーブルなどの標準機能での絞り込み手順と頻出エラーの具体的な回避策
エクセルで順位から名前を抽出する基本の関数と手順
まずは、エクセルで順位から名前を抽出するための基本的な関数の使い方から見ていきましょう。元データの並び順（社員番号順や日付順など）を崩さずに、特定の順位の人や上位・下位のメンバーを抜き出すには、使っているエクセルのバージョンや表のレイアウトに合わせて関数を選ぶのがポイントです。ここでは、解説を分かりやすくするために、以下のような共通のサンプル表（Sheet1のA1からF9の範囲）をイメージしながら進めていきますね。
【解説用のサンプル表イメージ（Sheet1）】
| 行 | A列（氏名） | B列（部署） | C列（得点） | D列（実技点） | E列（通常順位） | F列（連番順位） |
|---|---|---|---|---|---|---|
| 2 | 佐藤 | 営業A | 85 | 40 | 4 | 4 |
| 3 | 鈴木 | 営業B | 92 | 45 | 2 | 2 |
| 4 | 高橋 | 営業A | 78 | 35 | 7 | 7 |
| 5 | 田中 | 営業A | 92 | 48 | 2 | 3 |
| 6 | 伊藤 | 営業B | 85 | 42 | 4 | 5 |
| 7 | 渡辺 | 営業B | 60 | 30 | 8 | 8 |
| 8 | 山本 | 営業A | 85 | 38 | 4 | 6 |
| 9 | 中村 | 営業B | 95 | 49 | 1 | 1 |
※95点の中村さんが1位、92点の鈴木さんと田中さんが同点2位、85点の佐藤さん・伊藤さん・山本さんが同点4位というデータになっています。
INDEXとMATCH関数で1位や最高点を抜く
エクセルのバージョンを問わず、どんな環境でも確実に使えて一番汎用性が高いのが、INDEX関数とMATCH関数を組み合わせる方法です。お仕事でエクセル2016や2019などを長く使っている方にとっても、これさえ覚えておけば表の列がどんな順番で並んでいても柔軟に対応できるので、すごく頼りになる王道のやり方かなと思います。
この組み合わせがどう動いているかというと、まずMATCH関数が「探したい順位や最高点が、上から何番目にあるか」という位置（行番号）を見つけてくれます。そして、その見つけた位置番号を使って、INDEX関数が「氏名列のその位置にある名前」をスポッと抜き出す、というバトンリレーのような役割分担をしているんですね。
順位列を作らずに最高点（1位）や最下位を直接抜く数式
元の表にわざわざRANK関数で順位の列を作らなくても、最高点を出すMAX関数や、最小値を出すMIN関数を組み合わせれば、いきなり1位や最下位の名前を抜き出すことができます。
・最高点（1位）の名前を直接抽出する数式：=INDEX($A$2:$A$9, MATCH(MAX($C$2:$C$9), $C$2:$C$9, 0))
・最下位（最小値）の名前を直接抽出する数式：=INDEX($A$2:$A$9, MATCH(MIN($C$2:$C$9), $C$2:$C$9, 0))
先ほどのサンプル表で最高点の数式がどう計算されるか分解してみますね。まずMAX($C$2:$C$9)がC列の最高点である「95」を見つけます。次にMATCH(95, $C$2:$C$9, 0)が、C2からC9の中で95と完全一致（最後の引数の0が完全一致という意味です）する場所を探し、上から8番目にあるので「8」という数字を返します。最後にINDEX($A$2:$A$9, 8)が、氏名範囲の8番目にある「中村」を表示してくれる、という流れです。
あらかじめ作った順位列を使って特定の順位を抜く数式
もしすでにE列に=RANK.EQ(C2, $C$2:$C$9)などで順位の列を作ってある場合や、別のセル（たとえばI2セル）に「1」や「2」と抽出したい順位の数字を入力してある場合は、もっとシンプルな数式になります。
・I2セルに入力した順位の名前をE列（順位列）から抽出する数式：=INDEX($A$2:$A$9, MATCH(I2, $E$2:$E$9, 0))
この方法の素晴らしいところは、検索する列と名前を取り出す列を別々に1列ずつ指定できるため、後から表の途中に列を追加したり削除したりしても数式が壊れない点にあります。数式を下の行へオートフィルでコピーするときは、参照する範囲がズレないように、F4キーを押して$A$2:$A$9のように「$」マーク（絶対参照）をしっかり付けておくのを忘れないようにしてくださいね。こうした数式の基本ルールや絶対参照の仕組みについてもう少しおさらいしたい方は、エクセル表計算の作り方！初心者向け関数とスマホでの作成手順も参考にしてみてください。
XLOOKUP関数で左側の氏名列を逆引きする
もしお使いの環境がExcel 2021やExcel 2024、あるいはMicrosoft 365といった比較的新しいバージョンであれば、XLOOKUP関数を使うのが一番すっきりしていておすすめです。INDEXとMATCHの2つの関数を組み合わせなくても、この関数1つだけで左側の列にある名前を簡単に逆引きできます。
XLOOKUP関数の基本的な書き方は=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])となっています。引数がたくさんあるように見えますが、必須なのは最初の3つだけです。従来の関数では毎回指定していた「完全一致（0やFALSE）」が最初から標準設定になっているので、省略してしまって大丈夫なのが嬉しいところですね。
・最高点（1位）の名前を直接抽出する数式：=XLOOKUP(MAX($C$2:$C$9), $C$2:$C$9, $A$2:$A$9)
・I2セルの順位をE列から探してエラー対策込みで抽出する数式：=XLOOKUP(I2, $E$2:$E$9, $A$2:$A$9, "該当なし")
第2引数に「得点列や順位列（探す場所）」を指定し、第3引数に「氏名列（取り出したい場所）」を個別に指定するだけなので、氏名列が左側にあろうが右側にあろうが関係なく引っ張ってこられます。また、第4引数に"該当なし"や""（空白）を直接書いておけば、万が一該当する順位が見つからなかったときにも自動でその文字を表示してくれるので、わざわざIFERROR関数などを外側に付け足す手間も省けます。
ちなみにちょっとした応用として、第6引数の「検索モード」に-1を指定すると、「表の下から上へ（末尾から先頭へ向かって）検索する」という動きに変えることができます。たとえば同点の人が2人だけいるときに、1人目は通常のXLOOKUPで上から探し、2人目は検索モードを-1にして下から探す、といった使い分けもできたりします。
VLOOKUP関数で左側の列を引く回避策
エクセルでデータを抽出するといえば、やっぱりVLOOKUP関数が一番なじみ深いかなと思います。ただ、日本のビジネス現場でよく作られる表って、左側に「氏名や商品名」があって、右側に「得点や売上、順位」が並ぶレイアウトが圧倒的に多いですよね。ここでVLOOKUP関数を使って右端の順位から左端の名前を引こうとすると、うまくいかずに壁にぶつかってしまいます。
というのも、VLOOKUP関数（=VLOOKUP(検索値, 範囲, 列番号, 検索方法)）は、指定した範囲の「一番左端の列」から検索値を探し、そこから右方向へ数えて何列目かを取り出すというルールになっているからです。列番号にマイナスの数字を入れることはできないので、そのままでは左側の列を見に行くことができません。それでもVLOOKUP関数を使って解決したい場合は、次の4つの回避策のどれかを使うことになります。
回避策1：順位の列を氏名列より左側（A列）に移動する
表のレイアウト自体を変えても問題ないなら、これが一番シンプルで確実です。A列に順位、B列に氏名、C列以降に得点という並び順に列を移動してしまえば、順位が一番左端になるので、通常の=VLOOKUP(I2, $A$2:$B$9, 2, FALSE)という数式だけであっさり名前を取り出せます。
回避策2：表の右端に氏名をコピーした作業列を追加する
印刷や提出の都合で元の表の見た目を変えられない場合は、順位列（E列）のさらに右隣の列（F列）に=A2と入力して、氏名をそのまま表示させる作業列を作ります。そうすれば順位列の右側に名前が来るので、=VLOOKUP(I2, $E$2:$F$9, 2, FALSE)として普通に右方向への検索ができるようになります。作業列が目障りなら、列ごと非表示にして隠しておけばOKです。
回避策3：CHOOSE関数を組み合わせて仮想の表を作る
表の配置も変えたくないし、作業列も増やしたくないという場合に、すべてのバージョンで使える関数テクニックがCHOOSE関数との併用です。
・CHOOSE関数で列の左右を反転させる数式：=VLOOKUP(I2, CHOOSE({1,2}, $E$2:$E$9, $A$2:$A$9), 2, FALSE)
CHOOSE関数の最初に{1,2}という配列を書くことで、エクセルの計算メモリの中でこっそり「1列目がE列（順位）、2列目がA列（氏名）」という架空の2列表を作り出し、それをVLOOKUP関数に渡してあげるという仕組みです。
回避策4：CHOOSECOLS関数やHSTACK関数で列を再配置する
Microsoft 365やExcel 2024を使っているなら、新しく登場したCHOOSECOLS関数やHSTACK関数を使って列の順番を入れ替えることも可能です。=VLOOKUP(I2, CHOOSECOLS($A$2:$E$9, 5, 1), 2, FALSE)や、=VLOOKUP(I2, HSTACK($E$2:$E$9, $A$2:$A$9), 2, FALSE)と書くことで、5列目（順位）と1列目（氏名）をこの順番で並べた範囲を作れます。
ただ、正直なところ回避策3や4のように関数を複雑に組み合わせるくらいなら、最初からINDEX＋MATCHやXLOOKUPを使った方が数式も読みやすくて管理もラクかなと思います。ここで一度、それぞれの抽出方法の違いを表で比較しておきますね。
【順位から名前を抽出する主な手法の比較】
| 抽出手法 | 代表的な数式例（I2の順位から抽出） | 左側の抽出 | 列挿入への強さ | 対応バージョン |
|---|---|---|---|---|
| INDEX + MATCH | =INDEX($A$2:$A$9, MATCH(I2, $E$2:$E$9, 0)) | ○（制約なし） | ◎（壊れない） | 全バージョン |
| XLOOKUP | =XLOOKUP(I2, $E$2:$E$9, $A$2:$A$9, "") | ○（制約なし） | ◎（壊れない） | 2021 / 2024 / 365 |
| VLOOKUP単体 | =VLOOKUP(I2, $A$2:$B$9, 2, FALSE) ※A列が順位 | ×（右側のみ） | ×（列番号がずれる） | 全バージョン |
| VLOOKUP + CHOOSE | =VLOOKUP(I2, CHOOSE({1,2}, $E$2:$E$9, $A$2:$A$9), 2, FALSE) | ○（仮想反転） | ○（直接範囲指定） | 全バージョン |
| VLOOKUP + CHOOSECOLS | =VLOOKUP(I2, CHOOSECOLS($A$2:$E$9, 5, 1), 2, FALSE) | ○（列順再構成） | △（番号がずれる） | 2024 / 365 |
LARGEやSMALL関数で上位と下位を出す
1位の人だけでなく、「トップ3」や「トップ5」「トップ10」、あるいは逆にワーストの「下位3名」といった一覧を作りたいときは、指定した順位の数値そのものを取り出してくれるLARGE関数とSMALL関数が活躍します。
ここで最初に整理しておきたいのが、RANK系の関数とLARGE・SMALL関数の役割の違いです。RANK関数やRANK.EQ関数は「ある点数が全体の中で何位か」を調べる関数ですが、LARGE関数は「上から数えて○番目の点数そのもの」、SMALL関数は「下から数えて○番目の点数そのもの」を取り出す関数です。なお、同点の人に平均順位（2.5位など）をつけるRANK.AVG関数もありますが、小数点が出ると名前抽出の検索キーとしては使いづらいため、基本的には使いません。
【順位付け・数値抽出に関わる基礎関数の違い】
| 関数名 | 構文 | 役割と特徴 |
|---|---|---|
| RANK.EQ / RANK | =RANK.EQ(数値, 範囲, [順序]) | 数値が何位かを返す。同点は同じ順位になり次の順位が飛ぶ（0または省略で降順、1で昇順） |
| RANK.AVG | =RANK.AVG(数値, 範囲, [順序]) | 同点がある場合に順位の平均値（2.5位など）を返す。端数が出るため名前抽出には不向き |
| MAX / MIN | =MAX(範囲) / =MIN(範囲) | 最大値（1位の値）または最小値（最下位の値）をピンポイントで取得する |
| LARGE / SMALL | =LARGE(配列, 順位) | 大きい方（SMALLは小さい方）から数えて指定した順位番目の数値そのものを取り出す |
| ROW | =ROW([参照]) | 指定セルの行番号を返す。ROW(A1)を下へコピーすると1, 2, 3と増えるため順位指定に便利 |
たとえば、元表に順位列を作らずに、別の場所（I2セルに1、I3セルに2、I4セルに3…と順位が書いてある表）の隣に上位から順番に名前を抜き出したい場合、さきほどのINDEX＋MATCHやXLOOKUPの検索値の部分にLARGE関数を組み込みます。
・上位N位（I2セルの順位）の名前を直接抜く数式：=INDEX($A$2:$A$9, MATCH(LARGE($C$2:$C$9, I2), $C$2:$C$9, 0))
・下位N位（ワースト順位）の名前を直接抜く数式：=INDEX($A$2:$A$9, MATCH(SMALL($C$2:$C$9, I2), $C$2:$C$9, 0))
もしI列に1, 2, 3という順位の数字すら用意していない場合は、I2と指定している部分をROW(A1)に書き換えてみてください。ROW(A1)はA1セルの行番号である「1」を返すので、そのまま数式を下のセルへオートフィルすると自動的にROW(A2)で「2」、ROW(A3)で「3」と数字が増えていき、上から順にトップ3、トップ5と引っ張ってくることができます。
【重要：同点（同じ点数）がいる場合の落とし穴】
LARGE関数やSMALL関数とMATCH（またはXLOOKUP）を直接組み合わせるこのやり方は、全員の点数がバラバラで重複が一切ない場合のみ正常に動きます。もし同じ点数の人が複数いると、同じ人の名前が2回表示されてしまうため、次で解説する「連番化」などの対策が必須になります。
重複や同率順位で同じ人が出るのを防ぐ連番化
エクセルで順位から名前を抽出するときに、一番多くの方がつまずいて頭を抱えてしまうのが、この「同点（同率順位・重複）があると名前がうまく抜けない問題」です。なぜおかしくなってしまうのか、まずはその原因をサンプル表で見てみましょう。
サンプル表では、鈴木さん（3行目）と田中さん（5行目）がどちらも「92点」で同率2位になっています。ここでさきほどのLARGE($C$2:$C$9, 順位)を使った数式で上位3名を抜こうとすると、2位（2番目に大きい値）の検索でLARGE関数は「92」を返し、MATCH関数は上から探して最初に見つかった「鈴木」さんを返します。
問題は3位（3番目に大きい値）を抜くときです。95点、92点、92点と並んでいるので、3番目に大きい値もやっぱり「92」なんですね。するとLARGE関数は再び「92」を返し、MATCH関数はまた表の一番上から「92」を探し始めるため、最初に見つかる「鈴木」さんをもう一回連れてきてしまいます。結果として、2位にも3位にも鈴木さんの名前が出て、田中さんが消えてしまうんです。逆に、E列の通常順位（1, 2, 2, 4…）から「3」を探そうとすると、3位という数字自体が存在しないので#N/Aエラーになってしまいます。
この現象を解決するための4つのアプローチを順番に解説しますね。
対策パターンA：RANK.EQ＋COUNTIFで重複しない連番順位を作る（最もおすすめ）
どのバージョンのエクセルでも使えて、後から見返したときにも一番わかりやすい王道の解決策が、元の表に「同点でも上の行にいる人を優先して1から順番に連番を振る作業列（F列）」を作ることです。F2セルに以下の数式を入れて、F9セルまで下へコピーします。
・重複のない連番順位を作る数式（降順・高い順）：=RANK.EQ(C2, $C$2:$C$9) + COUNTIF($C$2:C2, C2) - 1
・重複のない連番順位を作る数式（昇順・タイムなど小さい順）：=RANK.EQ(C2, $C$2:$C$9, 1) + COUNTIF($C$2:C2, C2) - 1
この数式のすごいところは、後ろにくっついているCOUNTIF($C$2:C2, C2) - 1の部分です。範囲の始まり（$C$2）だけを「$」で固定して、終わり（C2）を固定しないでおくことで、数式を下の行へコピーするにつれて検索範囲が$C$2:C3、$C$2:C4、$C$2:C5とゴムのように伸びていきます。
つまり、「1行目から自分の行までに、自分と同じ点数の人が何人出てきたか」を数えているんですね。自分自身の分で必ず1回カウントされるので最後に-1を引くと、1人目の92点（鈴木さん）は「2位＋1人目−1＝2位」のままになり、2人目の92点（田中さん）は「2位＋2人目−1＝3位」へと自動的に1つズレてくれます。
【連番順位が計算される仕組み（F列）】
| 行 | 氏名 | 得点 | RANK.EQの結果 | COUNTIF($C$2:C2,C2)-1の結果 | 最終的な連番順位（F列） |
|---|---|---|---|---|---|
| 2行目 | 佐藤 | 85 | 4 | 0（85点の1人目） | 4 |
| 3行目 | 鈴木 | 92 | 2 | 0（92点の1人目） | 2 |
| 4行目 | 高橋 | 78 | 7 | 0（78点の1人目） | 7 |
| 5行目 | 田中 | 92 | 2 | 1（92点の2人目） | 3 |
| 6行目 | 伊藤 | 85 | 4 | 1（85点の2人目） | 5 |
| 7行目 | 渡辺 | 60 | 8 | 0（60点の1人目） | 8 |
| 8行目 | 山本 | 85 | 4 | 2（85点の3人目） | 6 |
| 9行目 | 中村 | 95 | 1 | 0（95点の1人目） | 1 |
こうして1から8までダブりのない連番（F列）ができあがれば、あとは別の表のI列に1, 2, 3…と番号を振り、J2セルに=INDEX($A$2:$A$9, MATCH(I2, $F$2:$F$9, 0))と入れるだけで、鈴木さんも田中さんも漏れなく順番通りに抽出できます。もし表示上の順位は「2位タイ」として両方とも「2」と見せたい場合は、順位表示用の列に=INDEX($E$2:$E$9, MATCH(I2, $F$2:$F$9, 0))を入れてE列の通常順位を引っ張ってくるようにすればバッチリです。
対策パターンB：別列の基準（実技点など）や微小な数値で優先順位をつける
もし同点のときに「単に表の上にある人を優先する」のではなく、「得点が同じなら実技点（D列）が高い人を上にする」といった明確なルール（タイブレーク条件）で順位を分けたい場合は、COUNTIFS関数を使います。
・得点が同じ場合に実技点（D列）が高い方を上位にする連番数式：=RANK.EQ(C2, $C$2:$C$9) + COUNTIFS($C$2:$C$9, C2, $D$2:$D$9, ">"&D2)
これは「自分と同じ得点（C列）で、かつ自分より実技点（D列）が高い人が何人いるか」を数えて順位に足す数式です。サンプル表だと、同じ92点でも鈴木さん（実技45点）より田中さん（実技48点）の方が実技点が高いので、田中さんが2位、鈴木さんが3位と判定されます。また、昔からの裏ワザとして、作業列に=C2 - ROW()*0.0001のように行番号にすごく小さな数字を掛けたものを引いておき、見た目上の小数点を隠すことで計算上の同点をなくしてしまう、という方法もあります。
対策パターンC：FILTER関数やTAKE関数で同率全員を一気に抜く（2021 / 365以降）
Excel 2021やMicrosoft 365をお使いなら、1つのセルに数式を入れるだけで結果が下のセルへ自動で広がる「スピル」機能を持ったFILTER関数を使うことで、作業列なしで同率の人を全員まとめて抜き出せます。
・同率1位の全員の名前を縦に並べて抽出する数式：=FILTER($A$2:$A$9, $C$2:$C$9=MAX($C$2:$C$9))
・同率を含めて「上位3位以内」に入る全員（氏名〜得点）を高い順で抜く数式：=SORT(FILTER($A$2:$C$9, $C$2:$C$9>=LARGE($C$2:$C$9, 3), "-"), 3, -1)
2つ目の数式は、「上から3番目の点数（LARGE関数）以上の人をすべてFILTER関数で抜き出し、SORT関数で3列目（得点）の降順（-1）に並べ替える」という動きをします。もし3位タイが何人かいて合計4人や5人になったとしても、全員自動で表示されます。さらにExcel 2024やMicrosoft 365なら、TAKE関数とCOUNTIFを組み合わせて=TAKE(SORT($A$2:$C$9, 3, -1), COUNTIF($C$2:$C$9, ">="&LARGE($C$2:$C$9, 3)))と書くことでも、3位以上の人数を自動で数えて全員分を切り出すことができます。
対策パターンD：作業列が使えない旧バージョンでの配列数式（2019以前）
「会社のパソコンがExcel 2019以前でFILTER関数が使えないけれど、元の表に作業列を足すのも禁止されている」という厳しい条件のときは、INDEX、SMALL、IF、ROWを組み合わせた配列数式で乗り切ることができます。
・作業列なしで最高点（1位）の全員を上から抽出する配列数式：=IFERROR(INDEX($A$1:$A$9, SMALL(IF($C$2:$C$9=MAX($C$2:$C$9), ROW($C$2:$C$9)), ROW(A1))), "")
※入力後に Ctrl + Shift + Enter キーを同時に押して確定し、下へコピーします。
・K列にLARGE関数で出した得点（K2）に対応する名前を重複なく抜く配列数式：=IFERROR(INDEX($A$1:$A$9, SMALL(IF($C$2:$C$9=K2, ROW($C$2:$C$9)), COUNTIF($K$2:K2, K2))), "")
※こちらも Ctrl + Shift + Enter で確定します（先頭のINDEXの範囲は必ず1行目の$A$1から指定してください）。
また、Ctrl + Shift + Enterを押すのが面倒な場合は、少し高度ですが=IFERROR(INDEX($A$1:$A$9, 1/LARGE(INDEX(($C$2:$C$9=MAX($C$2:$C$9))/ROW($C$2:$C$9), 0), ROW(A1))), "")のように、内部にINDEX(..., 0)と割り算を挟む書き方をすると、古いエクセルでも普通のEnterキーだけで確定できるようになります。
最後に、同順位の扱い方について4つのパターンを表にまとめておきます。ちなみに、同点がいても次の順位を飛ばさない「密な順位（1位、2位、2位、3位）」を作りたい場合は、旧バージョンなら=SUMPRODUCT(($C$2:$C$9>C2)/COUNTIF($C$2:$C$9, $C$2:$C$9))+1、365版なら=XMATCH(C2, SORT(UNIQUE($C$2:$C$9), , -1))という数式で作ることができますよ。
【同順位に関する4つの定義と名前抽出への向き・不向き】
| 順位の種類 | 並び方の例（95, 92, 92, 85点） | 代表的な数式（C2セル対象） | 名前抽出の検索キーとしての適性 |
|---|---|---|---|
| ① 通常の順位 | 1位, 2位, 2位, 4位 | =RANK.EQ(C2, $C$2:$C$9) | × 不適（3位がなくエラーになり、2位が重複する） |
| ② 平均順位 | 1位, 2.5位, 2.5位, 4位 | =RANK.AVG(C2, $C$2:$C$9) | × 不適（小数点が出て整数の順位検索でヒットしない） |
| ③ 飛ばさない順位 | 1位, 2位, 2位, 3位 | =XMATCH(C2, SORT(UNIQUE($C$2:$C$9), , -1)) | △ FILTER関数で「3位までの全員」を抜く場合のみ適する |
| ④ 重複なし連番 | 1位, 2位, 3位, 4位 | =RANK.EQ(C2, $C$2:$C$9)+COUNTIF($C$2:C2, C2)-1 | ◎ 最適（1対1で対応するため確実に全員を順番通り抜ける） |
エクセルの順位と名前の抽出における応用と注意点
基本的な抽出方法と同点対策がわかったところで、ここからはさらに実務で役立つ応用テクニックと注意点を見ていきましょう。エクセルで順位や名前を抽出する作業をもっと自動化したり、部署ごとのランキングを作ったり、エラーをきれいに消したりする方法をまとめています。
SORTやFILTER関数によるランキング自動更新
Excel 2021や2024、Microsoft 365を使っている方なら、動的配列関数（スピル機能）をパズルのように組み合わせることで、元の表の数字を書き換えたりデータを追加したりした瞬間に、全自動で並び替えられるランキング表をたった1つの数式で作ることができます。
必要な列（氏名と得点など）だけを選んで上位N件を抜く
元の表には「部署」や「実技点」など色々な列が入っているけれど、ランキング表には「1列目（氏名）」と「3列目（得点）」の2列だけを上位3名分だけスッキリ表示したい、というときは、Excel 2024やMicrosoft 365で使えるTAKE関数・CHOOSECOLS関数・SORT関数の3つを組み合わせます。
・氏名（1列目）と得点（3列目）の上位3名を自動抽出する数式：=TAKE(CHOOSECOLS(SORT($A$2:$F$9, 3, -1), 1, 3), 3)
内側から順番に見ると、まずSORT($A$2:$F$9, 3, -1)で表全体を3列目（得点）の高い順（-1）に並べ替えます。次にCHOOSECOLS(..., 1, 3)で1列目と3列目だけを抜き出し（もし3, 1と書けば得点・氏名の順に左右を入れ替えることもできますし、マイナスの数字で右端から数えた列を指定することもできます）、最後に一番外側のTAKE(..., 3)で上から3行分だけを切り出しています。ちなみに特定の行（たとえば1位と3位と5位の行だけ）をピンポイントで抜きたいときはCHOOSEROWS関数を使うこともできます。
複数基準（タイブレーク）での並び替えや名前だけの抽出にはSORTBY関数
「得点が同じなら実技点が高い順」のように複数の基準で並べ替えたいときや、「表全体ではなく上位5名の名前（A列）だけを抜き出したい」というときには、SORTBY関数（=SORTBY(配列, 基準配列1, [順序1], [基準配列2, 順序2], ...)）がすごく便利です。
・上位5名の「名前だけ」を一発で抽出する最短の数式：=TAKE(SORTBY($A$2:$A$9, $C$2:$C$9, -1), 5)
・得点（C列）を第1基準、実技点（D列）を第2基準にして上位5名の名前を抜く数式：=TAKE(SORTBY($A$2:$A$9, $C$2:$C$9, -1, $D$2:$D$9, -1), 5)
SORT関数は「取り出す範囲の中に並べ替えの基準となる列が含まれていること」が条件になりますが、SORTBY関数は「取り出したい範囲（氏名列だけ）」と「並べ替えの基準にする範囲（得点列）」が別々でも動いてくれるのが最大の強みです。また、LET関数やSEQUENCE関数を組み合わせて=LET(並替済, SORTBY($A$2:$C$9, $C$2:$C$9, -1), INDEX(並替済, SEQUENCE(5), 1))のように書くこともできます。
上位・下位の切り替えやデータ追加への完全自動化
もし下位（ワースト）5名を抜きたいなら、SMALL関数を使って=SORT(FILTER($A$2:$C$9, $C$2:$C$9<=SMALL($C$2:$C$9, 5), "-"), 3, 1)とするか、SORT関数の並び順を昇順（1）にします。さらに、たとえばG2セルに「上位」または「下位」、H2セルに件数の「5」と入力して、プルダウンなどで表示をパッと切り替えられるようにしたい場合は、IF関数を組み込むと便利です。
・セルの指定（G2に上位/下位、H2に件数）で動的に切り替える数式：=TAKE(SORT($A$2:$C$9, 3, IF(G2="上位", -1, 1)), H2)
※常に高い順（降順）の並びを保ったまま下位5件を切り出したい場合は、TAKEの行数にマイナスを指定する =TAKE(SORT($A$2:$C$9, 3, -1), IF(G2="上位", H2, -1*H2)) でも動きます。
また、Microsoft 365の新しい記法である「トリム参照（ドットを使ってA.:.Cのように書くことで、データが入っている有効な行だけを自動で認識する書き方）」と、見出しの1行目を除外するDROP関数を組み合わせて=TAKE(DROP(SORT(A.:.C, 3, -1), 1), 5)と書いておけば、今後データが何百行に増えようとも数式を一切直す必要がなくなります。こうしたスピル機能や新関数の考え方については、エクセル複数セル内で特定の文字をカウントする全手順の記事内で触れている動的配列の解説もあわせて読むとイメージが掴みやすいかなと思います。
COUNTIFSで複数条件や部署別の順位を出す
全体でのランキングではなく、「営業A課の中での1位を知りたい」「支店ごとの売上トップ3の名前を抜きたい」といったグループ別・条件付きの抽出をしたい場面も多いですよね。ただ、エクセルにはSUMIFSやCOUNTIFSはあっても、残念ながら「RANKIF」や「RANKIFS」という関数は用意されていません。そのため、自分で少し工夫して数式を組み立てる必要があります。
全バージョン対応：COUNTIFS関数でグループ内の順位を計算する
そもそも順位というものは、「自分より点数が高い人が何人いるか」を数えて、そこに「1」を足せば求めることができます（自分がトップなら、自分より高い人は0人なので、0＋1＝1位になりますよね）。この仕組みを利用して、COUNTIFS関数で「同じ部署の中で、自分より点数が高い人の数」を数えます。
・部署別（グループ内）の通常順位を出す数式：=COUNTIFS($B$2:$B$9, B2, $C$2:$C$9, ">"&C2) + 1
・部署内で同点がいた場合に重複しない連番順位にする数式：=COUNTIFS($B$2:$B$9, B2, $C$2:$C$9, ">"&C2) + COUNTIFS($B$2:B2, B2, $C$2:C2, C2)
SUMPRODUCT関数を使っても同じような計算はできるのですが、数式が読みやすくて動作も軽いCOUNTIFS関数を使うのがおすすめです。この数式をたとえばG列（作業列）に入れておけば、あとは「部署名」と「順位」を&（アンパサンド）で合体させて検索することで、特定の部署の特定順位の名前を引っ張ってこられます。
・「営業A」の「1位」の名前を抽出する数式（G列が部署内連番順位の場合）：
・XLOOKUPの場合： =XLOOKUP("営業A"&1, $B$2:$B$9&$G$2:$G$9, $A$2:$A$9)
・INDEX+MATCHの場合： =INDEX($A$2:$A$9, MATCH("営業A"&1, INDEX($B$2:$B$9&$G$2:$G$9, 0), 0))
Excel 2021 / 365以降：FILTER関数で作業列なしの一発抽出
新しいエクセルを使っているなら、COUNTIFSで作業列を作る必要もありません。FILTER関数で先に特定の部署や条件に合う人だけを絞り込んでしまい、それをSORTとTAKEで並べ替えて切り出すだけです。
・「営業A」の上位3名を抽出する数式（単一条件）：=TAKE(SORT(FILTER($A$2:$C$9, $B$2:$B$9="営業A", ""), 3, -1), 3)
・「営業A」かつ「実技点が40点以上」の人を高い順で抜く数式（AND条件）：=SORT(FILTER($A$2:$D$9, ($B$2:$B$9="営業A")*($D$2:$D$9>=40), "該当なし"), 3, -1)
・「営業A」または「営業B」の人を高い順で抜く数式（OR条件）：=SORT(FILTER($A$2:$D$9, ($B$2:$B$9="営業A")+($B$2:$B$9="営業B"), "該当なし"), 3, -1)
FILTER関数で複数の条件を指定するときは、それぞれの条件式をカッコ()で囲んで、「かつ（AND条件）」ならアスタリスク*（掛け算）、「または（OR条件）」ならプラス+（足し算）で繋ぐというルールになっているので、これだけ覚えておくと色々な絞り込みに応用できますよ。
【応用シナリオ別の関数構成まとめ】
| やりたいこと（シナリオ） | 使う関数の組み合わせ | 代表的な数式例 | 対応バージョン |
|---|---|---|---|
| 特定列のみ上位N件抽出 | TAKE + CHOOSECOLS + SORT | =TAKE(CHOOSECOLS(SORT($A$2:$F$9, 3, -1), 1, 3), 3) | 2024 / 365 |
| 複数基準で上位の名前のみ抽出 | TAKE + SORTBY | =TAKE(SORTBY($A$2:$A$9, $C$2:$C$9, -1, $D$2:$D$9, -1), 5) | 2024 / 365 |
| 上位・下位の切り替え抽出 | SORT + FILTER + IF + LARGE/SMALL | =SORT(FILTER($A$2:$C$9, IF(G2="上位", $C$2:$C$9>=LARGE($C$2:$C$9, H2), $C$2:$C$9<=SMALL($C$2:$C$9, H2)), "-"), 3, -1) | 2021 / 2024 / 365 |
| 部署別・グループ内の順位算出 | COUNTIFS | =COUNTIFS($B$2:$B$9, B2, $C$2:$C$9, ">"&C2) + 1 | 全バージョン |
| 複数条件（AND）のランキング | SORT + FILTER | =SORT(FILTER($A$2:$D$9, ($B$2:$B$9="営業A")*($D$2:$D$9>=40), "該当なし"), 3, -1) | 2021 / 2024 / 365 |
別シートへ抽出する際の絶対参照とテーブル化
実務では、日々のデータが並んでいる「入力用シート（Sheet1）」とは別に、会議や報告で使う「集計用シート（Sheet2）」を作って、そこに上位者の名前を表示させたいケースがよくあります。別シートから抜き出す場合も、基本的な関数の書き方は同じで、範囲の前にSheet1!のようにシート名が付くだけです。
・別シート（Sheet1）から名前を抽出する数式例：=IF($A2="", "", INDEX(Sheet1!$A$2:$A$9, MATCH($A2, Sheet1!$F$2:$F$9, 0)))
ここで絶対に気をつけたいのが、別シートの範囲をマウスで選択した直後に、必ずキーボードの「F4」キーを押してSheet1!$A$2:$A$9のように絶対参照（$マーク）にすることです。別シートを参照するときはうっかり「$」を付け忘れやすく、そのまま下の行へコピーすると参照範囲がSheet1!A3:A10のようにズレてしまい、1行目のデータが無視されてエラーや誤抽出の原因になってしまいます。
データが増減するなら「テーブル化（Ctrl + T）」が圧倒的に便利
また、元データの行数が毎月増えたり減ったりする場合は、あらかじめ元データの表の中のどこかをクリックして、キーボードの「Ctrl + T」（または「挿入」タブの「テーブル」）を押し、表を「テーブル化」しておくのが一番おすすめです。
たとえばテーブルに「成績表」という名前を付けておけば、別シートからでもシート名やセル番地を気にせず、以下のように項目名（構造化参照）を使って直感的に数式が書けます。
・テーブル化した表から名前やランキングを抽出する数式：
・1位の名前を抜く： =XLOOKUP(MAX(成績表[得点]), 成績表[得点], 成績表[氏名])
・トップ5の表を抜く： =TAKE(SORT(成績表, 3, -1), 5)
テーブル化しておけば、一番下の行に新しい人のデータを追加した瞬間に、関数の範囲も自動で広がるので、毎回範囲を修正する手間も絶対参照の付け忘れもなくなって本当にラクになりますよ。
ピボットテーブルやフィルターでトップ10に絞る
「複雑な関数を組むのはちょっと苦手…」「とりあえず今すぐ手早く上位10人の名前を確認したい」「同じ人の名前が何行も出てくる売上明細から、合計売上のトップ10を出したい」という場合は、関数を使わずにエクセルの標準機能だけで解決してしまうのが賢いやり方です。
方法1：オートフィルターの「トップテン」機能で絞り込む
元の表をそのまま絞り込んで上位や下位の人だけを表示させたいなら、フィルター機能にあらかじめ備わっている「トップテン」というメニューを使います。
【オートフィルターで上位N件に絞り込む手順】
- 表の見出し行を選択し、「データ」タブ（または「ホーム」タブ）の「フィルター」をクリックして、見出しに「▼」ボタンを出します。
- 「得点」や「売上」の列の「▼」をクリックし、「数値フィルター」＞「トップテン…」を選びます。
- 出てきた小窓の左端で「上位（または下位）」を選び、真ん中の数字を抽出したい件数（トップテンという名前ですが、3でも5でも好きな数字に変えられます）にし、右端で「項目（またはパーセント）」を選んで「OK」を押します。
- 最後に同じ列の「▼」から「降順で並び替え」をクリックすれば、1位から順番に整列します。
絞り込んだ結果を別シートに残したいときは、見えている表をそのままコピーして別シートへ貼り付ければOKです。また、複数条件での抽出と別セルへの出力を一度にやりたいときは、「データ」タブにある「フィルターオプション（詳細設定）」を使うのも有効ですね。ただし注意点として、フィルターで「営業A」のように部署を絞り込んだとき、あらかじめRANK関数で出していた全体順位の数字は再計算されず飛び飛びのままになるため、グループ内の順位が必要なときはさきほどのCOUNTIFS関数か、次のピボットテーブルを使ってください。
方法2：ピボットテーブルで集計・トップ10抽出・順位表示まで一気にやる
毎日の売上データのように、同じ担当者名や商品名が何十行にもわたって登場する表から「合計金額のランキング上位」を出したい場合、関数だけでやろうとするとUNIQUE関数や重複の削除でリストを作って、SUMIFで合計して、さらにRANKとINDEX＋MATCHを組んで…とすごく大変になります。こういうときはピボットテーブルを使うのが圧倒的に早いです。
【ピボットテーブルでトップ10抽出と順位表示を行う手順】
- 元データを選択して「挿入」タブ＞「ピボットテーブル」を作成し、「行」に『氏名』、「値」に『売上金額（または得点）』を入れます。
- ピボットテーブルの「行ラベル」の横にある「▼」をクリックし、「値フィルター」＞「トップ10…」を選択します。
- 「上位」「10（好きな件数に変更可能）」「項目」「合計 / 売上金額」を指定して「OK」を押せば、上位メンバーの名前と合計値だけに絞られます（同じシートに複数のピボットテーブルを並べていて、データが伸びて重なるのを防ぎたいときにも便利です）。
- さらに「順位」の列も表示させたい場合は、フィールドリストの『売上金額』をもう一度「値」エリアへドラッグして、同じ項目を2つ並べます。
- 追加した2つ目の項目をクリックして「値フィールドの設定」を開き、「計算の種類」タブの中から「降順での順位」を選び、基準フィールドに『氏名』を指定して「OK」を押します。
これだけで、関数を1文字も書かずに、集計から順位付け、上位の絞り込みまで完了したきれいなランキング表が作れます。また、順位の数字を出す代わりに、「ホーム」タブの「条件付き書式」＞「上位/下位ルール」や「データバー」をピボットテーブルに設定して、上位の人を目立たせるのも見やすくておすすめですよ。
エラー表示の原因とIFERRORでの回避策
順位から名前を抽出する数式を入れたときに、#N/Aや#SPILL!といったエラーが出てしまったり、なんだか意図しない結果になったりすることがあります。よく起きるトラブルの原因と解決策を一覧にまとめましたので、うまくいかないときはここをチェックしてみてください。
【順位・名前抽出でよく出るエラーとトラブルの解決策】
| エラー・症状 | なぜ起きるのか（主な原因） | 具体的な直し方・回避する数式 |
|---|---|---|
| #N/A | 探している順位や点数が範囲内に見つからない。①同点で順位が飛んでいる（2位が2人いて3位がない等）、②コピー時に絶対参照（$）を忘れて範囲がズレた、③数値と文字の型違いや余計な空白がある。 | RANK.EQ+COUNTIFで連番を作る。範囲に$を付ける。TRIM関数で空白を消す。見栄えを整えるならIFNAやIFERROR（XLOOKUPは第4引数）で""にする。 |
| #SPILL! | FILTERやSORTなどの結果が広がろうとする先のセルに、すでに別の文字やスペースが入っている、またはセル結合されている。あるいはテーブル（Ctrl+T）の中にスピル数式を入れた。 | エラーのセルをクリックすると出る点線の枠（スピル領域）の中にある邪魔な文字を消す。セル結合を解除する。数式をテーブルの外に出す。 |
| #NUM! | LARGEやSMALL関数の順位指定に「0以下」の数字を入れたか、データの件数を超える順位（8人しかいないのに10位など）を指定した。 | 順位の数字を1〜データ件数以内に収める。データが足りない枠を空白にするなら=IFERROR(元の数式, "")で丸ごと囲む。 |
| #NAME? | Excel 2019以前のバージョンで、XLOOKUPやFILTER、SORT、TAKEなどの新関数を使った。または関数名のスペルミス、文字を""で囲み忘れた。 | お使いのバージョンでも動くINDEX + MATCH + LARGE + RANK.EQ + COUNTIFの組み合わせに書き換える。 |
| #DIV/0! | 飛ばさない順位を出すSUMPRODUCT((...)/COUNTIF(...))などの式で、範囲内に空白セルがあり0で割り算をしてしまった。 | 空白行を含めないように、データが実際に入っている範囲だけを指定する。 |
| 同じ名前が2回出る | 同点の人がいるのに連番化をせず、そのままMATCH(LARGE(...))やXLOOKUP(LARGE(...))を使ったため、常に1人目だけがヒットしている。 | 作業列で=RANK.EQ(C2,$C$2:$C$9)+COUNTIF($C$2:C2,C2)-1を作って連番検索するか、FILTER関数を使う。 |
| 空白や0点が最下位に出る | 未入力のセルを参照した計算式が「0」になり、SMALL関数や昇順の並び替えで1位（最上位）に来てしまう。または数字が文字列になっている。 | 元の計算式を=IF(C2="","",計算式)にして未入力時は空白文字列""にする（LARGEやSMALLは文字を無視します）。文字列数字はVALUE関数で数値に戻す。 |
まだデータが入っていない行のエラー表示を消して表をきれいに見せるには、数式の一番外側をIFERROR関数（=IFERROR(元の数式, "")）で囲んであげるのが一番手軽です。関数の組み合わせ方やエラーへの向き合い方についてさらに基礎から確認したい場合は、初心者必見！エクセル 計算式の作り方の基本と応用テクの記事も役に立つかなと思います。
なお、この記事でご紹介した関数や手順は一般的なエクセルの仕様に基づいた目安となります。会社の重要な業績評価や給与・賞与に関わる査定データ、財務報告などでランキング抽出を行う際は、計算ミスや参照ズレがないか十分にテストを行ってください。バージョンごとの細かな仕様や最新の正確な情報はMicrosoft公式サイトをご確認いただき、業務システムに関わる最終的な判断は社内のシステム管理者や専門家にご相談くださいね。
エクセルで順位から名前を抽出する手法のまとめ
ここまで、エクセルで順位から名前を抽出するための色々なやり方を見てきました。たくさんの関数が出てきましたが、最後にご自身の状況に合わせて「結局どれを使えばいいのか」がすぐわかる早見表としてまとめておきますね。
【目的・バージョン別！最適解の選び方まとめ】
- 1位（最高点）の名前を1人だけ抜きたいとき：
・新バージョン（2021/365以降）：=XLOOKUP(MAX(得点範囲), 得点範囲, 氏名範囲)
・全バージョン共通：=INDEX(氏名範囲, MATCH(MAX(得点範囲), 得点範囲, 0)) - 同点の重複を防いで上位N人のランキング表を作りたいとき：
・全バージョン共通（最も確実）： 作業列に=RANK.EQ(C2, $C$2:$C$9)+COUNTIF($C$2:C2, C2)-1で連番を作り、INDEX+MATCHかXLOOKUPで引く
・新バージョン（作業列なし）：=SORT(FILTER(表範囲, 得点範囲>=LARGE(得点範囲, 順位), ""), 得点列番号, -1)またはTAKE+SORTBY - 部署別・グループ別に順位を出して名前を抜きたいとき：
・全バージョン共通： 作業列に=COUNTIFS(部署範囲, 部署セル, 得点範囲, ">"&得点セル)+1でグループ内順位を作る
・新バージョン（作業列なし）：FILTER関数で部署を絞り込んでからSORTとTAKEで並べる - 関数を使わずに手早く上位10人を絞りたいとき：
・明細の絞り込みなら「フィルターのトップテン」、合算して順位も出すなら「ピボットテーブルの値フィルター（トップ10）と降順での順位」を使う
最初は「左側の列が引けない」「同点の人がダブってしまう」といった壁にぶつかりやすいですが、一度連番化の仕組みやINDEX＋MATCH、あるいはXLOOKUPやFILTER関数のコツを掴んでしまえば、どんな表に出会っても迷わず名前を抽出できるようになります。ぜひご自身のエクセルのバージョンや表の形に合ったやり方を試して、日々の集計作業をグッとラクにしてみてくださいね。
