エクセルで売上、得点、評価点などを管理していると、数値の変化に合わせて順位表も自動更新したくなる場面があります。
手作業で並び替える方法では、データを追加するたびに操作が必要になり、順位の付け間違いや同順位の扱いにも注意が必要です。
そこで役立つのが、RANK.EQ関数とSORTBY関数を組み合わせた自動ランキング表です。
順位表を自動化する基本の流れです。
元データに順位の数式を入力します。
ランキング表示用の表で並び替え関数を使います。
テーブル化と条件付き書式で更新しやすいデザインに整えます。
この記事では、1行目に見出しがある売上データを例に、ランキング表の作り方、自動並び替え、同順位の処理、見やすいテンプレートの整え方まで解説します。
Microsoft 365やExcel 2021以降では動的配列関数を使うと便利ですが、古いバージョンでも使える数式もあわせて確認しましょう。
エクセルで順位表を自動作成する方法
それではまず、数値を入力するだけで順位が計算される基本のランキング表について解説していきます。
| 氏名 | 売上 | 順位 |
|---|---|---|
| 佐藤 | 125000 | 2 |
| 鈴木 | 148000 | 1 |
| 高橋 | 98000 | 3 |
順位列と元データの配置
順位表を作る前に、A列へ氏名、B列へ売上、C列へ順位というように、項目ごとの役割を決めておきます。
ここでは1行目をヘッダーとし、A2からA11に氏名、B2からB11に売上金額が入力されているものとします。
順位を求める対象範囲は、見出しを含めずにB2からB11のように設定することが大切です。
見出しセルの文字列を範囲に含めても計算結果が大きく崩れるとは限りませんが、数式の意味が分かりにくくなり、データ追加時の修正も増えます。
売上の列は、数値として入力されていることを確認しましょう。
数値が左寄せで表示される場合や、数字の前後に空白がある場合は、文字列として保存されているかもしれません。
サンプルの列構成です。
A列は氏名です。
B列は売上です。
C列は順位です。
D列以降は部門、目標、達成率などの補助項目として追加できます。
RANK.EQ関数による順位計算
C2セルを選択し、次の数式を入力します。
=RANK.EQ(B2,$B$2:$B$11,0)
RANK.EQ関数は、指定した数値が範囲内で何位になるかを返す関数です。
最初のB2は、順位を調べたい売上セルです。
次の$B$2:$B$11は、順位を比較する売上全体の範囲です。
最後の0は大きい数値を1位にする指定であり、売上や得点のランキングでは通常こちらを使います。
$記号を付けた絶対参照は、数式を下へコピーしても比較範囲を固定する役割があります。
C2に数式を入れたら、セル右下の小さな四角を下方向へドラッグして、必要な行までオートフィルします。
売上が最大の人には1、次に大きい人には2が表示され、入力値を変えると順位も自動で再計算されます。
【操作のポイント】比較範囲だけを絶対参照にし、判定するセルB2は相対参照のままにすると、下方向へのコピーが正しく機能します。
昇順と降順の使い分け
売上や試験の得点では、数字が大きいほど上位になるため、RANK.EQ関数の第3引数には0を指定します。
一方で、タイム、作業時間、欠席日数のように数字が小さいほど良い項目では、第3引数を1に変更します。
=RANK.EQ(B2,$B$2:$B$11,1)
第3引数を1にすると、最小値が1位になります。
ランキングの目的に合わせて、数値が大きいほど良いのか、小さいほど良いのかを先に決めることが、順位表づくりの基本です。
複数の評価項目を扱う場合は、総合点用の列を別に作り、その総合点を基準に順位を求めると見通しが良くなります。
総合点は、単純な合計だけでなく、評価項目ごとに重み付けした計算式でも構いません。
【操作のポイント】売上は降順、時間は昇順というように、ランキングの基準によって第3引数を0または1へ切り替えましょう。
同順位と連番順位の計算式
続いては、同じ売上や得点がある場合の順位表示と、連番にしたい場合の考え方を確認していきます。
| 氏名 | 得点 | RANK.EQ順位 |
|---|---|---|
| 田中 | 90 | 1 |
| 伊藤 | 90 | 1 |
| 渡辺 | 85 | 3 |
RANK.EQ関数の同順位表示
RANK.EQ関数は、同じ数値があると同じ順位を返します。
たとえば90点が2人いる場合は、どちらも1位となり、その次の85点は3位です。
これは競技や試験でよく使われる、順位の欠番が生じる方式です。
同点者を同じ順位として扱う必要があるなら、RANK.EQ関数だけで自然に対応できます。
順位表を見る人にとっても、同じ成績であることがひと目で伝わります。
ただし、1位、1位、3位のような表示を見慣れていない場合は、あらかじめ表の説明欄に同順位の扱いを記載しておくと親切です。
RANK.EQの順位は同点で同順位になります。
1位が2人なら次は3位です。
順位の欠番は計算ミスではありません。
COUNTIF関数による重複順位の調整
同じ売上であっても、表の中では1位、2位、3位のように必ず連番で表示したいケースもあります。
その場合は、RANK.EQ関数にCOUNTIF関数を組み合わせます。
=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1
前半のRANK.EQ関数は基本順位を求めています。
後半のCOUNTIF($B$2:B2,B2)-1は、現在の行までに同じ数値が何回現れたかを数え、2件目以降にだけ順位を加算する部分です。
同じ90点が最初に出た行ではCOUNTIFの結果が1になるため、加算値は0です。
同じ90点が2回目に出た行では結果が2になり、加算値が1になるため、順位は2位になります。
この数式では、同点者の並び順が元データの上から順番に決まる点を理解しておきましょう。
【操作のポイント】連番順位にする数式では、COUNTIFの開始セルだけを絶対参照にし、終点は現在行まで伸びる形にします。
平均順位と評価ルールの整理
RANK.AVG関数を使うと、同順位の人に平均順位を返せます。
1位と2位が同点の場合は、両者に1.5位を表示する計算です。
=RANK.AVG(B2,$B$2:$B$11,0)
平均順位は統計処理には向いていますが、社内ランキングや表彰順位では分かりにくい場合があります。
そのため、実務ではRANK.EQによる同順位、またはCOUNTIFを加えた連番順位のどちらかが選ばれることが多いでしょう。
順位を確定して表彰や評価に使うなら、同点時にどの項目で優先順位を決めるかも重要です。
たとえば売上が同じ場合に利益額、件数、登録日時の順に比較する、といったルールをあらかじめ決めておくと運用が安定します。
【操作のポイント】順位の数式を決める前に、同点を同順位にするか、連番にするか、別の基準で順位を分けるかを共有しておきましょう。
SORTBY関数による自動並び替え
続いては、元データを動かさずに、順位順のランキング表を別の場所へ自動表示する方法を確認していきます。
| 元データ | ||
|---|---|---|
| 氏名 | 売上 | 部門 |
| 佐藤 | 125000 | 営業 |
| 鈴木 | 148000 | 営業 |
SORTBY関数の基本構文
Microsoft 365やExcel 2021以降では、SORTBY関数を使ってランキング用の一覧を自動並び替えできます。
たとえばA列からC列までを元データとし、売上がB列にある場合は、ランキング表の先頭セルへ次の数式を入力します。
=SORTBY(A2:C11,B2:B11,-1)
最初のA2:C11は、並び替えて表示したいデータ全体です。
次のB2:B11は、並び替えの基準となる売上列です。
最後の-1は降順を意味するため、売上が大きい順に表示されます。
SORTBY関数は結果を自動で複数セルへ展開するため、数式を1つ入力するだけで表全体を作成できるのが特長です。
元データの売上を修正すると、ランキング表もすぐに入れ替わります。
元データの行順を保存したまま、閲覧用のランキングだけを作れる点も実務で便利です。
【操作のポイント】SORTBY関数を入力するセルの右側と下側は空白にして、展開先にデータがない状態で使用します。
順位列を含めたランキング表
ランキング表には氏名と売上だけでなく、順位も表示すると分かりやすくなります。
元データのC列にRANK.EQ関数で順位を作成している場合は、A2:C11をSORTBY関数の対象に含めるだけで構いません。
=SORTBY(A2:C11,B2:B11,-1)
この数式では氏名、売上、順位の3列が売上の大きい順に表示されます。
元データに順位列を作りたくない場合は、ランキング表の左側にSEQUENCE関数で連番を表示する方法もあります。
ただしSEQUENCE関数は、同点を考慮せずに1、2、3と連番を返します。
同順位を示したい表なら、RANK.EQ関数による順位列を元データ側に用意する方法が安全です。
順位の意味と並び順の意味は別であり、同点がある表では特に区別して考える必要があります。
【操作のポイント】同順位を表示したいときはRANK.EQの順位列を使い、単なる一覧の行番号にはSEQUENCE関数を使い分けます。
FILTER関数との組み合わせ
部門別、担当者別、期間別にランキングを表示したい場合は、FILTER関数とSORTBY関数を組み合わせます。
たとえばC列が部門で、F1セルに表示したい部門名が入力されている場合は、次のように設定できます。
=SORTBY(FILTER(A2:C11,C2:C11=F1),FILTER(B2:B11,C2:C11=F1),-1)
FILTER関数が指定部門の行だけを取り出し、SORTBY関数がその結果を売上順に並び替えます。
F1セルを営業から企画へ変更すれば、同じ数式のまま部門別ランキングを切り替えられます。
対象がない場合にエラーを避けたいときは、FILTER関数の第3引数に該当なしと表示する文字列を指定します。
ランキング表を会議資料やダッシュボードに使う場合、条件を入力するセルを目立つ位置へ置くと、閲覧する人が自分で表示を切り替えやすくなります。
【操作のポイント】部門名の表記ゆれがあるとFILTER関数で抽出できないため、入力規則のリストを使って表記を統一しましょう。
テーブル機能とランキング表のデザイン
続いては、データの追加にも対応しやすいテーブル機能と、見やすいランキング表のデザインを確認していきます。
| 順位 | 氏名 | 売上 | 達成率 |
|---|---|---|---|
| 1 | 鈴木 | 148000円 | 118% |
| 2 | 佐藤 | 125000円 | 104% |
テーブル化による参照範囲の自動拡張
元データを選択し、挿入タブからテーブルを選ぶと、入力範囲をテーブルとして管理できます。
見出しを含む範囲を選択してから操作し、先頭行をテーブルの見出しとして使用する設定を確認しましょう。
テーブル化すると、新しい行を末尾に追加したときに、数式や書式が自動で引き継がれます。
データ件数が毎月増える順位表では、固定範囲の数式よりテーブル参照の方が管理しやすいでしょう。
テーブル名を売上表などの分かりやすい名前に変更すると、数式の意味も把握しやすくなります。
テーブル名を売上表とした場合の順位式です。
=RANK.EQ([@売上],売上表[売上],0)
[@売上]は現在行の売上を表し、売上表[売上]はテーブル内の売上列全体を表します。
通常のセル番地より文字数は増えますが、列の役割が読み取りやすい書き方です。
【操作のポイント】テーブル化する前に空白行や結合セルをなくし、1行目の見出しを明確にしておくと安定します。
上位順位を目立たせる条件付き書式
順位表は、上位者がひと目で分かるデザインに整えると活用しやすくなります。
順位列を選択してホームタブの条件付き書式を開き、上位10項目などのルールを設定できます。
上位3位までだけを色分けしたい場合は、数式を使用して書式設定するルールが便利です。
=$C2<=3
この数式を適用範囲の先頭行に合わせて設定すると、C列の順位が3以下の行を強調できます。
順位列だけでなく行全体に書式を適用すると、誰が上位なのかを見つけやすくなります。
1位は金色系、2位は銀色系、3位は銅色系にすると、一般的な順位表の印象に近づきます。
ただし色だけに頼ると印刷時や白黒表示で判別しにくいため、順位数字も大きく見やすく整えるとよいでしょう。
【操作のポイント】条件付き書式の適用先を確認し、見出し行ではなく実際のデータ行だけを対象に設定します。
Excel画面でのランキング表示設定
ランキング表を作成したら、列幅、表示形式、フィルターなどを整えて、確認しやすいレイアウトに仕上げましょう。
売上列には桁区切りと通貨記号を設定し、順位列は中央揃えにすると読みやすくなります。
上のイメージでは、順位列のC2セルを起点に数式を確認し、条件付き書式で上位者を見分けやすくしています。
見やすいランキング表では、情報を増やしすぎず、順位、名前、評価値の3要素を最初に読めるようにすることが重要です。
フィルターボタンを利用すれば、特定部門だけを表示した状態で順位を確認することもできます。
【操作のポイント】順位、氏名、売上を左から近い位置に並べ、金額や比率の表示形式を統一するとランキング表の視認性が高まります。
テンプレート化とデータ更新の管理
続いては、毎月や毎週の集計で繰り返し使えるランキング表のテンプレート化について確認していきます。
| 対象月 | データ更新日 | ランキング状態 |
|---|---|---|
| 2026年8月 | 2026年8月25日 | 自動更新 |
入力用シートと表示用シートの分離
日常的に更新する順位表は、データを入力するシートとランキングを表示するシートを分けると管理しやすくなります。
入力用シートには元データだけを置き、表示用シートにはSORTBY関数、グラフ、上位者一覧などを配置します。
この構成にすると、入力担当者が元データを更新しても、レイアウトを崩す可能性を減らせます。
元データを直接並び替えず、表示用シートで並べ替えた結果を参照する設計にすると、履歴確認にも役立ちます。
シート名はデータ、ランキング、設定など、用途が分かる短い名前にするとよいでしょう。
テンプレートの基本構成です。
データシートは入力専用です。
ランキングシートは閲覧専用です。
設定シートには対象月や部門名の候補を置きます。
空白セルとエラー表示の処理
データが未入力の行まで順位の数式をコピーすると、空白セルにも順位が表示されることがあります。
空白行では順位を表示しないようにするには、IF関数で判定を追加します。
=IF(B2=””,””,RANK.EQ(B2,$B$2:$B$11,0))
この数式では、B2が空白なら空白を返し、値があるときだけRANK.EQ関数を実行します。
空白セル対策を入れておくと、将来のデータ追加に備えたテンプレートになります。
SORTBY関数とFILTER関数を使うランキング表でも、FILTER関数で売上列が空白ではない行だけを抽出すると表示が整います。
数式エラーをIFERROR関数で一律に隠す方法もありますが、設定ミスまで見えなくなることがあります。
まずはエラーの原因を確認し、想定された空白や該当なしだけを処理する考え方が安心です。
【操作のポイント】未入力の行があるテンプレートでは、IF関数を使って空白時の表示を制御してから数式を下へコピーします。
更新日時と印刷レイアウトの設定
共有用のランキング表には、いつのデータなのかが分かる更新日時を表示しておくと便利です。
今日の日付だけを表示する場合はTODAY関数、日時まで表示する場合はNOW関数を使えます。
=TODAY()
ただしNOW関数はブックの再計算時に変わるため、確定した更新日時を残したい場合には手入力やショートカットで入力する方法が向いています。
印刷する場合は、ページレイアウトタブで横向き、余白、印刷範囲を確認しましょう。
ランキング表の見出し行を各ページに印刷する設定にすると、複数ページになった場合も内容を追いやすくなります。
画面上だけでなく、PDF化や印刷時の見え方まで確認してテンプレートを完成させることが実用的です。
【操作のポイント】対象月、更新日、集計条件を表の上部にまとめると、別の人へ共有した際もランキングの前提が伝わります。
まとめ エクセルの順位表を自動で作成する方法
エクセルの順位表を自動で作成するには、まずRANK.EQ関数で数値に対応した順位を計算します。
売上や得点のように大きい数値を上位にする場合は、=RANK.EQ(B2,$B$2:$B$11,0)の形が基本です。
同点を同順位で表示するならRANK.EQ関数を使い、必ず連番にするならCOUNTIF関数を組み合わせます。
Microsoft 365やExcel 2021以降では、SORTBY関数を使うことで、元データを変更せずに売上順のランキング表を自動表示できます。
データ量が増える運用では、テーブル化によって数式や書式の適用範囲を自動で広げられます。
上位者を条件付き書式で強調し、金額、順位、更新日を読みやすく配置すれば、共有しやすいランキング表になります。
入力用シートと表示用シートを分け、空白セルへの対策も加えたテンプレートにしておくと、毎月の集計作業を大きく省力化できるでしょう。
