Excel回歸分析復(fù)習(xí)過程

1、Good is good, but better carries it.精益求精，善益求善。Excel回歸分析-Excel回歸分析（一）除了數(shù)據(jù)存儲和管理功能，Excel為基于工作表的數(shù)據(jù)分析提供了各類不同的工具和方法，用于各類通用的數(shù)據(jù)分析工作。從應(yīng)用和表現(xiàn)形式看，Excel的數(shù)據(jù)分析工具和方法可以分為以下幾個類別：1）基于工作表函數(shù)和公式的分析能力使用Excel內(nèi)置的公式計算和統(tǒng)計分析函數(shù)，例如通過本期的技巧文章“Excel矩陣函數(shù)和公式的使用”中介紹的矩陣函數(shù)，可以完成回歸分析。使用Excel的公式和函數(shù)功能，需了解相關(guān)的語法和參數(shù)，同時可能還需熟悉所使用的分析方法的數(shù)學(xué)推導(dǎo)過程。2）基

2、于用戶界面的數(shù)據(jù)分析工具Excel提供用于統(tǒng)計和計量分析的集成界面工具包，使用該工具包可進行描述統(tǒng)計、方差分析、假設(shè)檢驗、回歸抽樣等統(tǒng)計分析。在“分析工具庫”已正確加載的前提下，點擊Excel工具菜單中的“數(shù)據(jù)分析”選項，可調(diào)出數(shù)據(jù)分析功能選擇界面，選擇一項具體分析功能后即可進入詳細的輸入輸出和設(shè)置界面：在上步中選擇的不同功能項，會彈出不同的分析界面，一般情況下該分析界面包括參數(shù)的輸入和分析結(jié)果的輸出選擇以及與該功能相關(guān)的具體參數(shù)選項。數(shù)據(jù)分析工具提供交互界面的分析功能，其優(yōu)點是容易理解和使用，但輸出結(jié)果是靜態(tài)的，如需變更輸入數(shù)據(jù)或參數(shù)，都需重新啟動分析工具以獲得修正結(jié)果。為了輸出動態(tài)、可隨時

3、更改輸入選項的結(jié)果，需要使用Excel的函數(shù)和公式功能。3）其他快捷數(shù)據(jù)分析方法Excel中的某些對象操作內(nèi)含了簡單的可視化數(shù)據(jù)分析能力，例如區(qū)域的選擇、圖表數(shù)據(jù)的選擇等。這些快捷工具可以簡化使用函數(shù)或界面工具的輸入輸出過程。4）來自用戶自定義或第三方的增強數(shù)據(jù)分析工具Excel提供了用戶開發(fā)平臺，高級用戶可在此基礎(chǔ)上開發(fā)專用的數(shù)據(jù)分析函數(shù)或工具。同時，由于Excel的通用性，有許多基于Excel的商業(yè)統(tǒng)計和數(shù)據(jù)分析插件可供選擇。這些工具和軟件在不同程度和不同領(lǐng)域增強和擴充了Excel的數(shù)據(jù)分析能力。例如，DataDirectMX就是一個可以擴充Excel金融數(shù)據(jù)分析能力的第三方軟件。OLS回

4、歸分析一元回歸分析是估計一個因變量和一個自變量之間平均關(guān)系的統(tǒng)計方法。定義基本關(guān)系式為：Y=a+bx，回歸通過最小二乘法找出一條能最佳擬合所有觀測數(shù)據(jù)的直線，也即使殘差平方和最小化。方程參數(shù)a和b的計算公式為：多元回歸分析是估算一個因變量和兩個或兩個以上的自變量之間的平均關(guān)系的方法。如價格水平、廣告支出、消費者收入、喜好和競爭狀況對銷售額的影響；公司收益增長、收益波動、股票貝塔值、通貨膨脹率等對股票價格的影響等。多元回歸的模型形式為：其中，Y為自變量或被解釋變量，x1到xk為自變量或解釋變量，b1到bk為回歸系數(shù)，e為回歸誤差項。在Excel中進行多元回歸可利用數(shù)據(jù)分析中的回歸工具，也使用函數(shù)

