基于Excel的半圖解法在水庫調洪計算中的應用

王新華 張慧穎 黃輝曦 郭美華

摘 要：相對于列表試算法而言，半圖解法避免了大量的試算工作量，在人工調洪計算中廣泛應用。使用該方法時要事先繪制水位—庫容曲線、泄流量—庫容曲線、單輔助曲線，并反復查算曲線，工作量仍然較大，效率不高。在分析半圖解法基本原理和調洪計算步驟基礎上，提出實現曲線查算的關鍵步驟是將平面坐標系下各種曲線進行分段，根據x值所處的橫坐標區間段，利用線性內插法查出分段曲線上的縱坐標y值。利用Excel中內置的函數trend、offset、match嵌套使用，將曲線查算轉化為求分段線性內插值，從而在Excel中實現半圖解法調洪計算，大大提高了計算效率。

關鍵詞：半圖解法;調洪計算;trend函數;offset函數;match函數; Excel

中圖分類號：TV214; TV697?? 文獻標志碼：A

doi：10.3969/j.issn.1000-1379.2021.11.014

引用格式：王新華，張慧穎，黃輝曦，等.基于Excel的半圖解法在水庫調洪計算中的應用[J].人民黃河，2021，43（11）：76-80，158.

Application of Semi-Graphic Method Based on Excel in Reservoir Flood Regulation Calculation

WANG Xinhua1， ZHANG Huiying1， HUANG Huixi2， GUO Meihua1

（1. College of Water Conservancy， Yunnan Agricultural University，Kunming 650201， China;

2.Kunming Branch of Yunnan Provincial Hydrology and Water Resources Bureau， Kunming 650051， China）

Abstract： Compared to the list-try algorithm， the semi-graphic method， avoiding a lot of trial calculating work， is widely used in artificial flood regulation calculation. The water level storage curve， discharge and storage curve， auxiliary curve should be drawn in advance when using this method. Curve drawing and repeated search， the workload is still large and the efficiency is not high. Based on the analysis of the basic principle of semi-graphic method and the calculation steps of flood regulation， this paper put forward that the key step to realize the curve calculation and search was to segment all kinds of curves in the plane coordinate system and find out the y-coordinate on the segmented curve according to the x-coordinate interval and x value by linear interpolation method. By using the embedded three functions commands of trend， offset and match， the semi-graphic flood regulation calculation could be realized in Excel by transforming the curves search into finding the linear interpolation of segmented line segments， which greatly improved the calculation efficiency of the method.

Key words： semi-graphic method;flood regulation calculating; trend function; offset function; match function; Excel

水庫的調洪庫容、設計洪水位、校核洪水位、泄洪建筑物型式及尺寸擬定和已建水庫防洪安全復核等都要進行調洪計算。調洪計算的基本原理是聯合運用水量平衡方程和動力平衡方程（水庫蓄泄方程），求出逐時段末的水庫水位、庫容及下泄流量。常用的調洪計算方法有列表試算法、圖解法或半圖解法[1]。隨著計算機的普及以及多種算法的實現，水庫調洪出現了一些新的解法，如龍格-庫塔數值解法、人工神經網絡法、迭代法、雙斜率法等[2-4]。數值解法、人工神經網絡法、迭代法、雙斜率法等適合于計算機編程，但要求開發者有較高的計算機基礎和編程能力，且程序的編制和調試十分復雜，對大多數使用者來說，需要花費不少資金購買程序，處理程序運行中出現的錯誤比較困難。列表試算法概念清晰，容易掌握，但試算工作量較大，效率不高，現在大多是通過軟件編程計算代替試算迭代過程。半圖解法（又稱單輔助線法）通過將水量平衡方程適當變形，結合水位—庫容曲線、水位—下泄流量曲線，繪制一條下泄流量q與（VΔt+q2）輔助關系曲線，通過查算該輔助曲線和泄流量與庫容關系曲線，求出逐時段末的下泄流量，避免了大量的試算工作量，手工計算時效率較高，在計算機普及之前得到了廣泛的應用，但半圖解法需要手工繪制q=f（VΔt+q2）輔助線及由V=f（Z）和q=f（Z）轉化而來的q=f（V）關系曲線，繪圖比例尺選用不當時查圖值誤差可能較大，如果入庫洪水時段改變，需要重新繪制輔助線，工作量依然較大。Excel在日常工作中應用非常普遍，不少學者嘗試利用matlab軟件、VB程序或Excel自帶的VBA編制調洪計算程序[5-7]，這仍然需要開發者有一定的編程能力。

