Excel在水文頻率分析中的應用

王新華，郭美華，張慧穎

(云南農業(yè)大學水利學院，云南 昆明 650201)

1 排序、計算經驗頻率和樣本統(tǒng)計參數(shù)

配線前首先需要對水文樣本數(shù)據(jù)從大到小進行排序，計算經驗頻率和樣本統(tǒng)計參數(shù)均值、Cv和Cs。將原始數(shù)據(jù)洪峰流量數(shù)據(jù)輸入到Excel工作薄B2∶B25單元格中，C2∶C25區(qū)域填寫從大到小的序號。在D2單元格輸入公式=large($B$2:$B$25，C2)，然后復制此單元格的公式并將它粘貼到D3∶D25單元格區(qū)域，完成數(shù)據(jù)的從到小的排序；在E2單元格輸入公式=C2/(COUNT($B$2:$B$25)+1)，并粘貼該單元格公式至E3∶E25區(qū)域，完成經驗頻率計算。在F2單元格輸入公式=average($B$2:$B$25)得到系列的均值；在F3單元格輸入公式=stdev($B$2:$B$25)/average($B$2:$B$25)得到樣本系列的Cv值；在F4單元格輸入=2*F3，表示偏態(tài)系數(shù)Cs取值是Cv的2倍。

2 計算各頻率對應的水文變量理論值

為比較擬合程度，在單元格F5中輸入公式=SUMXMY2(G2∶G25，$D2:$D25)，該公式輸出的值為經驗點據(jù)和本次配線理論點據(jù)差值的平方和，值越大，說明擬合效果越差。

3 海森頻率格紙、經驗頻率和理論頻率曲線點繪

經驗頻率點和理論頻率點都需要將其繪制至海森頻率格紙。海森頻率格紙的縱坐標仍是普通分格，橫坐標則是以標準正態(tài)分布累積頻率p對應的分位數(shù)為基礎，把分位數(shù)對應的位置標注成頻率值。并以P=0.01%對應的分位數(shù)-3.72作為橫坐標起點(坐標原點，橫坐標標注成0.01%)，各累積頻率p對應分位數(shù)減去-3.72作為該頻率對應的橫坐標值，并把它標為相應的頻率p。例如p=1%對應的分位數(shù)為-2.33，因此它在海森格紙中橫坐標位置是-2.33-(-3.72)=1.39。Excel中標準正態(tài)分布的分位數(shù)用函數(shù)NORMSINV(P)來計算。在H2單元格中輸入公式=NORMSINV(E2)-NORMSINV(0.01%)，并復制粘貼到H3∶H25單元格中，就可得到E列各經驗頻率對應的橫坐標位置，見表1。

表1 某站年降水量頻率計算分析表

關于利用Excel制作海森機率格紙的文獻已有很多[2-5]，海森頻率格紙繪制主要步驟是①利用Excel中的帶直線散點圖制作功能，生成頻率格紙的縱橫垂直相交的網格圖。在表格中任意選相鄰的三列，比如N、O、P三列，用來生成海森頻率格紙的網格圖。在N列中給出從P=0.01%到P=99.99%依次增加的頻率p值(每個p要出現(xiàn)兩次，比如0.01%、0.01%、0.02%、0.02%、0.05%、0.05%、…、…)，在O列中公式輸入公式NORMSINV(p)-NORMSINV(0.01%)，以便求出前列頻率數(shù)據(jù)對應的橫坐標值，在P列有序給出頻率點對應的Y軸最小值和最大值，其順序見表2中P列示例，目的是為了使用O、P兩列數(shù)據(jù)形成的數(shù)據(jù)對繪圖時依次相連可形成相互垂直的折線。選中O、P列數(shù)據(jù)，點擊插入散點圖，并選擇帶帶直線的散點圖，則可生海森格紙的雛形。②添加橫坐標特征頻率刻度，完善海森頻率格紙圖。在Q、R、S、T列中相應列添加需要標注的頻率p值、頻率對應的橫坐標計算公式NORMSINV(p)-NORMSINV(0.01%)、Y軸下限值、Y軸上限值，在海森頻率格紙雛形圖中單擊右鍵、“選擇數(shù)據(jù)”、將R列數(shù)值區(qū)域作為X軸系列值、S列數(shù)字區(qū)域作為Y軸系列數(shù)據(jù)，點擊確定，就可把頻率格紙的橫坐標軸添加到圖中。然后選中已添加的線段，點擊右鍵，將數(shù)據(jù)標簽中的x值添加進去，并將x值(橫坐標值)修改成對應的頻率值，就可完成頻率格紙的繪制，制成的頻率格紙如圖1所示。

表2 海森頻率格紙制作數(shù)據(jù)表格

圖1 Excel制作的海森頻率格紙

在繪制好的海森頻率格紙中點擊鼠標右鍵，在出現(xiàn)的對話框中選中“添加數(shù)據(jù)”功能按鈕，在出現(xiàn)的數(shù)據(jù)源對話框中點擊“添加”，選中H2∶H25作為X軸系列值，選中D2∶D25作為軸系列值，系列名稱輸入“經驗頻率點據(jù)”，點擊對話框中的確定，即可將經驗頻率數(shù)據(jù)點繪到海森頻率格紙上，如圖2所示，圓點代表的就是經驗點據(jù)系列。

圖2 某站年降水量配線圖

再次添加數(shù)據(jù)，選中H2∶H25作為X軸系列值，選中G2∶G25作為Y軸系列值，系列名稱輸入“第一次配線”，點擊對話框中的確定，即可將第一次配線的理論頻率曲線繪制到海森頻率格紙上，見圖2中紅色破折線。

