用EXCEL做回歸分析的詳細(xì)步驟

、什么是回歸分析法''回歸分析”是解析''注目變量”和''因于變量”并明確兩者關(guān)系的統(tǒng)計(jì)方法。此時(shí)，我們把因子變量稱(chēng)為''說(shuō)明變量”，把注目變量稱(chēng)為、'目標(biāo)變量址（被說(shuō)明變量）"。清楚了回歸分析的目的后，下面我們以回歸分析預(yù)測(cè)法的步驟來(lái)說(shuō)明什么是回歸分析法：回歸分析是對(duì)具有因果關(guān)系的影響因素（自變量）和預(yù)測(cè)對(duì)象（因變量）所進(jìn)行的數(shù)理統(tǒng)計(jì)分析處理。只有當(dāng)變量與因變量確實(shí)存在某種關(guān)系時(shí)，建立的回歸方程才有意義。因此，作為自變量的因素與作為因變量的預(yù)測(cè)對(duì)象是否有關(guān)，相關(guān)程度如何，以及判斷這種相關(guān)程度的把握性多大，就成為進(jìn)行回歸分析必須要解決的問(wèn)題。進(jìn)行相關(guān)分析，一般要求出相關(guān)關(guān)系，以相關(guān)系數(shù)的大小來(lái)判斷自變量和因變量的相關(guān)的程度。二、回歸分析的目的回歸分析的目的大致可分為兩種：第一，“預(yù)測(cè)"。預(yù)測(cè)目標(biāo)變量，求解目標(biāo)變量y和說(shuō)明變量（x1，x2,…）的方程。y=a0+b1x1+b2x2+...+bkxk+誤差（方程A）把方程a叫做（多元）回歸方程或者（多元）回歸模型。a0是y截距，bl，b2,…，bk是回歸系數(shù)。當(dāng)k=l時(shí)，只有1個(gè)說(shuō)明變量，叫做一元回歸方程。根據(jù)最小平方法求解最小誤差平方和，非求出y截距和回歸系數(shù)。若求解回歸方程.分別代入xl，x2,.xk的數(shù)值，預(yù)測(cè)y的值。第二，“因子分析”。因子分析是根據(jù)回歸分析結(jié)果，得出各個(gè)自變量對(duì)目標(biāo)變量產(chǎn)生的影響，因此，需要求出各個(gè)自變量的影響程度。希望初學(xué)者在閱讀接下來(lái)的文章之前，首先學(xué)習(xí)一元回歸分析、相關(guān)分析、多元回歸分析、數(shù)量化理論I等知識(shí)。根據(jù)最小平方法，使用Excel求解y=a+bx中的a和b。那么什么是最小平方法？分別從散點(diǎn)圖的各個(gè)數(shù)據(jù)標(biāo)記點(diǎn)，做一條平行于y軸的平行線，相交于圖中直線（如下圖）0 5JOH20 25平行線的長(zhǎng)度在統(tǒng)計(jì)學(xué)中叫做''誤差〃或者'殘差〃。誤差（殘差）是指分析結(jié)果的運(yùn)算值和實(shí)際值之間的差。接這，求平行線長(zhǎng)度曲平方值?？梢园哑椒街悼醋鲞呴L(zhǎng)等于平行線長(zhǎng)度的正方形面積（如下圖）

