黃清濤，王光義，王建興

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

2.湖北省襄陽(yáng)市水文水資源勘測(cè)局，湖北 襄陽(yáng) 441003；

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

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

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

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

2.湖北省襄陽(yáng)市水文水資源勘測(cè)局，湖北 襄陽(yáng) 441003；

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

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

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

0 引言

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

1 流量模型率定實(shí)例研究

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

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

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

表1 萬(wàn)店水文站實(shí)測(cè)流量成果

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

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

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

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

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

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

圖2 規(guī)劃求解過(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 中實(shí)測(cè)流量求出相應(yīng)水位點(diǎn)繪曲線見圖1，可見擬合的關(guān)系與實(shí)測(cè)點(diǎn)據(jù)十分吻合。通過(guò)“三種檢驗(yàn)”（符號(hào)、適線和偏離數(shù)值檢驗(yàn)）和標(biāo)準(zhǔn)差計(jì)算[2]，完全符合水文資料整編規(guī)范要求。

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

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

在實(shí)際工作中，當(dāng)采用多項(xiàng)式率定水位～流量關(guān)系時(shí)，為避免水位基數(shù)過(guò)大造成方程不能回歸，可以將觀測(cè)水位減除斷流水位使得成為有效水位，再率定相關(guān)關(guān)系，中高水位曲線擬合較好，但因?yàn)閿嗔魉坏耐扑愦嬖谌我庑裕史桨覆蝗缛齾?shù)冪指數(shù)方程 Z = a Qb+ c。

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

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

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

表4 先覺廟（水庫(kù)）站溢洪道實(shí)測(cè)流量成果表

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

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

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

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

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

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

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

2 結(jié)語(yǔ)

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

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

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

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

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

[5] 中華人民共和國(guó)水利部.SL207-92 水工建筑物測(cè)流規(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－），男，湖北隨州人，教授級(jí)高工，從事水文情報(bào)預(yù)報(bào)和水資源監(jiān)測(cè)分析研究工作。