Excel在水文分析中的應用

摘要：利用excel中的繪圖及規劃求解功能，解決水文分析與計算中常用圖形的繪制、水位~流量關(guān)系曲線(xiàn)擬合的問(wèn)題。擬合的曲線(xiàn)用函數表達更利于計算機處理，而且對于一些實(shí)測點(diǎn)據的定線(xiàn)，還能減少徒手繪制因人而異所帶來(lái)的任意性。

關(guān)鍵詞：excel 曲線(xiàn)擬合 規劃求解

 

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

一、水位~流量關(guān)系圖形繪制

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

圖1

二、曲線(xiàn)擬合

水位~流量關(guān)系的散點(diǎn)圖繪出后，根據散點(diǎn)分布，一般采用目估的方法通過(guò)點(diǎn)群中心繪制一條光滑的曲線(xiàn)，建立水位~流量關(guān)系曲線(xiàn)，以便進(jìn)行相關(guān)的水文分析與計算的工作。該曲線(xiàn)也可以利用函數關(guān)系來(lái)描述，用近似函數表達比人工查圖快速和準確，更利于計算機的處理，而且對于一些實(shí)測點(diǎn)據的定線(xiàn)，還能減少徒手繪制因人而異所帶來(lái)的任意性。水文水利計算中，如庫容曲線(xiàn)（z~f），泄流曲線(xiàn)（z~q），河道基面曲線(xiàn)(z~f)，水位流量關(guān)系曲線(xiàn)（z~q）等都可以采用曲線(xiàn)擬合的方法來(lái)求其函數表達式。目前，在曲線(xiàn)擬合中采用的線(xiàn)型及計算方法有很多種，本文采用三參數冪函數（y=ax^b+c）線(xiàn)型，在excel中應用規劃求解的方法進(jìn)行曲線(xiàn)擬合。

“規劃求解”是一組命令的組成部分，這些命令有時(shí)也稱(chēng)作假設分析工具。借助“規劃求解”，可求得工作表上某個(gè)單元格（被稱(chēng)為目標單元格）中公式 的最優(yōu)值�！耙巹澢蠼狻睂�對直接或間接與目標單元格中公式相關(guān)聯(lián)的一組單元格中的數值進(jìn)行調整，最終在目標單元格公式中求得期望的結果�！耙巹澢蠼狻蓖ㄟ^(guò)調整所指定的可更改的單元格（可變單元格）中的值，從目標單元格公式中求得所需的結果。

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

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

圖2

單擊工具欄中的加載宏，選取“規劃求解”，在“設置目標單元格”編輯框中鍵入目標單元格的位置，在本例中是“$e$8”，并且根據目標函數的要求s→min，在“等于”欄選擇“最小值”選項，接下來(lái)設置可變單元格，a、b、c值是要進(jìn)行率定的，故可變單元格應填入a、b 、c參數所在的位置“$c$2:$c$4”。（圖3）。單擊“求解”，則率定結果立即顯示（如圖4所見(jiàn)），整個(gè)過(guò)程就是要求計算滿(mǎn)足單元格“$e$8”的值最小時(shí)對應的參數a、b、c的值。

 

 

 

 

 

 

 

圖3

 

 

 

 

圖4

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

h=0.2034q^0.4495+1.753

（三）注意的問(wèn)題

1.從函數h=aq^b+c來(lái)看，當q=0時(shí)，h=c，此時(shí)的c值實(shí)際上就是河底高程，因此，在參數率定時(shí)，對于c值，可以直接輸入河底高程，即參數c不參與曲線(xiàn)擬合�；蛘邊⑴c曲線(xiàn)率定，但是要添加必要的約束條件，即c大于等于河底高程（在圖3中點(diǎn)擊“添加”按鈕可以添加若干約束條件），否則，率定的結果可能在數學(xué)上是最優(yōu)的但是其物理含義無(wú)法解釋的現象。a、b的起始值一般取0.5，這樣可以減少函數迭代的次數。

2.率定好的數學(xué)公式在應用時(shí)應注意使用環(huán)境，在低水時(shí)由于受河底高程的限制，計算結果不會(huì )有太大的偏差，但是高水位時(shí)必須注意，在高水外延時(shí)不應與參與率定的原始數據中最高水位差別太多，否則可能會(huì )帶來(lái)較大的誤差。

圖5

論文出處(作者):

【Excel在水文分析中的應用】相關(guān)文章：

Excel在水文要素計算中的應用08-28

淺析Excel數據分析在醫學(xué)統計中的應用07-05

土木工程畢業(yè)論文-Excel在水文要素計算中的應用08-15

Excel在教學(xué)與管理中的應用08-08

EXCEL在中學(xué)物理實(shí)驗中的應用06-21

論Excel在日常工作中的應用07-15

稀土在電鍍中的應用分析09-17

VBA在限定Excel工作表用戶(hù)按鈕中的應用10-14

論聚類(lèi)分析在市場(chǎng)分析中的應用06-18

談聚類(lèi)分析在市場(chǎng)分析中的應用07-31