□周軍亭（平頂山水文水資源勘測(cè)局）

利用EXCEL電子表格開發(fā)洪水預(yù)報(bào)系統(tǒng)

□周軍亭（平頂山水文水資源勘測(cè)局）

利用EXCEL電子表格開發(fā)洪水預(yù)報(bào)系統(tǒng)，解決了一維插值、二維插值等計(jì)算難題，系統(tǒng)易學(xué)易用，提高了工作效率，也為洪水預(yù)報(bào)系統(tǒng)開發(fā)提供了新的途徑，具有很強(qiáng)的獨(dú)創(chuàng)性、推廣性。

EXCEL電子表格；開發(fā)；洪水預(yù)報(bào)系統(tǒng)

在日常的水情工作中，進(jìn)行洪水預(yù)報(bào)作業(yè)時(shí)，需要查很多圖表，還需要進(jìn)行很多復(fù)雜繁瑣的計(jì)算，比如產(chǎn)流計(jì)算、匯流計(jì)算等等。如果所有這些都要人工完成，費(fèi)時(shí)費(fèi)力，而且容易出錯(cuò)。通過編程開發(fā)洪水預(yù)報(bào)系統(tǒng)方便快捷，但對(duì)于一般的水情工作者又太難，有沒有一種更為簡便的方法呢？答案是肯定的。

1.EXCLE電子表格在洪水預(yù)報(bào)中應(yīng)用

現(xiàn)在，EXCLE電子表格在日常學(xué)習(xí)、工作中得到了廣泛應(yīng)用，它可以輸入輸出、顯示數(shù)據(jù)，并能把運(yùn)算后的結(jié)果顯示為需要的圖表，大大提高了工作效率，因而完全可以利用EXCEL電子表格開發(fā)出一套洪水預(yù)報(bào)系統(tǒng)。以沙河昭平臺(tái)水庫的洪水預(yù)報(bào)系統(tǒng)為例，詳細(xì)介紹一下如何利用EXCEL電子表格進(jìn)行洪水預(yù)報(bào)調(diào)度系統(tǒng)的開發(fā)。

昭平臺(tái)水庫洪水預(yù)報(bào)系統(tǒng)組主要包括：產(chǎn)流、匯流兩大部分。產(chǎn)流采用降雨徑流相關(guān)模型，匯流采用單位線推流。

1.1 先建立一個(gè)工作簿，取名“昭平臺(tái)水庫洪水預(yù)報(bào)系統(tǒng)”，在該工作簿里建立基本資料表、凈雨計(jì)算表、降雨凈雨查算表、水位庫容查算表、匯流計(jì)算表、水位預(yù)報(bào)表、預(yù)報(bào)成果表。

1.2 基本資料表（見表1），主要是錄入預(yù)報(bào)作業(yè)需要用到的一些基本的數(shù)據(jù)：前期影響雨量Pa、△t=2h的實(shí)時(shí)雨量、預(yù)報(bào)起始水位等。

表1 基本資料表

1.3 凈雨計(jì)算表（見表2），降雨時(shí)間、降雨量、Pa是對(duì)表1的引用，在C4單元格輸入：“=C3+B4”，是累計(jì)降雨的一個(gè)推求，累計(jì)凈雨?duì)可娴綄?duì)降雨徑流查算表的一個(gè)引用。

表2 昭平臺(tái)水庫凈雨計(jì)算表

