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

一、什么是回歸分析法

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

明確預(yù)測(cè)的具體目標(biāo)，也就確定了因變量。如預(yù)測(cè)具體目標(biāo)是下一年度的銷售量，那么銷售量Y就是因變量。通過(guò)市場(chǎng)調(diào)查和查閱資料，尋找與預(yù)測(cè)目標(biāo)的相關(guān)影響因素，即自變量，并從中選出主要的影響因素。

2．建立回歸預(yù)測(cè)模型

依據(jù)自變量和因變量的歷史統(tǒng)計(jì)資料進(jìn)行計(jì)算，在此基礎(chǔ)上建立回歸分析方程，即回歸分析預(yù)測(cè)模型。

3．進(jìn)行相關(guān)分析

什么是回歸分析法回歸分析是對(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)的程度。

4．檢驗(yàn)回歸預(yù)測(cè)模型，計(jì)算預(yù)測(cè)誤差

回歸預(yù)測(cè)模型是否可用于實(shí)際預(yù)測(cè)，取決于對(duì)回歸預(yù)測(cè)模型的檢驗(yàn)和對(duì)預(yù)測(cè)誤差的計(jì)算?；貧w方程只有通過(guò)各種檢驗(yàn)，且預(yù)測(cè)誤差較小，才能將回歸方程作為預(yù)測(cè)模型進(jìn)行預(yù)測(cè)。

5．計(jì)算并確定預(yù)測(cè)值

利用回歸預(yù)測(cè)模型計(jì)算預(yù)測(cè)值，并對(duì)預(yù)測(cè)值進(jìn)行綜合分析，確定最后的預(yù)測(cè)值。

二、回歸分析的目的

回歸分析的目的大致可分為兩種：第一，“預(yù)測(cè)”。預(yù)測(cè)目標(biāo)變量，求解目標(biāo)變量y和說(shuō)明變量(x1，x2，…)的方程。

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

把方程A叫做(多元)回歸方程或者(多元)回歸模型。a0是y截距，b1，b2，…，bk是回歸系數(shù)。當(dāng)k=l時(shí)，只有1個(gè)說(shuō)明變量，叫做一元回歸方程。根據(jù)最小平方法求解最小誤差平方和，非求出y截距和回歸系數(shù)。若求解回歸方程．分別代入x1，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軸的平行線，相交于圖中直線(如下圖)

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

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

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

三、回歸分析案例

接著上節(jié)的回歸分析的目的，我們來(lái)根據(jù)一個(gè)二手車的實(shí)例來(lái)繼續(xù)說(shuō)回歸分析。二手車價(jià)格的決定因素有：空調(diào)類型有無(wú)附加品(TV導(dǎo)航導(dǎo)航SR、天窗、空氣囊LD TV AW)  行駛距離，顏色車檢剩余有效月數(shù)、評(píng)分拍賣會(huì)地點(diǎn)等。

在這14個(gè)因子(說(shuō)明變量)中，最影響價(jià)格(目標(biāo)變量)的是什么？最不影響價(jià)格的是什么？通過(guò)定量求出影響度，然后根據(jù)多個(gè)因子(說(shuō)明變量)預(yù)測(cè)二手車價(jià)格(目標(biāo)變量)。

可以用方程2表示。二手車價(jià)格“這個(gè)目標(biāo)變量數(shù)據(jù)，既（“空調(diào)類型(AC WC)”  “有無(wú)TV導(dǎo)航”、    、“行駛距離”、  。車檢剩余有效月數(shù)”， “評(píng)分”）。

混合模型混臺(tái)模型是指因子巾既包含定性數(shù)據(jù)也包含定量數(shù)據(jù)的模型。在混臺(tái)模型中．把“空調(diào)”、 “TV導(dǎo)航”等定性因子叫做項(xiàng)目，把數(shù)據(jù)群(空調(diào)的“AC”，“WAC”，TV導(dǎo)航的“有”、“無(wú)”)叫做類別。

接下來(lái)，根據(jù)表l進(jìn)行回歸分析。

這節(jié)我們主要告訴大家回歸分析前，我們需要先根據(jù)自己的思維來(lái)了解分析，把這些需要注意的先分析出來(lái)，這樣對(duì)我們接下來(lái)的回歸分析有很大的幫助。

