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

一、什么是回歸分析法

“回歸分析”是解析“注目變量”和“因于變量”并明確兩者關(guān)系的統(tǒng)計(jì)方法。此時(shí)，我們把因子變量稱為“說明變量”，把注目變量稱為“目標(biāo)變量址(被說明變量)”。清楚了回歸分析的目的后，下面我們以回歸分析預(yù)測法的步驟來說明什么是回歸分析法：

　　回歸分析是對(duì)具有因果關(guān)系的影響因素（自變量）和預(yù)測對(duì)象（因變量）所進(jìn)行的數(shù)理統(tǒng)計(jì)分析處理。只有當(dāng)變量與因變量確實(shí)存在某種關(guān)系時(shí)，建立的回歸方程才有意義。因此，作為自變量的因素與作為因變量的預(yù)測對(duì)象是否有關(guān)，相關(guān)程度如何，以及判斷這種相關(guān)程度的把握性多大，就成為進(jìn)行回歸分析必須要解決的問題。進(jìn)行相關(guān)分析，一般要求出相關(guān)關(guān)系，以相關(guān)系數(shù)的大小來判斷自變量和因變量的相關(guān)的程度。 

二、回歸分析的目的 

回歸分析的目的大致可分為兩種：

第一，“預(yù)測”。預(yù)測目標(biāo)變量，求解目標(biāo)變量y和說明變量(x1，x2，…)的方程。

y=a0+b1x1+b2x2+…+bkxk+誤差（方程A）

 把方程A叫做(多元)回歸方程或者(多元)回歸模型。a0是y截距，b1，b2，…，bk是回歸系數(shù)。當(dāng)k=l時(shí)，只有1個(gè)說明變量，叫做一元回歸方程。根據(jù)最小平方法求解最小誤差平方和，非求出y截距和回歸系數(shù)。若求解回歸方程．分別代入x1，x2，…xk的數(shù)值，預(yù)測y的值。

第二，“因子分析”。因子分析是根據(jù)回歸分析結(jié)果，得出各個(gè)自變量對(duì)目標(biāo)變量產(chǎn)生的影響，因此，需要求出各個(gè)自變量的影響程度。 

希望初學(xué)者在閱讀接下來的文章之前，首先學(xué)習(xí)一元回歸分析、相關(guān)分析、多元回歸分析、數(shù)量化理論I等知識(shí)。

根據(jù)最小平方法，使用Excel求解y=a+bx中的a和b。那么什么是最小平方法？

分別從散點(diǎn)圖的各個(gè)數(shù)據(jù)標(biāo)記點(diǎn)，做一條平行于y軸的平行線，相交于圖中直線(如下圖)

平行線的長度在統(tǒng)計(jì)學(xué)中叫做“誤差”或者‘殘差”。誤差(殘差)是指分析結(jié)果的運(yùn)算值和實(shí)際值之間的差。接這，求平行線長度曲平方值?？梢园哑椒街悼醋鲞呴L等于平行線長度的正方形面積(如下圖) 

最后，求解所有正方形面積之和。確定使面積之和最小的a(截距)和b(回歸系數(shù))的值(如下圖)。

使用Excel求解回歸方程；“工具”→“數(shù)據(jù)分析”→“回歸”，具體操作步驟將在后面的文章中具體會(huì)說明。

線性回歸的步驟不論是一元還是多元相同，步驟如下：

• 1、散點(diǎn)圖判斷變量關(guān)系（簡單線性）；




• 2、求相關(guān)系數(shù)及線性驗(yàn)證；




• 3、求回歸系數(shù)，建立回歸方程；




• 4、回歸方程檢驗(yàn)；




• 5、參數(shù)的區(qū)間估計(jì)；




• 6、預(yù)測；

一元線性回歸操作和解釋

摘要

一元線性回歸可以說是數(shù)據(jù)分析中非常簡單的一個(gè)知識(shí)點(diǎn)，有一點(diǎn)點(diǎn)統(tǒng)計(jì)、分析、建模經(jīng)驗(yàn)的人都知道這個(gè)分析的含義，也會(huì)用各種工具來做這個(gè)分析。這里面想把這個(gè)分析背后的細(xì)節(jié)講講清楚，也就是后面的數(shù)學(xué)原理。

什么是一元線性回歸

