EXCEL在水文學線性回歸分析中的應用

程冬梅，劉志峰，王琳琳

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

0 前言

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

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

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

1.1 一元線性回歸模型

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

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

1.2 實現(xiàn)方法

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

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

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)關閉“常數(shù)為零”復選框，表示保留截距項，使其不為0。

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

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

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

結果見圖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之間的關系。

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

①選取“工具”——“數(shù)據(jù)分析”。②選定“回歸”。③選擇“確定”。④在“輸入Y區(qū)域”框輸E2:E16。⑤在“輸入X區(qū)域”框輸入A2:D16。⑥關閉“常數(shù)為零”復選框，表示保留截距項，使其不為0。⑦關閉“標記”復選框。⑧打開“置信水平”復選框，并使其值為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ù)的顯著性檢驗:

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

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

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

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

3 算例

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

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

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

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

試分析X與Y間的關系。

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)關閉“常數(shù)為零”復選框，表示保留截距項，使其不為0。

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

9)打開“置信水平”復選框，并使其值為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 結語

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

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

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

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