黃清濤，王光義，王建興

（1.湖北省隨州市水文水資源勘測局，湖北 隨州 441300；

2.湖北省襄陽市水文水資源勘測局，湖北 襄陽 441003；

3.湖北省黃石市水文水資源勘測局，湖北 黃石 435002）

利用 EXCEL 工具率定典型流量數(shù)學(xué)模型方法探討

黃清濤1，王光義2，王建興3

（1.湖北省隨州市水文水資源勘測局，湖北 隨州 441300；

2.湖北省襄陽市水文水資源勘測局，湖北 襄陽 441003；

3.湖北省黃石市水文水資源勘測局，湖北 黃石 435002）

介紹 EXCEL【規(guī)劃求解】功能擬合流量數(shù)學(xué)模型的操作方法和步驟，并與 EXCEL 內(nèi)置圖形函數(shù)擬合的流量數(shù)學(xué)模型進(jìn)行對比分析，說明該功能擬合流量數(shù)學(xué)模型的優(yōu)越性。分別以天然河道、水庫堰閘、水電站實測流量系列資料為實例，應(yīng)用該功能擬合不同類型的流量數(shù)學(xué)模型，結(jié)果表明【規(guī)劃求解】功能擬合流量數(shù)學(xué)模型均能滿足水文資料整編規(guī)范要求。

流量模型；規(guī)劃求解；曲線擬合；EXCEL

0 引言

水位和流量要素是防汛抗旱、水資源開發(fā)利用及水利水電工程調(diào)度運用最重要的水文信息。隨著計算機(jī)、網(wǎng)絡(luò)和通訊技術(shù)的快速發(fā)展，水位要素已經(jīng)實現(xiàn)了自動采集、存儲和處理，而流量要素因為影響因素復(fù)雜，信息采集困難成為水情信息化、自動化的難點。EXCEL 軟件具有強大的數(shù)據(jù)分析處理、表格和圖形繪制功能[1]。通過對湖北省若干水文站長期監(jiān)測的天然河道、水工建筑物、水電站等不同類型系列流量資料進(jìn)行相關(guān)分析擬合、計算、誤差統(tǒng)計和評估[2-3]，優(yōu)選配置不同的流量模型計算公式，將流量模型置入水情系統(tǒng)，水位、電站發(fā)電功率等要素實現(xiàn)在線監(jiān)測，江河實時流量的計算可全部由計算機(jī)自動完成。

1 流量模型率定實例研究

1.1 天然河道水位流量關(guān)系率定函數(shù)比選

1.1.1 利用 EXCEL【規(guī)劃求解】功能擬合曲線

用數(shù)學(xué)模型來擬合單一水位流量關(guān)系曲線的工作過去作過不少嘗試。水文資料整編規(guī)范[2]規(guī)定：單一曲線法推流，應(yīng)結(jié)合測站特性，應(yīng)用插值法或通過選用適當(dāng)?shù)臄?shù)學(xué)模型擬合曲線，用水位推算流量。以萬店水文站 2010 年實測流量資料[4]為例，說明利用 EXCEL【規(guī)劃求解】功能擬合曲線方法，成果如表1 所示。

表1 萬店水文站實測流量成果

利用 EXCEL 工具點繪的萬店水文站水位～流量關(guān)系節(jié)點點據(jù)如圖1 所示。

通過觀察，水位流量呈冪指數(shù)關(guān)系，于是構(gòu)造函數(shù) Z = a Qb+ c，式中：Z 為水位；Q 為流量；a，b，c 為待定系數(shù)。

利用 EXCEL【規(guī)劃求解】功能進(jìn)行曲線擬合步驟如下：首先按照表2 形式輸入實測水位、流量成果，在 D2～D4 列分別輸入任意假定的數(shù)據(jù)；在單元格 E2 按照公式 Z = a Qb+ c 輸入公式“＄D2*C2^＄3 +＄D＄4”確定并下拉填充至 E26；在單元格 F2輸入公式“=（E2-B2）^2”填充至 F26，將 F2～F26 之和存放至單元格 F27 中。

