Excel回歸在水文分析計(jì)算中的應(yīng)用

龐進(jìn) 邱夢(mèng)晨 解金瑞

【摘 要】回歸分析在水文分析計(jì)算經(jīng)常使用，但其大量的數(shù)據(jù)計(jì)算比較麻煩。Excel電子表” 中的回歸分析工具使統(tǒng)計(jì)指標(biāo)計(jì)算的工作大大簡(jiǎn)化，保證所建立的回歸模型中的變量x與y之間具有顯著的線性相關(guān)性，數(shù)據(jù)分析的準(zhǔn)確性和可靠性提高。本文從判斷上下游之間的洪峰流量是否存在顯著的線性關(guān)系，以及依靠上游已經(jīng)出現(xiàn)的洪水來預(yù)報(bào)下游即將出現(xiàn)的洪峰的方法和步驟，介紹如何應(yīng)用Excel電子表回歸分析工具進(jìn)行水文分析計(jì)算。

【關(guān)鍵詞】Excel；回歸分析；水文計(jì)算；應(yīng)用

中圖分類號(hào)： P333.9 文獻(xiàn)標(biāo)識(shí)碼： A 文章編號(hào)： 2095-2457（2018）13-0257-001

DOI：10.19694/j.cnki.issn2095-2457.2018.13.120

【Abstract】regression analysis is often used in hydrological analysis calculation， but its large amount of data calculation is troublesome. Excel spreadsheet "regression analysis tools of statistical indicators calculation work greatly simplified， ensure that the established regression model of the variables had a significant linear correlation between x and y， the veracity and reliability of data analysis. Peak flow between upstream and downstream from the judgment whether there was a significant linear relationship， and rely on the upstream has appeared to flood forecast methods and steps of the downstream flood peak of the upcoming， introduce how to use Excel spreadsheets regression analysis tools for hydrologic analysis and calculation.

【Key words】Excel regression analysis； Hydrological calculation application

回歸分析是一種處理變量的統(tǒng)計(jì)相關(guān)關(guān)系的一種數(shù)理統(tǒng)計(jì)方法，回歸分析廣泛很應(yīng)用， 例如實(shí)驗(yàn)數(shù)據(jù)的一般處理、 經(jīng)驗(yàn)公式的求得、因素分析、產(chǎn)品質(zhì)量的控制、氣象及地震預(yù)報(bào)、自動(dòng)控制中數(shù)學(xué)模型的制定等等。

1 Excel輸入相關(guān)水文分析資料

選取黃河下游高村、孫口水文站30個(gè)洪水資料，并應(yīng)用“Excel電子表”中的“回歸分析工具”計(jì)算上下游之間的洪峰流量之間是否具有相關(guān)關(guān)系的有關(guān)指標(biāo)。在“Windows”界面中，用鼠標(biāo)單臺(tái)“開始”，再單擊“程序”及其下拉菜單中的“ Microsoft Excel”程序，即可啟動(dòng)“ Excel電子表格”，出現(xiàn)一張標(biāo)為“Book1”的空白“Excel電子表”。

其中Q下、Q上分別為下游站、上游站洪峰流量，從中找出以上游洪峰流量（x）來表示下游洪峰流量（y）的回歸方程，即下游站洪峰流量的預(yù)報(bào)數(shù)學(xué)模型。

2 調(diào)用回歸分析工具計(jì)算相關(guān)統(tǒng)計(jì)指標(biāo)

在“Excel工作表”的“菜單”欄單擊“工具”選項(xiàng)，在下拉菜單中單擊“數(shù)據(jù)分析”選項(xiàng)，彈出“數(shù)據(jù)分析”的“分析工具”選項(xiàng)框，從中選中“回歸”分析工具（）。單擊“確定”后，彈出“分析工具”中的“回歸”分析對(duì)話框。

如果在“工具”菜單中沒有出現(xiàn)“數(shù)據(jù)分析”命令，必須在“Excel工作表”中安裝“分析工具庫”。即在“工具”菜單中，單擊“加載宏”命令。然后在“當(dāng)前加載宏”的對(duì)話框中選中“分析工具庫”，單擊“確定”按鈕，“數(shù)據(jù)分析”工具就加載完成。（注意：此時(shí)的Microsoft office 必須為完全安裝版，如果是典型安裝，則無法完成此項(xiàng)操作。）

