Excel在水文頻率分析中的應(yīng)用

王新華，郭美華，張慧穎

(云南農(nóng)業(yè)大學(xué)水利學(xué)院，云南 昆明 650201)

1 排序、計(jì)算經(jīng)驗(yàn)頻率和樣本統(tǒng)計(jì)參數(shù)

配線前首先需要對(duì)水文樣本數(shù)據(jù)從大到小進(jìn)行排序，計(jì)算經(jīng)驗(yàn)頻率和樣本統(tǒng)計(jì)參數(shù)均值、Cv和Cs。將原始數(shù)據(jù)洪峰流量數(shù)據(jù)輸入到Excel工作薄B2∶B25單元格中，C2∶C25區(qū)域填寫從大到小的序號(hào)。在D2單元格輸入公式=large($B$2:$B$25，C2)，然后復(fù)制此單元格的公式并將它粘貼到D3∶D25單元格區(qū)域，完成數(shù)據(jù)的從到小的排序；在E2單元格輸入公式=C2/(COUNT($B$2:$B$25)+1)，并粘貼該單元格公式至E3∶E25區(qū)域，完成經(jīng)驗(yàn)頻率計(jì)算。在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 計(jì)算各頻率對(duì)應(yīng)的水文變量理論值

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

3 海森頻率格紙、經(jīng)驗(yàn)頻率和理論頻率曲線點(diǎn)繪

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

表1 某站年降水量頻率計(jì)算分析表

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

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

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

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

圖2 某站年降水量配線圖

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

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

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

在目估適線法是目測(cè)理論頻率點(diǎn)和經(jīng)驗(yàn)頻率點(diǎn)的擬合情況，調(diào)整統(tǒng)計(jì)參數(shù)，重新配線，直到認(rèn)為滿意為止，該方法的缺點(diǎn)是缺乏客觀標(biāo)準(zhǔn)，成果因人而異。

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

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

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

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

5 結(jié)語(yǔ)

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