Excel を使った相関係数の計算・回帰分析

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