最后，求解所有正方形面積之和。確定使面積之和最小的a（最后，求解所有正方形面積之和。確定使面積之和最小的a（截距）和b（回歸系數(shù)）的值（如下圖）。使用Excel求解回歸方程；''工具''一''數(shù)據(jù)分析”一''回歸”，具體操作步驟將在后面的文章中具體會(huì)說(shuō)明。線性回歸的步驟不論是一元還是多元相同，步驟如下：1、散點(diǎn)圖判斷變量關(guān)系（簡(jiǎn)單線性）；2、求相關(guān)系數(shù)及線性驗(yàn)證；3、求回歸系數(shù)，建立回歸方程；4、回歸方程檢驗(yàn)；5、參數(shù)的區(qū)間估計(jì)；6、預(yù)測(cè)；一元線性回歸操作和解釋摘要一元線性回歸可以說(shuō)是數(shù)據(jù)分析中非常簡(jiǎn)單的一個(gè)知識(shí)點(diǎn)，有一點(diǎn)點(diǎn)統(tǒng)計(jì)、分析、建模經(jīng)驗(yàn)的人都知道這個(gè)分析的含義，也會(huì)用各種工具來(lái)做這個(gè)分析。這里面想把這個(gè)分析背后的細(xì)節(jié)講講清楚，也就是后面的數(shù)學(xué)原理。什么是一元線性回歸回歸分析（RegressionAnalysis）是確定兩種或兩種以上變量間相互依賴(lài)的定量關(guān)系的一種統(tǒng)計(jì)分析方法。在回歸分析中，只包括一個(gè)自變量和一個(gè)因變量，且二者的關(guān)系可用一條直線近似表示，這種回歸分析稱(chēng)為一元線性回歸分析。舉個(gè)例子來(lái)說(shuō)吧：比方說(shuō)有一個(gè)公司，每月的廣告費(fèi)用和銷(xiāo)售額，如下表所示：廣告費(fèi)〔萬(wàn)489&7128W69銷(xiāo)售歇〔萬(wàn)）920221517231810案例數(shù)據(jù)如果我們把廣告費(fèi)和銷(xiāo)售額畫(huà)在二維坐標(biāo)內(nèi)，就能夠得到一個(gè)散點(diǎn)圖，如果想探索廣告費(fèi)和銷(xiāo)售額的關(guān)系，就可以利用一元線性回歸做出一條擬合直線：擬合直線這條線是怎么畫(huà)出來(lái)的對(duì)于一元線性回歸來(lái)說(shuō)，可以看成Y的值是隨著X的值變化，每一個(gè)實(shí)際的X都會(huì)有一個(gè)實(shí)際的Y值，我們叫Y實(shí)際，那么我們就是要求出一條直線，每一個(gè)實(shí)際的X都會(huì)有一個(gè)直線預(yù)測(cè)的Y值，我們叫做Y預(yù)測(cè)，回歸線使得每個(gè)Y的實(shí)際值與預(yù)測(cè)值之差的平方和最小，即（Y1實(shí)際-Y1預(yù)測(cè)）八2+（Y2實(shí)際-Y2預(yù)測(cè)廣2+……+（Yn實(shí)際-Yn預(yù)測(cè)）八2的和最?。ㄟ@個(gè)和叫SSE，后面會(huì)具體講）?，F(xiàn)在來(lái)實(shí)際求一下這條線：我們都知道直線在坐標(biāo)系可以表示為Y=aX+b，所以（Y實(shí)際-Y預(yù)測(cè)）就可以寫(xiě)成（Y實(shí)際-（aX實(shí)際+b）），于是平方和可以寫(xiě)成a和b的函數(shù)。只需要求出讓Q最小的a和b的值，那么回歸線的也就求出來(lái)了。簡(jiǎn)單插播一下函數(shù)最小值怎么求：首先，一元函數(shù)最小值點(diǎn)的導(dǎo)數(shù)為零，比如說(shuō)Y=X^2,X^2的導(dǎo)數(shù)是2X，令2X=0，求得X=0的時(shí)候，Y取最小值。那么實(shí)質(zhì)上二元函數(shù)也是一樣可以類(lèi)推。不妨把二元函數(shù)圖象設(shè)想成一個(gè)曲面，最小值想象成一個(gè)凹陷，那么在這個(gè)凹陷底部，從任意方向上看，偏導(dǎo)數(shù)都是0。因此，對(duì)于函數(shù)Q，分別對(duì)于a和b求偏導(dǎo)數(shù)，然后令偏導(dǎo)數(shù)等于0，就可以得到一個(gè)關(guān)于a和b的二元方程組，就可以求出a和b了。這個(gè)方法被稱(chēng)為最小二乘法。下面是具體的數(shù)學(xué)演算過(guò)程，不愿意看可以直接看后面的結(jié)論。先把公式展開(kāi)一下：qi乳g=-咨 =黑-（忒._+■6））1+5）r-+un十=[J；-21^（it；-ft）4- -i?）:]+[ + +&）']*+R；2 -*）+（dA；+5）3]=if-2Y1aX[-2Ylb+aiXl:+laX^-b*-孔：--21^+a'xj-ZaX.b+b2=（I；3+...4-J；5）- +... -2^+.4 +a3（JCj1+..+X11^+2aiKJt|+-+JTJ+ni?JQ函數(shù)表達(dá)式展開(kāi)然后利用平均數(shù)，把上面式子中每個(gè)括號(hào)里的內(nèi)容進(jìn)一步化簡(jiǎn)。例如YE的平均則：0「+…+孔。=履1上式子兩邊x〃于是Q（a;^）= -lanXT-IbrrY- -UbnX-浴Q最終化簡(jiǎn)結(jié)果然后分別對(duì)Q求a的偏導(dǎo)數(shù)和b的偏導(dǎo)數(shù)，令偏導(dǎo)數(shù)等于0。些二-2nXf-S蕓-IbuX=08a壘二-誘-亳依-W8bQ分別對(duì)a和b求偏導(dǎo)數(shù)，令偏導(dǎo)數(shù)為0進(jìn)一步化簡(jiǎn)，可以消掉2n，最后得到關(guān)于a，b的二元方程組為-bX=0-Y-aX-b=0關(guān)于a,b的二元方程組最后得出a和b的求解公式：XT-XYa= （xy-xzb=Y-aX最小二乘法求出直線的斜率a和斜率b有了這個(gè)公式，對(duì)于廣告費(fèi)和銷(xiāo)售額的那個(gè)例子，我們就可以算出那條擬合直線具體是什么，分別求出公式中的各種平均數(shù)，然后帶入即可，最后算出a=1.98,b=2.25最終的回歸擬合直線為Y=1.98X+2.25,利用回歸直線可以做一些預(yù)測(cè)，比如如果投入廣告費(fèi)2萬(wàn)，那么預(yù)計(jì)銷(xiāo)售額為6.2萬(wàn)評(píng)價(jià)回歸線擬合程度的好壞我們畫(huà)出的擬合直線只是一個(gè)近似，因?yàn)榭隙ê芏嗟狞c(diǎn)都沒(méi)有落在直線上，那么我們的直線擬合程度到底怎么樣呢？在統(tǒng)計(jì)學(xué)中有一個(gè)術(shù)語(yǔ)叫做日八2（coefficientofdetermination，中文叫判定系數(shù)、擬合優(yōu)度，決定系數(shù)，系統(tǒng)不能上標(biāo)，這里是日八2是“R的平方”），用來(lái)判斷回歸方程的擬合程度。首先要明確一下如下幾個(gè)概念：總偏差平方和（又稱(chēng)總平方和，SST，SumofSquaresforTotal）:是每個(gè)因變量的實(shí)際值（給定點(diǎn)的所有Y）與因變量平均值（給定點(diǎn)的所有Y的平均）的差的平方和，即，反映了因變量取值的總體波動(dòng)情況。如下：5S?=尤，餌-廳=〔亳-廳-皿-ry-仇-廳SST公式回歸平方和（SSR，SumofSquaresforRegression）:因變量的回歸值（直線上的Y值）與其均值（給定點(diǎn)的Y值平均）的差的平方和，即，它是由于自變量x的變化引起的y的變化，反映了y的總偏差中由于x與y之間的線性關(guān)系引起的y的變化部分，是可以由回歸直線來(lái)解釋的。5SR=〔立-亍尸=由-廳+出-廳+…+Q：-月'SSR公式殘差平方和（又稱(chēng)誤差平方和，SSE，SumofSquaresforError）:因變量的各實(shí)際觀測(cè)值（給定點(diǎn)的Y值）與回歸值（回歸直線上的Y值）的差的平方和，它是除了x對(duì)y的線性影響之外的其他因素對(duì)y變化的作用，是不能由回歸直線來(lái)解釋的。這些概念還是有些晦澀，我個(gè)人是這么理解的:就拿廣告費(fèi)和銷(xiāo)售額的例子來(lái)說(shuō)，其實(shí)廣告費(fèi)只是影響銷(xiāo)售額的其中一個(gè)比較重要的因素，可能還有經(jīng)濟(jì)水平、產(chǎn)品質(zhì)量、客戶服務(wù)水平等眾多難以說(shuō)清的因素在影響最終的銷(xiāo)售額，那么實(shí)際的銷(xiāo)售額就是眾多因素相互作用最終的結(jié)果，由于銷(xiāo)售額是波動(dòng)的，所以用上文提到的每個(gè)月的銷(xiāo)售額與平均銷(xiāo)售額的差的平方和（即總平方和）來(lái)表示整體的波動(dòng)情況?；貧w線只表示廣告費(fèi)一個(gè)變量的變化對(duì)于總銷(xiāo)售額的影響，所以必然會(huì)造成偏差，所以才會(huì)有實(shí)際值和回歸值是有差異的，因此回歸線只能解釋一部分影響那么實(shí)際值與回歸值的差異，就是除了廣告費(fèi)之外其他無(wú)數(shù)因素共同作用的結(jié)果，是不能用回歸線來(lái)解釋的。因此SST（總偏差）=SSR（回歸線可以解釋的偏差）+SSE（回歸線不能解釋的偏差）那么所畫(huà)回歸直線的擬合程度的好壞，其實(shí)就是看看這條直線（及X和Y的這個(gè)線性關(guān)系）能夠多大程度上反映（或者說(shuō)解釋?zhuān)℡值的變化，定義R^2=SSR/SST或R^2=1-SSE/SST,R^2的取值在0，1之間，越接近1說(shuō)明擬合程度越好假如所有的點(diǎn)都在回歸線上，說(shuō)明SSE為0，則日八2=1，意味著Y的變化100%由X的變化引起，沒(méi)有其他因素會(huì)影響Y，回歸線能夠完全解釋Y的變化。如果RA2很低，說(shuō)明X和Y之間可能不存在線性關(guān)系還是回到最開(kāi)始的廣告費(fèi)和銷(xiāo)售額的例子，這個(gè)回歸線的日八2為0.73,說(shuō)明擬合程度還湊合。四、相關(guān)系數(shù)R和判定系數(shù)日八2的區(qū)別判定系數(shù)日八2來(lái)判斷回歸方程的擬合程度，表示擬合直線能多大程度上反映Y的波動(dòng)。在統(tǒng)計(jì)中還有一個(gè)類(lèi)似的概念，叫做相關(guān)系數(shù)R（這個(gè)沒(méi)有平方，學(xué)名是皮爾遜相關(guān)系數(shù)，因?yàn)檫@不是唯一的一個(gè)相關(guān)系數(shù)，而是最常見(jiàn)最常用的一個(gè)），用來(lái)表示X和Y作為兩個(gè)隨機(jī)變量的線性相關(guān)程度，取值范圍為【-1，1】。當(dāng)R=1，說(shuō)明X和Y完全正相關(guān)，即可以用一條直線，把所有樣本點(diǎn)（x,y）都串起來(lái)，且斜率為正，當(dāng)R=-1，說(shuō)明完全負(fù)相關(guān)，及可以用一條斜率為負(fù)的直線把所有點(diǎn)串起來(lái)。如果在R=0，則說(shuō)明X和Y沒(méi)有線性關(guān)系，注意，是沒(méi)有線性關(guān)系，說(shuō)不定有其他關(guān)系。就如同這兩個(gè)概念的符號(hào)表示一樣，在數(shù)學(xué)上可以證明，相關(guān)系數(shù)R的平方就是判定系數(shù)。變量的顯著性檢驗(yàn)變量的顯著性檢驗(yàn)的目的：剔除回歸系數(shù)中不顯著的解釋變量（也就是X），使得模型更簡(jiǎn)潔。在一元線性模型中，我們只有有一個(gè)自變量X，就是要判斷X對(duì)Y是否有顯著性的影響；多元線性回歸中，驗(yàn)證每個(gè)Xi自身是否真的對(duì)Y有顯著的影響，不顯著的就應(yīng)該從模型去掉。變量的顯著性檢驗(yàn)的思想：用的是純數(shù)理統(tǒng)計(jì)中的假設(shè)檢驗(yàn)的思想。對(duì)Xi參數(shù)的實(shí)際值做一個(gè)假設(shè)，然后在這個(gè)假設(shè)成立的情況下，利用巳知的樣本信息構(gòu)造一個(gè)符合一定分布的（如正態(tài)分布、T分布和F分布）的統(tǒng)計(jì)量，然后從理論上計(jì)算得到這個(gè)統(tǒng)計(jì)量的概率，如果概率很低（5%以下），根據(jù)''小概率事件在一次實(shí)驗(yàn)中不可能發(fā)生”的統(tǒng)計(jì)學(xué)基本原理，現(xiàn)在居然發(fā)生了?。ㄒ?yàn)槲覀兊慕y(tǒng)計(jì)量就是根據(jù)巳知的樣本算出來(lái)的，這些巳知樣本就是一次實(shí)驗(yàn)）肯定是最開(kāi)始的假設(shè)有問(wèn)題，所以就可以拒絕最開(kāi)始的假設(shè)，如果概率不低，那就說(shuō)明假設(shè)沒(méi)問(wèn)題。其實(shí)涉及到數(shù)理統(tǒng)計(jì)的內(nèi)容，真的比較難一句話說(shuō)清楚，我舉個(gè)不恰當(dāng)?shù)睦影桑罕热缬幸粋€(gè)口袋里面裝了黑白兩種顏色的球一共20個(gè)，然后你想知道黑白球數(shù)量是否一致，那么如果用假設(shè)檢驗(yàn)的思路就是這樣做：首先假設(shè)黑白數(shù)量一樣，然后隨機(jī)抽取10個(gè)球，但是發(fā)現(xiàn)10個(gè)都是白的，如果最開(kāi)始假設(shè)黑白數(shù)量一樣是正確的，那么一下抽到10個(gè)白的的概率是很小的，但是這么小概率的事情居然發(fā)生了，所以我們有理由相信假設(shè)錯(cuò)誤，黑白的數(shù)量應(yīng)該是不一樣的……總之，對(duì)于所有的回歸模型的軟件，最終給出的結(jié)果都會(huì)有參數(shù)的顯著性檢驗(yàn)，忽略掉難懂的數(shù)學(xué)，我們只需要理解如下幾個(gè)結(jié)論：T檢驗(yàn)用于對(duì)某一個(gè)自變量Xi對(duì)于Y的線性顯著性，如果某一個(gè)Xi不顯著，意味著可以從模型中剔除這個(gè)變量，使得模型更簡(jiǎn)潔。F檢驗(yàn)用于對(duì)所有的自變量X在整體上看對(duì)于Y的線性顯著性T檢驗(yàn)的結(jié)果看P-value，F(xiàn)檢驗(yàn)看SignificantF值，一般要小于0.05,越小越顯著（這個(gè)0.05其實(shí)是顯著性水平，是人為設(shè)定的，如果比較嚴(yán)格，可以定成0.01，但是也會(huì)帶來(lái)其他一些問(wèn)題，不細(xì)說(shuō)了）下圖是用EXCEL對(duì)廣告費(fèi)和銷(xiāo)售額的例子做的回歸分析的結(jié)果（EXCEL真心是個(gè)很強(qiáng)大的工具，用的出神入化一樣可以變成超神），可以看出F檢驗(yàn)是顯著的（SignificanceF為0.0017），變量X的T檢驗(yàn)是顯著的（P-value為0.00