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

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

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

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

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

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

1.EXCLE電子表格在洪水預報中應用

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

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

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

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

表1 基本資料表

1.3 凈雨計算表（見表2），降雨時間、降雨量、Pa是對表1的引用，在C4單元格輸入：“=C3+B4”，是累計降雨的一個推求，累計凈雨牽涉到對降雨徑流查算表的一個引用。

表2 昭平臺水庫凈雨計算表

如何制作降雨徑流查算表呢？其實這是一個二維線性插值的問題，什么是二維線性插值呢？對于一個二維數(shù)組r，我們假設(shè)對于任意一個浮點數(shù)i,r(a,i)到r(a+1,i)之間是線性變化的,r(i,b)到r(i,b+1)之間也是線性變化的(a,b都是整數(shù))，那么對于浮點數(shù)的坐標(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ù)學上，二維線性插值是有兩個變量的插值函數(shù)的線性插值擴展，其核心思想是在兩個方向分別進行一次線性插值。在人工查詢降雨徑流關(guān)系線的時候，其實是在圖上判斷給定條件降雨量P、前期影響雨量Pa在圖上的位置，用二維線性插值進行模擬計算，返回需要的徑流深R。

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

對于表中的節(jié)點數(shù)據(jù)，一定要再三核對，因為這牽涉到以后查算結(jié)果的準確性。在“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)”，鼠標移到單元格右下角，下拉自動填充公式。這里用到幾個函數(shù)，TREND函數(shù)是返回線性回歸擬合線的縱坐標，OFFSET函數(shù)的功能為以指定的引用為參照系，通過給定偏移量得到新的引用;choose函數(shù)從參數(shù)列表中選擇并返回一個值;還有“LASTX、LASTY、WY、WX”幾個自定義的名稱。對于引用過來的一組累計雨量數(shù)據(jù)，我們先判定給定的查詢條件“P”與“Pa”值在數(shù)據(jù)表格中的位置，返回一組凈雨的數(shù)據(jù)到C列，通過數(shù)據(jù)引用，凈雨計算表的累計凈雨列里就自動填充了凈雨數(shù)據(jù)，然后通過簡單的公式在時段凈雨列里計算出每個時段的凈雨。

1.4 匯流計算

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

表3 匯流計算表

因為昭平臺水庫的單位線是3條（根據(jù)凈雨深不同選擇不同的單位線），制作匯流計算表的難點在于：如何讓表格根據(jù)時段凈雨的大小自動選擇單位線進行匯流計算。在F3列輸入一個公式“=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ù)的作用是：計算數(shù)值在某個區(qū)域內(nèi)的出現(xiàn)頻率，然后返回一個垂直數(shù)組。也就是說先判斷凈雨深的大小，然后根據(jù)給定條件的選擇單位線數(shù)據(jù)列進行演算。

1.5 水位預報

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

表4 昭平臺水庫洪水演算表

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

打開“水位庫容查算”表，從已知的昭平臺水庫的水位庫容關(guān)系線摘錄節(jié)點數(shù)據(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ù)表中的位置，通過線性擬合，返回一個需要的值，這樣水位庫容查算就算做好了。

2.洪水預報系統(tǒng)的應用

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

3.結(jié)論

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

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

2012-07-06