■Excel:相関係数,回帰直線
.
◇このページで利用する関数等の一覧◇
  • グラフ--散布図
  • ツール--分析ツール
  • r表
  • SLOPE(yの配列の範囲,xの配列の範囲):傾き
  • INTERCEPT(yの配列の範囲,xの配列の範囲):y切片
  • CORREL(系列1の範囲,系列2の範囲):相関係数
  • PEARSON(系列1の範囲,系列2の範囲):相関係数
  • STEYX(系列1の範囲,系列2の範囲):推定値の標準誤差
  • TREND(既知のy,既知のx,新しいx,1):回帰直線上の推定値
  • FORECAST(新しいx,既知のy,既知のx):回帰直線上の推定値
■操作方法の要約■
 Excelを用いて,右の表1のようなデータ(2列)から図1のような散布図を作成し,相関係数,回帰直線を求めるには

(1)
 散布図の作成方法は[こちら

(2)
 図1の(2) 回帰直線 を表示するには:
散布図のマーカー(右図では青の点)の1つを右クリックし,「近似曲線の追加」→「線形近似」
(3)
 図1の(3)(4) 回帰直線,相関係数Rの2乗を表示するには:
●1
 グラフ中の「近似直線」を右クリック→「グラフに数式を表示する」「グラフにRー2乗値を表示する」をチェック→OK
(これらの式はテキストボックスなので,適当な場所に移動して見やすくすることができる.)
==>データが更新されれば自動的に追随する.(データ範囲が変われば,範囲指定の変更は必要)
●2
 回帰直線の方程式 y=ax+b のa,bだけを求めたいときは
=SLOPE(yの配列の範囲xの配列の範囲)→a
=INTERCEPT(yの配列の範囲xの配列の範囲)→b (※引数の順序に注意)
(4) 相関係数を求め,無相関の検定をするには
(実際には相関がないものに回帰直線を求めてもだめなので相関係数を先に求める方がよい)
●1
 散布図を作成しなくても,「分析ツール」を利用すれば
相関係数は求められる:「ツール」→「分析ツール」→「相関」→「OK」→入力元:入力範囲として表2の場合3列全体をドラッグ(範囲指定の際に,表2においてx,y,zというタイトルも含める場合,「先頭行をラベルとして使用」にチェック)→OK
 出力される表には,すべての組合せの相関係数が表示されるので,x−x組のように自分自身との相関係数は1となる.また,x−yの相関係数とy−xの相関係数は異なるが,右の表3においてはx-yの相関係数が0.807と表示されている.(R=0.807→R2=0.6515)==>データが更新されても再度出力しないと結果は追随しない.
●2 correlation :相関
 
==>散布図なしで,データ更新時に相関係数が追随するようにするには(ただし,データ範囲が変われば,範囲指定は必要)
関数
CORREL(系列1の範囲系列2の範囲)
または
関数PEARSON(系列1の範囲系列2の範囲)
を利用することができる.
(直接入力または挿入→関数→統計)
返される値は表3のうちの1つの値(0.807など)
●3
 標本相関係数をrとするとき母相関係数ρが0(無相関)でないための限界値は右のr表によって判断する.|r|がこの限界値以上ならば「相関あり」として回帰直線などを求める.
(5)
 図1の(5)のような推定値の標準誤差,信頼区間を求めるには
=STEYX(系列1の範囲,系列2の範囲) ==> 標準誤差Seとなる.

 図に示した灰色の線 (95%信頼区間の限界) は回帰直線±1.96Seの線で,この線はExcelグラフには表示されない.
(6)
 図1の(6)のように与えられたx座標に対応する回帰直線のy座標を数値として求めるには
=TREND(既知のy既知のx新しいx)
または
=FORECAST(新しいx,既知のy既知のx)
表1
x y
0.437 11.144
2.164 11.657
2.920 20.318
3.743 16.998
・・・ ・・・
   表2
x y z
0.437 11.144 23.857
2.164 11.658 23.911
2.921 20.318 24.116
3.743 16.998 24.229
・・・ ・・・ ・・・
図1
表3
   x y z
x 1     
y 0.807 1   
z 0.850 0.788 1

※ 推定値の標準誤差Seは,Excelの組み込み関数
=STEYX(系列1の範囲,系列2の範囲)で求められるが,これは
yとyの推定値y’の誤差から計算される標準偏差
σ =
ではなく
Se =
とする(nは点の個数=x座標の個数).(標本から母平均を求めるときの計算と同様に考えるが,今の場合xの平均yの平均が計算式にはいるので,自由度が2減る.)


両側検定用のr表
  有意水準α(両側検定)
自由度(n-2) 0.1 0.05 0.02 0.01
10 0.497 0.576 0.658 0.708
11 0.476 0.553 0.634 0.684
12 0.458 0.532 0.612 0.661
13 0.441 0.514 0.592 0.641
14 0.426 0.497 0.574 0.623
15 0.412 0.482 0.558 0.606
16 0.400 0.468 0.543 0.590
17 0.389 0.456 0.529 0.575
18 0.378 0.444 0.516 0.561
19 0.369 0.433 0.503 0.549
20 0.360 0.423 0.492 0.537
21 0.352 0.413 0.482 0.526
22 0.344 0.404 0.472 0.515
23 0.337 0.396 0.462 0.505
24 0.330 0.388 0.453 0.496
25 0.323 0.381 0.445 0.487
26 0.317 0.374 0.437 0.479
27 0.311 0.367 0.430 0.471
28 0.306 0.361 0.423 0.463
29 0.301 0.355 0.416 0.456
30 0.296 0.349 0.409 0.449
40 0.257 0.304 0.358 0.393
50 0.231 0.273 0.322 0.354
60 0.211 0.250 0.295 0.325
70 0.195 0.232 0.274 0.302
80 0.183 0.217 0.257 0.283
90 0.173 0.205 0.242 0.267
100 0.164 0.195 0.230 0.254
110 0.156 0.186 0.220 0.242
120 0.150 0.178 0.210 0.232

■解説■
◇回帰直線◇
 中学校の頃には理科の実験結果をまとめるときに,次の図のように点が「上にも下にも半分ずつ来るように」「直線からなるべく離れないように」目分量で直線を引いていたが,これを数学的に求める.
 各点から直線 y = ax + b までの(y方向の差)の合計
Σ yk - (axk+b)
は,上下で符号が逆になるから和は元々0となる.絶対値は変形しにくい.そこでΣ yk - (axk+b) 2が最小となるようなa,bを求める(最小2乗法).

 (途中経過略:結果のみ示す.)
 k の平均を mk の平均を n とおくと
a =
b = n - am

※ 統計の書物では,数学と違って,定数を先に書いて
y = α + βx
の形で書かれることが多い.(例えば,株式相場で日経平均をx,ある銘柄の株価をyで表わすとき,日経平均の増減に対するその銘柄の増減の割合はxの係数βとなる.過去何ヶ月かの変動を見たときのβの値はヒストリカル・ベータと呼ばれる.)
 Excelグラフでは数学の表示が踏襲されているので,ここでは数学の書き方に従って y = ax + b の形で表示した.


※ ここで求めたa,bを用いて元いてy = ax + bの方程式にしたものが図1(3)でグラフに追加される回帰直線の方程式に一致する.(点検用に併用するとよい.)

※ 回帰直線のグラフおよびその方程式は,相関が認められないような分布についても形式的に出てくる (直線の中で誤差の2乗和が一番小さいものを求めているだけだから).そこで,これを使うかどうかは,次に述べる相関係数の検定をしてから判断することが重要.(右の図2のような場合,x,yにはほとんど相関がなく,形式的に求まる回帰直線にも利用価値がない.)
 したがって,まず相関係数を求め,相関係数の検定を行って相関があると確かめた上で回帰直線を求める方がよい.
※何に回帰するのか・・・子の身長は親の身長と強い相関があるが,最終的に親の身長から予測される値よりも少し先祖返りするということで,回帰という名が付けられたらしい.(こんなイメージか?)

※ 左に示したa,bの計算をExcelで行うには次のように順次表を組み立てて行き,最後に和と商を求めればよい.
x y (x-m) y-n (x-m)2 (x-m)(y-n)
0.437 11.144 -9.191 -13.083 84.475 120.250
2.164 11.658 -7.464 -12.570 55.706 93.815
2.921 20.318 -6.707 -3.909 44.989 26.220
3.743 16.998 -5.885 -7.230 34.634 42.547
・・・ ・・・ ・・・ ・・・ ・・・ ・・・
上の図1の場合,a = 1.428,b = 10.475 となる.
※ Excelのグラフでは直線近似だけでなく,2次関数,・・・など曲線近似の結果も表示されるが,ここでは直線近似のみを扱う.

図2

◇相関係数◇

○ xが増えるとyも増えるとき,x,yは正の相関があるという.
xが増えるとyは減るとき,x,yは負の相関があるという.
直線的な関係に近いとき,強い相関があるといい,幅が広がるほど相関は弱くなる.

○ 単に相関係数と言えば,通常,以下に述べるピアソンの積率相関係数のことを指す.

k の平均を mk の平均を n とおくと
r =
 ア) r が1,-1に近いほど相関は強くなる.
 イ) r が0に近いほど相関は弱くなる.

