2013年5月1日水曜日

点数・得点を段階評価するためのエクセルシートの作成

お久しぶりの統計作業記事です.
今回は,スポーツをはじめとする何かしらの測定値を段階評価するための方法をご紹介します.
研究チックな統計処理ではありませんで,どちらかというとデータのフィードバックに向く計算方法です.
私も学生時代にスポーツ選手のサポートをしていた折,体力測定やらパフォーマンス測定をしたデータを,以下の様に加工して返していました.ご参考になれば.

どんなところで参考になるかというと,別に体力測定に限らず,平均値と標準偏差が出せるデータであればいい具合に処理できます.
テストの点数・得点といったようなデータをフィードバックする際に威力を発揮するでしょう.


まずは例としての完成版を先にお見せします.

このように,(例では体力測定の)データを打ち込んだら,その右横にアルファベットでA~Eの5段階評価ができるようにするシートを作成するというものです
誰がどんな程度の値なのか,一目瞭然になるという寸法です. 

では,作成過程を見ていきましょう. 
まずは5段階評価の基準を作ります.

 上図のような数式を,F列2行目のセルに入力しています.
式は,

=B13+1.5*B14
というものです.
以下,同様にして,


=B13+0.5*B14



次は, 


=B13-0.5*B14



最後は,
=B13-1.5*B14
と入力していきます.

今度は,評価を示す列であるC列に,IF関数を使ってA〜Eの5段階評価を自動的に出せるようにしましょう.
評価基準をもとに,手作業でやってもいいのですが,たくさんのデータを処理する際はIF関数を使ったほうが便利です.

C列2行目のところに,こんなIF関数の組み合わせを入力します.
「<」記号の向きに気をつけましょう.
=IF(B2>$F$2,"A",IF(B2>$F$3,"B",IF(B2>$F$4,"C",IF(B2>$F$5,"D","E"))))
※評価基準のセル参照のところは,「$」マークを忘れずに入力します.
で,これをオートフィルでコピーして,

以下のように状態に.これで完成.

ちなみに,これは今回測定した人達のデータから評価していますが,例えば全国平均値(と標準偏差)など,他に比較評価したい基準データが入手できるのであれば,

このように,そのまま利用できます.
上記であれば,今回測定した人達は全国平均を基準にすれば「C」以下の評価になると言えます.

ところで,以下のようなデータはどうでしょうか
つまり,値が小さいほど優秀と評価する場合です.
手作業でやるぶんには,評価基準を逆に読めばいいのでしょうが,オートフィルで済ましたい場合は,算出式を逆にしておく必要があります.

以下,評価基準の算出式を,マイナスをプラスに,プラスをマイナスにしてゆきます.
(まぁ,セルの入れ替えでも良いと思われ..)
 



そして,このような評価基準でIF関数を組む場合は,「>」記号を逆にしなければいけません.
まぁ,IF関数を組み慣れている人にとっては常識ですが,私はよく間違えて手こずった思い出があります.
「あれ,おかしいな」と感じる結果になったら,まずこれを疑ってください.

そしてオートフィル.

というわけで,あとはその評価をどのように返すか?ですが.

今回のようにA〜E(秀・優・良・可・不可とか)の表記でプリントしたり口頭で返すのも有用ですが,複数の測定・評価項目であれば1〜5の数値で出して,それを「レーダーチャート」にして返すという方法も知られています.
これについては,いろいろなところで紹介されていますから割愛します,そっちでググってみてください.


おまけです.
別に5段階評価しかできないわけではなく,数式をいじれば,以下のように9段階評価や3段階評価にもできます.
 お好みに合わせて使ってください.

9段階の式とIF関数は以下の通り.
 =B13+1.75*B14

以降,式の「1.75」のところを0.5ずつ数値を下げてゆき,
=B13+1.25*B14
=B13+0.75*B14
=B13+0.25*B14
=B13-0.25*B14
=B13-0.75*B14
=B13-1.25*B14
=B13-1.75*B14

これで8個の基準ができますので,あとはIF関数を
南国の鳥かイモムシみたいな状態にすることでオートフィルにできます.

=IF(B2>$G$2,"A",IF(B2>$G$3,"B",IF(B2>$G$4,"C",IF(B2>$G$5,"D",IF(B2>$G$6,"E",IF(B2>$G$7,"F",IF(B2>$G$8,"G",IF(B2>$G$9,"H","I"))))))))

3段階評価は以下の通り.
=B13+1*B14

で,2つ目はその式の数値をマイナスにします.
=B13-1*B14

せっかくですから,IF関数も記号文字にしてみました.
=IF(B2>$H$2,"◯",IF(B2>$H$3,"△","×"))
※バツ印は機種依存文字なので,上記の式では乗算記号「×」にしています.

覚えておくと応用がきくので,ぜひ!

え?
「なんで基準を作るときの数式に「1.5」とか「0.25」とか「1」を入れるのか?」ですって?
それは図を使って説明した方がいいので,また別の機会に.
※次の日,その解説記事を載せました.
標準偏差を用いた段階評価の仕組み

※後日,パーセンタイルを用いた段階評価を紹介しました.
四分位とパーセンタイルでの段階評価をエクセルで作成

もしくは,急ぎの方は以下の本を参考にしてください.
段階評価が意味するところや,その数値をいじる際の根拠がわかります.