エクセル 散布図の相関係数の求め方
次に相関係数を求めます。
シートの空いているところ(ここではセルC110)に「相関係数」を作成します。
下記のようなCORREL関数の数式を入力します。
=CORREL(B58:B107,C58:C107)
『B58:B10』はX変数データー入力範囲
『C58:C107』はY変数データー入力範囲
セルC110に相関係数 -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 ≦| r | → 強い相関あり
0.6 ≦| r | <0.8 → 相関あり
0.4 ≦| r | <0.6 → 弱い相関あり
| r |<0.4 → ほとんど相関なし
色々な散布図の見方
正の相関があり
xが増えるとyも増えていく傾向があるので”正の相関がある”といえる。Xを正しく管理すればyも管理することができる。
負の相関があり
xが増えるとyが減少する傾向があるので”負の相関が
ある”といえる。Xを正しく管理すればyも管理することが
できる。
正の相関がありそう
xが増えると傾向的にyも増えているように見えるので”正の相関があるそうだ”といえる。
但し点の並びが直線的でないのはyの値がx以外にも影響を受けていると推測されます。
x以外の要因についても調査することが必要です。
負の相関がありそうだ
xが増えると傾向的にyも減少しているように見えるので”負の相関があるそうだ”といえる。
但し点の並びが直線的でないのはyの値がx以外にも影響を受けていると推測されます。
x以外の要因についても調査することが必要です。
相関がない
xが増えがない”と いいます。x以外のyに影響する要因を見つけることが大切です。
散布図を見るときの注意事項
異常点がないか?
全体のならびから異常に飛び離れている点があればその原因を調べること。多くの場合測定誤り、不良品混入などの特別な原因なのでその際はそのデーターを省くこと。
層別の有無
全体としてみると相関がないようだが層別すると相関がある場合や逆に相関がありそうだが層別すると相関がない場合があるので散布図を作成する前に層別について検討し点の色を変更するとか印を変更(○、●)する必要があります。
統計学の相関係数
2つのデーターの間に相関関係があるかどうかを数値的に判断するには相関係数と呼ばれる指標を利用する。
変数x, yのデータがn組あるとき、つまり(X1,Y1)(X2,Y2)・・・・(Xn,Yn)相関係数は下記の式で表せる。
*つまり、相関係数は共分散SxyをXの標準偏差SxとYの標準偏差Syで割ったものである。
相関係数は通常rと言う記号で表示されー1から1までの値をとる。
-1 ≦ r ≦ +1
Excelで相関係数を計算するには、correl という関数を使 =correl(範囲1,範囲2)
相関係数は対になっている量に対して与えられるもので、範囲1と範囲2は並んでいなけれなりません。
相関に関する検定と推定
サンプルから求めたrは試料相関係数であって母集団の相関係数ρと一致するわけではない。
そこで母集団の相関の有無を正しく判断するために試料相関係数rの有意性の検定を行うことが必要になってくる。
相関に関する検定(無相関の検定)
1)仮説の設定
・帰無仮説 Ho: 母相関係数 ρ =0
・対立仮説 H1: 母相関係数 ρ≠0
2)検定統計量tの計算
・データーから相関係数rを計算する。
・相関係数rの自由度φを求める。
データーの対の数をnとすると
φ=n-2
・相関係数rとnより検定統計量tを算出する。
3)有意確率Pを求める
検定統計量tから有意確率P(%)を求める
t分布表もしくはExcel関数(TDIST)を使用する。
4)帰無仮説の判定をする。
有意確率P(%)と有意水準α(%)の比較をし判定を下す
P(%)>α(% →採択 『母相関係数ρ=0と相関係数rの差がない』
P(%)≦α(% →不採 『母相関係数ρ=0と相関係数rの差がある』
t分布
![[Tags] 1x1.trans エクセル 散布図作成](https://takuminotie.com/wp-content/themes/simplicity2/images/1x1.trans.gif)
有意確率Pを求める
相関に関する検定と推定(具体事例)
Exercise1:
サンプル組数が24、相関係数が0.476の時、母相関係数が0であるか
を検定てください。
<仮説の設定>
・帰無仮説 Ho: 母相関係数 ρ =0
・対立仮説 H1: 母相関係数 ρ≠0
<計算>
相関係数r 0.48
サンプル組数 24
自由度 22
検定統計量to 2.54 ← 計算式'(ABS(r)*SQRT(n-2))/SQRT(1-r*r)
有意確率P ### ← TDIST(to *両側検定
<判定: P=1.87% α(%)=5%
∴P≦α
→不採択 『母相関係数ρ=0と相関係数rの差がある』
つまり有意水準5%で有意差がある。
<説明>
相関係数r 0.476 自由度φ=22のときの検定統計量toは2.53869であるから図Aのようなt分布となる。(両側確率:1.87%)
一方、図Bは検定統計量toは2.538689の時のt分布である(両側確率:5%)
母相関係数ρがゼロの場合でも、相関係数rは-1から1の間でばらつくがB図のように有意水準を5%として信頼区間を表現すると黄色部分が95%の信頼区間となりこの範囲に入れば帰無仮説 Ho: 母相関係数 ρ =0 は採択される。
けれども例題の場合は95%の信頼区間の外にあるために仮説は破棄される。
但し有意水準を1%とした場合は採択される。
![[Tags] 1x1.trans エクセル 散布図作成](https://takuminotie.com/wp-content/themes/simplicity2/images/1x1.trans.gif)
相関に関する検定と推定
母相関係数の区間推定
1)相関係数rをZ変換した値Z(フィシャーのZ変換)をz変換図表もしくは’Excel関数FISHERを用いて求める。
2)母相関係数をZ変換した値ξ(フィシャーのZ変換)の信頼度95%の信頼限界を次式より求める
3)ξの信頼限界を逆変換して母相関係数の信頼限界を求める。
*z変換図表もしくは’Excel関数FISHERINVを用いて求める。
参考文献:QSS-普通科テキスト 統計的手法 日本規格協会
動画 散布図の書き方と相関
おすすめソフト エクセル統計2015
搭載機能:基本統計、1標本の推定と検定、2標本の比較、集計表の作成と分析、分散分析・多重比較、ノンパラメトリック検定、生存分析・ハザード分析、時系列分析・曲線のあてはめ、多変量解析、管理図・ROC曲線、特殊なグラフ、ユーティリティなど。
|
おすすめ QC七つ道具 参考文献:
図解入門ビジネス新QC七つ道具の使い方がよ~くわかる本 | Excelでいつでも使えるQC七つ道具と新QC七つ道具―解析と発想に役立つ14のツール | ポケット図解 QC七つ道具の基本がわかる本 |
【アマゾン おすすめ本 散布図】