回歸分析（Regression Analysis)是確定兩種或兩種以上變量間相互依賴的定量關(guān)系的一種統(tǒng)計(jì)分析方法。在回歸分析中，只包括一個(gè)自變量和一個(gè)因變量，且二者的關(guān)系可用一條直線近似表示，這種回歸分析稱為一元線性回歸分析。舉個(gè)例子來說吧：

比方說有一個(gè)公司，每月的廣告費(fèi)用和銷售額，如下表所示：

案例數(shù)據(jù)

如果我們把廣告費(fèi)和銷售額畫在二維坐標(biāo)內(nèi)，就能夠得到一個(gè)散點(diǎn)圖，如果想探索廣告費(fèi)和銷售額的關(guān)系，就可以利用一元線性回歸做出一條擬合直線：

擬合直線

這條線是怎么畫出來的

對(duì)于一元線性回歸來說，可以看成Y的值是隨著X的值變化，每一個(gè)實(shí)際的X都會(huì)有一個(gè)實(shí)際的Y值，我們叫Y實(shí)際，那么我們就是要求出一條直線，每一個(gè)實(shí)際的X都會(huì)有一個(gè)直線預(yù)測的Y值，我們叫做Y預(yù)測，回歸線使得每個(gè)Y的實(shí)際值與預(yù)測值之差的平方和最小，即（Y1實(shí)際-Y1預(yù)測）^2+（Y2實(shí)際-Y2預(yù)測）^2+ …… +（Yn實(shí)際-Yn預(yù)測）^2的和最小（這個(gè)和叫SSE，后面會(huì)具體講）。

現(xiàn)在來實(shí)際求一下這條線：

 

我們都知道直線在坐標(biāo)系可以表示為Y=aX+b，所以（Y實(shí)際-Y預(yù)測）就可以寫成（Y實(shí)際-（aX實(shí)際+b）），于是平方和可以寫成a和b的函數(shù)。只需要求出讓Q最小的a和b的值，那么回歸線的也就求出來了。

  簡單插播一下函數(shù)最小值怎么求：

首先，一元函數(shù)最小值點(diǎn)的導(dǎo)數(shù)為零，比如說Y=X^2，X^2的導(dǎo)數(shù)是2X，令2X=0，求得X=0的時(shí)候，Y取最小值。

那么實(shí)質(zhì)上二元函數(shù)也是一樣可以類推。不妨把二元函數(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è)方法被稱為最小二乘法。下面是具體的數(shù)學(xué)演算過程，不愿意看可以直接看后面的結(jié)論。

先把公式展開一下：

Q函數(shù)表達(dá)式展開

然后利用平均數(shù)，把上面式子中每個(gè)括號(hào)里的內(nèi)容進(jìn)一步化簡。例如

Y^2的平均

則：

上式子兩邊×n

于是

Q最終化簡結(jié)果

然后分別對(duì)Q求a的偏導(dǎo)數(shù)和b的偏導(dǎo)數(shù)，令偏導(dǎo)數(shù)等于0。

Q分別對(duì)a和b求偏導(dǎo)數(shù)，令偏導(dǎo)數(shù)為0

進(jìn)一步化簡，可以消掉2n，最后得到關(guān)于a，b的二元方程組為

關(guān)于a,b的 二元方程組

最后得出a和b的求解公式：

最小二乘法求出直線的斜率a和斜率b

有了這個(gè)公式，對(duì)于廣告費(fèi)和銷售額的那個(gè)例子，我們就可以算出那條擬合直線具體是什么，分別求出公式中的各種平均數(shù)，然后帶入即可，最后算出a=1.98，b=2.25

最終的回歸擬合直線為Y=1.98X+2.25，利用回歸直線可以做一些預(yù)測，比如如果投入廣告費(fèi)2萬，那么預(yù)計(jì)銷售額為6.2萬

評(píng)價(jià)回歸線擬合程度的好壞

我們畫出的擬合直線只是一個(gè)近似，因?yàn)榭隙ê芏嗟狞c(diǎn)都沒有落在直線上，那么我們的直線擬合程度到底怎么樣呢？在統(tǒng)計(jì)學(xué)中有一個(gè)術(shù)語叫做R^2（coefficient ofdetermination，中文叫判定系數(shù)、擬合優(yōu)度，決定系數(shù)，系統(tǒng)不能上標(biāo)，這里是R^2是“R的平方”），用來判斷回歸方程的擬合程度。

