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