上田太一郎 Excelでかんたん統計解析 (2007) 

2020.5.13 更新2020.5.17

 データ分析のためにいくつかのソフトが使用できます。

一つの候補は、多くのパソコンにインストールされているエクセルの「データ分析」ツールなのですが、

使用してみて、どういう解法が使われているか、公開されていないことがわかりました。

 結果の数値が表示されているところに、使った関数が示されていれば、解法がわかるのですが、

数値しか入っていないのです。

 そこで、勉強のために、この本の、使用例をみながら、どういう処理が行われているのか調べてみたいと思います。

 

 エクセルには、色んなバージョンがあり、操作方法は異なるかもしれませんが、

エクセルを開いて、データのタブを押して、一番右側に、分析ツールが表示されない場合は、

左上端のオフイスボタンを押して、右下のExcelのオプションを押して、左端列のアドインを押して、

アクティブでないアプリケーションの一覧の中から、分析ツールを選び、設定ボタン、OKボタンを押して、有効にしてください。

 さて、分析ツールが提供する分析法は、以下の通りです。

01 分散分析:一元配置 (analysys of variance: one-way)

02 分散分析:繰り返しのある二元配置

03 分散分析:繰り返しのない二元配置

04 相関

05 共分散

06 基本統計量

07 指数平滑

08 F検定:2標本を使った分散の検定

09 フーリエ解析

10 ヒストグラム

11 移動平均

12 乱数発生

13 順位と百分位数

14 回帰分析

15 サンプリング

16 t検定: 一対の標本による平均の検定

17 t検定: 等分散を仮定した2標本による検定

18 t検定: 分散が等しくないと仮定した2標本による検定

19 z検定: 2標本による平均の検定

 これらを順不同に解説します。 

 

06 基本統計量

 28人の生徒の国語の試験の成績データがあるとすれば、その

平均値、標準誤差、中央値(メジアン)、最頻値(モード)、標準偏差、分散、尖度、歪度 等を求めます。

 標準誤差 は、平均値のばらつき具合を示す量で、標準偏差をデータ数Nの平方根で割った値です。

   標準誤差= STDEV( )/SQRT(N)

08 F検定:2標本を使った分散の検定

 新肥料1と旧肥料2で育てた小松菜の硝酸イオン濃度データがあります。

 新肥料1で育てた松菜の硝酸イオン濃度データ  N1=12

  2691.67 2458.33 3691.67 3947.22 4447.22 2853.24 5447.22 3276.56 3015.66 2905.86 3652.30 4250.40

 旧肥料2で育てた小松菜の硝酸イオン濃度データ  N2=10

  4558.33 5011.46 6802.29 3880.42 3625.28 4682.44 4230.21 4476.80 3668.22 6243.65

があります。2つの標本データの分散に違いがあるかという問題です。

 計算結果は、

 µ1=3553.1125   分散1=752033.096

 µ2=4717.91     分散2=1119831.363

 観測された分散比=分散1/分散2 = 0.6715592

 P(F<=f) 片側    = F.DIST(分散比,11,9,TRUE) = 0.26291093

   この値が、0.05より大きいので、2つの分散は同等であるという検定結果になります。

 F境界値片側     = F.INV.RT(0.95,11,9) = 0.345277309

   この値よりも、分散比の方が大きいことから、2つの分散は同等であると判断されます。 

 

10 ヒストグラム

 28人の生徒の国語の試験の成績データがあるとします。

データ区間を決めて、各区間に何人いるか度数を求めて、棒グラフで示したものをヒストグラムといいます。

 データ区切りを、10, 20, 30, 40, 50, 60, 70, 80, 90, 100 と指定すると、

第一データ区間は10以下,第二データ区間は、10より大、20以下となります。

第十データ区間は、90より大、100以下。

 0以下の数字があった場合は、第一データ区間に入るようです。

 100以上の数字があった場合は、「次の級」にカウントされるようです。

 10点台 という言葉があり、これは、10点以上、20点未満をさすとすれば、

ヒストグラムのデータ区間の考え方とは、少し齟齬がありますね。

 

17 t検定: 等分散を仮定した2標本による検定

 新肥料1と旧肥料2で育てた小松菜の硝酸イオン濃度データがあります。

 新肥料1で育てた松菜の硝酸イオン濃度データ  N1=12

  2691.67 2458.33 3691.67 3947.22 4447.22 2853.24 5447.22 3276.56 3015.66 2905.86 3652.30 4250.40

 旧肥料2で育てた小松菜の硝酸イオン濃度データ  N2=10

  4558.33 5011.46 6802.29 3880.42 3625.28 4682.44 4230.21 4476.80 3668.22 6243.65

があります。硝酸イオン濃度に差があるか否かという問題です。

 新肥料1で育てた小松菜の硝酸イオン濃度データの標本平均 平均1 3553.1125

 旧肥料2で育てた小松菜の硝酸イオン濃度データの標本平均 平均2 4717.91

 新肥料1で育てた小松菜の硝酸イオン濃度データの標本分散 分散1 752033.096

 旧肥料2で育てた小松菜の硝酸イオン濃度データの標本分散 分散2 1119831.363

 共通分散=[分散1×(N1-1)+分散2×(N2-1)]/(N1+N2-2)= 917542.3162

 自由度=N1+N2−2=20

 検定統計量=(平均1-平均2)/√{[(1/N1)+(1/N2)]×共通分散}=-2.83998902

 片側有意確率=T.TEST(濃度データ1,濃度データ2,1,2)= 0.005059761

   片側有意確率が、0.05より小さいので、硝酸イオン濃度に差があるという結果となりす。

 片側棄却限界=T.INV(0.05,20)= 1.724718004

 両側有意確率=T.TEST(濃度データ1,濃度データ2,2,2)= 0.010119523

 両側棄却限界=T.INV.2T(0.05,20)=2.085962478

 

19 z検定: 2標本による平均の検定

 2種類の車1と2の燃費を計測したデータが、それぞれ35個あるサンプルデータを使用します。

 平均値で、燃費の差がわかりますが、その差が有意かどうかを、統計的に、検証します。

データ数が25以上なので、正規分布しているとみなして、t検定ではなく、z検定を使用します。

 車1の燃費データを、変数1、車2の燃費データを、変数2とし、

  データの入っているセル範囲を、それぞれの入力範囲に指定します。

 変数1と2の分散は、z検定では、既知なので、その値を入力する必要がありますが、

 ここでは、それぞれ、35個のデータから STDEV関数で、標準偏差を計算した値を、書き込みます。

 危険率α は、0.05 とします。

 計算結果は、以下の通りです。

   車Aの平均燃費は、12.002857、分散は、0.818521

   車Bの平均燃費は、12.591429、分散は、0.495513

   z=(µ1-µ2)/SQRT{(σ1/N1)+(σ2/N2)}=-3.0.7595

   P(Z<=z) 片側 = 0.0011924

   z境界値 片側 = 1.6448536

   P(Z<=z) 両側 = 0.0023847

   z境界値 両側 = 1.959964

 

01 分散分析:一元配置

 

 

 

 

 

 

 

     

 ご意見等がありましたら、think0298(@マーク)ybb.ne.jp におよせいただければ、幸いです。

 ホームページアドレス: https://think0298.stars.ne.jp