4 目估適線和優(yōu)化適線

一般情況下，第一次配線的理論數(shù)據(jù)點和經驗數(shù)據(jù)點擬合不是很完美，就本例而言，單元格F5顯示的離差平方和數(shù)據(jù)為34798，理論曲線上部小于經驗點擊較多，中部則高于經驗點據(jù)，需要調整統(tǒng)計參數(shù)，繪制一條擬合更好的理論頻率曲線。本例中，維持均值666.4mm不變，將Cv值增大到0.3，Cs=2.5Cv，并將這三個統(tǒng)計參數(shù)依次輸入到I2、I3、I4單元格中，復制G2單元格的公式，并將其粘貼到J2∶J25單元格區(qū)域，就可得到第二次配線計算的各頻率點的設計值，復制F4單元格中的公式到I7單元格，則I7單元格中顯示的數(shù)據(jù)就是第二次配線理論點據(jù)與經驗點據(jù)差值的平方和，顯示值27784，顯然比第一次配線的理論平方和34798要小，說明調整統(tǒng)計參數(shù)后擬合度有所改善。在海森頻率格紙上點擊鼠標右鍵，選中選擇數(shù)據(jù)源，在出現(xiàn)的對話框中點擊添加數(shù)據(jù)，選中H2∶H25作為X軸系列值，選中J2∶J25作為Y軸系列值，系列名稱輸入“第二次配線 均值666.4，Cv=0.3，Cs=0.75”，點擊對話框中的確定，即可將第二次配線的理論點據(jù)點繪到海森頻率格紙上。

在目估適線法是目測理論頻率點和經驗頻率點的擬合情況，調整統(tǒng)計參數(shù)，重新配線，直到認為滿意為止，該方法的缺點是缺乏客觀標準，成果因人而異。

為克服目估適線法的缺點，可采用優(yōu)化適線法利用計算機編程進行配線。優(yōu)化適線法采用離差平方和最小為原則，已有學者采用計算機編程方法來實現(xiàn)優(yōu)化適線配線[6-8]，這需要一定的編程能力。事實上Excel規(guī)劃求解工具完全可以完成優(yōu)化求解工作，無需進行復雜的編程。采用Excel中加載“規(guī)劃求解”工具欄，在K2、K3、K4，依次填入3個正數(shù)分別作為待優(yōu)化的均值、Cv、Cs,比如填寫1、2、3，然后在L2單元格中輸入=GAMMAINV(1-$E2，4/K$4^2，1)*K$2*K$3*K$4/2+K$2-2*K$2*K$3/K$4，并將其復制粘貼到L3∶L25區(qū)域中(也可以直接將G2單元格的公式直接粘貼到L2∶L25區(qū)域中)，得到一系列的理論頻率曲線縱坐標值；在K4單元格中輸入=SUMXMY2(L2∶L25，$D2∶$D25)，得到離差平方和，然后點擊工具欄“數(shù)據(jù)”選項卡下面的“規(guī)劃求解”按鈕，會彈出規(guī)劃求解對話框，如圖3所示，在設置目標中選中K5單元格，并選中最小值單選卡，可變單元格選取K2∶K4，然后點擊求解按鈕，則可變單元格中的數(shù)據(jù)將有最初值變?yōu)樽顑?yōu)值，本例中最優(yōu)值為均值673.8mm，Cv=0.29，Cs=1.09，離差平方和最小值為24006，顯然該值比前兩次配線的離差平方和都小，與經驗點據(jù)擬合最好。

圖3 Excel 規(guī)劃求解對話框

在海森頻率格紙上點擊鼠標右鍵，選中選擇數(shù)據(jù)源，在出現(xiàn)的對話框中點擊添加數(shù)據(jù)，選中H2∶H25作為X軸系列值，選中L2∶L25作為Y軸系列值，系列名稱輸入“優(yōu)化配線 均值673.8mm，Cv=0.29，Cs=1.09”，點擊對話框中的“確定”，即可將優(yōu)化配線的理論點據(jù)點繪到海森頻率格紙上。

從繪制的3條頻率曲線上可以看出，第一次配線和第二次配線的差別主要在于Cv值增大，理論頻率曲線上部抬高，下部降低，整體有順時針轉動的趨勢。優(yōu)化適線法的曲線和第二次配線的曲線非常貼近，說明目估適線法有一定的精度，基本可以滿足水文分析計算的需要。Excel中的規(guī)劃求解工具在不需要人工調整均值、Cv、Cs參數(shù)，只需任意給出三個統(tǒng)計參數(shù)的初值，在指定單元格區(qū)域中輸入包含Excel函數(shù)命令的理論頻率水文設計值計算公式、離差平方和計算公式，調用規(guī)劃求解工具，不到1秒就可得到最優(yōu)的統(tǒng)計參數(shù)值，比人工調整統(tǒng)計參數(shù)進行配線更快，且配線成果穩(wěn)定，不會因人而異，避免了使用目估適線選用最終頻率分析成果的迷茫性。

5 結語

使用Excel軟件中自帶的函數(shù)和規(guī)劃求解工具，可以很快的求出水文統(tǒng)計、繪制出水文頻率曲線，避免了手算過程中列表計算統(tǒng)計參數(shù)的繁瑣和配線過程中反復查算Ф值表、計算理論值、點繪經驗頻率點和理論頻率點的巨大工作量，大大提高了水文頻率分析的效率。