利用EXCEL電子表格開發洪水預報系統

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

利用EXCEL電子表格開發洪水預報系統

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

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

EXCEL電子表格；開發；洪水預報系統

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

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

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

昭平臺水庫洪水預報系統組主要包括：產流、匯流兩大部分。產流采用降雨徑流相關模型，匯流采用單位線推流。

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

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

表1 基本資料表

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

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

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

現在已經知道r(x,b)和r(x,b+1)了，而根據假設r(x,b)到r(x,b+1)也是線性變化的，所以:

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

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

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

1.4 匯流計算

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

表3 匯流計算表

因為昭平臺水庫的單位線是3條（根據凈雨深不同選擇不同的單位線），制作匯流計算表的難點在于：如何讓表格根據時段凈雨的大小自動選擇單位線進行匯流計算。在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”。與以上相同的函數不再贅述，這里FREQUENCY函數的作用是：計算數值在某個區域內的出現頻率，然后返回一個垂直數組。也就是說先判斷凈雨深的大小，然后根據給定條件的選擇單位線數據列進行演算。

1.5 水位預報

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

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

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

打開“水位庫容查算”表，從已知的昭平臺水庫的水位庫容關系線摘錄節點數據，為了計算的方便，要按照兩列輸入，一列水位數據，一列庫容數據。在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)”。這里公式的意義就是：通過判定給定數據在數據表中的位置，通過線性擬合，返回一個需要的值，這樣水位庫容查算就算做好了。

2.洪水預報系統的應用

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

3.結論

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

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

2012-07-06