用Excel做線性回歸分析

精品文檔

Excel

首先錄入數(shù)據(jù).以連續(xù)

10

年最大積雪深度和灌溉面積關(guān)系數(shù)據(jù)為例予以說(shuō)明。錄入結(jié)果見(jiàn)下圖（圖1）。圖1第二步，作散點(diǎn)圖如圖

2

所示，選中數(shù)據(jù)（包括自變量和因變量），點(diǎn)擊“圖表向?qū)А眻D標(biāo)；或者在“插入”菜單中打開(kāi)“圖表（

H）

(excel2007)”。圖表向?qū)У膱D標(biāo)為后，數(shù)據(jù)變?yōu)樗{(lán)色（圖

。選中數(shù)據(jù)隨意編輯精品文檔圖2點(diǎn)擊“圖表向?qū)А币院?，彈出如下?duì)話框（圖圖3在左邊一欄中選中“XY

散點(diǎn)圖”，點(diǎn)擊“完成”按鈕，立即出現(xiàn)散點(diǎn)圖的原始形式（圖

4）：隨意編輯精品文檔灌溉面積y(千畝)6050403020100

灌溉面積y(千畝)0 10 20

30圖4第三步，回歸觀察散點(diǎn)圖，判斷點(diǎn)列分布是否具有線性趨勢(shì)。只有當(dāng)數(shù)據(jù)具有線性分布特征時(shí)，才能采用線性回歸分析方法。從圖中可以看出，本例數(shù)據(jù)具有線性分布趨勢(shì)，可以進(jìn)行線性回歸。回歸的步驟如下：⑴

首先，打開(kāi)“工具”下拉菜單，可見(jiàn)數(shù)據(jù)分析選項(xiàng)（見(jiàn)圖

5）(2007

為”數(shù)據(jù)”右端的”數(shù)據(jù)分析”)：圖5用鼠標(biāo)雙擊“數(shù)據(jù)分析”選項(xiàng)，彈出“數(shù)據(jù)分析”對(duì)話框（圖6）：隨意編輯精品文檔圖6⑵

然后，選擇“回歸”，確定，彈出如下選項(xiàng)表：圖7進(jìn)行如下選擇：X、Y

值的輸入?yún)^(qū)域（B1:B11，C1:C11），標(biāo)志，置信度（95%），新工作表組，殘差，線性擬合圖。或者：X、Y

值的輸入?yún)^(qū)域（B2:B11，C2:C11），置信度（95%），

新工作表組，殘差，線性擬合圖。注意：選中數(shù)據(jù)“標(biāo)志”和不選“標(biāo)志”，X、Y

值的輸入?yún)^(qū)域是不一樣的：前者包括數(shù)據(jù)標(biāo)志：

后者不包括。這一點(diǎn)務(wù)請(qǐng)注意。隨意編輯精品文檔

⑶

再后，確定，取得回歸結(jié)果（圖隨意編輯精品文檔

⑷

最后，讀取回歸結(jié)果如下：截

距

：

；

斜

率

：

b

；

相

關(guān)

系

數(shù)

：

；

測(cè)

定

系

數(shù)

：

；F值：F

。⑸

建立回歸模型，并對(duì)結(jié)果進(jìn)行檢驗(yàn)?zāi)Ｐ蜑椋?/p>

至

于

檢

驗(yàn)

，

、

和

值

可

以

直

接

從

回

歸

結(jié)

果

中

讀

出

。

實(shí)

際

上

，

計(jì)算公式和結(jié)果為：

，檢驗(yàn)通過(guò)。有了

值，

值和

值均可計(jì)算出來(lái)。

值的F

顯然與表中的結(jié)果一樣。

值的計(jì)算公式和結(jié)果為：

F

隨意編輯精品文檔回歸結(jié)果中給出了殘差（

圖

10

），據(jù)此可以計(jì)算標(biāo)準(zhǔn)離差。首先求殘差的平方

i

，然后求殘差平方和

i

i

n

i

，于是標(biāo)準(zhǔn)i離差為

?

?

(

)

i

ii于是

~

~

進(jìn)而，可以計(jì)算

DW

值（參見(jiàn)圖

11），計(jì)算公式及結(jié)果為

ni

i

in

i

i取

，

，

（顯然

），查表得

d

，d

。l u顯然，DW=0.751

d

，可見(jiàn)有序列正相關(guān)，預(yù)測(cè)的結(jié)果令人懷疑。l隨意編輯精品文檔

最后給出利用

Excel

快速估計(jì)模型的方法：⑴

用鼠標(biāo)指向圖

4

中的數(shù)據(jù)點(diǎn)列，單擊右鍵，出現(xiàn)如下選擇菜單（圖12）：

⑵

點(diǎn)擊“添加趨勢(shì)線(R)”，彈出如下選擇框（圖13）：隨意編輯精品文檔

⑶

在“分析類型”中選擇“線性(L)”，然后打開(kāi)選項(xiàng)單（圖14）：

⑷

在選擇框中選中“顯示公式(E)”和“顯示

R

平方值(R)”（如圖

14），確定，立即得到回歸結(jié)果如下（圖

15）：隨意編輯6050

精品文檔圖表標(biāo)題y

=

1.8129x

+

2.3564R

=

0