【エクセル】平均と標準偏差で簡単に偏差値を算出!!

3 min 241 views
エクセル初心者

エクセル初心者

エクセルで偏差値を求めてみたいけど、やり方がわからないな。。。

エクセルで偏差値を求めるには、平均と標準偏差が必要だよ。これから解説するから、一緒に見ていこうね。

Dr.オフィス

Dr.オフィス

今回は、エクセルの平均と標準偏差を使って、偏差値を求める方法について解説します。

一般的に、エクセルで『平均』を使う頻度は高いですよね。

その一方で職種にもよりますが、『標準偏差』や『偏差値』については、『ざっくりは分かるけど、それをエクセルで使用したことがない』というユーザは多いのではないでしょうか。

エクセルで『標準偏差』や『偏差値』を簡単に求めることができますので、これから見ていきましょう。

エクセルで偏差値を求めるには
  1. AVERAGE関数で平均を求める
  2. STDEV.P関数を使って標準偏差を求める STDEV.P関数の書式 =STDEV.P(数値1,[数値2]…) ※数値1は必ず指定し、文字列は無視される
  3. ①と②で取得した値を偏差値の数式に当てはめる

以上の3ステップで偏差値を求めることができます。

エクセルの平均を求める基本と応用については、こちら≫エクセルの平均を求める方法は1つではない?基本から応用まで徹底解説!!で詳しく解説しています。ぜひ参考にしてみてください。

次項より、平均と標準偏差を使って偏差値を出す方法について、詳しく解説します。

※本記事は『OS:Windows10』画像は『Excelのバージョン:Office365』を使用しています。

エクセルで平均と標準偏差を使って偏差値を出してみよう

エクセルでは、平均と標準偏差を使って、簡単に偏差値を出すことができます。

例えば、テストの結果が発表されても、点数だけでは自分が全体の受験者のどの位置にいるのか分かりませんが、偏差値を参考にすると分かってきますよね。

そこで、平均と標準偏差使って偏差値を出す方法を下記の順番で解説します。

  • 標準偏差を求める方法
  • 偏差値を求める方法

【エクセル】標準偏差を求める方法

標準偏差とは、データのばらつきの大きさを表す指数です。

標準偏差が大きいほど、ばらつきが大きいことを示しますが、ここでは、偏差値を求めるのに必要な値、と考えておくことで問題ありません。

標準偏差を手動で求めると、とても複雑で大変なので、エクセルのSTDEV.P関数を使います。

それでは、下記の表を使って標準偏差を求めてみましょう。

セルC14に数式を入力
セルC14に数式を入力

セルC14を選択し、数式[=STDEV.P(C3:C12)]を入力します。

標準偏差が計算されたが表示桁数が多すぎる
標準偏差が計算されたが表示桁数が多すぎる

セルC14に標準偏差が計算されましたね。

四捨五入され標準偏差が[7.1]と表示される
四捨五入され標準偏差が[7.1]と表示される

標準偏差の表示桁数が多すぎるので、下記の通りにROUND関数を設定しましょう。

=ROUND(STDEV.P(C3:C12),1)

セルC14の標準偏差が[7.1]と表示されればOKです。

これで、標準偏差を求めることができました。とても簡単でしたね。

セルE14も同じように標準偏差を求めておきましょう。

標準偏差を求める公式を検索してみると、とても複雑だよ。その複雑な計算をエクセルでは、簡単に出来るからとても便利だね。

Dr.オフィス

Dr.オフィス

【エクセル】偏差値を求める方法

偏差値を求める公式は、[=(自分の得点-平均点)÷標準偏差×10+50]です。

前項で標準偏差を求めることが出来たので、その標準偏差と平均点を使って偏差値を求めてみましょう。平均点は、AVERAGE関数で算出済みです。

偏差値を求める
偏差値を求める

セルD3を選択し、数式[=ROUND((C3-$C$13)/$C$14*10+50,1)]を入力します。

数式をセルD13までコピー
数式をセルD13までコピー

出席番号1番の中間テストの偏差値がセルD3に表示されましたね。

オートフィルを使って、セルD3の数式をセルD13までコピーします。

セルF3にも偏差値を求める数式を入力
セルF3にも偏差値を求める数式を入力

セルF3からセルF13にも同じように偏差値を求めておきましょう。

セルF3に入力する数式は、下記の通りです。

=ROUND((E3-$E$13)/$E$14*10+50,1)

平均と標準偏差を使って偏差値が表示された表
平均と標準偏差を使って偏差値が表示された表

出席番号1番から10番までの、中間テストと期末テストに対する、それぞれの偏差値を出すことができました。

平均と標準偏差があれば、偏差値をすぐに把握できるね。

Dr.オフィス

Dr.オフィス

偏差値は平均と標準偏差を使って簡単に出せる!!

このように、平均と標準偏差があれば簡単に偏差値を出すことができ、対象の値が全体のどのあたりに位置しているのかが把握できますね。

偏差値は50を基準として、平均からどれくらいの差があるかを表した数値です。

偏差値で自分の位置を把握できる
偏差値で自分の位置を把握できる

例えば、上記の表で見ていくと、①中間テストで80点を取っていたとしても、偏差値が52.1なので実際の点数ほど上位ではないことが分かります。

また、②期末テストでいうと同じ80点でも偏差値は72.5なので、上位の方だということが分かりますね。

このように、点数が良いから上位だとか、点数が低いから下位のほうだと決めつけず、平均と標準偏差を使って自分の偏差値を出してみると、より参考になりますよ。

エクセルの平均を求める基本と応用については、こちら≫エクセルの平均を求める方法は1つではない?基本から応用まで徹底解説!!で詳しく解説しています。ぜひ参考にしてみてください。

ExcelドクターがおすすめするExcel本はこちら

カテゴリー:

20万人以上が参考にしたエクセルドクターで最も人気の記事

エクセルで家計簿を作る方法

ワード初心者

ワード初心者

エクセルで本格的な家計簿を作ってみたいんだけど詳しく解説してる本とかがないんだよね・・・
徹底的に解説してほしいな!

エクセルで本格家計簿の作り方なら私に任せて!過去に20万人以上のユーザーが参考にした解説記事を紹介するね♪

Dr.オフィス

Dr.オフィス

エクセルを少し使えるようになってきたら、本格的な家計簿を作ってみるのがとてもおすすめです。

収支シートの出来上がりイメージ
収支シートの出来上がりイメージ

基本機能をしっかりと使って作るから、エクセルをマスターするのにも家計簿づくりはぴったり!

20万人以上が参考にしたエクセルでの本格的な家計簿の作り方はこちら↓↓

【エクセル家計簿の作り方】月ごとに入力して合計を反映させれば見やすくなる!

関連記事