四、Excel回歸分析需要注意的事項(xiàng)

經(jīng)過(guò)上節(jié)，我們了解了回歸分析前，我們要先通過(guò)思維分析出來(lái)需要注意的事項(xiàng)，那么今天接著上一節(jié)的課來(lái)了解下Excel回歸分析需要注意的事項(xiàng)。包含的定性數(shù)據(jù)，不能直接使用Excel分析，需要將其轉(zhuǎn)換成虛擬變量（也叫O，1數(shù)據(jù)）。例如，  “空調(diào)（AC、WAC）”的數(shù)據(jù)，“AC”用“1”，“WAC”用“O”表示。同樣地，“導(dǎo)航（有導(dǎo)航、無(wú)導(dǎo)航）”的數(shù)據(jù)，  “有導(dǎo)航”用“1”，  “無(wú)導(dǎo)航”用“O”表示。表1是根據(jù)這種方法轉(zhuǎn)換的（0，1）數(shù)據(jù)表。直接使用Excel的對(duì)表1進(jìn)行回歸分析時(shí)，運(yùn)算結(jié)果不理想。理由如下；

以“導(dǎo)航”為例，各行“有導(dǎo)航”+“無(wú)導(dǎo)航”=1

此式成立。把公式變形，

“有導(dǎo)航”=1-“無(wú)導(dǎo)航”

所以“有導(dǎo)航”是“0”或是“1”，由“無(wú)導(dǎo)航。自動(dòng)決定。

線性代數(shù)中發(fā)生秩（矩陣秩）虧時(shí)，不能正確求出必要的逆矩陣。因此也不能求出回歸系數(shù)。

由于上述原因，進(jìn)行回歸分析時(shí)，需要從各個(gè)項(xiàng)目中刪除—列因子（表2）。

根據(jù)表2的數(shù)據(jù)進(jìn)行回歸分析，操作步驟如下：1、“工具”一“數(shù)據(jù)分析”

2、在彈出的“數(shù)據(jù)分析”對(duì)話框中選擇“回歸”，單擊“確定”（圖1）。

3、點(diǎn)擊“回歸”對(duì)話框的“Y值輸入?yún)^(qū)域”，選擇“二手車價(jià)格”的列數(shù)據(jù)，包括項(xiàng)目名稱；接著點(diǎn)擊“X值輸入?yún)^(qū)域”，選擇從“AC”到“中國(guó)、四國(guó)、九州”的區(qū)域，包括項(xiàng)目名稱；選中。標(biāo)志”，單擊“確定”。

（圖2）

系統(tǒng)彈出錯(cuò)誤信息，不能進(jìn)行回歸分析（圖3）。這是因?yàn)镋xcel回歸自由度的最大上限是16（P62小知識(shí)）。這里的回歸自由度是22，因此不能進(jìn)行回歸分析。

統(tǒng)計(jì)學(xué)中經(jīng)常出現(xiàn)“自由度”，即有效信息的數(shù)量。前面已經(jīng)提到，在Excel的回歸分析中，回歸自由度的最大上限是16?；貧w自由度在（多重）回歸分析、數(shù)量化理論|、混合模型中具有不同意義。表3是對(duì)回歸自由度的不同意義的總結(jié)。

五、分兩次進(jìn)行回歸分析

我們?cè)谇懊嫣岬竭^(guò)，當(dāng)回歸自由度在17以上時(shí)，Excel無(wú)法進(jìn)行回歸分析，那么就需要分兩次進(jìn)行回歸分析。第一次，把“空調(diào)”、“TV導(dǎo)航”、“導(dǎo)航”、“SR”、“天窗”、“空氣囊”、“LD”、“TV”、“AW”作為說(shuō)明變量(表1)，第二次，把“顏色”、“拍賣會(huì)地點(diǎn)”、“行駛距離”、“車檢剩余有效月數(shù)”、“評(píng)分”作為說(shuō)明變量(表2)，目標(biāo)變量都是“二手車價(jià)格”。

對(duì)表1、表2進(jìn)行回歸分析?；貧w分析的結(jié)果分別如表3、表4所示(具體操作步驟將在下一節(jié)詳細(xì)說(shuō)明)。

圖3

圖4