エクセルを用い平均値と標準偏差から偏差値を計算する

amazon：Excelで操る! ここまでできる科学技術計算 第2版 楽天： Excelで操る! ここまでできる科学技術計算 [ 神足　史人 ]

図１　クラスの点数分布グラフ 棒グラフの作り方：エクセルを用いて棒グラフ、折れ線グラフを作ってみよう

ｎ人のクラスでテストをしました。その結果をt1,t2,t3・・・・・tn点とします。 このクラスの平均値(Av)は全点を合計し、人数で割ります。 　平均値(Av) = ( t1 + t2 + ・・・・　+ tn ) / n Aクラスでは全員が５０点でした。Bクラスでは半数が０点で半数が１００点でした。 この場合、A,Bクラスとも平均点は５０です。でも、Bクラスはずいぶん点数がばら ついているので、先生はたいへんですね。 このばらついている程度を示す指標が標準偏差(σ)です。 各点数と平均値の差を平均すればばらつきがわかります。 　 { （　t1- Av ) + ( t2 - Av ) + ・・・・・+( tn - Av ) } / n しかしながら、この計算では（点数ー平均値）の正、負が相殺し、０となります。 そこで、負数がなくなるよう、（点数-平均値）を２乗してから合計し、人数で割り ます。 次に、２乗を解消するため、平方根（√）をとります。すなわち、 標準偏差(σ)　＝√[{ （　t1- Av )2 + ( t2 - Av )2 + ・・・・・+( tn - Av )2 }/ n　] 　 となります。 ちなみに、上記A,Bクラスでは下記となります。 Aクラスの標準偏差は０です。 Bクラスの標準偏差は50です。 平均値は感覚的に理解できる数値ですが、標準偏差は２乗和平均の平方根 ですから、感覚的に理解できる数値ではありません。他クラスとの比較など 相対的な値としてなら理解できます。

たった1日で即戦力になるExcelの教科書 入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス) 統計学が最強の学問である Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 統計数理は隠された未来をあらわにする Excelで学ぶ統計解析―統計学理論をExcelでシミュレーションすれば、視覚的に理解できる マンガでわかる統計学 ポートフォリオ最適化と数理計画法 (シリーズ・金融工学の基礎) ビジネスマンのための「数字力」養成講座 (ディスカヴァー携書 20) Excelで遊ぶ手作り数学シミュレーション (ブルーバックス)

エクセルを用いクラスの平均値と偏差値を求めてみよう。 エクセルの関数を使えば平均値(AVERAGE)、標準偏差(STDEVPA)は求まりますが、 ここでは計算内容を説明するため、計算式を組み込んでいます。 　　　　　　　図２　クラスの平均値と偏差値を計算したエクセルシート 受験者に対応する点数と偏差値をグラフにしました。 　　　　　　　　　　　　　　　図３　点数と偏差値のグラフ グラフ作成方法の参考　：　エクセル(EXCEL)でグラフを作ってみよう エクセル関数の平均値(AVERAGE)、標準偏差(STDEVPA)を使えば、次のようになります。 　図４　エクセル関数を使用し平均値と偏差値を求めた結果

偏差値の分布は正規分布？


図５　平均が５０で標準偏差が１０の正規分布グラフ

偏差値の分布は正規分布に近いようですが、一致するはずがありません。 平均値がμ、標準偏差がσの正規分布は 　　　f(x) =1/(√(2π)σ)exp{-(x-μ)2/(2σ2)} となり、平均が５０で標準偏差が１０の正規分布は図５のグラフになります。 NORMDIST(x,平均値,標準偏差,FALSE)が正規分布関数です。 　　　　　　　　　　　　　　　　　　　　図６　正規分布関数値を求めるエクセルシート 偏差値が正規分布に従う場合、偏差値が６０以上は全体の何％でしょう。 その場合、６０〜１００の分布関数の面積（積分）を計算すれば解ります。 　−∞〜xまでの分布関数の積分は NORMDIST(x,平均値,標準偏差,TRUE)です。 よって、６０〜１００までの分布関数の面積（積分)は 　　1　−　NORMDIST(６０,平均値,標準偏差,TRUE) となります。エクセルで計算すると１５．９%です。 　　　　　　　　　　　　図７　正規分布関数の積分 １００人中の１６番程度になります。

偏差値は感覚的に理解しがたい数値ですが、順位（１００人中の何番）に換算できれば 理解しやすい数値になります。偏差値と順位の関係表を作りましょう。 　　　　　　　　　　　　　図９　偏差値と順位の関係表

エクセルを用いた度数分布表とヒストグラム


図10 クラスのテスト結果のヒストグラム


エクセルを用いた最大値(最高点)、最小値(最低点)の計算と条件付き書式設定


　



エクセルを用い相関係数と回帰直線を求める

シンプソン公式を用いた正規分布関数の数値積分

エクセルを用いて成績順に並び替え(SORT)

エクセルを用いた移動平均の計算

エクセルを用いた標準正規分布に従う乱数(正規乱数)の発生とヒストグラム

エクセルを用いた指数分布に従う乱数(指数乱数)の発生とヒストグラム

エクセルを用いた二項分布の計算

エクセルを用いた超幾何分布の計算

エクセルを用いた正規分布の計算

エクセルを用いた対数正規分布の計算

エクセルを用いたポアソン分布、指数分布の計算

エクセルを用いたガンマ分布の計算

エクセルを用いたF分布の計算

エクセルを用いたt分布の計算

Excelを用いた地球温暖化計算


現状の二酸化炭素濃度を370ppmとしたときの濃度と大気温度変化のグラフ