圖1 萬店水文站水位～流量關(guān)系曲線

表2 規(guī)劃求解過程（1）

其次單擊 EXCEL 工具欄中的【加載宏】，選取【規(guī)劃求解】，在“設(shè)置目標(biāo)單元格”編輯框中鍵入目標(biāo)單元格的位置（“$F$27”）。接下來根據(jù)目標(biāo)函數(shù)——水位誤差平方和最小的要求，在“等于”欄選擇【最小值】選項，設(shè)置可變單元格。參數(shù) a，b，c 值是要進(jìn)行率定的，故可變單元格應(yīng)填入 a，b，c 參數(shù)所在的位置“$D$2:$D$4”，規(guī)劃求解過程如圖2 所示。單擊【求解】，則立即顯示率定結(jié)果，整個過程就是要求計算滿足單元格“$F$27”的值最小時對應(yīng)的參數(shù) a，b，c 的值。

圖2 規(guī)劃求解過程（2）

單擊【求解】按鈕并選擇【保存規(guī)劃求解結(jié)果】，將參數(shù) a，b，c 求解結(jié)果代入預(yù)先構(gòu)造函數(shù)，則可寫出方程 Z = 0.277 1Q0.4287+ 67.808 3，將流量作為因變量則數(shù)學(xué)表達(dá)式可寫為 Q = exp(1/0.428 7*ln ((Z - 67.808 3)/0.277 1)。利用該方程及表1 中實測流量求出相應(yīng)水位點繪曲線見圖1，可見擬合的關(guān)系與實測點據(jù)十分吻合。通過“三種檢驗”（符號、適線和偏離數(shù)值檢驗）和標(biāo)準(zhǔn)差計算[2]，完全符合水文資料整編規(guī)范要求。

1.1.2 利用 EXCEL 內(nèi)置圖形函數(shù)擬合曲線

EXCEL 內(nèi)置圖形函數(shù)有線性、對數(shù)、多項式、乘冪、指數(shù)及移動平滑等。將萬店水文站 2010 年實測流量成果分別選擇 EXCEL 內(nèi)置圖形函數(shù)不同的線型進(jìn)行率定，均不能獲得滿意的結(jié)果，其中采用三次多項式雖然率定線與實測點據(jù)匹配較好，但因為水位基數(shù)大，水位的指數(shù)次方對函數(shù)貢獻(xiàn)過大，淹沒了有效水位從而使方程不能回歸。采用其他內(nèi)置圖形函數(shù)擬合的方程也均不能滿足“三種檢驗”的要求，系統(tǒng)和偶然誤差過大，如表3 所示。

在實際工作中，當(dāng)采用多項式率定水位～流量關(guān)系時，為避免水位基數(shù)過大造成方程不能回歸，可以將觀測水位減除斷流水位使得成為有效水位，再率定相關(guān)關(guān)系，中高水位曲線擬合較好，但因為斷流水位的推算存在任意性，故方案不如三參數(shù)冪指數(shù)方程 Z = a Qb+ c。

通過以上擬合分析，對天然河道穩(wěn)定的水位～流量關(guān)系采用三參數(shù)指數(shù)函數(shù) Z = a Qb+ c 并利用EXCEL【規(guī)劃求解】功能擬合的方程為最優(yōu)解。水文測站特性差異性大，有的站水流關(guān)系難以用 1 條曲線描述，可以分水位級率定關(guān)系。

表3 EXCEL 內(nèi)置圖形函數(shù)率定萬店站水位～流量關(guān)系成果表

1.2 水庫閘孔出流關(guān)系率定

表4 先覺廟（水庫）站溢洪道實測流量成果表

圖3 先覺廟（水庫）站～μ 相關(guān)圖

1.3 水電站出流關(guān)系率定

水電站流量通用方程為 Q = Ns/9.8 η h，

式中：Q 為流量；Ns為電功率；h 為實測水頭，對沖擊式水能機(jī) h 由庫水位減水能機(jī)軸中心高程而得；η 為包括水能機(jī)、發(fā)電機(jī)、變壓器、傳動裝置和水力效率等在內(nèi)的合并效率系數(shù)[5]。

為建立水電站發(fā)電單機(jī)功率 N 與效率系數(shù) η 相關(guān)關(guān)系，按照不同功率級記錄開機(jī)臺數(shù)及總功率，觀測庫水位，測定相應(yīng)發(fā)電流量，計算相應(yīng)的單機(jī)效率系數(shù)。在 EXCEL 表格中點繪 N 與 η 相關(guān)點據(jù)，可知呈對數(shù)關(guān)系，利用 EXCEL 內(nèi)置圖形函數(shù)率定其N 與 η 關(guān)系（采用 EXCEL【規(guī)劃求解】功能擬合流量數(shù)學(xué)模型與內(nèi)置圖形函數(shù)率定結(jié)果完全相同）。三道河站 2013 年實測電功率與效率系數(shù)相關(guān)圖如圖4所示。

圖4 三道河站電功率與效率系數(shù)相關(guān)曲線

根據(jù)率定結(jié)果可寫出效率系數(shù)公式：η = 0.248* ln (N) -1.042 3，“三種檢驗”合格，標(biāo)準(zhǔn)差計算很小，完全符合水文資料整編規(guī)范要求。

2 結(jié)語

江河流量的影響因素及其復(fù)雜，在實現(xiàn)水利水文信息化時，應(yīng)選擇主要影響因素建立相關(guān)模型。對不同的流量模型，有的可以利用 EXCEL 內(nèi)置圖形函數(shù)直接建立關(guān)系，有的則需構(gòu)造函數(shù)利用 EXCEL【規(guī)劃求解】功能求解參數(shù)。模型建立后應(yīng)進(jìn)行誤差統(tǒng)計和評估，在使用時應(yīng)注意模型率定的范圍，當(dāng)天然河道測站控制發(fā)生轉(zhuǎn)移、水工建筑物尺寸改變、水電站（含電力抽水站）出力發(fā)生變化要重新率定新的參數(shù)[5]。

[1] 林祚頂.水文現(xiàn)代化與水文新技術(shù)[M].北京：中國水利水電出版社，2008: 507-551.

[2] 中華人民共和國水利部.SL247-2012 水文資料整編規(guī)范[S].北京：中國水利水電出版社，2012: 14-27.

[3] 水利部水文局.水工建筑物測流[M].北京：中國科學(xué)技術(shù)出版社，1994: 11.

[4] 水利部水文局.中華人民共和國水文年鑒（第 6 卷第16 冊）[M].鄭州：黃河水文勘測設(shè)計院，2010: 47-50.

[5] 中華人民共和國水利部.SL207-92 水工建筑物測流規(guī)范[S].北京：水利水電出版社，1992: 30-57.

Research on Method of Calibrating Typic Discharge Mathematic Model Using Excel Solver

HUANG Qingtao1, WANG Guangyi2, WANG Jianxing3

(1.Suizhou Hydrology and Water Resources Survey Bureau of Hubei Province, Suizhou 441300, China;
2.Xiangyang Hydrology and Water Resources Survey Bureau of Hubei Province, Xiangyang 441003, China；
3.Huangshi Hydrology and Water Resources Survey Bureau of Hubei Province, Huangshi 435002, China)

The article introduces the operating methods and steps of excel solver fit discharge mathematic model, compares it with the inner graphics function in excel, and proves the superiority of excel solver in fitting discharge mathematic model.Respectively based on the observed discharge data from natural river, reservoir and weir, and hydropower station, it applies excel solver to fit different kinds of discharge mathematic model, and the results prove that it can meet the code requirement of hydrologic data processing.

discharge model; solver; curve fitting; excel

P337；O212

A

1674-9405(2014)04-0028-04

2014-03-07

黃清濤（1963－），男，湖北隨州人，教授級高工，從事水文情報預(yù)報和水資源監(jiān)測分析研究工作。