Excel。アルファベット評価の平均を算出するには、どうしたらいいのでしょうか？
＜CODE関数・CHAR関数・AVERAGE関数＞
英語のテストのように、90点のような数値だと、平均値を求めることは簡単ですが、次の表にある、「理解力＝B」のようにアルファベット評価の場合、どうやってアルファベット評価の平均を算出したらいいのでしょうか？
この表は、A～Eの５段階評価です。
Aが一番よい評価で、Eが一番悪い評価という意味の表です。
考え方として、アルファベットごとに件数を算出して、Aが多いからAなど、見た眼で判断したら、曖昧になってしまいます。
今回以上の膨大なデータの場合、目視で判断なんか到底できません。
また、Aを1、Bを2など独自に設定した数値に置換して、その平均値を算出すればいいかもしれませんが、A～Eのアルファベットを置換するなどの作業が面倒です。
しかも、この独自に設定した数値を使った場合、最終的にアルファベットに戻さなければなりません。
その作業も置換を使うにしろ、VLOOKUP関数を使うにしろ、なかなか面倒です。
【文字を数値にする】
平均を算出することでお馴染みの、AVERAGE関数は、数値の平均を算出することはできても、文字を平均化することはできません。
逆に言えば、文字を数値化できれば算出することはできるわけです。
そこで、文字を数値にする、「CODE関数」というのがあります。
このCODE関数は、文字に割り振られている数値を算出してくれる関数です。
先程の評価表のとなりに、CODE関数をつかった表を算出してみます。
J2の数式は、
=CODE(B2)
この数式をオートフィルでコピーすると、A～Eまでのアルファベットに割り振られている数値が算出されます。
ちないに、Aは65です。
仮に、傾聴力から理解力までの項目がすべてAだったら、すべての項目が65になりますから、平均は65というように、平均値を算出することができます。
o2の数式は、
=AVERAGE(J2:N2)
と平均値を算出しております。
ただ、これだと数値のままなので、アルファベットで表示されていません。
【数値を文字にする】
算出された平均結果をつかい、アルファベットに戻すためには、CHAR関数を使います。
G2の数式は、
=CHAR(O2)
としております。
CHAR関数は、小数点以下は無視された数値を使って、文字に変換してくれます。
これで、アルファベット評価の平均した評価を算出することができました。
このままでもいいのですが、ちょっと気になることがあります。
それは、G6
5件中、Eが4件で、Dが1件。平均結果がDというのは、イメージが乖離しています。
このような場合は、平均値を算出する数式をアレンジする必要があります。
O2の数式を次のようにアレンジしました。
=ROUND(AVERAGE(J2:N2),0)
ROUND関数をつかって、四捨五入させました。
その結果が次の表です。
G6は、平均評価がEに変わりました。
状況によって、切り上げ、切り捨てなど、アレンジすると状況にマッチしやすくなると思います。
本来ならば、中央値や、標準偏差など様々な数値を算出する必要があるかもしれませんが、簡易版とアルファベット評価の平均を算出するアイディアをご紹介しました。
