水利工程論文-Excel在水文分析中的應(yīng)用摘要：利用excel中的繪圖及規(guī)劃求解功能，解決水文分析與計(jì)算中常用圖形的繪制、水位流量關(guān)系曲線擬合的問題。擬合的曲線用函數(shù)表達(dá)更利于計(jì)算機(jī)處理，而且對(duì)于一些實(shí)測(cè)點(diǎn)據(jù)的定線，還能減少徒手繪制因人而異所帶來的任意性。關(guān)鍵詞：Excel曲線擬合規(guī)劃求解Excel作為一個(gè)通用的計(jì)算機(jī)軟件，在各行業(yè)得到比較普遍的應(yīng)用，在水利行業(yè)，不僅在計(jì)算上有很大用途，其圖表功能也為我們進(jìn)行水利水文分析提供很大的便利，本文通過嘉積水文站實(shí)測(cè)水位流量資料，說明Excel在水文分析與計(jì)算中的應(yīng)用。一、水位流量關(guān)系圖形繪制在水文分析與計(jì)算中，水位流量關(guān)系的分析是最基本的，但是，采用手工方式在方格紙上點(diǎn)繪實(shí)測(cè)數(shù)據(jù)點(diǎn)是一項(xiàng)非常繁雜的工作，特別是當(dāng)實(shí)測(cè)數(shù)據(jù)量比較多的時(shí)候。而在excel中，只要輸入相關(guān)數(shù)據(jù)（在A列中輸入流量，在B列中輸入相應(yīng)水位），點(diǎn)取“插入”-“圖表”中選擇“xy散點(diǎn)圖”，根據(jù)提示就可以輕松的繪出水位流量關(guān)系的散點(diǎn)圖，如圖1圖1二、曲線擬合水位流量關(guān)系的散點(diǎn)圖繪出后，根據(jù)散點(diǎn)分布，一般采用目估的方法通過點(diǎn)群中心繪制一條光滑的曲線，建立水位流量關(guān)系曲線，以便進(jìn)行相關(guān)的水文分析與計(jì)算的工作。該曲線也可以利用函數(shù)關(guān)系來描述，用近似函數(shù)表達(dá)比人工查圖快速和準(zhǔn)確，更利于計(jì)算機(jī)的處理，而且對(duì)于一些實(shí)測(cè)點(diǎn)據(jù)的定線，還能減少徒手繪制因人而異所帶來的任意性。水文水利計(jì)算中，如庫容曲線（ZF），泄流曲線（Zq），河道基面曲線(ZF)，水位流量關(guān)系曲線（ZQ）等都可以采用曲線擬合的方法來求其函數(shù)表達(dá)式。目前，在曲線擬合中采用的線型及計(jì)算方法有很多種，本文采用三參數(shù)冪函數(shù)（y=axb+c）線型，在excel中應(yīng)用規(guī)劃求解的方法進(jìn)行曲線擬合。“規(guī)劃求解”是一組命令的組成部分，這些命令有時(shí)也稱作假設(shè)分析工具。借助“規(guī)劃求解”，可求得工作表上某個(gè)單元格（被稱為目標(biāo)單元格）中公式的最優(yōu)值?！耙?guī)劃求解”將對(duì)直接或間接與目標(biāo)單元格中公式相關(guān)聯(lián)的一組單元格中的數(shù)值進(jìn)行調(diào)整，最終在目標(biāo)單元格公式中求得期望的結(jié)果?！耙?guī)劃求解”通過調(diào)整所指定的可更改的單元格（可變單元格）中的值，從目標(biāo)單元格公式中求得所需的結(jié)果。對(duì)于三參數(shù)冪函數(shù)，按照H=aQb+c建立方程，其中a、b、c為待定參數(shù)，以差值平方和最小作為擬合目標(biāo)函數(shù)，即：在A、B列中分別輸入流量、水位數(shù)據(jù)，在C列中輸入?yún)?shù)a、b、c的初始值，在D列中按H=aQb+c方程輸入“=$C$2*b2$C$3+$C$4”，在E列中輸入“=（B2-D2）2”，然后用拖動(dòng)填充的方法填充D、E列，在E列數(shù)據(jù)尾輸入“=SUM（E2:E7）”，如圖2所示。圖2單擊工具欄中的加載宏，選取“規(guī)劃求解”，在“設(shè)置目標(biāo)單元格”編輯框中鍵入目標(biāo)單元格的位置，在本例中是“$E$8”，并且根據(jù)目標(biāo)函數(shù)的要求SMin，在“等于”欄選擇“最小值”選項(xiàng)，接下來設(shè)置可變單元格，a、b、c值是要進(jìn)行率定的，故可變單元格應(yīng)填入a、b、c參數(shù)所在的位置“$C$2:$C$4”。（圖3）。單擊“求解”，則率定結(jié)果立即顯示（如圖4所見），整個(gè)過程就是要求計(jì)算滿足單元格“$E$8”的值最小時(shí)對(duì)應(yīng)的參數(shù)a、b、c的值。圖3圖4應(yīng)用該方法率定出的加積水文站（二）水位流量關(guān)系如圖5，從圖5結(jié)果來看，擬合的效果非常好。該段面的水位流量關(guān)系式為：H=0.2034Q0.4495+1.753（三）注意的問題1.從函數(shù)H=aQb+C來看，當(dāng)Q=0時(shí)，H=C，此時(shí)的C值實(shí)際上就是河底高程，因此，在參數(shù)率定時(shí)，對(duì)于C值，可以直接輸入河底高程，即參數(shù)C不參與曲線擬合?；蛘邊⑴c曲線率定，但是要添加必要的約束條件，即C大于等于河底高程（在圖3中點(diǎn)擊“添加”按鈕可以添加若干約束條件），否則，率定的結(jié)果可能在數(shù)學(xué)上是最優(yōu)的但是其物理含義無法解釋的現(xiàn)象。a、b的起始值一般取0.5，這樣可以減少函數(shù)迭代的次數(shù)。2.率定好的數(shù)學(xué)公式在應(yīng)用時(shí)應(yīng)注意使用