Excel在水文分析中的應用

摘要：利用excel中的繪圖及規劃求解功能，解決水文分析與計算中常用圖形的繪製、水位~流量關係曲線擬合的問題。擬合的曲線用函式表達更利於計算機處理，而且對於一些實測點據的定線，還能減少徒手繪製因人而異所帶來的任意性。

關鍵詞：excel 曲線擬合 規劃求解

excel作為一個通用的計算機軟體，在各行業得到比較普遍的應用，在水利行業，不僅在計算上有很大用途，其圖表功能也為我們進行水利水文分析提供很大的便利，本文通過嘉積水文站實測水位~流量資料，說明excel在水文分析與計算中的應用。

一、水位~流量關係圖形繪製

在水文分析與計算中，水位~流量關係的分析是最基本的，但是，採用手工方式在方格紙上點繪實測資料點是一項非常繁雜的工作，特別是當實測資料量比較多的時候。而在excel中，只要輸入相關資料（在a列中輸入流量，在b列中輸入相應水位），點取“插入”-“圖表”中選擇“xy散點圖”，根據提示就可以輕鬆的繪出水位~流量關係的散點圖，如圖1

圖1

二、曲線擬合

水位~流量關係的散點圖繪出後，根據散點分佈，一般採用目估的方法通過點群中心繪製一條光滑的曲線，建立水位~流量關係曲線，以便進行相關的水文分析與計算的'工作。該曲線也可以利用函式關係來描述，用近似函式表達比人工查圖快速和準確，更利於計算機的處理，而且對於一些實測點據的定線，還能減少徒手繪製因人而異所帶來的任意性。水文水利計算中，如庫容曲線（z~f），洩流曲線（z~q），河道基面曲線(z~f)，水位流量關係曲線（z~q）等都可以採用曲線擬合的方法來求其函式表示式。目前，在曲線擬合中採用的線型及計算方法有很多種，本文采用三引數冪函式（y=axb+c）線型，在excel中應用規劃求解的方法進行曲線擬合。

“規劃求解”是一組命令的組成部分，這些命令有時也稱作假設分析工具。藉助“規劃求解”，可求得工作表上某個單元格（被稱為目標單元格）中公式 的最優值。“規劃求解”將對直接或間接與目標單元格中公式相關聯的一組單元格中的數值進行調整，最終在目標單元格公式中求得期望的結果。“規劃求解”通過調整所指定的可更改的單元格（可變單元格）中的值，從目標單元格公式中求得所需的結果。

對於三引數冪函式，按照h=aqb+c建立方程，其中a、b、c為待定引數，以差值平方和最小作為擬合目標函式，即：

在a、b列中分別輸入流量、水位資料，在c列中輸入引數a、b、c的初始值，在d列中按h=aqb+c方程輸入“=$c$2*b2^$c$3+$c$4”，在e列中輸入“=（b2-d2）^2”，然後用拖動填充的方法填充d、e列，在e列資料尾輸入“=sum（e2:e7）”，如圖2所示。

圖2

單擊工具欄中的載入巨集，選取“規劃求解”，在“設定目標單元格”編輯框中鍵入目標單元格的位置，在本例中是“$e$8”，並且根據目標函式的要求s→min，在“等於”欄選擇“最小值”選項，接下來設定可變單元格，a、b、c值是要進行率定的，故可變單元格應填入a、b 、c引數所在的位置“$c$2:$c$4”。（圖3）。單擊“求解”，則率定結果立即顯示（如圖4所見），整個過程就是要求計算滿足單元格“$e$8”的值最小時對應的引數a、b、c的值。

圖3

圖4

應用該方法率定出的加積水文站（二）水位~流量關係如圖5，從圖5結果來看，擬合的效果非常好。該段面的水位~流量關係式為：

h=0.2034q0.4495+1.753

（三）注意的問題

1.從函式h=aqb+c來看，當q=0時，h=c，此時的c值實際上就是河底高程，因此，在引數率定時，對於c值，可以直接輸入河底高程，即引數c不參與曲線擬合。或者參與曲線率定，但是要新增必要的約束條件，即c大於等於河底高程（在圖3中點選“新增”按鈕可以新增若干約束條件），否則，率定的結果可能在數學上是最優的但是其物理含義無法解釋的現象。a、b的起始值一般取0.5，這樣可以減少函式迭代的次數。

2.率定好的數學公式在應用時應注意使用環境，在低水時由於受河底高程的限制，計算結果不會有太大的偏差，但是高水位時必須注意，在高水外延時不應與參與率定的原始資料中最高水位差別太多，否則可能會帶來較大的誤差。

圖5