【Excel系列】Excel數(shù)據(jù)分析:相關(guān)與回歸分析

相關(guān)系數(shù)

15.1 相關(guān)系數(shù)的概念

著名統(tǒng)計學(xué)家卡爾·皮爾遜設(shè)計了統(tǒng)計指標(biāo)——相關(guān)系數(shù)(Correlation coefficient)桐玻。相關(guān)系數(shù)是用以反映變量之間相關(guān)關(guān)系密切程度的統(tǒng)計指標(biāo)瓤荔。相關(guān)系數(shù)是按積差方法計算,同樣以兩變量與各自平均值的離差為基礎(chǔ),通過兩個離差相乘來反映兩變量之間相關(guān)程度泵琳;著重研究線性的單相關(guān)系數(shù)皂林。

依據(jù)相關(guān)現(xiàn)象之間的不同特征,其統(tǒng)計指標(biāo)的名稱有所不同浴骂。如將反映兩變量間線性相關(guān)關(guān)系的統(tǒng)計指標(biāo)稱為相關(guān)系數(shù)(相關(guān)系數(shù)的平方稱為判定系數(shù));將反映兩變量間曲線相關(guān)關(guān)系的統(tǒng)計指標(biāo)稱為非線性相關(guān)系數(shù)宪潮、非線性判定系數(shù)溯警;將反映多元線性相關(guān)關(guān)系的統(tǒng)計指標(biāo)稱為復(fù)相關(guān)系數(shù)、復(fù)判定系數(shù)等狡相。相關(guān)系數(shù)的計算公式為:

復(fù)相關(guān)系數(shù)(multiple correlation coefficient):反映一個因變量與一組自變量(兩個或兩個以上)之間相關(guān)程度的指標(biāo)梯轻。它是包含所有變量在內(nèi)的相關(guān)系數(shù)。它可利用單相關(guān)系數(shù)和偏相關(guān)系數(shù)求得尽棕。其計算公式為:

當(dāng)只有兩個變量時喳挑,復(fù)相關(guān)系數(shù)就等于單相關(guān)系數(shù)。Excel中的相關(guān)系數(shù)工具是單相關(guān)系數(shù)。

15.2 相關(guān)系數(shù)工具的使用

CORREL 和 PEARSON 工作表函數(shù)均可計算兩個測量值變量之間的相關(guān)系數(shù)伊诵,條件是每種變量的測量值都是對 N 個對象進行觀測所得到的单绑。(丟失任何對象的任何觀測值都會導(dǎo)致在分析中忽略該對象。)相關(guān)系數(shù)分析工具特別適合于當(dāng) N 個對象中的每個對象都有兩個以上的測量值變量的情況曹宴。它提供一張輸出表(相關(guān)矩陣)搂橙,其中顯示了應(yīng)用于每個可能的測量值變量對的 CORREL(或 PEARSON)值。

與協(xié)方差一樣笛坦,相關(guān)系數(shù)是描述兩個測量值變量之間的離散程度的指標(biāo)区转。與協(xié)方差的不同之處在于,相關(guān)系數(shù)是成比例的版扩,因此它的值與這兩個測量值變量的表示單位無關(guān)废离。(例如,如果兩個測量值變量為重量和高度礁芦,當(dāng)重量單位從磅換算成千克時蜻韭,相關(guān)系數(shù)的值并不改變。)任何相關(guān)系數(shù)的值都必須介于 -1 和 +1 之間(包括 -1 和 +1)柿扣。

可以使用相關(guān)系數(shù)分析工具來檢驗每對測量值變量肖方,以便確定兩個測量值變量是否趨向于同時變動,即窄刘,一個變量的較大值是否趨向于與另一個變量的較大值相關(guān)聯(lián)(正相關(guān))窥妇;或者一個變量的較小值是否趨向于與另一個變量的較大值相關(guān)聯(lián)(負(fù)相關(guān));或者兩個變量的值趨向于互不關(guān)聯(lián)(相關(guān)系數(shù)近似于零)娩践。

【例】9個小麥品種(分別用A1,A2,...,A9表示)的6個性狀資料見表活翩,作相關(guān)系數(shù)計算。

表 15-1 試驗數(shù)據(jù)

(1)建立一張工作表翻伺,輸入數(shù)據(jù):

圖 15-1 EXCEL數(shù)據(jù)輸入

(2)從“數(shù)據(jù)”選項卡選擇“數(shù)據(jù)分析”材泄,從“數(shù)據(jù)分析”列表框中選擇“相關(guān)系數(shù)”,單擊“確定”彈出相關(guān)系數(shù)對話框吨岭。

圖 15-2 相關(guān)系數(shù)對話框

