エクセルで点数順の順位を付けるとき、同点の人を同順位にしたい場合や、同じ点数なら別の条件で順番を決めたい場合があります。
たとえば、テストの合計点を優先し、合計点が同じなら国語の点数が高い人を上位にするような順位付けです。
単純なRANK関数だけでは同点の並び順まで制御できないため、COUNTIFS関数、SUMPRODUCT関数、並べ替え、補助列を組み合わせる考え方が役立ちます。
複数条件の順位付けでは、最初に優先順位を決めることが大切です。
合計点を第一条件、科目点を第二条件、氏名や受験番号を第三条件としておくと、重複しない連番順位を作りやすくなります。
この記事では、1行目に見出しがある一覧表を使い、点数と条件別の順位を数式で求める方法を詳しく解説します。
エクセルで複数条件の順位を付ける基本式
それではまず、合計点と第二条件を使って重複しない順位を求める基本式について解説していきます。
| 氏名 | 合計点 | 国語 | 順位 |
|---|---|---|---|
| 佐藤 | 280 | 92 | 1 |
| 鈴木 | 270 | 88 | 2 |
| 高橋 | 270 | 84 | 3 |
| 田中 | 255 | 90 | 4 |
合計点だけで順位を付けるRANK.EQ関数
合計点だけを基準にする場合は、D2セルへRANK.EQ関数を入力します。
=RANK.EQ(B2,$B$2:$B$10,0)
最初のB2は順位を調べる対象の合計点です。
$B$2:$B$10は、比較対象となる合計点の範囲です。
最後の0は大きい数値を1位にする降順を表します。
この数式を下方向へオートフィルすれば、合計点が高い人ほど小さい順位番号になります。
ただし、270点のように同じ値が複数あると、どちらも2位となり、その次の順位は4位になります。
同点を同順位として扱うランキングなら、RANK.EQ関数だけで十分です。
一方で、名簿や成績表で1位、2位、3位と必ず連続した順位を表示したいなら、第二条件を加える必要があります。
同点を第二条件で分けるCOUNTIFS関数
合計点が同じ場合に国語点が高い人を上位にするなら、D2セルに次の数式を入力します。
=COUNTIF($B$2:$B$10,”>”&B2)+COUNTIFS($B$2:B2,B2,$C$2:C2,”>”&C2)+1
この式は、自分より合計点が高い人数を最初に数えます。
その後、同じ合計点の中で自分より国語点が高い人数を数え、最後に1を加えます。
つまり、合計点280点の佐藤さんは上位者がいないため1位です。
合計点270点の鈴木さんは、280点の人が1人いるため2位になります。
高橋さんは280点の人に加え、同じ270点で国語点が高い鈴木さんがいるため3位です。
COUNTIFS関数を使うと、第一条件が同じときだけ第二条件を比較できます。
数式中の$B$2:B2と$C$2:C2は、先頭行を固定して現在行までを広げる範囲です。
この指定により、同じ合計点かつ同じ国語点まで完全に一致するデータがある場合でも、入力順を保ちながら順位を振れます。
数式を入力するときの参照範囲
複数条件の順位付けでは、絶対参照と複合参照の違いを理解しておくとミスを減らせます。
$B$2:$B$10のように列と行の両方へ$を付けた範囲は、オートフィルしても変化しません。
全員の合計点と比較する範囲なので、ここは絶対参照にします。
対して$B$2:B2は、開始位置だけを固定する指定です。
下へコピーすると、2行目では$B$2:B2、3行目では$B$2:B3、4行目では$B$2:B4へ変化します。
この変化が、同じ値が完全に重なったときに先に現れた行を上位にする役目を果たします。
参照範囲の最終行を10行目に固定したなら、実際のデータもその行までに収める必要があります。
データが増える予定なら、表をテーブル化する方法も便利です。
順位式を作った後は、同点データを意図的に作って結果を確認しましょう。
【操作のポイント】比較範囲は絶対参照、現在行までの範囲は開始セルだけを絶対参照にすると、オートフィル後も正しく判定できます。
点数と条件別に優先順位を決める考え方
続いては、複数条件の順位付けを正しく設計するための優先順位を確認していきます。
| 優先順 | 判定項目 | 並び方 |
|---|---|---|
| 第一条件 | 合計点 | 大きい順 |
| 第二条件 | 国語点 | 大きい順 |
| 第三条件 | 受験番号 | 小さい順 |
第一条件と第二条件の決め方
複数条件の順位では、どの項目を最優先にするかで結果が変わります。
テスト成績なら合計点を第一条件にし、特定科目の点数を第二条件にする方法が一般的です。
営業成績なら売上金額を第一条件、契約件数を第二条件にすることがあります。
在庫管理なら在庫数を第一条件、入荷日を第二条件にするケースもあるでしょう。
順位付けの数式を書く前に、同じ第一条件なら何を優先するかを文章で決めてください。
判断基準が曖昧なまま数式を作ると、後から順位の根拠を説明できなくなります。
第一条件と第二条件のどちらも大きい値を優先するなら、COUNTIFSの比較演算子はどちらも>を使います。
第二条件で小さい値を優先する場合は、第二条件側だけ<に変更します。
例として、合計点が同じ場合に欠席日数が少ない人を上位にするなら、第二条件の比較は<を使います。
第三条件まで使う場合の補助列
第一条件と第二条件までが同じデータがある場合は、第三条件を追加します。
たとえば、合計点も国語点も同じなら、受験番号が小さい人を上位にするルールです。
このような条件が三つ以上になると、1本の数式は長くなり、修正時の確認も難しくなります。
実務では、判定用の補助列を作って段階的に計算すると管理しやすくなります。
まず合計点より上の人数を数える列を作り、次に同点かつ国語点が上の人数を数える列を作ります。
最後に同点かつ国語点も同じで受験番号が小さい人数を数え、各列の値を合計して1を加えます。
補助列は計算内容を見える化できるため、引き継ぎや監査にも向いています。
完成後に列を非表示にすれば、見た目をすっきり保つことも可能です。
昇順と降順を混在させる条件
順位付けでは、すべての条件を同じ方向に並べるとは限りません。
合計点は高いほど良いので降順ですが、受験番号や受付番号は小さいほど先にしたい場合があります。
このとき、数式の比較記号を条件ごとに変えます。
大きい数値を先にしたい条件には>を使います。
小さい数値を先にしたい条件には<を使います。
合計点が高い順、欠席日数が少ない順なら、第一条件は>、第二条件は<です。
日付を使う場合も同じ考え方です。
早い申込日を優先するなら、日付のシリアル値が小さいものを上位にするため<で比較します。
条件ごとに優先方向をメモしてから数式へ置き換えると、比較記号の逆転を防げます。
【操作のポイント】条件は第一、第二、第三の順に並べ、各項目について大きい順か小さい順かを先に決めておきます。
COUNTIFS関数で連番順位を作る手順
続いては、COUNTIFS関数を使って条件別の連番順位を作る具体的な手順を確認していきます。
| 氏名 | 合計点 | 英語 | 順位 |
|---|---|---|---|
| 伊藤 | 285 | 86 | 1 |
| 渡辺 | 275 | 95 | 2 |
| 山本 | 275 | 81 | 3 |
サンプルデータと数式の入力位置
ここではA列を氏名、B列を合計点、C列を英語、D列を順位として使います。
1行目は見出しであり、実際のデータは2行目から10行目まで入力されている前提です。
D2セルをクリックし、次の数式を貼り付けます。
=COUNTIF($B$2:$B$10,”>”&B2)+COUNTIFS($B$2:B2,B2,$C$2:C2,”>”&C2)+1
入力後にEnterキーを押すと、D2セルには1が表示されます。
合計点が最も高い行であれば、上位者の人数が0人になるためです。
数式バーでB列が合計点、C列が第二条件の列を参照していることを確認しましょう。
順位列を作る前に、空白行や文字列の点数が混ざっていないか確認することも重要です。
オートフィルで最終行までコピーする方法
D2セルに数式を入力できたら、セル右下に表示される小さな四角へマウスポインターを合わせます。
ポインターが黒い十字の形に変わったら、下方向へドラッグします。
隣接するB列やC列に連続データがある場合は、フィルハンドルをダブルクリックする方法も使えます。
すると、データがある最終行まで数式が自動的にコピーされます。
コピー後のD3セルでは、B2がB3に、C2がC3に自動調整されます。
一方で、$B$2:$B$10の比較範囲は固定されたままです。
オートフィル後は、最終行の数式をクリックして比較範囲がずれていないか確認しましょう。
空白行があるとダブルクリックで途中までしかコピーされないことがあるため、その場合はドラッグで範囲を指定します。
同点が完全一致した場合の扱い
合計点と英語点の両方が同じ場合、上記の数式では入力順に応じた連番になります。
これは現在行までの範囲でCOUNTIFS関数を集計しているためです。
先に入力されているデータは、同じ値の比較対象としてまだ数えられません。
後ろの行は先行する同じデータを数えるため、順位が一つ後になります。
完全一致を同順位として表示したい場合は、現在行までの範囲を使わず、両方の条件で上位者だけを集計する設計に変更します。
ただし、同順位が発生すると次の順位番号が飛ぶため、連番が必要な一覧には向きません。
順位の表示方法は、同順位を許可する競技方式か、必ず一意の順番を必要とする名簿方式かで選びます。
連番順位が必要なら、最後の同点を分ける条件を必ず一つ用意しておくと安心です。
【操作のポイント】D2に式を入れてからオートフィルし、同点の行が予想した順番になるかを実データで確認します。
SORTBY関数と並べ替えによる順位管理
続いては、Microsoft 365で使えるSORTBY関数と、エクセルの並べ替え機能を使った順位管理を確認していきます。
| 氏名 | 合計点 | 国語 | 並び順 |
|---|---|---|---|
| 佐藤 | 280 | 92 | 1 |
| 鈴木 | 270 | 88 | 2 |
| 高橋 | 270 | 84 | 3 |
SORTBY関数で複数条件の一覧を作る方法
SORTBY関数は、元の表を変更せずに、指定した条件順の一覧を別の場所へ表示できる関数です。
たとえば、A2:C10の表を合計点の降順、国語点の降順に並べたい場合は、空いているセルへ次の数式を入力します。
=SORTBY(A2:C10,B2:B10,-1,C2:C10,-1)
-1は降順、1は昇順を表します。
この式ではB列の合計点を優先して大きい順に並べ、同じ合計点の中ではC列の国語点が大きい順になります。
並び替えた結果の横に、SEQUENCE関数で1からの連番を表示すれば、順位表として利用できます。
=SEQUENCE(ROWS(A2:A10))
SORTBY関数は元データを動かさず、常に最新の順位一覧を表示できる点が魅力です。
ただし、古いエクセルではSORTBY関数が使えないため、その場合はCOUNTIFS関数か手動の並べ替えを使います。
データタブの並べ替えで条件を追加する方法
関数を使わず、その場で順位順に並べたい場合は、データタブの並べ替え機能が便利です。
表内の任意のセルをクリックしてから、データタブの並べ替えを選びます。
最優先されるキーに合計点を指定し、順序を降順に設定します。
次にレベルの追加を選択し、次に優先されるキーとして国語を指定して降順にします。
さらに同点を分けたいなら、受験番号を追加して昇順に設定できます。
並べ替えでは、関連する列をすべて含めた表全体を選択することが重要です。
一部の列だけを並べ替えると、氏名と点数の対応が崩れるおそれがあります。
元の順番を残したい場合は、事前に連番の管理列を追加しておくと復元しやすくなります。
順位番号を並べ替え後に表示する方法
並べ替えを完了した表では、順位列へ単純な連番を入れる方法があります。
データが2行目から始まる場合、D2セルに次の数式を入力します。
=ROW()-1
この数式は、行番号から見出し行の1を引き、2行目なら1位、3行目なら2位と表示します。
並べ替え済みの一覧で順位を見せるだけなら、COUNTIFS関数よりも見やすく簡単です。
ただし、新しいデータを追加したときや、元の表順へ戻したときは順位も変わります。
常に条件に従った順位を持たせたいなら、元データ側にCOUNTIFSの順位列を作る方法が向いています。
並べ替え後の連番は表示用、COUNTIFSの順位は判定用として使い分けると整理しやすくなります。
【操作のポイント】Microsoft 365ではSORTBY関数、一覧を一時的に整える場合はデータタブの並べ替えを選ぶと効率的です。
複数条件の順位付けで起こりやすいエラー
続いては、複数条件で順位を付ける際に発生しやすいエラーと対処方法を確認していきます。
| 症状 | 主な原因 | 確認箇所 |
|---|---|---|
| 順位が重複する | 条件不足 | 第三条件 |
| 順位が飛ぶ | RANK.EQの同順位 | 順位方式 |
| 並びが逆 | 比較記号の逆転 | 昇順と降順 |
順位が重複する場合の確認項目
複数条件を設定したのに同じ順位が表示される場合は、条件列の参照先を確認します。
たとえば、合計点と国語点で順位を分けるつもりが、COUNTIFS関数でB列を二度参照していることがあります。
第二条件はC列など、実際に比較したい列を指定しなければなりません。
また、合計点と第二条件が完全に一致するデータがあるなら、第三条件を追加する必要があります。
受験番号、社員番号、入力順の連番など、必ず異なる値になる列が第三条件に適しています。
すべての同点を一意に並べたい場合、最後の条件には重複しない管理番号を使うと確実です。
文字列の氏名を条件に使うこともできますが、あいうえお順やアルファベット順が意図した規則か事前に確認しましょう。
空白セルと文字列が混ざる場合の対処
点数列に空白セルや未入力の文字列が含まれると、順位が予想と異なることがあります。
空白の人へ順位を表示したくないなら、IF関数で数式を囲みます。
=IF(B2=””,””,COUNTIF($B$2:$B$10,”>”&B2)+COUNTIFS($B$2:B2,B2,$C$2:C2,”>”&C2)+1)
この式では、B2が空白なら空白を返し、点数があるときだけ順位計算を実行します。
点数が数値ではなく文字列として入力されていると、比較が正しく行われない場合があります。
セルの左上に緑の三角が出ているときは、数値が文字列として保存されている可能性があります。
エラー表示から数値に変換するか、VALUE関数で数値化してから計算しましょう。
表の範囲が増えた場合の修正方法
比較範囲を$B$2:$B$10と指定している状態で11行目以降にデータを追加すると、新しいデータは順位計算の対象外になります。
この場合は、数式の最終行を実際のデータに合わせて修正します。
たとえば20行目まで増えたなら、$B$2:$B$20へ変更します。
より手間を減らしたい場合は、データ範囲をテーブルとして設定します。
テーブル内に追加した行は数式の対象へ自動的に含まれやすく、行ごとの数式コピーも自動化できます。
定期的にデータが増える管理表では、最初からテーブル化しておくと範囲漏れを防げます。
【操作のポイント】順位が正しくないときは、条件列、比較記号、絶対参照の範囲、空白セルの順に確認します。
まとめ エクセルで条件別に複数順位を付ける方法
エクセルで点数を複数条件に対応させて順位付けする場合は、最初に第一条件、第二条件、第三条件の優先順を整理することが重要です。
合計点だけの同順位でよければRANK.EQ関数が手軽です。
同点を別の点数や条件で分け、1位から連続する順位を作りたい場合は、COUNTIF関数とCOUNTIFS関数を組み合わせます。
合計点が高い人を優先し、同点なら科目点が高い人を優先する式は、成績表や評価表で幅広く使えます。
完全に同じ条件のデータまで連番にしたいときは、受験番号や管理番号などの第三条件を追加しましょう。
Microsoft 365を利用している場合は、SORTBY関数で条件別に並べ替えた一覧を別の場所へ作る方法も便利です。
データが増える表では、比較範囲の最終行が不足していないか、テーブル化で自動拡張できないかを確認してください。
複数条件の順位付けを一度作っておけば、毎月の集計やテスト結果の整理を正確かつ短時間で進められるようになります。
