Excel。通知書など5段階相対評価を算出するにはどうしたらいいの？
＜XLOOKUP+PERCENTRANK.INC関数＞
資料を作成するにあたり、相対的に評価をつけないといけないことがあるわけです。
例えば、先生ご用達というか、「通知書」などの相対評価表です。
では、どうやったら、楽に相対評価を算出することができるのでしょうか？
次の表のD列の評価フィールドを算出していきます。
準備するのは、F1:H6の相対評価表です。
H2は、累計を算出するための数式が設定してあります。
H2の数式は、
=SUM($G$2:G2)
この数式を、H6までオートフィルで数式をコピーしています。
作業スペースをつくらないで、一発で算出するならば、XLOOKUP+ PERCENTRANK.INC関数で算出できるのですが、説明の為、数式を分解して説明していきます。
得点が全体でどの位置にあるのかという相対位置がわからないことには、相対評価を算出することはできません。
そこで登場するのが、PERCENTRANK.INC関数です。
PERCENTRANK.INC関数は、百分位を算出することできる関数です。
E列には、PERCENTRANK.INC関数をつかって百分位を算出しています。
E2の数式は、
=PERCENTRANK.INC($C$2:$C$11,C2,2)
最初の引数は、配列。
範囲選択なので、C2:C11。オートフィルで数式をコピーするので、絶対参照を設定していきます。
2つ目の引数は、C2。
これで、C2の値は、全体の中で、何パーセントの位置にあるのかがわかります。
最後の引数は、有効桁数。
小数点第何位まで表示するのかということですね。
この数式を、次のように変更する必要があります。
E2の数式を変更します。
=1-PERCENTRANK.INC($C$2:$C$11,C2,2)
なぜ、1から減算する必要があったのかというと、原因は、評価表にあります。
百分位は、100％の位置にあるデータは100%と算出されるのはいいのですが、Aの配分累計は10%の位置で、Eの配分累計が100%となっています。
ようするに「逆」になっているので、１から減算する必要があるわけです。
あとは、=1-PERCENTRANK.INC($C$2:$C$11,C2,2) で算出した値が、どの配分累計に属しているのかを算出すればいいわけです。
VLOOKUP関数をつかって評価を検索抽出するように思われますが、F1;H6の表。
一番左側の列に、検索抽出したい値があるわけです。
これだと、VLOOKUP関数は使えません。
INDEX+MATCH関数なんて方法もありますが、XLOOKUP関数が登場したわけですから、XLOOKUP関数をつかえば、一番左側に検索抽出の列があっても問題ありません。
D2に設定した数式は、
=XLOOKUP(E2,$H$2:$H$6,$F$2:$F$6,,1,-1)
引数の確認をしておきましょう。
検索値が、「E2」。
検索範囲が、「$H$2:$H$6」 この範囲は、検索したい範囲です。
戻り範囲が、「$F$2:$F$6」 抽出したい範囲です。
見つからない場合は、省略とします。
一致モードには「1」。これは、「完全合致または次に大きい項目」を選択します。
検索モードには、「-1」の「末尾から先頭へ検索」を選択しました。
本当ならば、スピル機能を考えて範囲選択するのですが、今回は、VLOOKUP関数っぽく使用しております。
二段階の計算式を一つにまとめたのが、次の数式です。
=XLOOKUP(1-PERCENTRANK.INC($C$2:$C$11,C2,2),$H$2:$H$6,$F$2:$F$6,,1,-1)
この数式を、D2に設定すれば相対評価を算出することができます。
見慣れない関数のネストで、長い数式になると、わかりにくくなりますので、二段階で算出するのでいいと思います。
ということで、Excelには様々な関数が用意されていますので、色々組み合わせみると、意外につかえるネストとか見つかるかもしれませんね。
