Excel數(shù)據(jù)統(tǒng)計(jì)與分析Excel數(shù)據(jù)統(tǒng)計(jì)與分析第四章分析數(shù)據(jù)間的相關(guān)性

世間萬物總是存在不同程度的聯(lián)系函數(shù)關(guān)系統(tǒng)計(jì)關(guān)系例：正方形體積與邊長，年齡與身高，父母身高與孩子身高，身高與體重，考試成績與學(xué)習(xí)時(shí)間，學(xué)歷與收入，收入與幸福感，物品價(jià)格與質(zhì)量第四章分析數(shù)據(jù)間的相關(guān)性世間萬物總是存在不同程度的聯(lián)系例線性相關(guān)XY正線性相關(guān)負(fù)線性相關(guān)線性相關(guān)XY正線性相關(guān)負(fù)線性相關(guān)XY非線性相關(guān)XY非線性相關(guān)4.2兩數(shù)值型數(shù)據(jù)間的相關(guān)性（1）圖形分析法散點(diǎn)圖是統(tǒng)計(jì)關(guān)系分析中最常用的圖形工具，它將數(shù)據(jù)以點(diǎn)的形式畫在直角平面上，它將一組數(shù)據(jù)作為縱軸，將另一組數(shù)據(jù)作為縱軸，事物對(duì)象的每個(gè)個(gè)體以點(diǎn)的形式出現(xiàn)。4.2兩數(shù)值型數(shù)據(jù)間的相關(guān)性（1）圖形分析法第四章分析數(shù)據(jù)間的相關(guān)性課件（1）若所有點(diǎn)落在一條直線上，說明數(shù)據(jù)間是線性相關(guān)，是函數(shù)關(guān)系，不是統(tǒng)計(jì)關(guān)系。（2）所有點(diǎn)雜亂無章，從形態(tài)上看不出任何特征和規(guī)律，表明數(shù)據(jù)間不相關(guān)（3）圖形點(diǎn)大致呈某種曲線形態(tài)，表明數(shù)據(jù)間存在非線性相關(guān)（4）所有點(diǎn)大致落在一條直線周圍，表明數(shù)據(jù)間有線性相關(guān)性。（1）若所有點(diǎn)落在一條直線上，說明數(shù)據(jù)間是線性相關(guān)，是函數(shù)關(guān)實(shí)踐1999年31個(gè)省市自治區(qū)個(gè)人購買商品住房住宅面積與商品住宅銷售額的數(shù)據(jù)，現(xiàn)要求利用散點(diǎn)圖法分析個(gè)人購買商品住房住宅面積與商品住宅銷售額之間的相關(guān)性。實(shí)踐1999年31個(gè)省市自治區(qū)個(gè)人購買商品住房住宅面積與商品（2）數(shù)值分析法圖形能夠直觀展現(xiàn)數(shù)據(jù)間的相關(guān)關(guān)系，但并不精確。簡(jiǎn)單相關(guān)系數(shù)通過數(shù)字準(zhǔn)確描述數(shù)據(jù)間線性相關(guān)的方向和強(qiáng)弱程度。（2）數(shù)值分析法簡(jiǎn)單相關(guān)系數(shù)簡(jiǎn)單相關(guān)系數(shù)取值范圍[-1,1]

簡(jiǎn)單相關(guān)系數(shù)>0，表示兩個(gè)數(shù)據(jù)正線性相關(guān)簡(jiǎn)單相關(guān)系數(shù)<0，表示兩個(gè)數(shù)據(jù)負(fù)線性相關(guān)簡(jiǎn)單相關(guān)系數(shù)=0，表示兩個(gè)數(shù)據(jù)不存在線性相關(guān)簡(jiǎn)單相關(guān)系數(shù)>0.8，表示兩個(gè)數(shù)據(jù)相關(guān)性很強(qiáng)簡(jiǎn)單相關(guān)系數(shù)<0.3，表示兩個(gè)數(shù)據(jù)相關(guān)性較弱簡(jiǎn)單相關(guān)系數(shù)求簡(jiǎn)單相關(guān)系數(shù)的方法：（1）簡(jiǎn)單相關(guān)系數(shù)可通過函數(shù)Correl或Pearson實(shí)現(xiàn)（2）“數(shù)據(jù)分析”中相關(guān)系數(shù)命令求簡(jiǎn)單相關(guān)系數(shù)的方法：實(shí)踐1999年31個(gè)省市自治區(qū)個(gè)人購買商品住房住宅面積與商品住宅銷售額的數(shù)據(jù)，現(xiàn)要求函數(shù)和“數(shù)據(jù)分析”命令分析個(gè)人購買商品住房住宅面積與商品住宅銷售額之間的相關(guān)性。思考：是否容易受到極端值的影響？