本文通過利用Excel中常用的3個函數組合實現曲線查算功能，使得半圖解法可以在Excel 中輕松實現，從而快速高效地解決水庫調洪計算問題。使用者只要擁有一臺安裝有Office 軟件的電腦，在打開的Excel表格中完成相應公式函數輸入，將調洪計算需要的資料數據錄入表格中相應位置就可完成調洪計算任務。編制好的調洪計算表格只需再更換一下水位庫容資料、溢洪道寬度及流量系數數據、入庫洪水過程數據，就可以用于其他水庫的調洪計算。

1 半圖解法調洪計算基本原理和步驟

水庫的蓄泄方程由泄水建筑物型式決定，一般為堰流公式或閘口出流公式，出庫流量為堰頂水頭H或閘上下游水頭差H的函數，而水頭H取決于水庫水位Z，水位Z又與庫容V成單值函數關系，因此下泄流量q可以轉化為庫容V的單值函數 q=f（V）。

在給定的時段內，水庫的水量平衡方程為

Q1+Q22Δt-q1+q22Δt=V2-V1（1）

將水量平衡方程整理移項后可寫為

（V2Δt+q22）=Q1+Q22-q1+（V1Δt+q12）（2）

式中：Q1、Q2分別為計算時段初、末的入庫流量，m3/s;q1、q2分別為計算時段初、末的出庫流量，m3/s;V1、V2分別為計算時段初、末的水庫蓄水量，m3;Δt為計算時段長，s。

式（2）中等號右端項是已知項，左端項是未知項，兩個括號中都包含V/Δt和q/2，它們都是q的函數，進而可以建立q=f（VΔt+q2） 函數關系，并繪制出q—（VΔt+q2）輔助曲線。

調洪計算時入庫洪水過程Q—t、水位庫容曲線Z—V、泄洪建筑物型式及尺寸、水庫初始蓄水量V1和初始下泄流量q1、計算的時段Δt都是已知的，可以計算出式（2）等號右端各項的值。查輔助曲線就可得到時段末的下泄流量q2，進而得到時段末的蓄水量V2。將計算的時段末q2、V2值作為相鄰的下一時段的初值q1、V1，重復以上計算方法，逐時段進行計算、查圖就可得到整個下泄流量過程及水庫蓄水量的變化過程，再通過查水位庫容曲線就可得到水庫的水位變化過程。

根據以上半圖解法原理可知，調洪計算的核心步驟有4個：

（1）幾種曲線的繪制。根據水位庫容資料繪制水位庫容曲線Z—V;根據泄流公式得到水位與下泄流量關系曲線q—Z;結合水位庫容曲線將水位與下泄流量曲線轉化為下泄流量與庫容關系曲線q—V;結合給定的固定計算時段Δt和曲線q—V，計算出（VΔt+q2），并繪制輔助曲線q—（VΔt+q2）。

（2）時段末下泄流量q2查算。計算式（2）等號右端已知項的值Q1+Q22-q1+（V1Δt+q12），并將該值賦給（V2Δt+q22）;查輔助曲線，由（V2Δt+q22）的值查出對應的下泄流量q2。

（3）時段末庫容V2推求。 由（V2Δt+q22）的值、q2的值、固定時段Δt的值計算出V2。

（4）時段末水位Z2的查算。由V2的值查水位庫容曲線得到相應的Z2值。

2 Excel函數套用實現曲線查算功能

半圖解法步驟中數值計算都較為簡單，繁瑣的任務在于二維坐標系下曲線查算，需要根據水位庫容曲線、水位下泄流量曲線轉化而成的泄流量庫容曲線、單輔助曲線，逐時段查算相應的q2、V2、Z2。曲線查找不外乎是根據已知的x值，查函數值y=f（x）。Excel表格中有大量的內置函數，通過一些函數的套用，可以實現這些查找、計算功能，使繁瑣的人工查算由計算機替代，從而高效快捷地完成調洪計算。

為了實現平面直角坐標系下曲線的查找功能，需要在Excel表格中將輔助曲線、下泄流量與庫容曲線細分成若干區間段（離散化），每一區間段內的曲線可以用直線近似代替，這樣已知x值，先判斷x值所處的橫坐標處于哪個區間段，然后在該區間段內利用線性內插函數就可以求出y值。

