基于Excel的半圖解法在水庫調(diào)洪計算中的應(yīng)用

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

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

關(guān)鍵詞：半圖解法;調(diào)洪計算;trend函數(shù);offset函數(shù);match函數(shù); Excel

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

doi：10.3969/j.issn.1000-1379.2021.11.014

引用格式：王新華，張慧穎，黃輝曦，等.基于Excel的半圖解法在水庫調(diào)洪計算中的應(yīng)用[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

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

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

1 半圖解法調(diào)洪計算基本原理和步驟

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

在給定的時段內(nèi)，水庫的水量平衡方程為

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的函數(shù)，進而可以建立q=f（VΔt+q2） 函數(shù)關(guān)系，并繪制出q—（VΔt+q2）輔助曲線。

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

根據(jù)以上半圖解法原理可知，調(diào)洪計算的核心步驟有4個：

（1）幾種曲線的繪制。根據(jù)水位庫容資料繪制水位庫容曲線Z—V;根據(jù)泄流公式得到水位與下泄流量關(guān)系曲線q—Z;結(jié)合水位庫容曲線將水位與下泄流量曲線轉(zhuǎn)化為下泄流量與庫容關(guān)系曲線q—V;結(jié)合給定的固定計算時段Δ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）的值查出對應(yīng)的下泄流量q2。

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

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

2 Excel函數(shù)套用實現(xiàn)曲線查算功能

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

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

在Excel軟件中線性內(nèi)插函數(shù)命令為trend， 其功能是根據(jù)已知的y系列數(shù)組和x系列數(shù)組，得到以y為因變量、x為自變量的線性回歸方程，并由新的x值代入回歸方程中求取新的y值，其語法為：trend（known_ys，known_xs，new_xs）。其中：known_ys 為已知的y數(shù)組系列;known_xs是已知的x數(shù)組系列;new_xs是新的x值，由x值代入線性方程可求出待插值y。該函數(shù)返回值為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函數(shù)功能是以指定單元格為參照系，通過給定偏移量得到新的引用數(shù)組區(qū)域。該函數(shù)返回的引用可以是一個單元格或單元格區(qū)域。其語法為：offset（reference，rows，cols，[height]，[width]）。reference指的是作為偏移引用的參照系;rows為相對于參照單元格向下偏移的行數(shù);cols為相對于參照單元格向右偏移的列數(shù);height為所要引用的區(qū)域的行數(shù);width為所要引用區(qū)域的列數(shù)，省略時視為與參照系的列數(shù)相同。例如在某單元格中輸入公式=offset（C 2， 2，1），返回的是位于單元格C3下方2行、右側(cè)3列、高度（行數(shù)）為2、寬度（列數(shù)）為1的單元區(qū)域（即F5：F6單元區(qū)域）。

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

通過這3個函數(shù)的套用就可解決已知曲線的查算問題。例如某水庫的水位庫容曲線數(shù)據(jù)存放在Excel表格A、B兩列（見表1），在表2的H列中輸入新的水位系列值，想要通過A、B兩列中的水位庫容數(shù)據(jù)線性內(nèi)插出H列水位對應(yīng)的庫容值，并將結(jié)果存放在表2的J列對應(yīng)的位置上（比如H4單元格的水位Z為571.9 m，其對應(yīng)的庫容值放在J4單元格中），則只需在J4單元格輸入套用函數(shù)“=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，然后把該單元格中的公式粘貼到該列其他單元格中就可得到水位系列相應(yīng)的庫容值。

3 Excel函數(shù)套用在半圖解法水庫調(diào)洪中的應(yīng)用

已知某?。ǘ┬退畮斓乃粠烊萸€、20 a一遇入庫洪水過程線，分別存放在同一張表格內(nèi)A、B、C、D列中。水庫汛限水位為正常蓄水位570.90 m，與溢洪道堰頂高程齊平;泄洪建筑物為無閘門控制的寬頂堰，堰頂高程570.90 m，堰寬B=2 m，流量系數(shù)m=0.36，調(diào)洪計算采用等時段間隔長為1 h，這些數(shù)據(jù)分別存放在F列單元格$F$1：$F$5區(qū)域中。原始數(shù)據(jù)見表1。

（1）各種曲線繪制數(shù)據(jù)的生成 。在H列中產(chǎn)生水庫起調(diào)水位以上等高差的水位序列，在I列生成與H列對應(yīng)的下泄流量，J列生成與H列水位對應(yīng)的庫容值，K列生成（VΔt+q2）值。具體操作如下：在H2單元格輸入起調(diào)水位值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”， 然后選中該單元格，鼠標放到該單元格右下角并下拉若干行，得到水位序列對應(yīng)的下泄流量q值;在J2單元格輸入套用函數(shù)“=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）對應(yīng)的庫容V。在K2單元格輸入公式“=J2/（0.36*$F$5）+I2/2”， 然后選中該單元格，鼠標放到該單元格右下角并下拉若干行，可得到不同下泄流量q對應(yīng)的（VΔt+q2）。生成的各種曲線數(shù)據(jù)成果見表2。

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

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

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

P列、Q列分別為下泄流量q及其對應(yīng)的輔助線值（VΔt+q2），其中P列中的值由同行的Q列值查算得到（即由q—（VΔt+q2）輔助曲線的已知橫坐標（VΔt+q2）值查求曲線的縱坐標q值）。在單元格P3中為調(diào)洪計算開始時的第一個時段初的下泄流量（為已知值），本例中P3單元格“=$I$2”，即汛限水位對應(yīng)的下泄流量值，在P4單元格中輸入套用函數(shù)“=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）”，相當于在該單元格完成輔助曲線查找功能，將該單元格公式復(fù)制粘貼到該列P4以下其他單元格中就完成了由Q列的（V2Δt+q22）值查輔助曲線得到q2值的任務(wù)。Q列存放（VΔt+q2）的值，R列存放Q1+Q22-q1+（V1Δt+q12）的值，根據(jù)式（2）得知這兩列數(shù)值是相等的，但前者要比后者錯后一個單元格（一個時段）。在Q3單元格輸入已知值（V1Δt+q12）=$K$2，即汛限水位對應(yīng)的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單元格公式復(fù)制粘貼到Q列中Q4單元格以下其余行中，R3單元格中公式復(fù)制粘貼到R列中R3單元格以下其余行中，就完成了已知入庫洪水過程對應(yīng)的下泄流量計算。

S列存放時段末庫容值、T列存放時段末水位值。根據(jù)已經(jīng)求得的（V2Δt+q22）值（位于Q列）和q2值（位于P列），很容易求得V2=[（V2Δt+q22）-q22]×Δt;水位由水位庫容曲線查算得到。在Excel中具體實現(xiàn)方法是：在S3單元格中輸入“=（Q3-P3/2）*（0.36*$F$5）”，并下拉到該列其他單元格中完成公式復(fù)制，得出各時段末的庫容值;在T3單元格中輸入套用函數(shù)“=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）”，并下拉到該列其他單元格中完成公式復(fù)制，得到各時段末的水位值。

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

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

4 結(jié) 語

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

參考文獻：

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

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

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

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

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

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

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

【責任編輯 許立新】