“練習(xí)”sheet中求兩組的相關(guān)性實(shí)踐1999年31個(gè)省市自治區(qū)個(gè)人購買商品住房住宅面積與商品4.3兩品質(zhì)數(shù)據(jù)間的相關(guān)性分析性別跟職稱是否相關(guān)？學(xué)歷與收入是否相關(guān)?（1）圖形分析法復(fù)式柱形圖是柱形圖的擴(kuò)展，主要用于對(duì)事物兩個(gè)或多個(gè)特征的分類對(duì)比。**實(shí)踐4.3兩品質(zhì)數(shù)據(jù)間的相關(guān)性分析性別跟職稱是否相關(guān)？學(xué)歷與收入（2）數(shù)值分析法---列聯(lián)表在該圖基礎(chǔ)上進(jìn)一步計(jì)算一些簡(jiǎn)單的百分比。**分別按行與按列對(duì)上表進(jìn)行分析。（2）數(shù)值分析法---列聯(lián)表實(shí)踐給定的性別與受教育程度數(shù)據(jù)進(jìn)行相關(guān)性分析，做出他們的列聯(lián)表。

請(qǐng)進(jìn)行分析，并得出結(jié)論實(shí)踐給定的性別與受教育程度數(shù)據(jù)進(jìn)行相關(guān)性分析，做出他們的列聯(lián)列聯(lián)表示例獲不獲得學(xué)分與性別是有關(guān)系的，男生獲得學(xué)分的可能更大一些列聯(lián)表示例獲不獲得學(xué)分與性別是有關(guān)系的，男生獲得學(xué)分的可能更列聯(lián)表示例獲不獲得學(xué)分與性別是不相關(guān)的？？？專業(yè)選修課男女合計(jì)獲得51015未獲得153045合計(jì)204060公共選修課男女合計(jì)獲得301040未獲得301040合計(jì)602080列聯(lián)表示例獲不獲得學(xué)分與性別是不相關(guān)的？？？專業(yè)選修課男女合4.4相關(guān)的可靠性檢驗(yàn)總體與樣本常常存在這樣的情況，我們所觀察的只是部分或有限的個(gè)體，而需要判斷的總體對(duì)象范圍卻是大量的，甚至是無限的。比如說為了考察某公司生產(chǎn)的一批電腦芯片的質(zhì)量，需要了解芯片使用壽命這一指標(biāo)。我們關(guān)心的是這一整批芯片的質(zhì)量，但由于各種原因，只能抽取其中的一小部分進(jìn)行測(cè)試。這時(shí)，這一整批芯片的質(zhì)量和被抽取出來的那一部分的質(zhì)量就構(gòu)成了“整體”和“部分”的關(guān)系了。

可靠性研究的就是分析樣本數(shù)據(jù)所體現(xiàn)的相關(guān)或不相關(guān)在總體數(shù)據(jù)間是否依然存在4.4相關(guān)的可靠性檢驗(yàn)總體與樣本可靠性假設(shè)檢驗(yàn)的基本原理假設(shè)檢驗(yàn)的基本原理顯著性水平顯著性水平α是當(dāng)原假設(shè)正確卻被拒絕的概率通常人們?nèi)?.05或0.01這表明，當(dāng)做出接受原假設(shè)的決定時(shí)，其正確的可能性(概率)為95%或99%顯著性水平顯著性水平α是當(dāng)原假設(shè)正確卻被拒絕的概率（1）數(shù)值型數(shù)據(jù)相關(guān)的可靠性檢驗(yàn)數(shù)值型數(shù)據(jù)相關(guān)的可靠性檢驗(yàn)步驟如下：（1）首先提出兩總體是否相關(guān)的假設(shè)，通常假設(shè)是兩總體不相關(guān)。（2）計(jì)算差距。計(jì)算當(dāng)前相關(guān)系數(shù)與所提假設(shè)成立之間的差距。（3）決策。若差距足夠小，則當(dāng)前的假設(shè)成立。若差距較大，說明提出的假設(shè)是站不住腳的。統(tǒng)計(jì)中國，通常的把握程度為0.95，0.90，0.99（1）數(shù)值型數(shù)據(jù)相關(guān)的可靠性檢驗(yàn)數(shù)值型數(shù)據(jù)相關(guān)的可靠性檢驗(yàn)步第四章分析數(shù)據(jù)間的相關(guān)性課件相關(guān)系數(shù)的差距

