エクセル初心者
エクセルで偏差値を求めてみたいけど、やり方がわからないな。。。
エクセルで偏差値を求めるには、平均と標準偏差が必要だよ。これから解説するから、一緒に見ていこうね。
Dr.オフィス
今回は、エクセルの平均と標準偏差を使って、偏差値を求める方法について解説します。
一般的に、エクセルで『平均』を使う頻度は高いですよね。
その一方で職種にもよりますが、『標準偏差』や『偏差値』については、『ざっくりは分かるけど、それをエクセルで使用したことがない』というユーザは多いのではないでしょうか。
エクセルで『標準偏差』や『偏差値』を簡単に求めることができますので、これから見ていきましょう。
- AVERAGE関数で平均を求める
- STDEV.P関数を使って標準偏差を求める STDEV.P関数の書式 =STDEV.P(数値1,[数値2]…) ※数値1は必ず指定し、文字列は無視される
- ①と②で取得した値を偏差値の数式に当てはめる
以上の3ステップで偏差値を求めることができます。
エクセルの平均を求める基本と応用については、こちら≫エクセルの平均を求める方法は１つではない？基本から応用まで徹底解説！！で詳しく解説しています。ぜひ参考にしてみてください。
次項より、平均と標準偏差を使って偏差値を出す方法について、詳しく解説します。
※本記事は『OS：Windows10』画像は『Excelのバージョン：Office365』を使用しています。
目次
エクセルで平均と標準偏差を使って偏差値を出してみよう
エクセルでは、平均と標準偏差を使って、簡単に偏差値を出すことができます。
例えば、テストの結果が発表されても、点数だけでは自分が全体の受験者のどの位置にいるのか分かりませんが、偏差値を参考にすると分かってきますよね。
そこで、平均と標準偏差使って偏差値を出す方法を下記の順番で解説します。
- 標準偏差を求める方法
- 偏差値を求める方法
【エクセル】標準偏差を求める方法
標準偏差とは、データのばらつきの大きさを表す指数です。
標準偏差が大きいほど、ばらつきが大きいことを示しますが、ここでは、偏差値を求めるのに必要な値、と考えておくことで問題ありません。
標準偏差を手動で求めると、とても複雑で大変なので、エクセルのSTDEV.P関数を使います。
それでは、下記の表を使って標準偏差を求めてみましょう。
セルC14を選択し、数式[=STDEV.P(C3:C12)]を入力します。
セルC14に標準偏差が計算されましたね。
![四捨五入され標準偏差が[7.1]と表示される](https://excel-doctor.jp/wp-content/uploads/mean-standarddeviation-3.png)
標準偏差の表示桁数が多すぎるので、下記の通りにROUND関数を設定しましょう。
=ROUND(STDEV.P(C3:C12),1)
セルC14の標準偏差が[7.1]と表示されればOKです。
これで、標準偏差を求めることができました。とても簡単でしたね。
セルE14も同じように標準偏差を求めておきましょう。
標準偏差を求める公式を検索してみると、とても複雑だよ。その複雑な計算をエクセルでは、簡単に出来るからとても便利だね。
Dr.オフィス
【エクセル】偏差値を求める方法
偏差値を求める公式は、[=(自分の得点－平均点)÷標準偏差×10＋50]です。
前項で標準偏差を求めることが出来たので、その標準偏差と平均点を使って偏差値を求めてみましょう。平均点は、AVERAGE関数で算出済みです。
セルD3を選択し、数式[=ROUND((C3-$C$13)/$C$14*10+50,1)]を入力します。
出席番号1番の中間テストの偏差値がセルD3に表示されましたね。
オートフィルを使って、セルD3の数式をセルD13までコピーします。
セルF3からセルF13にも同じように偏差値を求めておきましょう。
セルF3に入力する数式は、下記の通りです。
=ROUND((E3-$E$13)/$E$14*10+50,1)
出席番号1番から10番までの、中間テストと期末テストに対する、それぞれの偏差値を出すことができました。
平均と標準偏差があれば、偏差値をすぐに把握できるね。
Dr.オフィス
偏差値は平均と標準偏差を使って簡単に出せる！！
このように、平均と標準偏差があれば簡単に偏差値を出すことができ、対象の値が全体のどのあたりに位置しているのかが把握できますね。
偏差値は50を基準として、平均からどれくらいの差があるかを表した数値です。
例えば、上記の表で見ていくと、①中間テストで80点を取っていたとしても、偏差値が52.1なので実際の点数ほど上位ではないことが分かります。
また、②期末テストでいうと同じ80点でも偏差値は72.5なので、上位の方だということが分かりますね。
このように、点数が良いから上位だとか、点数が低いから下位のほうだと決めつけず、平均と標準偏差を使って自分の偏差値を出してみると、より参考になりますよ。
エクセルの平均を求める基本と応用については、こちら≫エクセルの平均を求める方法は１つではない？基本から応用まで徹底解説！！で詳しく解説しています。ぜひ参考にしてみてください。