用Excel進(jìn)行一元線性回歸分析

精選優(yōu)質(zhì)文檔-----傾情為你奉上精選優(yōu)質(zhì)文檔-----傾情為你奉上專心---專注---專業(yè)專心---專注---專業(yè)精選優(yōu)質(zhì)文檔-----傾情為你奉上專心---專注---專業(yè)用Excel進(jìn)行一元線性回歸分析Excel功能強(qiáng)大，利用它的分析工具和函數(shù)，可以進(jìn)行各種試驗(yàn)數(shù)據(jù)的多元線性回歸分析。本文就從最簡(jiǎn)單的一元線性回歸入手.在數(shù)據(jù)分析中，對(duì)于成對(duì)成組數(shù)據(jù)的擬合是經(jīng)常遇到的，涉及到的任務(wù)有線性描述，趨勢(shì)預(yù)測(cè)和殘差分析等等。很多專業(yè)讀者遇見此類問題時(shí)往往尋求專業(yè)軟件，比如在化工中經(jīng)常用到的Origin和數(shù)學(xué)中常見的MATLAB等等。它們雖很專業(yè)，但其實(shí)使用Excel就完全夠用了。我們已經(jīng)知道在Excel自帶的數(shù)據(jù)庫中已有線性擬合工具，但是它還稍顯單薄，今天我們來嘗試使用較為專業(yè)的擬合工具來對(duì)此類數(shù)據(jù)進(jìn)行處理。文章使用的是2000版的軟件,我在其中的一些步驟也添加了2007版的注解.首先錄入數(shù)據(jù).以連續(xù)10年最大積雪深度和灌溉面積關(guān)系數(shù)據(jù)為例予以說明。錄入結(jié)果見下圖（圖1）。圖1第二步，作散點(diǎn)圖如圖2所示，選中數(shù)據(jù)（包括自變量和因變量），點(diǎn)擊“圖表向?qū)А眻D標(biāo)；或者在“插入”菜單中打開“圖表（H）(excel2007)”。圖表向?qū)У膱D標(biāo)為。選中數(shù)據(jù)后，數(shù)據(jù)變?yōu)樗{(lán)色（圖2）。圖2點(diǎn)擊“圖表向?qū)А币院螅瑥棾鋈缦聦?duì)話框（圖3）：圖3在左邊一欄中選中“XY散點(diǎn)圖”，點(diǎn)擊“完成”按鈕，立即出現(xiàn)散點(diǎn)圖的原始形式（圖4）：圖4第三步，回歸觀察散點(diǎn)圖，判斷點(diǎn)列分布是否具有線性趨勢(shì)。只有當(dāng)數(shù)據(jù)具有線性分布特征時(shí)，才能采用線性回歸分析方法。從圖中可以看出，本例數(shù)據(jù)具有線性分布趨勢(shì)，可以進(jìn)行線性回歸。回歸的步驟如下：=1\*GB2⑴首先，打開“工具”下拉菜單，可見數(shù)據(jù)分析選項(xiàng)（見圖5）(2007為”數(shù)據(jù)”右端的”數(shù)據(jù)分析”)：圖5用鼠標(biāo)雙擊“數(shù)據(jù)分析”選項(xiàng)，彈出“數(shù)據(jù)分析”對(duì)話框（圖6）：圖6=2\*GB2⑵然后，選擇“回歸”，確定，彈出如下選項(xiàng)表：圖7進(jìn)行如下選擇：X、Y值的輸入?yún)^(qū)域（B1:B11，C1:C11），標(biāo)志，置信度（95%），新工作表組，殘差，線性擬合圖?；蛘撸篨、Y值的輸入?yún)^(qū)域（B2:B11，C2:C11），置信度（95%），新工作表組，殘差，線性擬合圖。注意：選中數(shù)據(jù)“標(biāo)志”和不選“標(biāo)志”，X、Y值的輸入?yún)^(qū)域是不一樣的：前者包括數(shù)據(jù)標(biāo)志：最大積雪深度x(米)灌溉面積y(千畝)后者不包括。這一點(diǎn)務(wù)請(qǐng)注意。圖8-1包括數(shù)據(jù)“標(biāo)志”圖8-2不包括數(shù)據(jù)“標(biāo)志”=3\*GB2⑶再后，確定，取得回歸結(jié)果（圖9）。圖9線性回歸結(jié)果=4\*GB2⑷最后，讀取回歸結(jié)果如下：截距：；斜率：；相關(guān)系數(shù)：；測(cè)定系數(shù)：；F值：。=5\*GB2⑸建立回歸模型，并對(duì)結(jié)果進(jìn)行檢驗(yàn)?zāi)Ｐ蜑椋褐劣跈z驗(yàn)，R、R2和F值可以直接從回歸結(jié)果中讀出。實(shí)際上，，檢驗(yàn)通過。有了R值，F(xiàn)值和t值均可計(jì)算出來。F值的計(jì)算公式和結(jié)果為：顯然與表中的結(jié)果一樣。t值的計(jì)算公式和結(jié)果為：回歸結(jié)果中給出了殘差（圖10），據(jù)此可以計(jì)算標(biāo)準(zhǔn)離差。首先求殘差的平方，然后求殘差平方和，于是標(biāo)準(zhǔn)離差為于是圖10y的預(yù)測(cè)值及其相應(yīng)的殘差等進(jìn)而，可以計(jì)算DW值（參見圖11），計(jì)算公式及結(jié)果為取，，（顯然），查表得，。顯然，DW=0.751，可見有序列正相關(guān)，預(yù)測(cè)的結(jié)果令人懷疑。圖11利用殘差計(jì)算DW值最后給出利用Excel快速估計(jì)模型的方法：=1\*GB2⑴用鼠標(biāo)指向圖4中的數(shù)據(jù)點(diǎn)列，單擊右鍵，出現(xiàn)如下選擇菜單（圖12）：圖12=2\*GB2⑵點(diǎn)擊“添加趨勢(shì)線(R)”，彈出如下選擇框（圖13）：圖13=3\*GB2⑶在“分析類型”中選擇“線性(L)”，然后打開選項(xiàng)單（圖14）：圖14=4\*GB2⑷在選擇框中選中“顯示公式(E)”和“顯示R平方值(R)”（如圖14），確定，立即得到回歸結(jié)果如下（圖15）：圖15在圖15中，給出了回歸模型和相應(yīng)的測(cè)定系數(shù)即擬合優(yōu)度。

