EXCELで点数に応じて相対評価 「上から〇%はA評価」をやってみる｜ABC分析

学校の成績表など点数を「相対評価」で評価をしたいときは、PERCENTRANK関数 が役に立ちます。

相対評価とは 『クラスの上位10%が「A」評価、次の20%が「B」評価』 といった評価方法です。いわゆる「5段階評価」などがこれに当たります。
また、企業のマーケティングにおいては「ABC分析」の材料としても活用できます。

前回の「順位表を作る」や、「☆マークを使って5段階評価」などの関連記事と合わせて読んでもらえると、色々と評価方法の幅が広がります。

では、さっそく説明していきます。

得点一覧表と判定基準表を作る

Excelで評価をする

まずは下準備です。

上の表は、A列からC列がクラスの成績表です。B列にそれぞれの点数が入力されています。
※この場では便宜的に人数は10名とします。

今回のゴールは、C列の「判定」（赤枠）に「A」から「E」の判定結果（相対評価）を表示させることです。
そして、右側の表は「判定基準」です。H列（青枠）は A〜E評価のそれぞれの「配分」、そしてF列（緑枠）はAを0%としてB以降のトップ（A）との差を%で示す「基準(値)」とします。

「基準」のセルF4に入る数式は、「=F3 + H3」となります。（この数式をセルF7までコピーしておきます。）

PERCENTRANK関数

判定に必要な「隠し列」を仕込む

ここで、成績表の「枠外」（D列）に数式を仕掛けます。

ここでの数式の役割は、生徒の点数が上位から何%になるのかを求めます。
ここで使用するのが PERCENTRANK関数 です。
PERCENTRANK関数 は、1位を「1」として、2 位以下を0から1の間の少数で表す関数で;

のように使用します。今回のケースに当てはめて表現すると；

となり、 [有効桁数] は省略されます。
実際に見てみるのが簡単なので D列に数式を入れてみましょう。

セルD3の数式を見てみると、=1-PERCENTRANK ($B$3:$B$12, B3) となっています。
PERCENTRANK関数 をそのまま使うと、1位の人の数値が１になり最下位が０になりますが「上位から〇％」を表すには、1位が０で最下位が１となる必要があります。そのため数式の先頭に「1-」を入れることによって数字の並びを逆転させています。
また、数式中で参照範囲の B3:B12 は絶対参照 $B$3:$B$12 にしておきましょう。

※このD列は、最終的に フォントカラーを「白」にするか、列の幅をゼロにする などしてユーザーの目に触れないように隠します。

PERCENTRANK関数 のバリエーション

実はこの PERCENTRANK関数には3つのバリエーションがあります。

この記事では互換性も考慮して PERCENTRANK関数 としますが、PERCENTRANK.INC関数と置き換えても同じです。

VLOOKUP関数 で判定結果を表示する

VLOOKUP関数

下準備が完了したらいよいよ判定結果を算出し評価します。

ここで使用される関数が VLOOKUP関数 です。
仕事で Excel を使っている方なら知らない人はいないくらいメジャーな関数ですね。
これをC列の個々のセルに入力していきます。

セルC3に入る数式は；

となります。
つまり、セルD3に表示されいる「0.67」が「判定基準」のどのレンジに入るかを求めます。
安部さんの得点、60点はクラスの上位から67％の位置に相当するため、判定基準に照らし合わせて「C判定」と評価されました。

このセルC3の数式を、セルC12までコピーして完成です。

最後にD列のフォントカラーを白にして不可視の状態にして完成です。

「EXCELで相対評価」いかがでしたでしょうか。
VLOOKUP関数 の末尾、検索の型（TRUE or FALSE）については、また別の機会に説明したいと思います。

関連記事・広告

併せて読んでおくとさらに便利

EXCELでベスト5の順位表を作る方法　Part 1
LARGE関数・RANK関数
学校の成績ベスト5、〇〇の都道府県別ベスト10。。。 日々データ分析に携わっていると、仕事でも何かと「順位」「ベスト〇位」を求められることがしばしばです。

EXCELで 5段階評価（0.5刻み） を☆マークを使って表す方法
条件付き書式・REPT関数
「星みっつです！」なんて某料理番組のような評価をエクセルで表したい。しかも、0.5刻みでひとつ「半」ふたつ「半」も表してみたい。

※その他関連記事は、このページの下の方にもあります。