エクセル 散布図作成

パッと読むための見出し

1 エクセル　散布図の相関係数の求め方
2 色々な散布図の見方
3 統計学の相関係数
4 動画　散布図の書き方と相関

エクセル　散布図の相関係数の求め方

次に相関係数を求めます。

シートの空いているところ（ここではセルC110）に「相関係数」を作成します。

下記のようなCORREL関数の数式を入力します。

=CORREL(B58:B107,C58:C107)

『B58:B10』はX変数データー入力範囲

『C58:C107』はY変数データー入力範囲

セルC11０に相関係数　－0.8112118が計算、表示されます。

X変数、Y変数がともに数量（間隔尺度・比尺度）であり、直線的関係が見られる場合は、ピアソンの積率相関係数（単相関係数）を用います。この例でも変数はともに数量（間隔尺度）ですからピアソンの積率相関係数（単相関係数）を求めることができます。

CORREL関数　説明：

CORREL関数は指定した二つの配列の相関係数を返す事で二つの配列の相関関係を調べる事ができます。

引数の指定方法：
配列1 数値を含む名前、配列、セル範囲の参照を指定
配列2 もう一方のデータが入力されているセル範囲、数値を含む名前や配列を指定

C2セルに　=CORREL(A2:A4,B2:B4)を入力すると以下の表の結果となります。

C2セルの=CORREL(A2:A4,B2:B4)では収益の行のA2：A4を参照し、B2：B4は来店者数を参照しています。
そして、CORREL関数によって利益と生産数の相関関係を表す相関係数0.9958がC2セルに出力されます。
（小数点第四位まで表示）

	A	B	C
1	利益	生産数	相関係数
2	50000	120	0.9958
3	60000	130
4	30000	90

散布図の相関係数　グラフの見方

相関係数による相関関係の強弱の判断基準は下記のとうりである。

0.8　≦｜　ｒ　｜ → 強い相関あり

0.6　≦｜　ｒ　｜＜0.8 → 相関あり

0.4　≦｜　ｒ　｜＜0.6 → 弱い相関あり

｜　ｒ　｜＜0.4 → ほとんど相関なし

色々な散布図の見方

正の相関があり

ｘが増えるとｙも増えていく傾向があるので”正の相関がある”といえる。Ｘを正しく管理すればｙも管理することができる。

負の相関があり

ｘが増えるとｙが減少する傾向があるので”負の相関が
ある”といえる。Ｘを正しく管理すればｙも管理することが
できる。

正の相関がありそう

ｘが増えると傾向的にｙも増えているように見えるので”正の相関があるそうだ”といえる。

但し点の並びが直線的でないのはｙの値がｘ以外にも影響を受けていると推測されます。
ｘ以外の要因についても調査することが必要です。

負の相関がありそうだ

ｘが増えると傾向的にｙも減少しているように見えるので”負の相関があるそうだ”といえる。
但し点の並びが直線的でないのはｙの値がｘ以外にも影響を受けていると推測されます。
ｘ以外の要因についても調査することが必要です。

相関がない

ｘが増えがない”といいます。ｘ以外のｙに影響する要因を見つけることが大切です。

散布図を見るときの注意事項

異常点がないか？

全体のならびから異常に飛び離れている点があればその原因を調べること。多くの場合測定誤り、不良品混入などの特別な原因なのでその際はそのデーターを省くこと。

層別の有無

全体としてみると相関がないようだが層別すると相関がある場合や逆に相関がありそうだが層別すると相関がない場合があるので散布図を作成する前に層別について検討し点の色を変更するとか印を変更（○、●）する必要があります。

統計学の相関係数

２つのデーターの間に相関関係があるかどうかを数値的に判断するには相関係数と呼ばれる指標を利用する。

変数x, yのデータがn組あるとき、つまり(X1,Y1)(X2,Y2)・・・・（Xn,Yn)相関係数は下記の式で表せる。

