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

2018/02/03

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

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

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

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

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

Excelで評価をする

まずは下準備です。

excel_評価1
上の表は、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の間の少数で表す関数で;

=PERCENTRANK (配列, X, [有効桁数] )

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

=PERCENTRANK (B列の得点の集まり, 判定したい生徒の得点 )

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

excel_評価2

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

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

PERCENTRANK関数 のバリエーション

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

PERCENTRANK.INC関数:値Xの配列内での順位を百分率(0以上1以下)で表します
PERCENTRANK.EXC関数:値Xの配列内での順位を百分率(0より大きく1より小さい)で表します
PERCENTRANK関数:Excel2007以前のバージョンと互換性があり、動きは PERCENTRANK.INC と同じです

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

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

VLOOKUP関数

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

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

excel_評価3
セルC3に入る数式は;

=VLOOKUP (D3, $F$3:$G$7, 2, TRUE )

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

このセルC3の数式を、セルC12までコピーして完成です。
excel_%e8%a9%95%e4%be%a14
最後にD列のフォントカラーを白にして不可視の状態にして完成です。
excel_評価5

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

関連記事



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

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

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



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

-EXCEL&VBA
-, , ,

シェアしていただけると助かります。