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

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

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

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

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

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

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

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

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

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

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