EXCEL在水文學(xué)線性回歸分析中的應(yīng)用

程冬梅，劉志峰，王琳琳

(1.寧陽縣水務(wù)局，山東寧陽 271400;2.山東省水利工程管理局，濟南 250013;3.山東省環(huán)境監(jiān)測中心站，濟南 250013)

0 前言

自然界中的許多變量，它們之間的關(guān)系可以概括為3種類型。第一種是確定性關(guān)系，即一個變量的值完全由另一個或另幾個變量的值所決定;第二種是一個變量的取值與另一個變量的值毫無關(guān)系;第三種是一個變量的取值與另一個變量有一定的關(guān)系，它除受到另一個變量值的制約外，還受到其他變量的影響，因此它不完全由另一個變量確定。水文工作中所研究的變量，很多屬于第三種關(guān)系，即相關(guān)關(guān)系［1］。相關(guān)關(guān)系雖然不是確定性的，但往往也存在一定規(guī)律，可以借助于數(shù)學(xué)工具，利用回歸分析來研究符合這種相關(guān)關(guān)系的水文變量之間的變化趨勢，并用函數(shù)來描述。

回歸分析是水文學(xué)研究和實踐中的極其重要的工具［1］。進行分析時，不但要建立回歸方程，還要對回歸方程進行顯著性檢驗。在探求水文變量之間的關(guān)系時，往往這些變量是幾十年的水文資料，數(shù)據(jù)十分繁雜，用人工計算既花費太多的時間，又很難保證精度要求，所以需要借助計算機來完成。如果只進行水文變量的線性回歸分析，用微軟公司office辦公系統(tǒng)的EXCEL軟件就可以達到目的，而且簡單易用，只要有一點windows操作的經(jīng)驗，甚至不需要任何基礎(chǔ)知識就可以在短時間內(nèi)快速掌握其主要內(nèi)容和基本操作，完成回歸分析。

1 利用EXCEL進行一元線性回歸分析

1.1 一元線性回歸模型

水文學(xué)中有不少情況是用一元線性回歸分析來解決的，其回歸模型為

其中固定的未知參數(shù)β0，β1稱為回歸參數(shù)，變量x稱為回歸變量。

1.2 實現(xiàn)方法

通過例子［2］來闡述EXCEL求解方法。

例1數(shù)組(X)與數(shù)組(Y)的數(shù)據(jù)如下，試分析X與Y間的關(guān)系。

1)首先在Excel中建立工作表，樣本X數(shù)據(jù)存放在A1:A27，其中A1存標記X;樣本Y數(shù)據(jù)存放在B1:B27，其中B1存標記Y。

2)選取“工具”、“數(shù)據(jù)分析”…。

3)選定“回歸”。

4)選擇“確定”。

5)在“輸入Y區(qū)域”框輸入B1:B27。

6)在“輸入X區(qū)域”框輸入A1:A27。

7)關(guān)閉“常數(shù)為零”復(fù)選框，表示保留截距項，使其不為0。

8)打開“標記”復(fù)選框，表示有標記行。

9)打開“置信水平”復(fù)選框，并使其值為95%。

10)在“輸出區(qū)域”框，確定單元格E2。

結(jié)果見圖1所示，其中SS為平方和、MS表示均方、df為自由度。由此得到:

1)回歸方程:Y=0.6754+0.5951 X;

2)F統(tǒng)計量的值:F=1107.942。由于P{F＞1107.942}=1.34353E－21(數(shù)值＜0.01為極顯著;數(shù)值＜0.05、＞0.01為顯著;數(shù)值＞0.05為不顯著)，故所建回歸方程極顯著。

圖1 利用EXCEL進行一元線性回歸分析截圖

2 利用EXCEL進行多元線性回歸分析

2.1 多元線性回歸模型

以二元線性回歸分析問題為例進行說明，其回歸模型為

未知參數(shù) β0，β1……，βp稱為回歸系數(shù)，變量 x1(i=1，……，p)稱為回歸因子。

2.2 實現(xiàn)方法

通過例子［2］來闡述EXCEL求解方法。

例2 數(shù)據(jù)如下所示，試分析X1，X2，X3，X4與Y之間的關(guān)系。

解 首先在Excel中建立工作表，其中樣本X數(shù)據(jù)輸入在A2:D16;樣本Y數(shù)據(jù)輸入在E2:E16。

