同点の次を飛ばさず、グループ別に順位を付けるには？
同点を1・1・2位のように順位付けする方法です。グループごとの点数一覧を作り、各点数以上の値が何種類あるかを数えて順位にします。
同点があった後も番号を飛ばさず、1・1・2・3位と並べたい場合は、グループ内の点数を重複なく一覧にします。その一覧を使い、各項目について自分の点数以上の値を数えると順位が求められます。JOIN SHEETでは結合と集計を組み合わせる方法で、専用の順位ボタンや自動連番を使うわけではありません。
GroupとEntryで項目を識別する
この架空の例では高い点数ほど上位です。同じGroup内でEntryが重ならないようにします。たとえば同姓同名の2人に同じEntryを使うと、後の集計で1人にまとまってしまいます。別々のIDを用意してください。
| Group | Entry | Score |
|---|---|---|
| G1 | A | 90 |
| G1 | B | 90 |
| G1 | C | 70 |
| G1 | D | 50 |
| G2 | E | 70 |
| G2 | F | 50 |
G1の90点の2項目はどちらも1位で、70点は2位です。G2のEも70点ですが、G2内では1位です。表全体を並べ替えるだけでは、このグループ別順位の列は作れません。
グループ内の各点数を1回だけ残す
- Scoresを読み込み、Scoreが数値型であることを確認して結果の基準にします。空欄の点数は除き、元データと点数一覧で同じ対象を使います。
- 「結果を整理」の「集計」でGroupとScoreを「まとめる項目」に指定します。「集計方法」は「件数」のまま、「結果の項目名」にOccurrencesと入力し、「適用」して次の5行を確認します。
- この5行を元の点数表と結合できるよう、「結果を保存」でExcelに保存し、Levelsというシートとして読み込み直します。Occurrencesは正しくまとめられたかの確認に使い、最終的な順位には使いません。
| Group | Score | Occurrences |
|---|---|---|
| G1 | 90 | 2 |
| G1 | 70 | 1 |
| G1 | 50 | 1 |
| G2 | 70 | 1 |
| G2 | 50 | 1 |
自分以上の点数の種類を数える
新しい作業に元の点数表Scoresと、保存した点数一覧Levelsを読み込みます。Group同士を「一致する行のみ」で結合し、Scoresを結果の基準にします。これで各項目に同じグループの点数一覧が付きます。Levels側のGroupとOccurrencesは非表示にし、比較に使うScoreは表示したままにします。
「結果を整理」で次の順序に設定します。
- 「計算」でLevelsのScoreから元のScoreを引き、ScoreGapにします。この例では列名が重複するため、2つ目の点数はLevels_Scoreと表示されます。
- 「絞り込み」でScoreGapが0「以上」の行を残します。同じ点数も含めることで、最高点の順位が1になります。
- 「集計」を追加し、元のGroup、Entry、Scoreを「まとめる項目」で1つずつ選びます。「集計方法」は「件数」のまま、「結果の項目名」にDenseRankと入力します。
- 「並べ替え」を追加してGroupを「昇順」にします。同じステップの「並べ替え項目を追加」でDenseRank、Entryを順に追加し、どちらも「昇順」にして「適用」を押します。
同点の2項目は同じ順位になる
| Group | Entry | Score | DenseRank |
|---|---|---|---|
| G1 | A | 90 | 1 |
| G1 | B | 90 | 1 |
| G1 | C | 70 | 2 |
| G1 | D | 50 | 3 |
| G2 | E | 70 | 1 |
| G2 | F | 50 | 2 |
G1のDは50点です。自分以上の異なる点数は50・70・90の3種類なので3位になります。最初の一覧でAとBの90点を2回数えていたら、Dは4位になってしまいます。最初の集計がGroup・Scoreだけでまとめられているか確認してください。
結合途中の行数は、各グループの元項目数 × 点数の種類数です。例では4×3+2×2=16行ですが、大きなグループでは急増します。小さなデータで確認し、大規模な順位計算には専用の順位機能があるツールも検討してください。この順位は同点に同じ番号を付けるもので、同点同士の優劣は決めません。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
How To Effectively Dense Rank Groups in a Table Using Powerqueryの質問を参考に、架空のデータを用意しました。