現(xiàn)在只需要在“回歸”分析對(duì)話框中輸入相應(yīng)的數(shù)據(jù)并確定相關(guān)的選項(xiàng)即可。首先，單擊“Y值輸入?yún)^(qū)域（Y）：”右邊的紅色箭頭，折疊對(duì)話框，然后用鼠標(biāo)選中Q下（y值）。單擊折疊對(duì)話框，回到“分析工具”的“回歸”分析對(duì)話框。用同樣的方法單擊“X值輸入?yún)^(qū)域（X）：”右邊的紅色箭頭，折疊對(duì)話框，然后用鼠標(biāo)選中Q上（x）值。再次單擊折疊對(duì)話框，回到“分析工具”的“回歸”分析對(duì)話框。在“回歸分析對(duì)話框”中除了在“Y值輸入?yún)^(qū)域”因變量數(shù)據(jù)區(qū)域的引用和在“X值輸入?yún)^(qū)域”輸入對(duì)自變量數(shù)據(jù)區(qū)域的引用外，還有一些根據(jù)分析的需要所作的選項(xiàng)。主要有：標(biāo)志、置信度、常數(shù)為零、輸出區(qū)域、新工作表組、新工作簿、殘差、標(biāo)準(zhǔn)殘差、殘差圖、正態(tài)概率圖等復(fù)選框，根據(jù)分析的需要進(jìn)行選擇。這里我們選中“殘差”、“標(biāo)準(zhǔn)殘差”、“殘差圖”復(fù)選框。

檢查“回歸分析對(duì)話框”中的各個(gè)選項(xiàng)，然后單擊“確定”按鈕，系統(tǒng)在默認(rèn)情況下，在當(dāng)前工作簿中插入新工作表（按當(dāng)前出現(xiàn)的表格數(shù)依次后續(xù)排列，如目前表格最大為sheet3，則分析結(jié)果表格自動(dòng)生成sheet 4），并從新工作表的A1單元格開始粘貼回歸分析工具的計(jì)算結(jié)果。這樣回歸分析的計(jì)算結(jié)果就計(jì)算出來了。

3 水文預(yù)報(bào)效果檢驗(yàn)

當(dāng)然，該模型是否達(dá)到水文預(yù)報(bào)的精度和標(biāo)準(zhǔn)，我們還需要采用相應(yīng)的規(guī)范用以檢驗(yàn)。由于本次指示介紹Excel回歸分析工具在水文計(jì)算中的應(yīng)用，本次未作預(yù)報(bào)結(jié)果的實(shí)際應(yīng)用檢驗(yàn)，只是繪制擬合效果圖，作預(yù)報(bào)的效果檢驗(yàn)。

擬合效果圖可以反映預(yù)報(bào)值和實(shí)際值之間的擬合程度，從而斷定預(yù)報(bào)模型的準(zhǔn)確程度。可以看出，預(yù)報(bào)值與實(shí)際值之間的擬合程度較好，但實(shí)際應(yīng)用效果未曾檢驗(yàn)，但我們可以斷定，采用Excel電子表進(jìn)行水文預(yù)報(bào)，如果適當(dāng)?shù)卦黾訁?shù)，即采用多元線性回歸分析，其效果是顯而易見的（應(yīng)用此方法作單站水文預(yù)報(bào)，目前在高村水文站已經(jīng)試用，其各項(xiàng)精度指標(biāo)均達(dá)到了《水文情報(bào)預(yù)報(bào)規(guī)范（SL250-2000）》中的甲級(jí)標(biāo)準(zhǔn)）。

4 結(jié)論

顯而易見，Excel電子表回歸分析工具的應(yīng)用，為我們水文分析計(jì)算帶來了極大的便利，不僅能夠利用其進(jìn)行分析計(jì)算，建立起用于相應(yīng)的回歸方程，而且能夠保證所建立的回歸模型中的變量x與y之間具有顯著的線性相關(guān)性，杜絕出現(xiàn)假相關(guān)、偽相關(guān)的情況，進(jìn)而使數(shù)據(jù)分析的準(zhǔn)確性和可靠性提高?？梢灶A(yù)言，Excel電子表回歸分析工具在水文計(jì)算中的普遍應(yīng)用，將會(huì)使測(cè)站在水文測(cè)報(bào)上有更加可靠的依據(jù)。該工具在探索應(yīng)用中的領(lǐng)域?qū)?huì)越來越寬，也將越來越被水文行業(yè)所使用。

【參考文獻(xiàn)】

[1]《應(yīng)用回歸分析》中國人民大學(xué)出版社.2007.

[2]《中長期水文預(yù)報(bào)》.河海大學(xué)函授部.2004.

[3]熊美貞.Excel在水文水利分析與計(jì)算中的應(yīng)用[J].科技與創(chuàng)新，2015（01）：102-103.

[4]Shanghong Zhang，Bohua Zhao，E.Erdun.Watershed Characteristics Extraction and Subsequent Terrain Analysis Based on Digital Elevation Model in Flat Region[J].Journal of Hydrologic Engineering，2014.

[5]Marcelo Cerucci，Gopi K.Jaligama.Hydrologic and Water Quality Integration Tool：HydroWAMIT[J].Journal of Environmental Engineering，2008，134（8）.

[6]J.P.Wang，Duruo Huang，Su-Chin Chang，Logan Brant.On-site earthquake early warning with multiple regression analysis：Featuring two user-friendly applications for Excel[J].Computers and Geosciences，2013，58.

[7]Angus M.Brown.A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet[J].Computer Methods and Programs in Biomedicine，2001，65（3）.

[8]David J Greensmith. Ca analysis： An Excel based program for the analysis of intracellular calcium transients including multiple， simultaneous regression analysis[J]. Computer Methods and Programs in Biomedicine，2014，113（1）.