首先要明確一下如下幾個(gè)概念：

總偏差平方和（又稱總平方和，SST，Sum of Squaresfor Total）：是每個(gè)因變量的實(shí)際值（給定點(diǎn)的所有Y）與因變量平均值（給定點(diǎn)的所有Y的平均）的差的平方和，即，反映了因變量取值的總體波動(dòng)情況。如下：

SST公式

回歸平方和（SSR，Sum of Squares forRegression）：因變量的回歸值（直線上的Y值）與其均值（給定點(diǎn)的Y值平均）的差的平方和，即，它是由于自變量x的變化引起的y的變化，反映了y的總偏差中由于x與y之間的線性關(guān)系引起的y的變化部分，是可以由回歸直線來解釋的。

SSR公式

殘差平方和（又稱誤差平方和，SSE，Sum of Squaresfor Error）:因變量的各實(shí)際觀測值(給定點(diǎn)的Y值)與回歸值（回歸直線上的Y值）的差的平方和，它是除了x對(duì)y的線性影響之外的其他因素對(duì)y變化的作用，是不能由回歸直線來解釋的。

這些概念還是有些晦澀，我個(gè)人是這么理解的：

就拿廣告費(fèi)和銷售額的例子來說，其實(shí)廣告費(fèi)只是影響銷售額的其中一個(gè)比較重要的因素，可能還有經(jīng)濟(jì)水平、產(chǎn)品質(zhì)量、客戶服務(wù)水平等眾多難以說清的因素在影響最終的銷售額，那么實(shí)際的銷售額就是眾多因素相互作用最終的結(jié)果，由于銷售額是波動(dòng)的，所以用上文提到的每個(gè)月的銷售額與平均銷售額的差的平方和（即總平方和）來表示整體的波動(dòng)情況。

回歸線只表示廣告費(fèi)一個(gè)變量的變化對(duì)于總銷售額的影響，所以必然會(huì)造成偏差，所以才會(huì)有實(shí)際值和回歸值是有差異的，因此回歸線只能解釋一部分影響

那么實(shí)際值與回歸值的差異，就是除了廣告費(fèi)之外其他無數(shù)因素共同作用的結(jié)果，是不能用回歸線來解釋的。

因此SST（總偏差）=SSR（回歸線可以解釋的偏差）+SSE（回歸線不能解釋的偏差）

那么所畫回歸直線的擬合程度的好壞，其實(shí)就是看看這條直線（及X和Y的這個(gè)線性關(guān)系）能夠多大程度上反映（或者說解釋）Y值的變化，定義

R^2=SSR/SST 或 R^2=1-SSE/SST, R^2的取值在0，1之間，越接近1說明擬合程度越好

假如所有的點(diǎn)都在回歸線上，說明SSE為0，則R^2=1，意味著Y的變化100%由X的變化引起，沒有其他因素會(huì)影響Y，回歸線能夠完全解釋Y的變化。如果R^2很低，說明X和Y之間可能不存在線性關(guān)系

還是回到最開始的廣告費(fèi)和銷售額的例子，這個(gè)回歸線的R^2為0.73，說明擬合程度還湊合。

四、相關(guān)系數(shù)R和判定系數(shù)R^2的區(qū)別

判定系數(shù)R^2來判斷回歸方程的擬合程度，表示擬合直線能多大程度上反映Y的波動(dòng)。

在統(tǒng)計(jì)中還有一個(gè)類似的概念，叫做相關(guān)系數(shù)R（這個(gè)沒有平方，學(xué)名是皮爾遜相關(guān)系數(shù)，因?yàn)檫@不是唯一的一個(gè)相關(guān)系數(shù)，而是最常見最常用的一個(gè)），用來表示X和Y作為兩個(gè)隨機(jī)變量的線性相關(guān)程度，取值范圍為【-1，1】。

當(dāng)R=1，說明X和Y完全正相關(guān)，即可以用一條直線，把所有樣本點(diǎn)（x,y）都串起來，且斜率為正，

當(dāng)R=-1，說明完全負(fù)相關(guān)，及可以用一條斜率為負(fù)的直線把所有點(diǎn)串起來。

如果在R=0，則說明X和Y沒有線性關(guān)系，注意，是沒有線性關(guān)系，說不定有其他關(guān)系。