5、LINEST獲得結(jié)構(gòu)。Excel最多允許16個回歸變量，若方程超出此限制，需使用其它計量分析軟件。回歸模型的工作表展示本例通過工作表的數(shù)據(jù)組織，直觀地說明回歸分析的最終目的：殘差平方和SSR的最小化。給定自變量X和應(yīng)變量Y的數(shù)據(jù)，同時假定方程參數(shù)a和b也為給定，可以得到根據(jù)一元回歸模型計算的應(yīng)變量預(yù)測值Y、預(yù)測值和實際值的差以及殘差的平方和數(shù)據(jù)，進一步通過調(diào)整初始的參數(shù)a和b，可以直觀觀察各數(shù)據(jù)點的殘差及其平方和的變動。其中C-E各列數(shù)據(jù)均包含公式，即為根據(jù)給定的X和Y原始數(shù)據(jù)和給定的模型參數(shù)a和b計算得出：回歸模型的目的是實現(xiàn)殘差平方和SSR即單元格E5的最小化，可以使用ExcelSolve

6、r工具進行求解。從工具菜單中啟動“規(guī)劃求解”，將目標(biāo)單元格設(shè)定為E5的最小值，可變單元格為參數(shù)B4：B5，界面如下：點擊“求解”即返回最優(yōu)結(jié)果：除了使用規(guī)劃求解方法，通過Excel進行回歸分析有四種途徑可供選擇：快捷方式：圖表趨勢線界面工具：數(shù)據(jù)分析函數(shù)方法：LINEST及其他公式函數(shù)方法：矩陣代數(shù)方法一：圖表趨勢線用Excel的圖表向?qū)Ы⒁粋€XY散點圖，而后選中數(shù)據(jù)系列，在右鍵菜單中選擇“添加趨勢線”，可對圖表數(shù)據(jù)生成一個快捷的擬合序列。在添加趨勢線功能中，可供選擇的趨勢線模型包括：?對數(shù)：y=c+b*ln(x)，即線性-對數(shù)模型。?指數(shù)：y=a*exp(bx)，即對數(shù)-線性模型，兩邊取對

7、數(shù)后變成ln(y)=ln(a)+b*x?乘冪：y=a*(xb)，即雙對數(shù)模型，因取對數(shù)后模型變成ln(y)=ln(a)+b*ln(x)?多項式：y=a+b*x+c*x2+d*x3+.?移動平均，移動平均僅適用于時間序列數(shù)據(jù)。在XY散點圖中，選中圖表的序列對象，點擊右鍵彈出快捷菜單：選擇“添加趨勢線”后出現(xiàn)對話框，從中可以選擇預(yù)測模型和顯示選項，如選擇線性模型并要求顯示公式：完成后的效果如下：方法二：數(shù)據(jù)分析工具中的回歸分析趨勢線快捷方法只適用于一元回歸，并且僅返回簡要的統(tǒng)計信息。對于詳盡的回歸分析，可以通過Excel數(shù)據(jù)分析界面工具實現(xiàn)。使用數(shù)據(jù)分析工具之前，要確認“分析工具庫”插件處于加載狀

8、態(tài)：從Excel工具菜單中選擇“數(shù)據(jù)分析”，在列表功能中選擇“回歸”：確定后出現(xiàn)回歸分析界面對話框，在此對話框中進行輸入輸出設(shè)定，包括自變量和應(yīng)變量的所在區(qū)域、回歸選項及輸出結(jié)果，Excel將返回預(yù)定格式的數(shù)據(jù)和圖表結(jié)果：回歸分析（三）Excel提供的回歸分析函數(shù)主要包括：?使用函數(shù)INTERCEPT、SLOPE、RSQ、STEYX和FORECAST擬合回歸線?使用函數(shù)LINEST擬合回歸線?使用函數(shù)TREND獲得回歸預(yù)測結(jié)果這些函數(shù)的使用方法如下：其中，LINEST函數(shù)返回的結(jié)果排列格式為：以上函數(shù)結(jié)果可同數(shù)據(jù)分析工具的回歸結(jié)果進一步對比：但界面工具返回的是靜態(tài)結(jié)果，而函數(shù)方法可以返回動態(tài)的