0.95把握度下的差距

TINV函數(shù)：TINV（probability，degrees_freedom），probability為1-把握程度；degrees_freedom為n-1

相關(guān)系數(shù)的差距0.95把握度下的差距實(shí)踐

個(gè)人購買商品住在面積與商品住宅銷售額之間的相關(guān)性進(jìn)行可靠性檢驗(yàn)。1）求差距2）求95%決策標(biāo)準(zhǔn)3）求99%決策標(biāo)準(zhǔn)4）比較與結(jié)論實(shí)踐個(gè)人購買商品住在面積與商品住宅銷售額之間的相關(guān)性進(jìn)行可卡方分析的主要步驟:（1）首先提出兩總體是否相關(guān)的假設(shè)，通常假設(shè)行變量總體與列變量總體不相關(guān)。（2）計(jì)算差距。計(jì)算目前列聯(lián)表中的頻數(shù)數(shù)據(jù)所呈現(xiàn)的實(shí)際相關(guān)性與行列變量總體不相關(guān)之間的差距有多少（3）決策。若差距足夠小，則當(dāng)前的假設(shè)成立。若差距較大，說明提出的假設(shè)是站不住腳的。統(tǒng)計(jì)中，通常的把握程度為0.95，0.90，0.99（2）品質(zhì)型數(shù)據(jù)相關(guān)的可靠性檢驗(yàn)卡方分析的主要步驟:（2）品質(zhì)型數(shù)據(jù)相關(guān)的可靠性檢驗(yàn)計(jì)算差距

為列聯(lián)表每個(gè)單元格中的實(shí)際頻數(shù)為列聯(lián)表每個(gè)單元格中的期望頻數(shù)計(jì)算差距為列聯(lián)表每個(gè)單元格中的實(shí)際頻數(shù)為列聯(lián)表每個(gè)單元格中的實(shí)踐對(duì)性別與受高等教育程度之間的相關(guān)性進(jìn)行可靠性檢驗(yàn)。方法一：利用卡方分析1）計(jì)算2）計(jì)算3）計(jì)算CHIINV4）比較及結(jié)論實(shí)踐對(duì)性別與受高等教育程度之間的相關(guān)性進(jìn)行可靠性檢驗(yàn)。方法二：利用Chitest函數(shù)（1）假設(shè)性別與高等教育程度無關(guān)，得到期望頻數(shù)區(qū)域。（2）利用Chitest函數(shù)，求得chitest(實(shí)際頻數(shù),期望頻數(shù))的值，該值代表假設(shè)成立的概率。（3）若該概率為小概率事件，則認(rèn)為假設(shè)不成立，反之，則成立。方法二：利用Chitest函數(shù)實(shí)踐實(shí)驗(yàn)內(nèi)容（一）隨機(jī)抽取由10名大學(xué)生組成的樣本，研究他們?cè)诟咧信c大學(xué)的英語成績得出下表結(jié)果：（單位：分）

試測(cè)定其相關(guān)程度。

實(shí)踐實(shí)驗(yàn)內(nèi)容試測(cè)定其相關(guān)程度。（二）下面是幾家百貨商店銷售額和利潤率的資料：（二）下面是幾家百貨商店銷售額和利潤率的資料：1．以縱軸表示利潤率，橫軸表示每人月平均銷售額，畫出散點(diǎn)圖，觀察并說明兩變量之間存在何種關(guān)系。2．計(jì)算每人月平均銷售額與利潤率之間的相關(guān)系數(shù)，并說明其相關(guān)的密切程度。3.檢驗(yàn)每人月平均銷售額與利潤率之間的相關(guān)程度，顯著性水平取0.011．以縱軸表示利潤率，橫軸表示每人月平均銷售額，畫出散點(diǎn)圖，4.5數(shù)據(jù)的線性回歸分析回歸效應(yīng)