如何制作降雨徑流查算表呢？其實(shí)這是一個(gè)二維線性插值的問題，什么是二維線性插值呢？對(duì)于一個(gè)二維數(shù)組r，我們假設(shè)對(duì)于任意一個(gè)浮點(diǎn)數(shù)i,r(a,i)到r(a+1,i)之間是線性變化的,r(i,b)到r(i,b+1)之間也是線性變化的(a,b都是整數(shù))，那么對(duì)于浮點(diǎn)數(shù)的坐標(biāo)(x,y)滿足(a<=x

現(xiàn)在已經(jīng)知道r(x,b)和r(x,b+1)了，而根據(jù)假設(shè)r(x,b)到r(x,b+1)也是線性變化的，所以:

這就是二維線性插值，在數(shù)學(xué)上，二維線性插值是有兩個(gè)變量的插值函數(shù)的線性插值擴(kuò)展，其核心思想是在兩個(gè)方向分別進(jìn)行一次線性插值。在人工查詢降雨徑流關(guān)系線的時(shí)候，其實(shí)是在圖上判斷給定條件降雨量P、前期影響雨量Pa在圖上的位置，用二維線性插值進(jìn)行模擬計(jì)算，返回需要的徑流深R。

打開“P～Pa～R”表，查看預(yù)報(bào)方案圖冊(cè)，從昭平臺(tái)水庫的降雨徑流關(guān)系線圖（是一組以Pa分級(jí)的曲線）摘錄節(jié)點(diǎn)數(shù)據(jù)，輸入到表格在e列到m列輸入）中。

對(duì)于表中的節(jié)點(diǎn)數(shù)據(jù)，一定要再三核對(duì)，因?yàn)檫@牽涉到以后查算結(jié)果的準(zhǔn)確性。在“F3”單元格里輸入以下公式“=TREND(IF(A4=LASTY,OFFSET($E$3,WY,WX-(B4=LASTX),,2),CHOOSE({1,2},TREND(OFFSET($E$3,WY,WX,2),OFFSET($E$3,WY,,2),A4),TREND(OFFSET($E$3,WY,WX+(B4<>LASTX),2),OFFSET($E$3,WY,,2),A4))),OFFSET($E$3,,WX-(B4=LASTX),,2),B4)”，鼠標(biāo)移到單元格右下角，下拉自動(dòng)填充公式。這里用到幾個(gè)函數(shù)，TREND函數(shù)是返回線性回歸擬合線的縱坐標(biāo)，OFFSET函數(shù)的功能為以指定的引用為參照系，通過給定偏移量得到新的引用;choose函數(shù)從參數(shù)列表中選擇并返回一個(gè)值;還有“LASTX、LASTY、WY、WX”幾個(gè)自定義的名稱。對(duì)于引用過來的一組累計(jì)雨量數(shù)據(jù)，我們先判定給定的查詢條件“P”與“Pa”值在數(shù)據(jù)表格中的位置，返回一組凈雨的數(shù)據(jù)到C列，通過數(shù)據(jù)引用，凈雨計(jì)算表的累計(jì)凈雨列里就自動(dòng)填充了凈雨數(shù)據(jù)，然后通過簡單的公式在時(shí)段凈雨列里計(jì)算出每個(gè)時(shí)段的凈雨。

1.4 匯流計(jì)算

打開匯流計(jì)算表，制作一表格（如表3）。

表3 匯流計(jì)算表

因?yàn)檎哑脚_(tái)水庫的單位線是3條（根據(jù)凈雨深不同選擇不同的單位線），制作匯流計(jì)算表的難點(diǎn)在于：如何讓表格根據(jù)時(shí)段凈雨的大小自動(dòng)選擇單位線進(jìn)行匯流計(jì)算。在F3列輸入一個(gè)公式“=OFFSET($B$2,IF(ROW(B2)-COLUMN(B2)+1>0,ROW(B2)-COLUMN(B2)+1,4^7),MATCH(1,FREQUENCY(F$2,{20,40}),))*F$2/10”。與以上相同的函數(shù)不再贅述，這里FREQUENCY函數(shù)的作用是：計(jì)算數(shù)值在某個(gè)區(qū)域內(nèi)的出現(xiàn)頻率，然后返回一個(gè)垂直數(shù)組。也就是說先判斷凈雨深的大小，然后根據(jù)給定條件的選擇單位線數(shù)據(jù)列進(jìn)行演算。

1.5 水位預(yù)報(bào)