就如同這兩個(gè)概念的符號(hào)表示一樣，在數(shù)學(xué)上可以證明，相關(guān)系數(shù)R的平方就是判定系數(shù)。

變量的顯著性檢驗(yàn)

變量的顯著性檢驗(yàn)的目的：剔除回歸系數(shù)中不顯著的解釋變量（也就是X），使得模型更簡潔。在一元線性模型中，我們只有有一個(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ù)已知的樣本算出來的，這些已知樣本就是一次實(shí)驗(yàn)）肯定是最開始的假設(shè)有問題，所以就可以拒絕最開始的假設(shè)，如果概率不低，那就說明假設(shè)沒問題。

其實(shí)涉及到數(shù)理統(tǒng)計(jì)的內(nèi)容，真的比較難一句話說清楚，我舉個(gè)不恰當(dāng)?shù)睦影桑罕热缬幸粋€(gè)口袋里面裝了黑白兩種顏色的球一共20個(gè)，然后你想知道黑白球數(shù)量是否一致，那么如果用假設(shè)檢驗(yàn)的思路就是這樣做：首先假設(shè)黑白數(shù)量一樣，然后隨機(jī)抽取10個(gè)球，但是發(fā)現(xiàn)10個(gè)都是白的，如果最開始假設(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è)變量，使得模型更簡潔。

F檢驗(yàn)用于對(duì)所有的自變量X在整體上看對(duì)于Y的線性顯著性

T檢驗(yàn)的結(jié)果看P-value，F(xiàn)檢驗(yàn)看Significant F值，一般要小于0.05，越小越顯著（這個(gè)0.05其實(shí)是顯著性水平，是人為設(shè)定的，如果比較嚴(yán)格，可以定成0.01，但是也會(huì)帶來其他一些問題，不細(xì)說了）

下圖是用EXCEL對(duì)廣告費(fèi)和銷售額的例子做的回歸分析的結(jié)果（EXCEL真心是個(gè)很強(qiáng)大的工具，用的出神入化一樣可以變成超神），可以看出F檢驗(yàn)是顯著的（Significance F為0.0017），變量X的T檢驗(yàn)是顯著的（P-value為0.0017），這倆完全一樣也好理解，因?yàn)槲覀兪且辉貧w，只有一個(gè)自變量X。

用Excel做線性回歸分析

還有一點(diǎn)是intercept（截距,也就是Y=aX+b中的那個(gè)b）的T檢驗(yàn)沒有通過，是不顯著的，一般來說，只要F檢驗(yàn)和關(guān)鍵變量的T檢驗(yàn)通過了，模型的預(yù)測能力就是OK的。

最后推薦一個(gè)很好的統(tǒng)計(jì)學(xué)課程

可汗學(xué)院公開課：統(tǒng)計(jì)學(xué)計(jì)劃詳情 - 網(wǎng)易云課堂

這門課是統(tǒng)計(jì)學(xué)入門課程，將涵蓋統(tǒng)計(jì)學(xué)所有的主要知識(shí)，包括：隨機(jī)變量、均值方差標(biāo)準(zhǔn)差、統(tǒng)計(jì)圖表、概率密度、二項(xiàng)分布、泊松分布、正態(tài)分布、大數(shù)定律、中心極限定理、樣本和抽樣分布、參數(shù)估計(jì)、置信區(qū)間、伯努利分布、假設(shè)檢驗(yàn)和p值、方差分析、回歸分析等內(nèi)容。

一共80多條視頻，全長800多分鐘，只能說這是我看過的最好的統(tǒng)計(jì)學(xué)教程，沒有之一，如果本科能看到這個(gè)水平的教程，我也不會(huì)靠考場上的紙條過這門課了。

其實(shí)如果懂一點(diǎn)真正的統(tǒng)計(jì)學(xué)，生活中會(huì)多一些看問題的角度，還是挺有意思的。

一元線性回歸就說到這里，謝謝大家！

轉(zhuǎn)自：http://www.jianshu.com/p/fcd220697182#

使用Excel數(shù)據(jù)分析工具進(jìn)行多元回歸分析

在“數(shù)據(jù)”工具欄中就出現(xiàn)“數(shù)據(jù)分析”工具庫，如下圖所示：

給出原始數(shù)據(jù)，自變量的值在A2：I21單元格區(qū)間中，因變量的值在J2：J21中，如下圖所示：

假設(shè)回歸估算表達(dá)式為：

