Excel回歸分析

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

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

3、方法Excel中的某些對(duì)象操作內(nèi)含了簡(jiǎn)單的可視化數(shù)據(jù)分析能力，例如區(qū)域的選擇、圖表數(shù)據(jù)的選擇等。這些快捷工具可以簡(jiǎn)化使用函數(shù)或界面工具的輸入輸出過(guò)程。4） 來(lái)自用戶自定義或第三方的增強(qiáng)數(shù)據(jù)分析工具Excel提供了用戶開(kāi)發(fā)平臺(tái)，高級(jí)用戶可在此基礎(chǔ)上開(kāi)發(fā)專用的數(shù)據(jù)分析函數(shù)或工具。同時(shí)，由于Excel的通用性，有許多基于Excel的商業(yè)統(tǒng)計(jì)和數(shù)據(jù)分析插件可供選擇。這些工具和軟件在不同程度和不同領(lǐng)域增強(qiáng)和擴(kuò)充了Excel的數(shù)據(jù)分析能力。例如，DataDirect MX就是一個(gè)可以擴(kuò)充Excel金融數(shù)據(jù)分析能力的第三方軟件。OLS回歸分析一元回歸分析是估計(jì)一個(gè)因變量和一個(gè)自變量之間平均關(guān)系的統(tǒng)

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

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

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

7、 c + b*ln(x) ，即線性-對(duì)數(shù)模型。? 指數(shù)： y = a*exp(bx)，即對(duì)數(shù)-線性模型，兩邊取對(duì)數(shù)后變成ln(y) = ln(a) + b*x ? 乘冪：y = a*(xb)，即雙對(duì)數(shù)模型，因取對(duì)數(shù)后模型變成ln(y) = ln(a) + b*ln(x)? 多項(xiàng)式：y = a + b*x + c*x2 + d*x3 + . ? 移動(dòng)平均，移動(dòng)平均僅適用于時(shí)間序列數(shù)據(jù)。在XY散點(diǎn)圖中，選中圖表的序列對(duì)象，點(diǎn)擊右鍵彈出快捷菜單：選擇“添加趨勢(shì)線”后出現(xiàn)對(duì)話框，從中可以選擇預(yù)測(cè)模型和顯示選項(xiàng)，如選擇線性模型并要求顯示公式： 完成后的效

8、果如下：方法二：數(shù)據(jù)分析工具中的回歸分析趨勢(shì)線快捷方法只適用于一元回歸，并且僅返回簡(jiǎn)要的統(tǒng)計(jì)信息。對(duì)于詳盡的回歸分析，可以通過(guò)Excel數(shù)據(jù)分析界面工具實(shí)現(xiàn)。使用數(shù)據(jù)分析工具之前，要確認(rèn)“分析工具庫(kù)”插件處于加載狀態(tài)：從Excel工具菜單中選擇“數(shù)據(jù)分析”，在列表功能中選擇“回歸”：確定后出現(xiàn)回歸分析界面對(duì)話框，在此對(duì)話框中進(jìn)行輸入輸出設(shè)定，包括自變量和應(yīng)變量的所在區(qū)域、回歸選項(xiàng)及輸出結(jié)果，Excel將返回預(yù)定格式的數(shù)據(jù)和圖表結(jié)果：回歸分析（三）Excel提供的回歸分析函數(shù)主要包括：? 使用函數(shù)INTERCEPT、SLOPE、RSQ、STEYX和FORECAST擬合回歸線? 

9、;使用函數(shù)LINEST擬合回歸線? 使用函數(shù)TREND獲得回歸預(yù)測(cè)結(jié)果這些函數(shù)的使用方法如下：其中，LINEST函數(shù)返回的結(jié)果排列格式為：以上函數(shù)結(jié)果可同數(shù)據(jù)分析工具的回歸結(jié)果進(jìn)一步對(duì)比：但界面工具返回的是靜態(tài)結(jié)果，而函數(shù)方法可以返回動(dòng)態(tài)的統(tǒng)計(jì)分析結(jié)果，隨原始數(shù)據(jù)的改變而改變。方法四：矩陣代數(shù)使用矩陣代數(shù)方法需要了解Excel矩陣函數(shù)的使用和回歸模型參數(shù)的矩陣代數(shù)表達(dá)式。通過(guò)在工作表中的步步推導(dǎo)和計(jì)算，可以得出回歸的參數(shù)結(jié)果：在熟悉矩陣操作的情況下，僅需一步操作就可達(dá)到目的：回歸分析方法的選擇o 趨勢(shì)線是最簡(jiǎn)捷的辦法，但僅適用于一元回歸； o 界面工具方法的輸入輸出更友好，但只能返回

10、靜態(tài)結(jié)果； o 函數(shù)方法可以返回動(dòng)態(tài)數(shù)據(jù)，但需了解函數(shù)語(yǔ)法和輸出格式； o 矩陣代數(shù)方法靈活性最高，但用戶友好程度最低?；貧w模型的計(jì)算公式說(shuō)明回歸分析可以靈活應(yīng)用于描述不同函數(shù)形式的變量關(guān)系。一元回歸模型中的參數(shù)的計(jì)算公式為，其中，n為樣本觀測(cè)點(diǎn)數(shù)，上劃線的X和Y分別代表均值。根據(jù)上述公式，可以在原始數(shù)據(jù)基礎(chǔ)上逐步計(jì)算回歸的參數(shù)估計(jì)值?？梢钥闯觯?） 回歸線通過(guò)X和Y的均值點(diǎn)；2） 最小二乘斜率是樣本Y值的加權(quán)平均值；3） 權(quán)重之和為零；回歸模型的函數(shù)形式回歸分析可以靈活應(yīng)用于描述不同函數(shù)形式的變量關(guān)系。線性模型可分為參數(shù)線性和變量線性模型，線性回歸僅指參數(shù)線性的

11、回歸模型，而解釋變量無(wú)需是線性的。比較：。主要的參數(shù)線性變量非線性模型形式：? 線性-對(duì)數(shù)：y = a + b*ln(x) + u ? 對(duì)數(shù)-線性： ln(y) = a + b*x + u ? 雙對(duì)數(shù)：ln(y) = a + b*ln(x) + u ? 多項(xiàng)式：y = a + b*x + c*x2 + d*x3 + . + u ? 雙曲：y = a + b*(1/x) + uExcel中處理非線性模型，可通過(guò)兩種方法實(shí)現(xiàn)：數(shù)據(jù)變換或趨勢(shì)線方法。前者是將非現(xiàn)性的數(shù)據(jù)轉(zhuǎn)換為線性數(shù)據(jù)后進(jìn)行回歸分析，方法和普通回歸分析相同，后者則是利用Excel的添加

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