數(shù)據(jù)區(qū)域可包括變量名稱拉宗,但不包括樣本編號。每個變量的樣本按行排列的選擇“逐行”按列排列的選擇“逐列”辣辫。若包括變量名稱旦事,則選擇“標(biāo)志位于第一列”

(3)單擊“確定”得偏相關(guān)系數(shù)如下表。

圖 15-3 結(jié)果輸出

Excel分析工具中的“相關(guān)系數(shù)”僅計算出相關(guān)系數(shù)的值急灭,并未進行相關(guān)性檢驗姐浮。相關(guān)系數(shù)檢驗可由相關(guān)系數(shù)臨界值來判斷。

相關(guān)系數(shù)為可決系數(shù)的平方根葬馋,可決系數(shù)為回歸平方和與總誤差平方和之比卖鲤,而F統(tǒng)計量為回歸均方和與總均方和之比肾扰,由于可借助F臨界值求得相關(guān)系數(shù)臨界值。即:

本例中n=9,在G9單元格輸入=SQRT(FINV(0.05,1,7)/(FINV(0.05,1,7)+7))蛋逾,在B12輸入=IF(ABS(B4)>$G$9,"相關(guān)性顯著","不顯著")并復(fù)制集晚,得:

圖 15-4 由函數(shù)判斷顯著性

協(xié)方差

16.1 協(xié)方差的概念

在概率論和統(tǒng)計學(xué)中,協(xié)方差用于衡量兩個變量的總體誤差区匣。而方差是協(xié)方差的一種特殊情況偷拔,即當(dāng)兩個變量是相同的情況。兩個實數(shù)隨機變量X與Y之間的協(xié)方差定義為:  COV(X沉颂,Y)=E[(X-E(X))(Y-E(Y))]其中条摸,E是期望值悦污。

16.2 協(xié)方差工具的使用

當(dāng)您對一組個體進行觀測而獲得了 N 個不同的測量值變量時铸屉,“相關(guān)”和“協(xié)方差”工具可在相同設(shè)置下使用∏卸耍“相關(guān)”和“協(xié)方差”工具都會提供一張輸出表(矩陣)彻坛,其中分別顯示每對測量值變量之間的相關(guān)系數(shù)或協(xié)方差。不同之處在于協(xié)方差的取值在 -1 和 +1 之間踏枣,而協(xié)方差沒有限定的取值范圍昌屉。相關(guān)系數(shù)和協(xié)方差都是描述兩個變量離散程度的指標(biāo)。

“協(xié)方差”工具為每對測量值變量計算工作表函數(shù) COVAR 的值茵瀑。(當(dāng)只有兩個測量值變量间驮,即 N=2 時,可直接使用 COVAR马昨,而不要使用“協(xié)方差”工具竞帽。)在“協(xié)方差”工具的輸出表中的第 i 行、第 i 列的對角線上的輸入值是第 i 個測量值變量與其自身的協(xié)方差鸿捧;這正好是用工作表函數(shù) VARP 計算得出的變量的總體方差屹篓。

可以使用“協(xié)方差”工具來檢驗每對測量值變量,以便確定兩個測量值變量是否趨向于同時變動匙奴,即堆巧,一個變量的較大值是否趨向于與另一個變量的較大值相關(guān)聯(lián)(正相關(guān));或者一個變量的較小值是否趨向于與另一個變量的較大值相關(guān)聯(lián)(負(fù)相關(guān))泼菌;或者兩個變量中的值趨向于互不關(guān)聯(lián)(協(xié)方差近似于零)谍肤。

【例】9個小麥品種(分別用A1,A2,...,A9表示)的5個性狀資料見表,作協(xié)方差計算哗伯。

(1)建立一張工作表荒揣,輸入數(shù)據(jù):

圖 16-1 輸入數(shù)據(jù)

(2)從“數(shù)據(jù)”選項卡選擇“數(shù)據(jù)分析”,從“數(shù)據(jù)分析”列表框中選擇“協(xié)方差”笋颤,單擊“確定”彈出協(xié)方差對話框乳附。

圖 16-2 協(xié)方差工具對話框

數(shù)據(jù)區(qū)域可包括變量名稱内地,但不包括樣本編號。每個變量的樣本按行排列的選擇“逐行”按列排列的選擇“逐列”赋除。若包括變量名稱阱缓,則選擇“標(biāo)志位于第一列”

(3)單擊“確定”得協(xié)方差如下表。

圖 16-3 結(jié)果輸出

回歸

17.1 回歸基本公式

對于多元回歸举农,仍可用最小二乘法荆针。

決定系數(shù)與調(diào)整的決定系數(shù):


17.2 回歸工具的使用

“回歸”分析工具通過對一組觀察值使用“最小二乘法”直線擬合來執(zhí)行線性回歸分析。本工具可用來分析單個因變量是如何受一個或幾個自變量的值影響的颁糟。

