Excel回歸在水文分析計算中的應用

龐進 邱夢晨 解金瑞

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

【關鍵詞】Excel；回歸分析；水文計算；應用

中圖分類號： P333.9 文獻標識碼： A 文章編號： 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

回歸分析是一種處理變量的統計相關關系的一種數理統計方法，回歸分析廣泛很應用， 例如實驗數據的一般處理、 經驗公式的求得、因素分析、產品質量的控制、氣象及地震預報、自動控制中數學模型的制定等等。

1 Excel輸入相關水文分析資料

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

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

2 調用回歸分析工具計算相關統計指標

在“Excel工作表”的“菜單”欄單擊“工具”選項，在下拉菜單中單擊“數據分析”選項，彈出“數據分析”的“分析工具”選項框，從中選中“回歸”分析工具（）。單擊“確定”后，彈出“分析工具”中的“回歸”分析對話框。

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

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

檢查“回歸分析對話框”中的各個選項，然后單擊“確定”按鈕，系統在默認情況下，在當前工作簿中插入新工作表（按當前出現的表格數依次后續排列，如目前表格最大為sheet3，則分析結果表格自動生成sheet 4），并從新工作表的A1單元格開始粘貼回歸分析工具的計算結果。這樣回歸分析的計算結果就計算出來了。

3 水文預報效果檢驗

當然，該模型是否達到水文預報的精度和標準，我們還需要采用相應的規范用以檢驗。由于本次指示介紹Excel回歸分析工具在水文計算中的應用，本次未作預報結果的實際應用檢驗，只是繪制擬合效果圖，作預報的效果檢驗。

擬合效果圖可以反映預報值和實際值之間的擬合程度，從而斷定預報模型的準確程度。可以看出，預報值與實際值之間的擬合程度較好，但實際應用效果未曾檢驗，但我們可以斷定，采用Excel電子表進行水文預報，如果適當地增加參數，即采用多元線性回歸分析，其效果是顯而易見的（應用此方法作單站水文預報，目前在高村水文站已經試用，其各項精度指標均達到了《水文情報預報規范（SL250-2000）》中的甲級標準）。

4 結論

顯而易見，Excel電子表回歸分析工具的應用，為我們水文分析計算帶來了極大的便利，不僅能夠利用其進行分析計算，建立起用于相應的回歸方程，而且能夠保證所建立的回歸模型中的變量x與y之間具有顯著的線性相關性，杜絕出現假相關、偽相關的情況，進而使數據分析的準確性和可靠性提高。可以預言，Excel電子表回歸分析工具在水文計算中的普遍應用，將會使測站在水文測報上有更加可靠的依據。該工具在探索應用中的領域將會越來越寬，也將越來越被水文行業所使用。

【參考文獻】

[1]《應用回歸分析》中國人民大學出版社.2007.

[2]《中長期水文預報》.河海大學函授部.2004.

[3]熊美貞.Excel在水文水利分析與計算中的應用[J].科技與創新，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）.