【ビジネス統計スペシャリスト対策】Excelで一元配置分散分析をする方法
はじめに
ご訪問ありがとうございます！まちゅけんです。
ヨメちゃんです。
１：Excelを使った一元配置分散分析の操作
２：一元配置分散分析の使い道
この記事ではエクセル分析スペシャリストの学習に役立つ、エクセルの使い方について解説をします。今回のテーマは一元配置分析編です。スプレッドシートを使用する方法は別でご紹介をしています。
https://machuken.com/post-735/「ビジネス統計スペシャリストってなに？」という方は以下の記事を参考にして下さい。
操作方法
それでは早速、Excelを使った一元配置分散分析の操作方法について説明をします。前提として、Excelのデータ分析ツールは初期設定の状態では使うことができません。「初期設定のままだ！」という方は、データ分析ツールの有効化についてまとめた記事があるので参考にして下さい。
操作方法といっても分析をするデータがなければ何もできません。ケーススタディとして下記の事例を使ってデータ分析を行います。
とある小学校６年生は１・２・３組で構成されています。ある日のこと各クラスの担任の先生は、どのクラスが一番国語の成績が優秀であるか話をすることになりました。参考にするのは最近行ったばかりの国語のテスト（100点満点）です。
１組担任「うちは点数の高い生徒が多くいるよ」
２組担任「平均的に高いのはうちかな」
３組担任「こっちは満点が２人もいるからね」
１組担任「どのクラスにも点数の高い子はいるよね。単純な平均点の比較で分かることなのかな？」
| １組 | ２組 | ３組 |
|---|---|---|
| 80 | 85 | 100 |
| 20 | 70 | 100 |
| 70 | 70 | 55 |
| 65 | 70 | 65 |
| 10 | 75 | 70 |
| 80 | 85 | 55 |
| 85 | 80 | 55 |
| 70 | 80 | 60 |
| 65 | 65 | 70 |
| 80 | 75 | 55 |
| 70 | 80 | 65 |
| 65 | 75 | 55 |
| 80 | 80 | 65 |
| 20 | 55 | 55 |
| 30 | 70 | 60 |
| 85 | 65 | 50 |
| 90 | 80 | 65 |
| 25 | 85 | 55 |
| 80 | 65 | 65 |
| 30 | 70 | 60 |
一元配置分散分析は一言でいうと「３グループ以上あるグループ毎の平均点の差は偶然か？必然か？」を知るための分析です。今回のようなケースには打ってつけと言えます。３グループ以上と言ったのは意味があります。２グループの平均点の比較はｔ検定という別の検定を用いるからです。ｔ検定は３グループ以上に対応していません。従って今回のケースでは一元配置分散分析を使うことになります。
一元配置分散分析のツールを選択
Excelのデータ分析ツールを開いてツールの中から一元配置分散分析を選択します。
範囲指定をする
分析をするデータの範囲指定をします。３クラス分のデータを選択します。範囲指定をする際は先頭行の”１組、２組、３組”まで選択して下さい。出力するデータに名前も併せて記載するためです。入力範囲の横の空欄をクリックしたあとに範囲指定をすると自動で入力されます。最後に注意点として、先頭行をラベルとして使用にチェックを入れて下さい。
指定した範囲の先頭列に数字以外のデータを入れた場合は必ず先頭行をラベルとして使用にチェックを入れましょう。これはExcelに対して「先頭行のデータは分析対象のグループ名だよ」と伝えることになります。チェックを入れ忘れると上の画像のようなアラートが出ます。
結果がでた…けど？
おめでとうございます！分析結果を算出することができました。とは言えこのままだと少し見づらいですよね。結果は出力してくれるのですが、出力先のセル幅は自動に変更してくれません。少し面倒ですが手入力で整えましょう。
出力結果の完成
これで一元配置分散分析の操作は以上です。
データから分かること
結果は分かったけど聞いたことない単語がたくさん。
ひとによっては馴染みのない言葉ばかりかもしれないね。けど実はこの結果で見なければいけない箇所は多くないんだ。
それでは今回出力した表からわかることを解説します。表の種類は２つ。概要と分散分析表です。
概要
| グループ | 個数 | 合計 | 平均 | 分散 |
|---|---|---|---|---|
| １組 | 20 | 1200 | 60 | 697.37 |
| ２組 | 20 | 1480 | 74 | 64.74 |
| ３組 | 20 | 1280 | 64 | 183.16 |
これが今回の結果ですね。文字通り、１～３組ごとに国語の点数の概要です。ここで注目すべきは平均と分散です。この２つを組み合わせて分かることは、「各クラスの生徒の点数は平均点からどれくらいバラつきがあるか」ということです。ちなみに分散の平方根を標準偏差といいます。例えば１組の分散は697.37です。これの平方根、すなわち標準偏差は26（整数になるまで四捨五入）になります。つまり、「１組の生徒は平均点±26点の範囲にいるよ」ということが言えます。この中では２組が生徒間での点数のバラつきが小さいことが一目で確認できます。
今回の例で言うと、各生徒の点数から平均点を引き算したものを２乗して積み上げた数字のことを言います。 ２乗する理由としては、生徒によっては引き算した値がマイナスになるため、２乗することでマイナスを打ち消して絶対値の合計を出すためです。なので合計した値は点数と同じ単位ではなくなります。～点として表記するためには分散の平方根、つまり標準偏差を計算します。標準偏差では～点のような単位が復活します。
分散分析表
| 変動要因 | 変動 | 自由度 | 分散 | 分散比 | P-値 | F境界値 |
|---|---|---|---|---|---|---|
| グループ間 | 2080 | 2 | 1040 | 3.30 | 0.044 | 3.159 |
| グループ内 | 17960 | 57 | 315.09 | |||
| 合計 | 20040 | 59 |
ここでも馴染みのない言葉があるかもしれませんが見てほしい数字はたったの１つ。それはP-値です。一元配置分散分析は 「３グループ以上あるグループ毎の平均点の差は偶然か？必然か？」 を知ることができるといいました。それをパーセンテージ化しているのがP-値です。「３クラスの平均点に差が出たのは4.4%の確率で偶然だよ」ということです。言い換えると「３クラスは95.6%の確率で国語の平均的に差が出る」とも言っています。つまり国語の平均点に開きが出たことには意味があると解釈するのが一般的です。今回のケースでは２組の平均点が一番高く、またクラス内のバラつきが小さいので「２組は国語において優秀」と判断をすることができます。一つ注意点を挙げると、一元配置分散分析は「３以上の複数グループのどこかに差がある」とまでしか断定ができません。従ってこれより詳細な分析をするためにはｔ検定という別の分析ツールを用いて、1組と２組・１組と３組・２組と３組…と総当たりで平均点の差に意味があるかを調べる必要があります。とはいえ今回のケースでは２組が優秀なのはすぐに分かりますね。
「P-値以外の見方を知りたい！」という方に向けてそれぞれの言葉の意味と計算方法についてまとめたので参考にして下さい。
●変動要因
クラスの平均点が全体の平均点に対してどれくらいズレているかを知ることが一元配置分散分析のポイントです。
・グループ間（各クラスの平均点と学年の平均の差）
・グループ内（各生徒の点数と学年の平均点の差）
この二つを計算します。変動要因とはこのグループ間とグループ内のことを指しています。参考書によっては変動要因を因子と言ったりもします。
●変動
変動要因で説明したズレの計算値です。こちらも教科書によっては平方和と言うことがあります。それぞれの計算方法は下記の通りです。敢えて数式は使わずに説明をします。
・グループ間
クラスの平均点と学年の平均点との差を2乗してクラスの人数分かけ算した値を、3クラス分合計した値です。2乗する理由としては、引き算した値がマイナスになることあるため、二乗することでマイナスを打ち消しています。
・グループ内
各生徒の点数と学年の平均点との差を2乗した値を60人分合計した値です。2乗する理由は同じです。
●自由度
推測統計の概念の説明が必要になるうえにそこまで重要なことではないので省略しますが計算方法は下記の通りです。
・合計(60人-1)
・グループ間(3クラス-1)
・グループ内(60人-1)-(3人-1)
総人数とクラス数からそれぞれマイナス1をして引き算をします。
●分散
変動を自由度で割った値です。
●分散比
グループ間の分散をグループ内の分散で割った値です。この数値がたまたま出る確率がP-値と考えて下さい。
●F境界値
F分布という説明が一から必要になるので説明を省略しますが、この値よりも分散比が大きければ「結果は偶然ではない」ということができます。
まとめ
Excelで一元配置分散分析をする方法について、統計学に馴染みのない方でもわかっていただけるように解説をしました。推測統計の概念や仮説検定・有意水準といったことに一切触れていませんので説明としては完全ではありません。しかしそれでいいのです。ビジネス統計スペシャリストはどんな仕事にも役立てることのできるスキルが満載です。もしも「統計学をイチから学んでみたい！」と思った方は統計検定への挑戦をオススメします。一元配置分散分析は統計検定２級相当の内容です。それ用に解説もしていますのでお読み下さい。
お読みいただきありがとうございました！
ありがとうございました！