①選取“工具”——“數(shù)據(jù)分析”。②選定“回歸”。③選擇“確定”。④在“輸入Y區(qū)域”框輸E2:E16。⑤在“輸入X區(qū)域”框輸入A2:D16。⑥關(guān)閉“常數(shù)為零”復(fù)選框，表示保留截距項，使其不為0。⑦關(guān)閉“標記”復(fù)選框。⑧打開“置信水平”復(fù)選框，并使其值為95%。⑨在“輸出區(qū)域”框，確定單元格G1。

圖2 利用EXCEL進行多元線性回歸分析截圖

由此得到:① 回歸方程:Y=59.6881+1.45441X1+0.54959X2+0.06771X3－0.0817X4;②回歸方程的顯著性檢驗。

由于F統(tǒng)計量值為:F=90.9964，而P{F＞90.9964}=8.01843E－08(數(shù)值＜0.01為極顯著;數(shù)值＜0.05＞0.01為顯著;數(shù)值＞0.05為不顯著)，故所建回歸方程是極顯著的;③回歸系數(shù)的顯著性檢驗:

關(guān)于X1，由于P{t＞8.196}=9.5E－6(數(shù)值 ＜0.01為極顯著;數(shù)值＜0.05＞0.01為顯著;數(shù)值＞0.05為不顯著)，故X1是顯著的;

關(guān)于X2，由于P{t＞4.369}=0.0014(情況同X1)，故X2是顯著的;

關(guān)于X3，由于P{t＞0.413}=0.68896(情況同 X1)，故X3是不顯著的;

關(guān)于X4，由于P{t＞－0.6863}=0.50811(情況同X1)，故X4是不顯著的。

3 算例

現(xiàn)有河南省洛陽市瓦廟站和興華站的年降雨量同步觀測系列［3］，見表1。興華站缺測1996年—1999年4年的年降雨量，要求建立兩站年降雨量的回歸方程，計算年初降雨量。

表1 瓦廟站、興華站年降水量 /mm

設(shè)瓦廟站年降水量系列為 xi，興華站年降水量系列為yi。

點匯兩站年降水量的散點圖發(fā)現(xiàn)，兩變量的關(guān)系在圖上呈直線趨勢，故決定建立Y對X的回歸直線方程，所以此類問題為一元線性回歸分析問題。

試分析X與Y間的關(guān)系。

1)首先在Excel中建立工作表，樣本X數(shù)據(jù)存放在A1:A20，其中A1存標記X;樣本Y數(shù)據(jù)存放在B1:B20，其中B1存標記Y。

2)選取“工具”、“數(shù)據(jù)分析”…。

3)選定“回歸”。

4)選擇“確定”。

5)在“輸入Y區(qū)域”框輸入B1:B20。

6)在“輸入X區(qū)域”框輸入A1:A20。

7)關(guān)閉“常數(shù)為零”復(fù)選框，表示保留截距項，使其不為0。

8)打開“標記”復(fù)選框，表示有標記行。

9)打開“置信水平”復(fù)選框，并使其值為95%。

10)在“輸出區(qū)域”框，確定單元格D23。

由此得到:

1)回歸方程:Y=22.6660+0.8834 X;

2)F統(tǒng)計量的值:F=69.910。由于P{F＞69.910}=1.99018 E －07(數(shù)值＜0.01為極顯著;數(shù)值＜0.05＞0.01為顯著;數(shù)值＞0.05為不顯著)，故所建回歸方程顯著。

3)由建立的回歸方程可得興華站1996年—1999年4 a的年降雨量(單位:mm)分別為 883.5、410.7、672.1、579.6。

圖3 利用EXCEL進行線性回歸分析截圖

4 結(jié)語

微軟公司office辦公系統(tǒng)的EXCEL軟件功能十分強大，可以進行大量的數(shù)學(xué)運算。用它來解決水文學(xué)中的回歸分析問題，既可以節(jié)約購買其它回歸分析專業(yè)軟件的花銷，又可以提高運算效率，達到事半功倍的效果。

［1］黃振平.水文統(tǒng)計學(xué)［M］.南京:河海大學(xué)出版社，2003.

［2］李永樂，胡慶軍.應(yīng)用數(shù)理統(tǒng)計［M］.長沙:國防科技大學(xué)出版社，1995.

［3］葉守澤.水文水利計算［M］.北京:中國水利水電出版社，1992.