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

3 min 54 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本はこちら

カテゴリー:

本を読みたいけど忙しくて読めない

そんな人におすすめなのがAmazonオーディオブック『Audible』

オーディブルのバナー
ワード初心者

ワード初心者

本を読んでビジネスに役立つ情報を身に着けたいけど、忙しくてなかなか本を読む時間がないんだよね・・・

社会人になるとなかなか本を読む時間って作りにくいよね。

そんな時におすすめのサービスがAmazonオーディオブックの『Audible』だよ!

Dr.オフィス

Dr.オフィス

本は読むから聴くに変わってきています。

聴くだけなら通勤中や運転中などちょっとしたときにできますね。

オーディオブックを提供しているサービスはいくつかありますが、一番のおすすめはAmazonオーディオブックの『Audible』です。

なぜ、おすすめかと言うと、最初に登録したときに1冊無料で聴くことができるからです。

その1冊は、『Audible』内のすべての本から選ぶことができます。

たとえばこちら

Audible』のビジネス書ランキングでもトップの「アウトプット大全」が無料で聴けちゃいます

学ぶだけでなくアウトプットすることで結果に変えることができることを解説した本で、ビジネスマンなら必ず読んでおきたい1冊ですね。

他にもこちら

自分の本当の強みを見つけることによって、自分の適職を見つけ出す。

それを可能にしてくれる本が「適職の結論 (あなたが気づいていない 「本当の強み」がわかる)」です。

他にもたくさんのビジネス本があって、そのなかから1冊を無料で聴くことができます。

しかも、すでに配信が決定しているオーディオブックもあり、その予約もできます。

まったく新本が追加されないといった心配もありません。

ビジネス本の他にも、語学学習本や小説、映画のノベルティー本などなんと40万冊のオーディオブックが用意されています。

ビジネス本をすぐに聴きたいという方はぜひ無料体験をどうぞ。

Audibleなら、なかなか時間の取れない忙しい方でも運転、通勤、家事、育児など普段の生活の中で学ぶことができます。

空いてる耳を使ってビジネス力をアップしましょう。

Dr.オフィス

Dr.オフィス

30日以内に解約すれば完全に無料で1冊見ることができます。

もし気に入って継続したとしても解約はいつでもでき、購入した本はいつでも聴くことができます。

Amazonの超太っ腹なAudible無料体験でビジネス力を高めましょう。

\登録は簡単5分/

関連記事