大会の順位表、営業成績の評価表、社内コンテストなどでは、順位に合わせてポイントを付ける場面があります。
しかし、順位ごとに点数を手入力すると、同順位や参加者数の変更があったときに修正漏れが起こりやすくなります。
エクセルならRANK関数、IF関数、XLOOKUP関数などを組み合わせることで、ランキング別の点数を自動計算できます。
順位と点数の対応表を別に用意して参照する方法は、配点ルールが変わっても式を直さずに済む実用的な方法です。
1行目に見出しがある表なら、順位の計算結果を使って2行目から数式を入力し、オートフィルで下方向へコピーできます。
この記事では、順位に応じて点数を付ける基本式から、同順位、参加者数、エラー表示への対応までを順番に解説します。
エクセルで順位別ポイントを自動計算する方法
それではまず、順位に応じた点数を最も管理しやすく自動計算する方法について解説していきます。
| 氏名 | 得点 | 順位 | ポイント |
|---|---|---|---|
| 佐藤 | 92 | 1 | 10 |
| 鈴木 | 88 | 2 | 7 |
| 高橋 | 81 | 3 | 5 |
| 田中 | 75 | 4 | 3 |
順位とポイント表の配置
まず、A列に氏名、B列に得点、C列に順位、D列にポイントを入力する一覧表を作成します。
さらに、F列とG列には順位と配点の対応表を置きます。
たとえばF2からG5に、1位は10点、2位は7点、3位は5点、4位以下は3点というルールを入力します。
対応表の例です。
F2に1、G2に10、F3に2、G3に7、F4に3、G4に5、F5に4、G5に3を入力します。
配点表を数式の外に置くことで、ルール変更に強い順位表になります。
順位の数値とポイントの数値を分離しておくことが、後から見直しやすい表づくりの基本です。
RANK関数による順位の算出
C2には、B列の得点から順位を求める数式を入力します。
=RANK.EQ(B2,$B$2:$B$11,0)
RANK.EQ関数は、指定した数値が範囲内で何位かを返す関数です。
最初のB2は判定したい得点、$B$2:$B$11は全員の得点範囲、最後の0は大きい得点を1位にする指定です。
$記号で範囲を固定することにより、下へコピーしても比較対象がずれません。
数式をC2に入力したら、セル右下のフィルハンドルを使って最終行までコピーしましょう。
【操作のポイント】得点の範囲は空白行を含めず、実際の参加者が入っている最終行までを絶対参照で指定します。
XLOOKUP関数によるポイントの取得
D2には、C2で求めた順位を使ってポイント表から配点を取り出す式を入力します。
=XLOOKUP(C2,$F$2:$F$5,$G$2:$G$5,3,-1)
XLOOKUP関数の最初のC2は検索する順位です。
$F$2:$F$5は順位の検索列、$G$2:$G$5は返したいポイント列を表します。
4番目の3は、該当する順位がないときに返す値です。
最後のマイナス1は、検索値以下で最も近い順位を探す指定であり、4位、5位、6位でも4位以下の3点を返せます。
XLOOKUP関数なら、順位ごとの点数を数式内に並べずに済みます。
【操作のポイント】4位以下を同じ点数にする場合は、対応表の最後に4とその配点を登録しておくと管理が簡単です。
IF関数によるランキング別の配点設定
続いては、少人数の表で順位ごとの配点を直接数式に書く方法を確認していきます。
| 順位 | 配点ルール | 入力結果 |
|---|---|---|
| 1 | 10点 | 10 |
| 2 | 7点 | 7 |
| 3 | 5点 | 5 |
| 4以上 | 3点 | 3 |
入れ子のIF関数による基本式
配点の種類が少ない場合は、D2にIF関数を入れ子で入力する方法も使えます。
=IF(C2=1,10,IF(C2=2,7,IF(C2=3,5,3)))
この式では、C2が1なら10、2なら7、3なら5を返し、それ以外は3を返します。
順位表だけを別に用意しなくても作れるため、小規模なイベントでは手早い方法です。
一方で、配点の段階が増えるほど数式が長くなり、修正しにくくなります。
1位から10位まで異なる点数にするようなルールでは、対応表を使う方法が向いています。
IFS関数による条件分岐
Microsoft 365やExcel 2019以降では、IFS関数を使うと複数条件を少し読みやすく記述できます。
=IFS(C2=1,10,C2=2,7,C2=3,5,C2>=4,3)
IFS関数は、条件と結果を順番に並べて判定します。
最後にC2が4以上という条件を置くことで、4位以下に共通の点数を与えられます。
ただし、順位が空白のときまで3点を表示したくない場合は、先頭に空白判定を加える必要があります。
=IF(C2=””,””,IFS(C2=1,10,C2=2,7,C2=3,5,C2>=4,3))
空白セルには空白を返す式にしておくと、未入力の参加者欄に不要なポイントが表示されません。
【操作のポイント】数式を完成させてから下へコピーし、空白行にも意図しない点数が出ていないか確認します。
SWITCH関数と配点表の使い分け
順位が1位、2位、3位のように完全一致で決まる場合は、SWITCH関数も選択肢になります。
=SWITCH(C2,1,10,2,7,3,5,3)
最後の3はどの条件にも一致しないときの既定値です。
ただし、5位以下は1点、11位以下は0点など、順位帯が複数あるルールでは数式が分かりにくくなることがあります。
その場合はXLOOKUP関数と対応表を使うほうが、配点基準を目で確認できます。
数式の短さだけでなく、担当者が変更できるかどうかも方法を選ぶ基準です。
【操作のポイント】固定的で簡単な配点はIF関数、変更の可能性がある配点は対応表と検索関数を選びます。
同順位と順位飛ばしへの対応
続いては、同じ得点の人がいるときの順位表示とポイント処理を確認していきます。
| 氏名 | 得点 | RANK.EQの順位 | ポイント |
|---|---|---|---|
| 佐藤 | 90 | 1 | 10 |
| 鈴木 | 85 | 2 | 7 |
| 高橋 | 85 | 2 | 7 |
| 田中 | 80 | 4 | 3 |
RANK.EQ関数の同順位処理
RANK.EQ関数では、同じ得点には同じ順位が表示されます。
上の例では鈴木さんと高橋さんがともに2位となり、その次の順位は4位になります。
これは競技や成績表でよく使われる順位飛ばしの方式です。
同順位の人に同じポイントを与えるルールなら、RANK.EQ関数と配点表をそのまま組み合わせられます。
4位以下を3点にする設定なら、次の田中さんにも3点が表示されます。
【操作のポイント】同順位の扱いは、集計を始める前に順位表の運用ルールとして共有しておきます。
RANK.AVG関数による平均順位
RANK.AVG関数は、同順位が占める順位の平均を返します。
2位と3位が同順位なら、どちらにも2.5という順位を返す仕組みです。
=RANK.AVG(B2,$B$2:$B$11,0)
平均順位は統計処理では便利ですが、順位別ポイントを直接付ける用途には注意が必要です。
対応表が整数の順位だけで作られている場合、2.5を検索して意図どおりの配点にならない可能性があります。
順位にポイントを付ける一覧では、原則としてRANK.EQ関数のほうが扱いやすいでしょう。
同順位のポイント配分ルール
同順位の扱いは数式だけでなく、ポイント配分のルールにも影響します。
たとえば1位が2人いる場合、両者に10点を渡すのか、1位と2位の点数を合計して等分するのかで結果が変わります。
前者なら通常の検索式で対応できます。
後者のように平均配点を求める場合は、順位だけでなく同じ得点の人数をCOUNTIF関数などで数える設計が必要です。
複雑なルールでは、計算式より先に配点規程を文章で確定させることが大切です。
【操作のポイント】同点者全員に同じ点を付けるのか、ポイントを分けるのかを表の注記にも明記します。
得点順の順位表とオートフィル操作
続いては、得点から順位とポイントを入力し、数式をオートフィルで広げる実際の操作を確認していきます。
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 氏名 | 得点 | 順位 | ポイント |
| 佐藤 | 92 | =RANK.EQ(B2,$B$2:$B$6,0) | =XLOOKUP(C2,$F$2:$F$5,$G$2:$G$5,3,-1) |
順位列への数式入力
まずC2セルを選択して、順位を求めるRANK.EQ関数を入力します。
数式バーに入力したらEnterキーを押し、1位などの順位が表示されることを確認します。
ここで比較範囲を絶対参照にしていないと、下の行へコピーした際に範囲がずれて正しい順位になりません。
F4キーで参照範囲に$記号を付ける操作を覚えておくと、数式作成が速くなります。
ポイント列への検索式入力
次にD2セルを選択し、順位に対応する点数を返すXLOOKUP関数を入力します。
対応表がF2からG5にある場合、検索列と戻り列も絶対参照にします。
1位の行に10点が表示されたら、順位とポイントの連動は成功です。
順位の式とポイントの式は別列に置くことで、誤った結果が出たときにどちらの計算を確認すべきか判断しやすくなります。
【操作のポイント】得点、順位、ポイントの列を分けると、関数の検証と印刷時の確認が行いやすくなります。
フィルハンドルによる数式コピー
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 氏名 | 得点 | 順位 | ポイント | |
| 2 | 佐藤 | 92 | 1 | 10 ▪ | |
| 3 | 鈴木 | 88 | 2 | 7 | |
| 4 | 高橋 | 81 | 3 | 5 |
数式が入ったD2セルの右下にある小さな四角を、最終行まで下へドラッグします。
このフィルハンドル操作により、行番号だけが変わった数式が各行に自動入力されます。
データが連続している場合は、フィルハンドルをダブルクリックしても隣接列の最終行までコピーできます。
コピー後は最下行のポイントまで確認し、空白やエラーがないかを見ます。
【操作のポイント】数式をコピーした直後に、上位、途中、最下位の3行を確認すると参照ミスを早く見つけられます。
参加者数と配点ルールの応用
続いては、参加者数に応じて配点を変える場合や、得点順を整える応用方法を確認していきます。
| 参加者数 | 1位 | 2位 | 3位 | 4位以下 |
|---|---|---|---|---|
| 10人以上 | 10 | 7 | 5 | 3 |
| 5人から9人 | 7 | 5 | 3 | 1 |
COUNTA関数による参加者数の確認
参加人数を条件に配点を変えるなら、氏名欄の入力数をCOUNTA関数で数えます。
=COUNTA(A2:A11)
この式はA2からA11のうち、空白ではないセルの個数を返します。
たとえば参加者数をH2で求めるなら、ポイント列の式でH2を参照できます。
人数を手入力せず一覧から数える方法なら、参加者の追加や欠席者の削除にも対応しやすくなります。
人数別のポイント表の設計
人数ごとにルールが異なる場合は、配点表を人数区分ごとに分けて作成します。
小さな表ではIF関数で人数を判定してもよいですが、区分が多いなら別シートにルール表を作る方法が便利です。
大会ごとに配点が変わる場合でも、ポイント表の数字を書き換えるだけで同じ数式を使えます。
配点ルールをセルに持たせる設計は、引き継ぎ資料としても役立ちます。
【操作のポイント】参加者数の境目となる人数を事前にテストし、想定どおりの配点になるか確認します。
SORT関数と並べ替えの使い方
順位を表示したあとに、表そのものを得点の高い順に並べたい場合があります。
元データを並べ替えるなら、表内のセルを選択してデータタブの並べ替えから得点列を降順に指定します。
元の入力順を残したい場合は、別の場所にSORT関数を使う方法もあります。
=SORT(A2:D11,2,-1)
この式はA2からD11の表を、2列目である得点を基準に降順で表示します。
順位の計算範囲と並べ替え後の表示範囲を混同しないことが、集計ミスを防ぐ要点です。
【操作のポイント】提出用の順位表は、元データを複製したシートで並べ替えると修正履歴を残しやすくなります。
まとめ エクセルで順位に応じて点数を付ける方法
エクセルでランキング別のポイントを自動計算するには、まずRANK.EQ関数で得点順位を求め、XLOOKUP関数で対応表から配点を取得する方法が基本です。
配点表を別セルに作る方法は、順位別のポイントを後から変更しやすく、実務で特に使いやすい設計です。
配点の種類が少ない場合はIF関数やIFS関数でも対応できますが、条件が増えるほど対応表を使う利点が大きくなります。
同順位ではRANK.EQ関数が同じ順位を返し、次の順位を飛ばす点にも注意しましょう。
参加者数によってポイントが変わる場合はCOUNTA関数で人数を数え、配点ルールを表として管理すると安全です。
順位、ポイント、対応表を分け、数式の絶対参照とオートフィルを正しく使えば、更新に強いランキング表を作成できます。