○ 相関係数の検定(母相関係数ρ=0の検定)
 標本相関係数をr,母相関係数をρとするとき,ρ=0ならば無相関となる.そこで,ρ=0という帰無仮説を立てて,これが棄却されるときに「相関がある」と考える.
 rの値に対して
が自由度n-2のt分布に従うことが知られており,これを用いて検定できるが,もっと簡単に,その結果がr表:「rの値→棄却域の値」としてまとめられているので,これを利用する.
 r表では,各自由度(標本の組の数-2),有意水準αに対して,この値よりも |r| が大きければρ=0が棄却され,相関があると判断できる.α=0.05でρ=0が棄却されれば,「相関がある」,α=0.01でρ=0が棄却されれば「強い相関がある」と考える.(ただし,両側検定用の値なので,片側検定に用いるときはαとして2倍の値を用いる.)
ピアソンの積率相関係数:[概要]
i) 絶対温度で測れば強い相関が見られるが,摂氏温度で測れば相関がないというような,原点の取り方で相関が変わるのを防ぐために,x,yとも平均を原点にする.
x’ = xk - m y’ = yk - n
ii) x’y’>0となる点が多ければ,正の相関
 x’y’<0となる点が多ければ,負の相関が見られる.
 x’y’>0x’y’<0となる点が同程度ならほとんど相関がない.