在Excel軟件中線性內插函數命令為trend， 其功能是根據已知的y系列數組和x系列數組，得到以y為因變量、x為自變量的線性回歸方程，并由新的x值代入回歸方程中求取新的y值，其語法為：trend（known_ys，known_xs，new_xs）。其中：known_ys 為已知的y數組系列;known_xs是已知的x數組系列;new_xs是新的x值，由x值代入線性方程可求出待插值y。該函數返回值為y=y1+y2-y1x2-x1（x-x1）。例如在Excel某單元格中輸入公式=trend（{1，6}，{ 9}，7），返回值為4.33，相當于已知y1、y2分別為1和6，x1、x2分別為3和9。換句話說，已知直線的兩個點的坐標（xi，yi）分別為（ 1）、（9，6），則當x等于7 時，該直線上的y坐標值y=y1+y2-y1x2-x1（x-x1）=1+6-19-3（7-3）=4.33。

offset函數功能是以指定單元格為參照系，通過給定偏移量得到新的引用數組區域。該函數返回的引用可以是一個單元格或單元格區域。其語法為：offset（reference，rows，cols，[height]，[width]）。reference指的是作為偏移引用的參照系;rows為相對于參照單元格向下偏移的行數;cols為相對于參照單元格向右偏移的列數;height為所要引用的區域的行數;width為所要引用區域的列數，省略時視為與參照系的列數相同。例如在某單元格中輸入公式=offset（C 2， 2，1），返回的是位于單元格C3下方2行、右側3列、高度（行數）為2、寬度（列數）為1的單元區域（即F5：F6單元區域）。

match函數功能是在指定單元格區域內搜索指定項，返回該項在單元格區域中的相對排列位置。其語法為match（lookup_value，lookup_array，[match_type]）。其中：lookup_value為要在lookup_array區域中查找的值;lookup_array為要搜索的單元格區域;match_type為搜索的類型，取值為1，0，或-1。當match_type取1或省略時，要求lookup_array區域中的數值按升序排列，match函數會查找小于或等于lookup_value值的最大值并給出該最大值在lookup_array數組中的相對位置。例如在某單元格中輸入“=match（2.2，{ 2.1， 4}）”，則其返回值為3，表示在數組（ 2.1， 4）中小于等于2.2的最大值（為2.1）在該數組中的相對排序位置為3。

通過這3個函數的套用就可解決已知曲線的查算問題。例如某水庫的水位庫容曲線數據存放在Excel表格A、B兩列（見表1），在表2的H列中輸入新的水位系列值，想要通過A、B兩列中的水位庫容數據線性內插出H列水位對應的庫容值，并將結果存放在表2的J列對應的位置上（比如H4單元格的水位Z為571.9 m，其對應的庫容值放在J4單元格中），則只需在J4單元格輸入套用函數“=trend（offset（B$1，match（H4，A$2：A$100，1），0，2，1），offset（A$1，match（H4，A$2：A$100，1），0，2，1），H4）”，即可顯示庫容值為51.27，然后把該單元格中的公式粘貼到該列其他單元格中就可得到水位系列相應的庫容值。

3 Excel函數套用在半圖解法水庫調洪中的應用

已知某小（二）型水庫的水位庫容曲線、20 a一遇入庫洪水過程線，分別存放在同一張表格內A、B、C、D列中。水庫汛限水位為正常蓄水位570.90 m，與溢洪道堰頂高程齊平;泄洪建筑物為無閘門控制的寬頂堰，堰頂高程570.90 m，堰寬B=2 m，流量系數m=0.36，調洪計算采用等時段間隔長為1 h，這些數據分別存放在F列單元格$F$1：$F$5區域中。原始數據見表1。

（1）各種曲線繪制數據的生成 。在H列中產生水庫起調水位以上等高差的水位序列，在I列生成與H列對應的下泄流量，J列生成與H列水位對應的庫容值，K列生成（VΔt+q2）值。具體操作如下：在H2單元格輸入起調水位值570.90 m，H3單元格輸入公式“=H2+0.50”，然后選中H3單元格，鼠標放到該單元格右下角并下拉若干行，得到以0.50 m為間隔的遞增的水位序列;在I2單元格輸入堰流計算公式“=$F$4*$F$2*（2*9.8）^0.5*（H3-$F$3）^1.5”， 然后選中該單元格，鼠標放到該單元格右下角并下拉若干行，得到水位序列對應的下泄流量q值;在J2單元格輸入套用函數“=trend（offset（B$1，match（H2，A$2：A$100，1），0，2，1），offset（A$1，match（H2，A$2：A$100，1），0，2，1），H2）”， 然后選中該單元格，鼠標放到該單元格右下角并下拉若干行，可得到不同水位Z（或不同下泄流量q）對應的庫容V。在K2單元格輸入公式“=J2/（0.36*$F$5）+I2/2”， 然后選中該單元格，鼠標放到該單元格右下角并下拉若干行，可得到不同下泄流量q對應的（VΔt+q2）。生成的各種曲線數據成果見表2。

