Excel を使った相関係数の計算 回帰分析 準備データは授業のホームページ上に Excel ブックの状態 ( ファイル名 pop_traffic.xlsx) で用意してあるので, これをダウンロードして保存しておく ダウンロードされたファイルを開いたら,DATA シート中の空欄 (POP,TK の列 ) をそれぞれの合計値 (POP の場合は,POP1~POP3) で埋めるように,SUM 関数あるいは和の式を使って処理しておく 散布図による確認総人口と輸送トンキロ数との関係を散布図で示してみよう グラフを作成するには, シート中の連続するデータをあらかじめドラッグしておくのが原則であるが, 総人口 (E 列 ) と輸送トンキロ数 (J 列 ) のように不連続な範囲を指定したい時には,Ctrl キーを使ったドラッグが有効である 図 1 のように, まず, どちらかの連続範囲を通常の ( セル範囲を指定する ) ドラッグ操作で指示しておき, 離れた場所にある範囲を指示する際にキーボードの Ctrl キーを押しながらドラッグ操作を行う 1 通常のドラッグ操作で指定 2 キーボードの Ctrl キーを押しながら 2 つめの範囲をドラッグ操作で指定 図 1 Ctrl キーを使った不連続な範囲のドラッグ指定の手順 その後のグラフの作成手順の詳細は省略するが, 図 2 を参考に仕上げてみよう 1/9
タイトルを修正 軸ラベルを挿入グラフツール デザイン グラフ要素を追加 軸ラベル 第 1 横 ( 縦 ) 軸 凡例は削除 横軸は, 軸の目盛範囲の最小値 最 大値を手動で設定して調整 図 2 散布図の仕上げ見本 相関係数の計算 散布図を見ると, 因果関係はともかく, 人口と輸送量の間には相関関係があるように思 われる 相関の強さを数値的に示すには, 相関係数 1 を用いることが多い 相関係数はデータ数や 数値の大きさによらず,-1 から +1 の値を示すので, 異なるデータ群の比較にも利用できる Excel では,SUM 関数などと同様にワークシート中で利用できる組込み関数として CORREL 関数が用意されており, これを使用することで, 容易に相関係数を求めることが できる 1 特に断らない場合は,Pearso( 人名 ) の積率相関係数を指す 他に Spearma の順位相関係数と呼ばれるものもある 積率相関係数は, 個のデータの組,(x1,y1),(x2,y2), (x,y) 1 ( xk x)( yk y) k 1 が与えられた時に, rxy で計算される ( x や y はそれぞ 1 2 1 2 ( xk x) ( yk y) k 1 れ x や y の算術平均 ) 分子は x と y の共分散, 分母は x の分散と y の分散の幾何平均の形になっている k 1 2/9
ワークシート中の適当なセルで, =CORREL(E2:E14,J2:J14) と入力すると,(E2~E14 の範囲の ) 総人口と (J2~J14 の範囲の ) 輸送トンキロ数との相関係数が求められる POP と TK の相関係数 0.9711 散布図への近似曲線の追加による回帰分析次に, 人口と輸送量との関係を, 数式モデルとして与えることを考える 散布図で見ると, 人口の増加に伴って輸送量がいわば 直線的に 伸びていることが予見されるが, これを, 輸送量 ( 輸送トンキロ ) 傾き 人口 切片のように, 人口によって輸送量が決まるといった現象を ( 二次元グラフ上の直線という ) 数式で示してみることで, 人口の推計値によって輸送量も推計できる, という予測のツールとして利用が可能になる 上式で, 切片や傾きは決める必要があるが, それには今知られている輸送量と人口のデータからなるべく乖離しないように, 合理的に決める必要がある その決定方法には, いくつかの種類があるが, 最も単純なものは最小自乗法と呼ばれ, Excel にもその数値解を求める機能が備わっている 一般に既知のデータから数値モデルのパラメータ ( 上の場合は切片や傾き ) を決める手法を回帰分析 2と呼んでいる 後述の 分析ツール を使う手段もあるが, 二次元の場合 3には, 散布図のオプション ( 近似曲線の追加 ) によって, 直線式や多項式の当てはめ ( つまり回帰分析 ) の結果をグラフ上に重ね書きすることができる 図 3 のように, 散布図を描いた後で, グラフツール デザイン グラフ要素を追加 近似曲線からその他の近似曲線オプションを選択する 2 直線に当てはめる場合は, 式の形が一般に線形結合と呼ばれることから, 線形回帰分析と限定して呼ぶこともある ちなみに式が線形でない場合は, 非線形回帰分析と呼ぶ 3 説明される変数 ( 被説明変数あるいは従属変数, この場合は輸送量 ) に対して説明変数 ( あるいは独立変数 ) が 1 つの場合を特に単回帰分析と呼ぶ ( 複数の場合は重回帰分析と呼ぶ ) 3/9
あらかじめグラフをクリックして選択しておく グラフ上に数式と R 2 ( 決定 係数 ) を示す 図 3 近似曲線の追加 を用いた散布図上での簡便な ( 単 ) 回帰分析の手順 4/9
結果の例を図 4 に示す オプションによって指示した回帰分析の結果がグラフ上に示されることが分かる この場合は, 先に想定した式に当てはめると, 輸送量 ( 輸送トンキロ ) 11.88 人口 9614543 と求められたことを示している 4 ただし, この結果が統計学的にどの程度の信頼性を持っているかは示されないので, より精緻な検証を行うには,Excel の 分析ツール や SPSS などの統計処理ソフトを用いる必要がある ( 近似曲線で示す手順はあくまで簡便法ということ ) 図 4 散布図上に近似曲線 ( 回帰分析による直線 ) を追加した例 ( 輸送トンキロ =11.88 人口 -961543 で, 決定係数 ( 説明力 ) は 94.31%) 分析ツールによる回帰分析 前述のように, 統計的な信頼性を示す必要がある場合や, 説明変数が複数のモデル ( 重 回帰分析 ) を扱う場合には, 統計処理用のソフトを使う必要がある Excel でも限定的な機 能ながら, 回帰分析を行うソフト 5 が組み込まれているので, これを使った分析手順を紹介 しておく 分析ツールは, データリボンから呼び出す データリボンに見えていない場合には, 図 5 4 決定係数は, この式によって, 全体のバラツキをどの程度説明できることになるかという目安を示す割合と考えればよい 5 アドイン ( メインのソフト ( この場合は Excel) と連動する, 追加的なソフトのこと アドオン, プラグインも同様 ) として提供されている 分析ツール この機能の一部として 回帰分析 が使用できる 5/9
の手順で分析ツールを使用できるように設定し直し, あらためてデータリボンに切り替え る 分析ツールが使用可能になったら, 回帰分析用の設定パネルを呼び出し, 図 6 に示す 手順で, 必要なデータ範囲をドラッグ操作で示せばよい ファイルを選択 アドインを選択 管理 :Excel アドインを選択して 設定 をクリック オプションを選択 図 5 分析ツール アドインの設定 ( データリボンに分析ツールが表示されていない場合の設定手順 ) 6/9
1 の範囲をドラッグで指示 この場所をクリックしてから 2 の範囲をドラッグで指示 12 の先頭行が名前である場合にチェック 必要に応じて適宜チェック ( 今回は 残差グラフの作成 のみ選択 ) 2 1 図 6 回帰分析 の進め方 OK をクリックして処理を進めると, 図 7のような結果が新しいワークシート上に示される 見るべきポイントは 4 点ほどある 1. 補正 R2 データ数や説明変数の数によって補正された決定係数 1を 100% とする割合として読み替えればよい 図 7 の例では, 説明力は,80.82% と読むことになる 一般的には 90% 以上となるのが望ましいが, 社会科学で扱うような事象では 60% 程度で 7/9
も良好とする場合が多い 低すぎる場合は説明変数の増減を検討する必要がある なお, グラフの近似曲線で示される決定係数は, 補正前の 重決定 R2 に相当しており, この値はデータ数を考慮していない ( 一般にデータ数が少ないと説明力は見かけ上高くなる ) ので, この値については参考程度に扱うべきである 2. 有意 F この値は確率として読み, 想定した数式モデル ( 直線式 ) の変数に関わる係数 ( 傾き ) が すべて 0 である 確率 6として示される 図 7の例では,3.41687E-08 7 なので, すべて 0 である 確率はほぼ 0 であるから, 少なくとも1つは 0 ではない = 数式は意味を持っている ことを示している このように, この値は小さいほどよく, 一般に 0.05(5%) 未満であれば (95% 以上の確率で意味がある 8 ということなので ) 良好とされる 3. 係数この値が, 想定した数式に対応する値 ( 切片や傾き ) となる 4.P- 値この値は確率として読み, 想定した数式モデル ( 直線式 ) の変数に関わる係数ごとに, その係数が 0 である 確率として示される 有意 F の読み方と同様に, この値は小さいほどよく, 一般に 0.05(5%) 未満であれば (95% 以上の確率で意味があるということなので ) 良好とされる 5% より大きな値を示すものがあれば, その変数は数式には無関係ということなので, 数式モデルの再考を検討する 6 統計学では, 仮説検定と呼ばれる手法の結果を示している 7 文字 E を含む数値の形式を指数形式と呼び, この例では 3.41687E-08 3.41687 10-8 0.0000000341687 と読みかえる ゆうい 8 このことを ( 統計的に ) 有意 と表現する 8/9
説明力 モデル全体の信頼性 モデル式の係数 係数の信頼性 図 7 分析例とその読み方 ( 注目点 ) ( 実際の結果を, 読みやすくなるように列幅を調整してある ) 演習余力のある人は, 説明変数を年代別の人口 (POP1~POP3) に変更して ( 図 6 における入力 X 範囲を B2~B14 に変えて ), 重回帰分析を行ってみなさい 決定係数の違いや係数の信頼性を検討した上で, 係数の大きさによって何が表されているか, 吟味してみなさい 9/9