1887年生物統(tǒng)計(jì)學(xué)家高爾頓在研究豌豆和人體的身高遺傳規(guī)律時(shí)，首先提出“回歸”的思想。1888年他又引入“相關(guān)”（Correlation）的概念。原來，他在研究人類身高的遺傳時(shí)發(fā)現(xiàn)，不管祖先的身高是高還是低，成年后代的身高總有向一般人口的平均身高回歸的傾向。高爾頓由此的出結(jié)論,人的生理結(jié)構(gòu)是穩(wěn)定的,所有有機(jī)組織都趨于標(biāo)準(zhǔn)狀態(tài),這種效應(yīng)叫回歸效應(yīng)4.5數(shù)據(jù)的線性回歸分析回歸效應(yīng)人均收入是否會(huì)顯著影響人均食品消費(fèi)支出；貸款余額是否會(huì)影響到不良貸款；航班正點(diǎn)率是否對(duì)顧客投訴次數(shù)有顯著影響；廣告費(fèi)用支出是否對(duì)銷售額有顯著影響；一元回歸的例子人均收入是否會(huì)顯著影響人均食品消費(fèi)支出；一元回歸的例子回歸分析的概念因變量與自變量之間的關(guān)系用一條線性方程來表示；因變量(dependentvariable)：被預(yù)測(cè)或被解釋的變量，用y表示。自變量(independentvariable)：預(yù)測(cè)或解釋因變量的一個(gè)或多個(gè)變量，用x表示。涉及一個(gè)自變量的回歸為一元回歸分析回歸分析的概念回歸分析的步驟回歸分析的步驟實(shí)例分析為便于控制某產(chǎn)品的生產(chǎn)成本，需要研究該產(chǎn)品產(chǎn)量與生產(chǎn)成本之間的數(shù)量變化關(guān)系，并預(yù)算當(dāng)產(chǎn)量達(dá)到一定水平時(shí)的成本是多少。實(shí)例分析為便于控制某產(chǎn)品的生產(chǎn)成本，需要研究該產(chǎn)品產(chǎn)量與生產(chǎn)第一：確定自變量和因變量第一：確定自變量和因變量第二：確定回歸方程

根據(jù)收集的數(shù)據(jù)確定自變量與因變量之間的數(shù)學(xué)關(guān)系式，一元線性回歸中估計(jì)的回歸方程為：其中：是估計(jì)的回歸直線在y

軸上的截距，是直線的斜率，它表示對(duì)于一個(gè)給定的x

的值，是y

的估計(jì)值，也表示x