==> Σx’y’すなわちΣ(k - m)(yk - n)を候補にする.
iii) cmの単位で測ると,mメートルの単位で測ったときよりも,100倍×100倍の相関が出てくるというようなスケールの取り方で相関が変わるのを防ぐため,各々の大きさで割る.
 ==> r =
■例と答■

 別添ファイル( correl.xls )Sheet1のデータについて, (1) 散布図を作成し, (2) 相関係数を求め,無相関の検定をし,(3) 回帰直線を表示し, (4) 回帰直線の方程式を求め, (5) 推定値の標準誤差, (6) x=20のときのyの予測値,  (7) x=20のときのy予測値の95%信頼区間 を求めよ.
(解答)
(1) まず散布図を作成し,(3)近似曲線の追加:線形近似とする.

(4) 近似直線の右クリックで回帰直線の方程式,R-2乗を表示
もしくは
 回帰直線の方程式は
a =
b = n - am
で確かめることができる.
もしくは
 =SLOPE(B2:B51,A2:A51) によりa = 1.428,=INTERCEPT(B2:B51,A2:A51) により b = 10.48


(2) 相関係数は
  =SQRT(R2の値)により r = 0.827
もしくは,
  メニューから「ツール」→「分析ツール」→「相関」により r =0 .827
もしくは
  =CORREL(A1:A41,B1:B41) により r = 0.827
もしくは
  =PEARSON(A1:A41,B1:B41) により r = 0.827
もしくは
 r =
を表を組み立てて求める. r = 0.827
 
 r表より,自由度38で有意水準α=0.05の場合, r = 約0.28,α=0.01の場合r=約0.36 だから,0.827は強い相関がある.
 
(5) 推定値の標準誤差は =STEYX(B2:B41,A2:A41) により Se = 3.128
もしくは
 y=1.6964x-0.3196 または TREND($B$2:$B$41,$A$2:$A$41,A2,1) で各々のxに対するyの推定値を求め(これをy’とおく),
Se =
(6) y=1.6964x-0.3196 にx=20 を代入して y’ = 33.609
もしくは
=TREND($B$2:$B$41,$A$2:$A$41,20,1) で y’= 33.609
もしくは
 =FORCAST($B$2:$B$41,$A$2:$A$41,20,1) で y’= 33.609