（2）調洪計算表格編制。半圖解法調洪計算表格中包含等時段離散化的時段值、逐時段入庫洪水流量值Q、時段平均入庫流量Q-、逐時段下泄流量q、逐時段末（V2Δt+q22）值、式（2）等號右端項Q1+Q22-q1+（V1Δt+q12）值、逐時段末的水庫蓄水量值V2和對應水位值Z2。本文中依次將其放入Excel表格工作簿M、N、O、P、Q、R、S、T列中相應位置。

當水庫洪水過程是不等時段間隔時，難以繪制輔助曲線，不能應用半圖解法;或者即使是等時段間隔洪水過程，但時段長Δt較大，調洪時可能會錯過最大下泄流量與入庫洪水過程線的交點，不能給出準確的壩前最高水位。為了解決這兩種情況下遇到的問題，需要將入庫洪水轉化為等時段間距的洪水過程線，時段長度在單元格$F$5中給出，并可根據需要由使用者隨時調整，在M、N兩列中完成該功能。在M3單元格輸入0，表示從0時刻（第1時段初）開始，M4單元格輸入公式“=M3+$F$5”，然后選中該M4單元格，鼠標放到該單元格右下角并下拉若干行，完成按等時段長遞增的時間序列;在N3單元格輸入函數套用公式“=trend（offset（$D$1，match（M C$2：C$100），0，2），offset（$C$1，match（M C$2：C$100），0，2），M3）”，然后選中該N3單元格，鼠標放到該單元格右下角并下拉若干行，即可求出給定洪水過程的等時段間隔內插出來的流量值。

O列中為計算平均入庫流量，即N列中上下相鄰兩個單元格的平均值，從O4單元格開始錄入公式“=（N3+N4）/2”，在該列中其余各行粘貼O4單元格中的公式，就可得到各時段平均入庫流量。

P列、Q列分別為下泄流量q及其對應的輔助線值（VΔt+q2），其中P列中的值由同行的Q列值查算得到（即由q—（VΔt+q2）輔助曲線的已知橫坐標（VΔt+q2）值查求曲線的縱坐標q值）。在單元格P3中為調洪計算開始時的第一個時段初的下泄流量（為已知值），本例中P3單元格“=$I$2”，即汛限水位對應的下泄流量值，在P4單元格中輸入套用函數“=trend（OFFSET（I$1，match（Q4，K$2：K$100），0，2，1），offset（K$1，match（Q4，K$2：K$100），0，2，1），Q4）”，相當于在該單元格完成輔助曲線查找功能，將該單元格公式復制粘貼到該列P4以下其他單元格中就完成了由Q列的（V2Δt+q22）值查輔助曲線得到q2值的任務。Q列存放（VΔt+q2）的值，R列存放Q1+Q22-q1+（V1Δt+q12）的值，根據式（2）得知這兩列數值是相等的，但前者要比后者錯后一個單元格（一個時段）。在Q3單元格輸入已知值（V1Δt+q12）=$K$2，即汛限水位對應的V1Δt+q12值，在R3單元格中輸入“=O4-P3+Q3”，相當于計算Q1+Q22-q1+（V1Δt+q12）的值，并將該值賦給Q4單元格，即在Q4單元格中輸入“=R3”，表示水量平衡方程式（V2Δt+q22）=Q1+Q22-q1+（V1Δt+q12）。將Q4單元格公式復制粘貼到Q列中Q4單元格以下其余行中，R3單元格中公式復制粘貼到R列中R3單元格以下其余行中，就完成了已知入庫洪水過程對應的下泄流量計算。