每變動(dòng)一個(gè)單位時(shí)，y的平均變動(dòng)值。第二：確定回歸方程根據(jù)收集的數(shù)據(jù)確定自變量使因變量的觀察值與估計(jì)值之間的離差平方和達(dá)到最小來求得和的方法。即用最小二乘法擬合的直線來代表x與y之間的關(guān)系與實(shí)際數(shù)據(jù)的誤差比其他任何直線都小使因變量的觀察值與估計(jì)值之間的離差平方和達(dá)到最小來求得xy(xn,yn)(x1,y1)(x2,y2)(xi,yi)｝ei=yi-yi＾xy(xn,yn)(x1,y1)(

根據(jù)最小二乘法的要求，可得求解和的公式如下根據(jù)最小二乘法的要求，可得求解和的公式如下

在Excel中，用Intercept（known_y’s，known_x’s）函數(shù)用來計(jì)算回歸方程的截距用Slope（known_y’s,known_x’s）函數(shù)來計(jì)算回歸方程的回歸系數(shù)在Excel中，用Intercept（k因此，得到該數(shù)據(jù)的一元線性回歸方程為：y=124.15+0.42x因此，得到該數(shù)據(jù)的一元線性回歸方程為：y=124.15+0.在散點(diǎn)圖中，選中數(shù)據(jù)點(diǎn)，單擊右鍵，選擇“添加趨勢(shì)線”-“線性”，并在選項(xiàng)標(biāo)簽中要求給出公式和相關(guān)系數(shù)等，可以得到擬合的直線，如下圖所示。在散點(diǎn)圖中，選中數(shù)據(jù)點(diǎn)，單擊右鍵，選擇“添加趨勢(shì)線”-“線性第三：對(duì)線性回歸方程的統(tǒng)計(jì)檢驗(yàn)對(duì)回歸方程檢驗(yàn)的內(nèi)容第三：對(duì)線性回歸方程的統(tǒng)計(jì)檢驗(yàn)對(duì)回歸方程檢驗(yàn)的內(nèi)容（1）回歸方程的擬合優(yōu)度檢驗(yàn)回歸方程的擬合優(yōu)度檢驗(yàn)就是要檢驗(yàn)樣本數(shù)據(jù)點(diǎn)聚集在回歸直線周圍的密集程度，從而評(píng)價(jià)回歸方程對(duì)樣本數(shù)據(jù)的代表程度。（1）回歸方程的擬合優(yōu)度檢驗(yàn)1.判定系數(shù)因變量的各個(gè)觀測(cè)值之間的差異主要由兩個(gè)方面的原因造成的，一是由自變量的不同值造成的，二是其他因素造成的。1.判定系數(shù)某一個(gè)觀測(cè)值的變差的分解xyy{}}某一個(gè)觀測(cè)值的變差的分解xyy{}}總平方和(SST)反映因變量的n個(gè)觀察值與其均值的總離差；回歸平方和(SSR)反映自變量x的變化對(duì)因變量y取值變化的影響，是由于x與y之間的線性關(guān)系引起的y的取值變化，也稱為可解釋的平方和；殘差平方和(SSE)反映除x以外的其他因素對(duì)y取值的影響，也稱為不可解釋的平方和或剩余平方和；離差平方和的分解總平方和(SST)離差平方和的分解SST=SSR+SSE總平方和(SST){回歸平方和(SSR)殘差平方和(SSE){{SST=SSR+SSE總平方和{回歸平方和殘差平方和回歸平方和占總離差平方和的比例反映回歸方程的擬合程度；取值范圍在[0,1]之間；

R21，說明回歸方程擬合的越好；R20，說明回歸方程擬合的越差一元線性回歸中，判定系數(shù)等于y和x相關(guān)系數(shù)的平方，即R2＝(r)2；回歸平方和占總離差平方和的比例反映回歸方程的擬合程度；在Excel中，判定系數(shù)可用Rsq(known_y’s,known_x’s)函數(shù)來計(jì)算。

判定系數(shù)=0.85，說明樣本數(shù)據(jù)點(diǎn)緊密的聚集在回歸直線周圍，該回歸方程對(duì)數(shù)據(jù)的擬合度較好。在Excel中，判定系數(shù)可用Rsq(known_y’s,kn（2）回歸方程的顯著性檢驗(yàn)回歸方程的顯著性檢驗(yàn)是檢驗(yàn)因變量與自變量之間的線性關(guān)系是否顯著，是否可以用線性模型來描述因變量與自變量之間的關(guān)系。（2）回歸方程的顯著性檢驗(yàn)線性關(guān)系的檢驗(yàn)步驟提出假設(shè)H0：1=0所有回歸系數(shù)與零無顯著差異，y與全體x的線性關(guān)系不顯著計(jì)算檢驗(yàn)統(tǒng)計(jì)量F確定顯著性水平，并根據(jù)分子自由度1和分母自由度n-2找出臨界值F作出決策：若F>F,拒絕H0；若F<F,不能拒絕H0線性關(guān)系的檢驗(yàn)步驟提出假設(shè)計(jì)算檢驗(yàn)統(tǒng)計(jì)量F確定顯著性水平，F(xiàn)統(tǒng)計(jì)量計(jì)算復(fù)雜，可直接借助“數(shù)據(jù)分析”命令完成**利用“回歸”功能完成分析F統(tǒng)計(jì)量計(jì)算復(fù)雜，可直接借助“數(shù)據(jù)分析”命令完成第四:利用線性回歸方程進(jìn)行預(yù)測(cè)回歸方程中描述了當(dāng)自