Excel関数で偏差値を計算する式

Q

Excelで偏差値を計算する関数って何？

SunShine!

Excelには、偏差値を計算させる関数はないよ。
自分で数式を組むんだよ。

Excel
偏差値の計算

定期試験とか模試とかで、偏差値って気にしてたよね。
50以上だとちょっと安心したり、60以上だとすごいねとか言われたり。。

あれってExcelだとどーやって計算するんだ？って話ね。
関数はないから、別の関数を活かしながら式を組みましょう。

ここでの目的は偏差値を計算することだから、偏差値が何かとかはざっくりで。
実例を挙げて数式も載せとく。
値の意味を詳しく知りたい人はググってみてね。

偏差値って？

50を基準にして、平均からどれくらい離れてるかを表現する値。
平均に対して、個別の値がばらけてると値が大きくなって、まとまってると50周辺になるってやつね。

極端な話だと、全員が50点だと、全員の偏差値が50になるってこと。

テスト結果でよく見るよね。
偏差値が50より数字が大きければ平均点以上。
ただ平均点以上ってだけじゃなく、どれくらい平均点以上なのかってのが分かるんだな。
東大とか早慶とか、偏差値70台ってのは、平均からかなり離れてる。

詳しくはググってくれ。

偏差値を計算する式

実例は以下。
右側の表の方が、平均に対するばらつきが小さいから、同じ100点でも偏差値が異なる。
名前とかは気にしないでｗ

ここを目指して数式をつくるよ。

値Aがたくさん並んでる感じ。
たとえばテスト結果なら、点数が値Aに相当する。
以降、偏差値の計算対象になる値は「値A」って書いてくから。

値Aの平均値と標準偏差が必要なの。

まずは平均と標準偏差を準備

準備ね。
偏差値を計算するために必要な中間計算値を準備します。

平均と標準偏差。
それぞれ以下で。

平均

平均だからAverageね。
英語まま。

=AVERAGE(B3:B12)

標準偏差

標準偏差だから、Standard Deviation。
で、STDEV。
いろんなパターンがあるんだけど、PAで。

=STDEVPA(B3:B12)

値Aの集合をそのまま使うとか、集合を拡大解釈して予測するとか、いくつかやり方があるんだ。
Excelってよくできてるよね。

数式を組む

準備が整ったら数式を組みましょう。

で、本丸ね。
考え方は以下。

値Aと平均値の差を求め、標準偏差で割ったら、桁数を直して、50に加える。

C3セルの数式は以下。

=(B3-B$14)/B$15*10+50

ドルマークは、行数を変更せずにコピる用。

受験以外に使う？

偏差値っていったら、テスト結果だよね。
東大合格のためにはどこそこの模試で偏差値いくつみたいな話。
辛い思いや苦い思い出のある人も多かろう。
現在進行で戦ってる人もいよう。がんばれ～

ビジネスシーンでも、成績は気になるもんだよね。
営業の売上成績とか。受注率とかも？
ずっと成績とはお付き合いがあるわけだな。

成績ってキーワード以外でも使うところ知りたい。。

品質管理とかリスク管理とかでは登場するんだろう。
あとは、ずばりデータ分析そのもの。
平均からどれくらいのばらつきで離れてるか分かるんだから、使えるよね。