(7) 33.609±1.96Se = 33.609±6.131により 27.478≦y’≦39.740
■相関と因果関係■〜♪作者の読書感想文
 「事例でわかる統計解析の基本」(*1)(柳谷晃著/日本能率協会マネジメントセンター)p.161には,夏の気温や湿度とビール売上高の相関が紹介されている.また,「やさしい統計学」(*2)(田畑吉雄著/現代数学社)p.123には各都市の街灯の数と性犯罪の発生件数の相関が紹介されている.この話を元に相関と因果関係について考察してみる.
 まず,右の図式1においては,相関関係,因果関係とも認められる.(*1)では気温よりもむしろ湿度との関係が強いことが示されているが,ここでは話を簡単にするために気温のみに絞る.
 気温が高くなっても,子どもはビールを飲まない.牛乳や清涼飲料水が売れる.そこで,夏の気温と牛乳,清涼飲料水の売れ行きも相関,因果関係ともありそうだ.クーラーを使うので,電力消費量も同様.(図式2)

 次に,各都市を比較すると,街灯の数と性犯罪の数には相関がある.しかし,街灯を減らしても性犯罪は減らない.(*2)
(図式1) 相関:○,因果関係:○
 夏の気温・湿度→ビールの消費量


(図式2)

相関関係:@ABCどの組合わせにも有り
因果関係:@→A(またはBまたはC)に有り


(図式3) ABは相関:○,因果関係:×
 逆に,因果関係があるが,相関関係はない例を考えてみる.母馬:Aが子馬:Bを産み,子馬:Bが競馬で優勝したとき
 自然的な因果関係としては,ある出来事Aが起こらなければ,他の出来事Bは起こらなかったといえるとき,AはBと因果関係があるとする.そこで,母馬:Aが子馬:Bを産んだことは,Bが優勝したことと自然的な因果関係がある.
 次に,馬の世界では,母が強いと子どもも強いことが多いので,子馬:Bの成績と子馬:Cの成績にも相関がある.しかし,因果関係はない.
(図式4) @Aの因果関係:○,ABの相関関係:○△
 @母馬:A → 子馬:B → 競馬の成績A
        └→子馬:C → 競馬の成績B
.
(まとめ)

 「あることが起こらなければ,他のあることが起こらない」といえるときは,これらの間には,自然的な因果関係がある.しかし,そのことが起こればかなりの確率で他のことが起こるといえる場合しか社会的な因果関係は認められない.

 右図式@CDのように因果関係がある事象(直系の子孫となる事象)には,相関が認められるものが多い(因果関係があっても相関がないこともある--優秀な親の子どもが必ず優秀とは限らない.)右図式5のDEのように共通の親事象から発生しているもの同士にはどちら側を説明変数にしても相関が認められることがある.しかし,DEには因果関係はない.(それぞれの子どもが親に似ているだけ

 社会現象は複雑に関連し合っているので原因・結果の関係は必ずしも明らかでないことが多い.むしろ,右のFGHのように関連があるのかどうか分からないことの方が多い.このとき,相関関係が認められれば様々な角度から因果関係を調べていくきっかけとなる 
(図式5)

相関と因果関係,例2つずつ:
(相関,因果関係が認められるもの)
ペルー沖の海水温の高低→世界的な異常気象
1日当りの喫煙本数×年数→肺ガンの発生率

(両論があり,よく分からないもの)2007.3現在
丸山ワクチン→?ガンの治療
FM電波の異常,地震雲→?地震の予知

(相関を調べてほしいという段階)2007.3現在
インフルエンザ治療薬の服用→?異常行動
携帯電話の電波帯域→?脳腫瘍の発生

(部分集合の取り方で相関の有無が分かれるもの)
入試の成績→入学後の成績
(限られた部分集合を合格させる学校:相関なし,ほぼ全員合格させる学校:相関あり)
(言い伝え)
「弘法さんと天神さんは仲が悪い.」・・・「弘法さん」とは弘法大師の縁日(毎月21日)のこと,「天神さん」とは菅原道真の縁日(毎月25日)のこと.表題の言い伝えは,21日が晴(雨)なら25日は雨(晴)になるということを表わしている.長年の経験から気づいた負の相関関係を表わしたものと考えられる.
 冬場の天候は三寒四温といわれ,4日後は天候が変わる確率が高いといえば因果関係についての今日的な説明になる.

(因果関係はないが,相関関係は考えられるもの)
「他人に風邪をうつせば自分は直る」という俗説.・・・上記図式3と同様に,@に「数日間の経過」があってABに各々その結果として「自分について,日にち薬で風邪が直ってくる」ことと,「他人について,保菌・潜伏期間が経過して風邪の症状が出る」ことが平行して起こるという経験を持つ人が多いかもしれない.
○=== メニューに戻る