例:某企業(yè)成本與銷售額如下表(單元:萬元)航背,試進行多元線性回歸。

表 17-1 數(shù)據(jù)資料

(1)打開一張EXCEL表格棱貌,輸入數(shù)據(jù)如下玖媚。

圖 17-1 在EXCEL輸入數(shù)據(jù)

(2)數(shù)據(jù)|分析|數(shù)據(jù)分析|回歸,彈出回歸對話框并設(shè)置如下:

圖 17-2 回歸對話框設(shè)置

(3)單擊“確定”得如下輸出結(jié)果婚脱。

摘要表:

表中Multiple R為復(fù)相關(guān)系數(shù)今魔;R Square為決定系數(shù);Adjusted R Square調(diào)整的決定系數(shù)障贸。

表 17-2 輸出結(jié)果(摘要) SUMMARY OUTPUT

表 17-3 輸入結(jié)果(方差分析表)

參數(shù)值错森、參數(shù)檢驗與置信區(qū)間。

表 17-4 輸出結(jié)果(回歸系數(shù)篮洁、t檢驗及置信區(qū)間

表 17-5 輸出結(jié)果(殘差)RESIDUAL OUTPUT

表中的標(biāo)準(zhǔn)殘差=(殘差-殘差的均值)/殘差的標(biāo)準(zhǔn)差即

圖 17-3 結(jié)果輸出殘差圖

表 17-6 輸入結(jié)果(百分比排位)PROBABILITY OUTPUT


圖 17-4 正態(tài)分布圖


大數(shù)據(jù)訂閱號(ID:BigData07)
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末涩维,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子袁波,更是在濱河造成了極大的恐慌瓦阐,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,681評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件锋叨,死亡現(xiàn)場離奇詭異垄分,居然都是意外死亡,警方通過查閱死者的電腦和手機娃磺,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,205評論 3 399
  • 文/潘曉璐 我一進店門薄湿,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人偷卧,你說我怎么就攤上這事豺瘤。” “怎么了听诸?”我有些...
    開封第一講書人閱讀 169,421評論 0 362
  • 文/不壞的土叔 我叫張陵坐求,是天一觀的道長。 經(jīng)常有香客問我晌梨,道長桥嗤,這世上最難降的妖魔是什么须妻? 我笑而不...
    開封第一講書人閱讀 60,114評論 1 300
  • 正文 為了忘掉前任,我火速辦了婚禮泛领,結(jié)果婚禮上荒吏,老公的妹妹穿的比我還像新娘。我一直安慰自己渊鞋,他們只是感情好绰更,可當(dāng)我...
    茶點故事閱讀 69,116評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著锡宋,像睡著了一般儡湾。 火紅的嫁衣襯著肌膚如雪罢缸。 梳的紋絲不亂的頭發(fā)上粱坤,一...
    開封第一講書人閱讀 52,713評論 1 312
  • 那天途戒,我揣著相機與錄音崎坊,去河邊找鬼。 笑死宵蕉,一個胖子當(dāng)著我的面吹牛享郊,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播宋税,決...
    沈念sama閱讀 41,170評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼讼油!你這毒婦竟也來了杰赛?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 40,116評論 0 277
  • 序言:老撾萬榮一對情侶失蹤矮台,失蹤者是張志新(化名)和其女友劉穎乏屯,沒想到半個月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體瘦赫,經(jīng)...
    沈念sama閱讀 46,651評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡辰晕,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,714評論 3 342
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了确虱。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片含友。...
    茶點故事閱讀 40,865評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖校辩,靈堂內(nèi)的尸體忽然破棺而出窘问,到底是詐尸還是另有隱情,我是刑警寧澤宜咒,帶...
    沈念sama閱讀 36,527評論 5 351
  • 正文 年R本政府宣布惠赫,位于F島的核電站,受9級特大地震影響故黑,放射性物質(zhì)發(fā)生泄漏儿咱。R本人自食惡果不足惜庭砍,卻給世界環(huán)境...
    茶點故事閱讀 42,211評論 3 336
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望混埠。 院中可真熱鬧逗威,春花似錦、人聲如沸岔冀。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,699評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽使套。三九已至罐呼,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間侦高,已是汗流浹背嫉柴。 一陣腳步聲響...
    開封第一講書人閱讀 33,814評論 1 274
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留奉呛,地道東北人计螺。 一個月前我還...
    沈念sama閱讀 49,299評論 3 379
  • 正文 我出身青樓,卻偏偏與公主長得像瞧壮,于是被迫代替她去往敵國和親登馒。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 45,870評論 2 361

推薦閱讀更多精彩內(nèi)容