試使用Excel數(shù)據(jù)分析工具庫中的回歸分析工具對(duì)其回歸系數(shù)進(jìn)行估算并進(jìn)行回歸分析：

點(diǎn)擊“數(shù)據(jù)”工具欄中中的“數(shù)據(jù)分析”工具庫，如下圖所示：

在彈出的“數(shù)據(jù)分析”-“分析工具”多行文本框中選擇“回歸”，然后點(diǎn)擊 “確定”，如下圖所示：

彈出“回歸”對(duì)話框并作如下圖的選擇：

上述選擇的具體方法是：

在“Y值輸入?yún)^(qū)域”，點(diǎn)擊右側(cè)折疊按鈕，選取函數(shù)Y數(shù)據(jù)所在單元格區(qū)域J2：J21，選完后再單擊折疊按鈕返回；這過程也可以直接在“Y值輸入?yún)^(qū)域”文本框中輸入J2：J21；

在“X值輸入?yún)^(qū)域”，點(diǎn)擊右側(cè)折疊按鈕，選取自變量數(shù)據(jù)所在單元格區(qū)域A2：I21，選完后再單擊折疊按鈕返回；這過程也可以直接在“X值輸入?yún)^(qū)域”文本框中輸入A2：I21；

置信度可選默認(rèn)的95%。

在“輸出區(qū)域”如選“新工作表”，就將統(tǒng)計(jì)分析結(jié)果輸出到在新表內(nèi)。為了比較對(duì)照，我選本表內(nèi)的空白區(qū)域，左上角起始單元格為K10.點(diǎn)擊確定后，輸出結(jié)果如下：

第一張表是“回歸統(tǒng)計(jì)表”(K12：L17): 

其中：

Multiple R：（復(fù)相關(guān)系數(shù)R）R²的平方根，又稱相關(guān)系數(shù)，用來衡量自變量x與y之間的相關(guān)程度的大小。本例R=0.9134表明它們之間的關(guān)系為高度正相關(guān)。（Multiple：復(fù)合、多種）

R Square：復(fù)測定系數(shù)，上述復(fù)相關(guān)系數(shù)R的平方。用來說明自變量解釋因變量y變差的程度，以測定因變量y的擬合效果。此案例中的復(fù)測定系數(shù)為0.8343，表明用用自變量可解釋因變量變差的83.43%

Adjusted R Square：調(diào)整后的復(fù)測定系數(shù)R²，該值為0.6852，說明自變量能說明因變量y的68.52%，因變量y的31.48%要由其他因素來解釋。（ Adjusted：調(diào)整后的）

標(biāo)準(zhǔn)誤差：用來衡量擬合程度的大小，也用于計(jì)算與回歸相關(guān)的其它統(tǒng)計(jì)量，此值越小，說明擬合程度越好

觀察值：用于估計(jì)回歸方程的數(shù)據(jù)的觀察值個(gè)數(shù)。

第二張表是“方差分析表”：主要作用是通過F檢驗(yàn)來判定回歸模型的回歸效果。

該案例中的Significance F（F顯著性統(tǒng)計(jì)量）的P值為0.00636，小于顯著性水平0.05，所以說該回歸方程回歸效果顯著，方程中至少有一個(gè)回歸系數(shù)顯著不為0.（Significance：顯著）

第三張表是“回歸參數(shù)表”：

K26：K35為常數(shù)項(xiàng)和b1～b9的排序默認(rèn)標(biāo)示.

L26：L35為常數(shù)項(xiàng)和b1～b9的值，據(jù)此可得出估算的回歸方程為：

該表中重要的是O列,該列的O26：O35中的 P-value為回歸系數(shù)t統(tǒng)計(jì)量的P值。

值得注意的是：其中b1、b7的t統(tǒng)計(jì)量的P值為0.0156和0.0175，遠(yuǎn)小于顯著性水平0.05，因此該兩項(xiàng)的自變量與y相關(guān)。而其他各項(xiàng)的t統(tǒng)計(jì)量的P值遠(yuǎn)大于b1、b7的t統(tǒng)計(jì)量的P值，但如此大的P值說明這些項(xiàng)的自變量與因變量不存在相關(guān)性，因此這些項(xiàng)的回歸系數(shù)不顯著

轉(zhuǎn)載于：http://blog.sina.com.cn/s/blog_a20c88b601014j9x.html