＊つまり、相関係数は共分散SxyをXの標準偏差SxとYの標準偏差Sｙで割ったものである。

相関係数は通常ｒと言う記号で表示されー１から１までの値をとる。

-1　≦　ｒ　≦　+１

Excelで相関係数を計算するには、correl という関数を使 =correl(範囲１,範囲２)
相関係数は対になっている量に対して与えられるもので、範囲１と範囲２は並んでいなけれなりません。

相関に関する検定と推定

サンプルから求めたｒは試料相関係数であって母集団の相関係数ρと一致するわけではない。
そこで母集団の相関の有無を正しく判断するために試料相関係数ｒの有意性の検定を行うことが必要になってくる。

相関に関する検定（無相関の検定）

1)仮説の設定

・帰無仮説　Ho：　　母相関係数　ρ =0

・対立仮説　H1：　　母相関係数　ρ≠0

2)検定統計量ｔの計算

・データーから相関係数ｒを計算する。

・相関係数ｒの自由度φを求める。
データーの対の数をｎとすると

φ＝ｎ－２

・相関係数ｒとｎより検定統計量ｔを算出する。

3）有意確率Ｐを求める

検定統計量ｔから有意確率Ｐ（％）を求める

ｔ分布表もしくはＥｘｃｅｌ関数（ＴＤＩＳＴ）を使用する。

4）帰無仮説の判定をする。

有意確率Ｐ（％）と有意水準α（％）の比較をし判定を下す

Ｐ（％）＞α（％ →採択『母相関係数ρ＝０と相関係数ｒの差がない』

Ｐ（％）≦α（％ →不採『母相関係数ρ＝０と相関係数ｒの差がある』

ｔ分布

有意確率Ｐを求める

相関に関する検定と推定（具体事例）

Ｅｘｅｒｃｉｓｅ１：
サンプル組数が24、相関係数が0.476の時、母相関係数が０であるか
を検定てください。
＜仮説の設定＞
・帰無仮説　Ho：　　母相関係数　ρ =0

・対立仮説　H1：　　母相関係数　ρ≠0
＜計算＞

相関係数ｒ 0.48
サンプル組数 24
自由度 22
検定統計量to 2.54 ← 計算式'(ABS(ｒ)*SQRT(ｎ-2))/SQRT(1-ｒ*ｒ）
有意確率P ### ← ＴＤＩＳＴ(to ＊両側検定

＜判定：　Ｐ＝1.87％ α（％）＝5％

∴P≦α

→不採択『母相関係数ρ＝０と相関係数ｒの差がある』

つまり有意水準５％で有意差がある。

＜説明＞

相関係数ｒ　0.476　自由度φ＝２２のときの検定統計量toは2.53869であるから図Aのようなｔ分布となる。（両側確率：１．８７％）

一方、図Bは検定統計量toは2.538689の時のｔ分布である（両側確率：５％）

母相関係数ρがゼロの場合でも、相関係数ｒは-1から1の間でばらつくがB図のように有意水準を５％として信頼区間を表現すると黄色部分が９５％の信頼区間となりこの範囲に入れば帰無仮説　Ho：　　母相関係数　ρ =0　は採択される。

けれども例題の場合は９５％の信頼区間の外にあるために仮説は破棄される。
但し有意水準を１％とした場合は採択される。

相関に関する検定と推定

母相関係数の区間推定

1)相関係数ｒをZ変換した値Z（フィシャーのZ変換）をｚ変換図表もしくは’Excel関数FISHERを用いて求める。

2)母相関係数をZ変換した値ξ（フィシャーのZ変換）の信頼度９５％の信頼限界を次式より求める

3）ξの信頼限界を逆変換して母相関係数の信頼限界を求める。

＊ｚ変換図表もしくは’Excel関数FISHERINVを用いて求める。

参考文献：ＱＳＳ－普通科テキスト　統計的手法　　　日本規格協会

エクセル　散布図作成