S列存放時段末庫容值、T列存放時段末水位值。根據已經求得的（V2Δt+q22）值（位于Q列）和q2值（位于P列），很容易求得V2=[（V2Δt+q22）-q22]×Δt;水位由水位庫容曲線查算得到。在Excel中具體實現方法是：在S3單元格中輸入“=（Q3-P3/2）*（0.36*$F$5）”，并下拉到該列其他單元格中完成公式復制，得出各時段末的庫容值;在T3單元格中輸入套用函數“=trend（offset（$A$1，match（S4，B$2：B$100），0，2，1），offset（$B$1，match（S4，B$2：B$100），0，2，1），S4）”，并下拉到該列其他單元格中完成公式復制，得到各時段末的水位值。

至此就完成了半圖解法水庫的調洪計算表格編制和計算。按以上步驟完成單元格數據及公式輸入后，幾乎在同時就出現調洪計算結果，程序運行時間不超過1 s。表3為本例中時段長為1 h的調洪計算成果。從表1中看出，在給定的入庫洪水下，在第5時段末水庫達到最高庫水位573.09 m，最大下泄流量為10.35 m3/s。但最大下泄流量與該時段末的入庫流量8.00 m3/s并不相等，相對差22.7%。可見以固定1 h間隔時段長進行調洪計算時，最高洪水位出現時刻入庫流量與下泄流量不相等，成果不盡合理，主要原因是計算時段間隔較大，造成一定的誤差。根據這個調洪結果可以判斷出水庫最高水位理論上應該在第4 h和第5 h之間出現。

為了得到較為精確的最高洪水位出現時刻及其對應的最高洪水位，將單元格F5中的計算時段間隔由1 h改為0.45 h，程序會自動重新計算（VΔt+q2）值，得到新的調洪計算結果，見表4。從調洪結果中可以看出，計算時段縮短為0.45 h后，在4.5 h時入庫流量和出庫流量分別為10.50、10.43 m3/s，已經非常接近，二者相差不足1%，可以認為二者近似相等，水庫最高水位為573.10 m。該時段的調洪得到壩前最高水位和時段為1 h的調洪最高壩前水位相差1 cm，最大下泄流量相差0.08 m3/s，雖然和時段為1 h的調洪計算成果相差不大，但其結果解釋起來更合理。

4 結 語

利用Excel函數套用編制的半圖解法水庫調洪計算程序，不需要掌握復雜的編程語言和程序語句，只需要trend、oeeset、match三個函數簡單套用，按本文中的步驟建立好各單元格計算的公式，就可很快得到調洪計算結果。編制好的調洪計算表格程序可以用于其他水庫的調洪，只需在表格A、B兩列中更換水位、庫容數據，C、D兩列更換入庫洪水過程數據，F列F1～F5中依次更換汛限水位、溢洪道寬度、堰頂高程、流量系數、擬采用的計算時段間隔長度，在不到1 s的時間內就可得到水庫調洪計算結果。該方法計算效率很高，程序的通用性強，適合于溢洪道上無閘門控制且以堰頂高程為水庫起調水位的水庫調洪計算。當溢洪道上有閘門控制出流時，同一水位下可以有不同的閘門開啟度，因而可以有多個下泄流量對應某一水位，水位泄流曲線不是單值函數關系，要想使用半圖解法，必須事先擬定好調洪調度方式，確定各水位下對應的唯一的下泄流量，得到水位與下泄流量的關系曲線，羅列于H列、I列對應位置，就可得到調洪結果。

參考文獻：

[1] 雒文生，宋星原.工程水文及水利計算[M].2版.北京：中國水利水電出版社，2010：265-271.

[2] 郭世興，王光社.水庫調洪計算方法的應用研究[J].人民黃河，2016，38（7）：24-26.

[3] 顏婷莉，鐘平安，劉偉莉.水庫調洪演算方法比較與改進[J].水力發電，2007，33（3）：26-28.

[4] 廖小龍，陳俊賢，朱毅峰.一種簡單實用的水庫調洪數值-解析法[J].水力發電學報，2014，33（5）：44-47.

[5] 黃啟有.單輔助線法在水庫調洪計算中的應用研究[J].人民珠江，2018，39（3）：54-56.

[6] 雷靜思，郭純青.VB程序與Excel結合在水庫調洪演算中的應用[J].廣西水利水電，2015（4）：48-51.

[7] 安航永，趙文龍，張雅萍.基于半圖解法的MATLAB程序和Excel在水庫調洪演算中的聯合運用[J].陜西水利，2019（4）：69-71，76.

【責任編輯 許立新】