用EXCEL進行洪峰流量計算

 

一、前言

在水利工作中，經常需要計算設計洪水的洪峰流量，如水庫的調洪演算、防洪堤的高度、攔沙壩的大小等等許許多多方面，都要知道洪峰流量的大小，推求洪峰流量一般有4種方法，一種是根據流量資料，通過頻率的分析計算來求出設計洪水的洪峰流量；第二種是根據暴雨資料，通過頻率計算求出設計暴雨，再通過流域的產流和匯流計算，推求出設計洪水的洪峰流量；第三種是經驗公式法，利用簡化的經驗公式來估算設計洪水的洪峰流量；第四種是推理公式法。在缺乏資料的小流域內，利用推理公式是推求洪峰量的主要方法。

由推理公式計算設計洪峰流量，需要計算三個方程：

從以上的公式可看出，要求得洪峰流量Qm，必須求得Ψ和τ的值，而Ψ和τ互為參變，傳統的方法是通過圖解法和諾模圖法求解，圖解法需要畫圖，比較麻煩，諾模圖法需要查圖，容易出錯，精度也不高。還有沒有快捷而又精準的方法呢？答案是肯定的，這就是用EXCEL來計算洪峰流量。EXCEL是我們常用的軟體，其簡潔的界面、豐富的函數、可編程的宏語言常常使我們事半功倍，工作輕鬆而更有效率。下面就用推理公式法推求洪峰流量為例，介紹EXCEL在其中的應用。

二、計算方法

為使敘述清晰易懂，下面以用編輯好的表格為例，介紹在EXCEL表格中用推理公式計算洪峰流量的方法。見圖1。

　　　　　　　　　　　　圖1

首先，在1至10行輸入要用到的基本公式，目的是讓人一目了然，了解計算的來龍去脈，也便於以後的理解。

在14至18行輸入基本數據，包括流域面積、河流長度、河道平均坡降、暴雨衰減指數、流域中心最大24小時降雨量，其中暴雨衰減指數和最大24小時降雨量可以從水文手冊上查取，有條件的最好將之掃描下來，保存到表格文件的同一目錄下，在計算表格中用超級連結將之連結起來，今後查算就十分方便了，再也不用東翻西找，滑鼠一點超級連結就可查算。

第20至24行是計算雨力的值，最大24小時降雨量變差係數可由水文手冊上查取，偏差係數一般採用變差係數的3.5倍值，離均係數與設計頻率有關，可查表計算，將離均係數表全部錄入，放入另一張工作表中，見圖2：

 圖2

我們一般常用的頻率有5、10、20、30、50、100、200、300、500、1000年一遇，為實現離均係數自動查算，還需要動一番腦筋，離均係數表的橫列為頻率，縱列是Cs值，在離均係數表的N3和N4單元格中分別填入「=推理公式法!E21」、「=100/推理公式法!F22」，在N9單元格中填入：

「=INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(3,14,)),A:A,1),MATCH(INDIRECT(ADDRESS(4,14,)),2:2,1)))+(INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(3,14,)),A:A,1)+1,MATCH(INDIRECT(ADDRESS(4,14,)),2:2,1)))-INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(3,14,)),A:A,1),MATCH(INDIRECT(ADDRESS(4,14,)),2:2,1))))*(N3-INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(3,14,)),A:A,1),1)))/(INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(3,14,)),A:A,1)+1,1))-INDIRECT(ADDRESS(MATCH(INDIRECT(ADDRESS(3,14,)),A:A,1),1)))」，這個算式的作用是用提供的頻率和Cs值通過內插法得出離均係數的值，其中用到了INDIRECT、ADDRESS、MATCH三個函數，其用法可以參看EXCEL的幫助。

    第25行至28行是計算損失係數u值，用到了三個公式：

  

 

前面兩個公式來自《廣西中小河流設計洪水計算方法研究》，後一個公式來自《水保水文》。

    第29行是確定匯流係數m的值，可採用查表法計算，見表1： 

    匯流係數也可用公式法計算，對於湘江流域或者相似的流域，可用下列公式計算：

第30行是計算τ0的值，其計算公式為：

由於公式比較煩雜，以前將其做成諾模圖，用查圖法計算，比較麻煩，現在有了EXCEL，利用其強大的計算能力，將公式輸入，就可輕鬆計算出來了。

    第31至32行是求解二元方程組：

    　　　　　　τ=τ0Ψ-1/4-n

    　　　　　　Ψ＝1-u /Sp×τn

EXCEL不能計算方程組，但通過變通可解決此問題，把其中的一元經過代入，變為一元方程。將Ψ代入，公式變為：τ=τ0（1-u/Sp×τn）（-1/4-n），利用EXCEL提供的單變量求解工具可漂亮地得出其中一元τ的值，另一元ψ的值也不難解出。下面介紹具體操作方法：在工作表中的空白單元格中填入任意數值，本文是在單元格「B46」中填入「1」，在「B47」單元格中輸入公式「=E30*(1-E28/E24*B46^E17)^(-1/(4-E17))-B46」，單擊工具菜單欄，選擇單變量求解，彈出單變量求解窗口，目標單元格選擇「B47」，目標值填入「0」，可變單元格選擇「B46」，按確定按鈕，電腦通過迭代，非常快的解出答案。為便於操作，可以將單變量求解做成一個宏，在工作表中利用工具畫入一個名為「解方程」的按鈕，打開MICROSOFT　VISUAL　BASIC編輯器，在編輯框中輸入：

Private Sub CommandButton1_click()

    Sheets("推理公式法").Select

    Range("b47").GoalSeek　Goal:=0, ChangingCell:=Range("b46")

End Sub

以後求解方程只需按下「解方程」按鈕就行了。

最終，第33行是計算洪峰流量Qm，所有的參數都求出了值，將其一一代入方程 ，得出我們所需要的洪峰流量Qm的值。

這樣，用推理公式求解洪峰流量的表格就做好了，今後求洪峰流量，只需輸入幾個值，按一下按鈕，幾分鐘之內就可得出答案。

三、計算實例

表格完成後，以後進行洪峰流量的計算就十分簡單了。下面以計算實例來說明該表格的用法。首先在E14、E15、E16輸入流域面積、河流長度、河道平均坡降等基本數據，實例為流域面積為2.1km2,河流長度為1.05km，平均坡降為0.0204,暴雨衰減指數、流域中心最大24暴雨量和最大24降雨量變差係數Cv值可在各省的水文手冊上查取，本例為暴雨衰減指數為0.66, 流域中心最大24暴雨量為100, 最大24降雨量變差係數Cv值為0.4,如果要計算10年一遇的洪峰流量，就在F22單元格中輸入10,有了這些數據，表格自動計算出雨力Sp的值為52mm, 損失係數u的值為1.791,我們還需要輸入最後一個數據－匯流係數m，可從表1 匯流參數m查用表查得值為0.9,輸入E29單元格，最後我們要做的只需一點滑鼠，點擊解方程按鈕，就可快速地求出洪峰流量的值為50.765m3/s。

四、結論

本文用EXCEL進行洪峰流量計算，巧妙地運用了單變量求解工具來解決二元方程組，並用EXCEL函數來實現表格的自動查算，相對於傳統方法，快速而精準，一勞而永逸，極大地提高了工作效率。其實，在實際工作中，我們也會遇到許多相類似的問題，本文的目的在於拋磚引玉，聰明的你，一定會利用EXCEL強大的計算優勢，結合工程實際，解決更多的問題。