9、統(tǒng)計分析結(jié)果，隨原始數(shù)據(jù)的改變而改變。方法四：矩陣代數(shù)使用矩陣代數(shù)方法需要了解Excel矩陣函數(shù)的使用和回歸模型參數(shù)的矩陣代數(shù)表達式。通過在工作表中的步步推導(dǎo)和計算，可以得出回歸的參數(shù)結(jié)果：在熟悉矩陣操作的情況下，僅需一步操作就可達到目的：回歸分析方法的選擇趨勢線是最簡捷的辦法，但僅適用于一元回歸；界面工具方法的輸入輸出更友好，但只能返回靜態(tài)結(jié)果；函數(shù)方法可以返回動態(tài)數(shù)據(jù)，但需了解函數(shù)語法和輸出格式；矩陣代數(shù)方法靈活性最高，但用戶友好程度最低?；貧w模型的計算公式說明回歸分析可以靈活應(yīng)用于描述不同函數(shù)形式的變量關(guān)系。一元回歸模型中的參數(shù)的計算公式為，其中，n為樣本觀測點數(shù)，上劃線的X和Y分別代表

10、均值。根據(jù)上述公式，可以在原始數(shù)據(jù)基礎(chǔ)上逐步計算回歸的參數(shù)估計值。可以看出：1）回歸線通過X和Y的均值點；2）最小二乘斜率是樣本Y值的加權(quán)平均值；3）權(quán)重之和為零；回歸模型的函數(shù)形式回歸分析可以靈活應(yīng)用于描述不同函數(shù)形式的變量關(guān)系。線性模型可分為參數(shù)線性和變量線性模型，線性回歸僅指參數(shù)線性的回歸模型，而解釋變量無需是線性的。比較：。主要的參數(shù)線性變量非線性模型形式：?線性-對數(shù)：y=a+b*ln(x)+u?對數(shù)-線性：ln(y)=a+b*x+u?雙對數(shù)：ln(y)=a+b*ln(x)+u?多項式：y=a+b*x+c*x2+d*x3+.+u?雙曲：y=a+b*(1/x)+uExcel中處理非線性

11、模型，可通過兩種方法實現(xiàn)：數(shù)據(jù)變換或趨勢線方法。前者是將非現(xiàn)性的數(shù)據(jù)轉(zhuǎn)換為線性數(shù)據(jù)后進行回歸分析，方法和普通回歸分析相同，后者則是利用Excel的添加趨勢線功能，選擇合適的模型形式。本例采用三種不同的模型形式進行擬合，效果及比較結(jié)果如下：對本例不同模型擬合的對比結(jié)果表明雙曲模型的殘差平方和最低。OLS回歸的缺陷：蒙特卡洛模擬演示OLS回歸在處理異常值時的表現(xiàn)較差。本節(jié)通過一個實例和蒙特卡洛模擬分析方法說明OLS回歸在穩(wěn)健性方面的缺陷。考慮兩個數(shù)據(jù)樣本，其中一個為干凈數(shù)據(jù)，另一個樣本包含一個異常值（J19單元格）：從圖表和樣本的描述統(tǒng)計可以看出，異常值的引入導(dǎo)致OLS回歸效果發(fā)生很大變動，也即單個數(shù)據(jù)觀察值的變動可以完全破壞OLS回歸結(jié)果，因此OLS回歸的穩(wěn)健性存在較大缺陷。假定方程誤差項服從均值為0，標(biāo)準(zhǔn)差為10的正態(tài)分布，進一步通過蒙特卡洛模擬可以觀察OLS回歸的參數(shù)結(jié)果。工作表中給定B5單元格的隨機項標(biāo)準(zhǔn)差，而后通過函數(shù)“=NORMINV(RAND