對(duì)于一個(gè)水庫，如何預(yù)報(bào)最高水位呢？如果不考慮泄流，可以根據(jù)求得的凈雨計(jì)算出來水量，由起始的水位與庫容，推出入庫后的總庫容，然后由庫容曲線查出水位。打開水位預(yù)報(bào)表。制作洪水演算表（見表4）。B3是對(duì)初始水位的一個(gè)引用，起始庫容是表格自動(dòng)算出來的，“入庫流量”列是對(duì)入庫流量表格最后一列“合計(jì)”的引用，在E3列里輸入公式“=(E3+E4)/2×(A4-A3)×24×3600”反推入庫的水量，泄水量由資料表里的泄流數(shù)據(jù)計(jì)算而得，在C4單元格里輸入“=(F4-G4)/100000000+C3”，由入庫水量、泄水量推出實(shí)時(shí)的蓄量，由于要知道最高水位，可以先找出最大的蓄量，在i4輸入“=INDEX(C:C,MATCH(MAX($C:$C),$C:$C,))”，這樣就找出了最大蓄量的最大值。

表4 昭平臺(tái)水庫洪水演算表

如何由蓄量自動(dòng)查出對(duì)應(yīng)水位呢？這里主要牽涉到一維線性插值問題，什么是一維插值呢？已知離散點(diǎn)上的數(shù)據(jù)集，即已知在點(diǎn)集X上對(duì)應(yīng)的函數(shù)值Y，構(gòu)造一個(gè)解析函數(shù)（其圖形為一曲線）通過這些點(diǎn)，并能夠求出這些點(diǎn)之間的值，這一過程稱為一維插值。也就是說，從一組試驗(yàn)觀測(cè)數(shù)據(jù)（Xi，Yi），i=0,1,2，……，n之中找到自變量X與因變量Y之間的函數(shù)關(guān)系，一般可用一個(gè)近似函數(shù)y=f（x）來表示。

打開“水位庫容查算”表，從已知的昭平臺(tái)水庫的水位庫容關(guān)系線摘錄節(jié)點(diǎn)數(shù)據(jù)，為了計(jì)算的方便，要按照兩列輸入，一列水位數(shù)據(jù)，一列庫容數(shù)據(jù)。在E2單元格（也可以是別的單元格）中輸入“Z”，把F2單元格鏈接到初始水位，在F3單元格中輸入公式“=IF(F2=OFFSET(A4,LAST,),OFFSET(B4,LAST,),TREND(OFFSET(B4,MATCH(F2,OFFSET(A4,1,,LAST)),,2),OFFSET(A4,MATCH(F2,OFFSET(A4,1,,LAST)),,2),F2)”。這里公式的意義就是：通過判定給定數(shù)據(jù)在數(shù)據(jù)表中的位置，通過線性擬合，返回一個(gè)需要的值，這樣水位庫容查算就算做好了。

2.洪水預(yù)報(bào)系統(tǒng)的應(yīng)用

打開預(yù)報(bào)成果表，把預(yù)報(bào)的洪峰時(shí)間、最高水位、最大流量都引用過來，這樣一個(gè)完整的洪水預(yù)報(bào)系統(tǒng)就做好了，作業(yè)預(yù)報(bào)時(shí)，輸入時(shí)段凈雨、前期影響雨量、起始水位，系統(tǒng)會(huì)自動(dòng)預(yù)報(bào)出結(jié)果。

3.結(jié)論

本預(yù)報(bào)系統(tǒng)利用EXCEL電子表格開發(fā)，技術(shù)成熟，適用范圍廣，拓展性好。洪水預(yù)報(bào)方案中有許多關(guān)系線，徑流深——峰量關(guān)系線等，都可以用上面的插值計(jì)算解決。利用EXCEL電子表格開發(fā)洪水預(yù)報(bào)系統(tǒng)，方便快捷，是在洪水預(yù)報(bào)作業(yè)時(shí)，節(jié)省了人力、物力，提高了工作效率，為防汛調(diào)度決策贏得了寶貴的時(shí)間。其實(shí)，可以利用EXCEL強(qiáng)大的計(jì)算功能完成很多工作。

周軍亭，（1975-），男，漢族，工程師，研究方向：水文水資源，從事水情工作。

2012-07-06