2007回歸分析使用Excel2007的“分析工具庫”做多元回歸分析首先，要求你的Excel2007是企業(yè)版或者專業(yè)版的（其他版本是否有此功能我不能確定），通常，在安裝Excel2007的時(shí)候，選擇“全部在硬盤中運(yùn)行”就會(huì)使你的Excel具有“分析工具庫”。如果你的Excel沒有這個(gè)功能，請(qǐng)按照下面的操作添加此功能：打開控制面板（在WindowsXP中，Win7的操作應(yīng)該與此類似），找到Office。點(diǎn)擊“更改”。 選中“添加或刪除功能”。然后點(diǎn)繼續(xù)。在隨后出現(xiàn)的界面中，點(diǎn)開Excel選項(xiàng)，選擇“在本機(jī)運(yùn)行全部程序”。Office此時(shí)會(huì)進(jìn)入安裝界面，所以此時(shí)你必須插入office的安裝盤，或者幫助計(jì)算機(jī)找到office的源文件。裝好之后，你的Excel2007應(yīng)該具備“分析工具庫”了。第二步，激活Excel2007的分析工具庫。這才是我們的正事兒。打開Excel2007。點(diǎn)擊紅圈圈這個(gè)地方。會(huì)出現(xiàn)一個(gè)下拉菜單。在這個(gè)菜單的最底部，有“Excel選項(xiàng)”這個(gè)按鈕，點(diǎn)擊之。在隨后的界面中，點(diǎn)擊加載項(xiàng)；看到管理“Excel加載項(xiàng)”，點(diǎn)旁邊的“轉(zhuǎn)到”按鈕。在出現(xiàn)的界面中，勾選“分析工具庫”，然后點(diǎn)確定。“分析工具庫”就安裝成功了。打開你要做回歸的Excel工作表。點(diǎn)菜單上的“數(shù)據(jù)”以及“數(shù)據(jù)分析”按鈕。選擇“回歸”分別輸入Y值區(qū)域和X值區(qū)