應(yīng)用Excel軟件實(shí)現(xiàn)海森概率格紙的繪制

鄧 歡，代俊峰，方榮杰，白凱華

(桂林理工大學(xué)環(huán)境科學(xué)與工程學(xué)院，廣西 桂林 541006)

從20世紀(jì)60年代中后期以來，我國水文頻率分析工作中一直采用皮爾遜Ⅲ型曲線(簡寫P-III型)[1]。SL44—2006《水利水電工程設(shè)計(jì)洪水計(jì)算規(guī)范》中規(guī)定水文現(xiàn)象的概率分布模型一般應(yīng)采用皮爾遜Ⅲ型分布模型[2]。P-III型分布模型的隨機(jī)變量與其超過制累積頻率呈非線性，水文頻率曲線繪在縱橫坐標(biāo)都為均勻分格的常規(guī)坐標(biāo)紙上呈“S”型，其兩端陡峭，即上部急劇上升，下部急劇下降，中間平緩，而曲線兩端正是工程設(shè)計(jì)頻率所用的部位，同時(shí)這也不便于采用目估適線法判斷其與經(jīng)驗(yàn)點(diǎn)據(jù)擬合的好壞程度。為了方便和精確繪制曲線，以及分析計(jì)算，需要設(shè)計(jì)一種坐標(biāo)紙，使頻率曲線繪在上面成直線或近似直線，這種坐標(biāo)紙稱為概率格紙。為了解決常規(guī)坐標(biāo)紙上頻率曲線兩端曲率大，曲線不便外延的問題，1913年美國水利工程師海森提出使用正態(tài)概率格紙，使正態(tài)分布頻率曲線繪在其上能成為直線，因此正態(tài)概率格紙也被稱為海森概率格紙[3]。若使P-III型分布曲線成直線，則需對每一個(gè)CS繪制一張專用概率格紙，比較繁瑣，因此也采用海森概率格紙。雖然P-III型頻率曲線繪在其上并不能成直線，但彎曲程度大為減緩近似直線，有利于曲線外延。

Excel軟件具有簡單易操作和兼容性強(qiáng)的特點(diǎn)，在水文水利計(jì)算中應(yīng)用廣泛，如在水能調(diào)節(jié)計(jì)算、渠均勻流水力計(jì)算和徑流調(diào)節(jié)計(jì)算等方面[4- 6]。同時(shí)Excel也是繪制海森概率格紙的常用工具[7- 9]。利用Excel并在其中嵌套VisualBasic語言程序也是當(dāng)前繪制水文頻率分析曲線的重要手段之一[10- 12]。本文通過理解超過制概率和分位數(shù)等在海森概率格紙中的含義，正確處理其在Excel中的函數(shù)表達(dá)，主要突出設(shè)計(jì)的原理和有Excel中繪制的技巧。

1 設(shè)計(jì)原理

按照水文習(xí)慣，以隨機(jī)變量值為縱坐標(biāo)，與之對應(yīng)的超過制累積頻率為橫坐標(biāo)，在常規(guī)坐標(biāo)紙上繪出標(biāo)準(zhǔn)正態(tài)分布曲線Q(x)，如圖1A所示。

(1)

式中，x—隨機(jī)變量值；Q(x)—標(biāo)準(zhǔn)化正態(tài)隨機(jī)變量X的超過制概率，也稱超過制累積頻率，水文上簡稱頻率，其值用P表示。

圖1 海森概率格紙?jiān)O(shè)計(jì)原理示意圖

海森概率格紙具有以下特點(diǎn)：

(1)縱坐標(biāo)沒有變化，仍是分格均勻的常規(guī)坐標(biāo)。

(2)橫坐標(biāo)分格關(guān)于P=50%處對稱，中間較密，向兩端逐漸變得稀疏，這是由于標(biāo)準(zhǔn)正態(tài)分布圖形是關(guān)于P=50%處對稱，中間彎曲程度小，向兩端彎曲程度逐漸變大導(dǎo)致的。

(3)由于P=0及P=100%的標(biāo)準(zhǔn)正態(tài)變量值分別趨于+∞和-∞，在圖上繪不出，同時(shí)水文頻率在變量最小值和最大值處是趨向于0和100%，而不能等于0和100%，因此海森概率格紙橫坐標(biāo)一般是從0.01%到99.99%。

2 橫坐標(biāo)取值規(guī)律

海森概率格紙橫坐標(biāo)之間的間距可用公式(1)的反函數(shù)值或上側(cè)分位數(shù)x來表示。由于P=50%對應(yīng)的x值為0，而海森概率格紙令橫坐標(biāo)為P=0.01%時(shí)為0，現(xiàn)以P=0.01%作為橫坐標(biāo)原點(diǎn)，那么P=50%距P=0.01%的距離可表示為Q-1(0.01%)-Q-1(50%)=3.719，P=99.99%距P=0.01%的距離為Q-1(0.01%)-Q-1(99.99%)=7.438。同理，其他任意頻率P的橫坐標(biāo)與P=0.01%的距離L為：

L=Q-1(0.01%)-Q-1(P)

(2)

對應(yīng)Excel內(nèi)置函數(shù)NORM.S.INV(probability)，就可表示為：

L= NORM.S.INV(1-0.01%)-NORM.S.INV(1-P)

(3)

海森概率格紙橫坐標(biāo)部分取值見表1中的系列③。

3 海森概率格紙的繪制方法

3.1 網(wǎng)格線的繪制

海森坐標(biāo)格紙縱坐標(biāo)是均勻分格的，則橫向網(wǎng)格線為均勻分布，可由Excel軟件的圖表功能自動生成，然后根據(jù)需求設(shè)置坐標(biāo)軸格式，縱坐標(biāo)最小值設(shè)置為0，最大值可據(jù)水文變量的最大值確定。橫坐標(biāo)分格不均勻，縱向網(wǎng)格線就無法通過Excel軟件自動生成，下面以圖2為例進(jìn)行縱向網(wǎng)格線的繪制，設(shè)置縱坐標(biāo)最大值為3000，各條縱向網(wǎng)格線上、下兩個(gè)端點(diǎn)的坐標(biāo)列于圖2中。

圖2 海森概率格紙示例圖

在Excel中新建sheet表，輸入表1的數(shù)據(jù)，①表示縱向網(wǎng)格線的頻率值，⑤和⑥表示縱向網(wǎng)格線下端點(diǎn)和上端點(diǎn)的x軸和y軸坐標(biāo)值。在工具條上點(diǎn)擊“插入”，選擇“圖表”中的“散點(diǎn)圖”，生成圖表區(qū)，選中圖表區(qū)單擊鼠標(biāo)右鍵，從彈出的快捷菜單中點(diǎn)擊“選擇數(shù)據(jù)”選項(xiàng)，在彈出的對話框中選擇“添加”，會彈出“編輯數(shù)據(jù)系列”對話框，用“x軸系列值”和“y軸系列值”文本框右邊的“拾取器”按鈕拾取所需數(shù)據(jù)，單擊“確定”按鈕，圖表中就添加17條縱向線段即縱向網(wǎng)格線。

表1 海森概率格紙縱向網(wǎng)格線端點(diǎn)坐標(biāo)

在添加數(shù)據(jù)時(shí)有兩種方法，一種如圖3A的連接方法，將每條網(wǎng)格線的數(shù)據(jù)當(dāng)作一個(gè)系列，共有17個(gè)系列數(shù)據(jù)，通過在同一圖表區(qū)“選擇數(shù)據(jù)”中逐個(gè)添加各系列，繪制出17條縱向線段。通常為了美觀，一般要繪制100多條縱向網(wǎng)格線，操作起來步驟多，且不利于后期圖形的修改；另一種方法是把全部網(wǎng)格線的端點(diǎn)數(shù)據(jù)當(dāng)作一個(gè)系列，在圖表區(qū)“選擇數(shù)據(jù)”中只需要添加一個(gè)系列，將各點(diǎn)順次連接起來，如圖3B的連接方法，這種方法操作和圖形修改都比較便利，至于橫線部分在繪橫向網(wǎng)格線或繪圖區(qū)邊框時(shí)會被覆蓋掉，不會影響后期添加水文頻率曲線。

圖3 縱向網(wǎng)格線連接方法

3.2 橫坐標(biāo)的標(biāo)注

由于Excel繪圖時(shí)默認(rèn)坐標(biāo)軸刻度是均勻等分的，無法直接實(shí)現(xiàn)制海森概率格紙橫坐標(biāo)頻率刻度和重現(xiàn)期刻度的標(biāo)注，因此要另外進(jìn)行標(biāo)注。在海森概率格紙橫坐標(biāo)上除了對頻率刻度要進(jìn)行標(biāo)注外還要對重現(xiàn)期刻度進(jìn)行標(biāo)注，重現(xiàn)期和頻率存在如下關(guān)系[15]：

(4)

在標(biāo)注時(shí)通常不需要將所有橫坐標(biāo)都標(biāo)注出來，而是選擇性地進(jìn)行標(biāo)注，常見標(biāo)注數(shù)據(jù)列于表2。表2中①表示的是要標(biāo)注的頻率值，系列③和④表示頻率值對應(yīng)的標(biāo)注位置，②表示的是要標(biāo)注的重現(xiàn)期值，系列③和⑤表示重現(xiàn)期對應(yīng)的標(biāo)注位置。

表2 海森概率格紙橫坐標(biāo)標(biāo)注數(shù)據(jù)表

將表2的數(shù)據(jù)輸入到Excel中，在繪制好網(wǎng)格線的同一圖表中單擊鼠標(biāo)右鍵，點(diǎn)擊“選擇數(shù)據(jù)”選項(xiàng)，再選擇“添加”，彈出“編輯數(shù)據(jù)系列”對話框，用“x軸系列值”和“y軸系列值”的“拾取器”按鈕拾取系列③和④的數(shù)據(jù)，單擊“確定”按鈕，圖中縱坐標(biāo)為0處就添加了一條直線，用相同的方法拾取系列③和⑤的數(shù)據(jù)，以圖中縱坐標(biāo)為3000處再添加一條直線。選中其中一條直線，單擊鼠標(biāo)右鍵，點(diǎn)擊“添加數(shù)據(jù)標(biāo)簽”，軟件會給這條線上每個(gè)數(shù)據(jù)點(diǎn)添加數(shù)據(jù)標(biāo)簽，選中數(shù)據(jù)標(biāo)簽，再單擊鼠標(biāo)右鍵，選擇“設(shè)置數(shù)據(jù)標(biāo)簽格式”，在彈出的對話框中根據(jù)需求修改“標(biāo)簽選項(xiàng)”，在“標(biāo)簽位置”選項(xiàng)中設(shè)置代表頻率的標(biāo)簽靠下，代表重現(xiàn)期的標(biāo)簽靠上。請注意在“標(biāo)簽包括”選項(xiàng)中無論選擇“x值”還是“y值”，發(fā)現(xiàn)所有的標(biāo)簽顯示的是④和⑤的值，并不是①和②對應(yīng)的值，因此要對顯示出的數(shù)據(jù)標(biāo)簽進(jìn)行修改。可以在某個(gè)數(shù)據(jù)標(biāo)簽上雙擊鼠標(biāo)選中這個(gè)數(shù)據(jù)標(biāo)簽，手工輸入對應(yīng)的顯示值或在編輯欄中輸入“=‘拾取某單元格的值’”，“拾取某單元格的值”是指在工作表中單擊鼠標(biāo)拾取要顯示的數(shù)據(jù)單元格，就可對各個(gè)數(shù)據(jù)標(biāo)簽進(jìn)行逐一修改。Excel2013以上版本還可以對全部標(biāo)簽數(shù)據(jù)進(jìn)行批量修改，操作步驟為：單擊選中頻率或重現(xiàn)期的全部標(biāo)簽數(shù)據(jù)，再單擊鼠標(biāo)右鍵，從彈出的快捷菜單中選擇“設(shè)置數(shù)據(jù)標(biāo)簽格式”選項(xiàng)，打開“標(biāo)簽選項(xiàng)”對話框，在“標(biāo)簽包括”中勾選“單元格中的值”，彈出“數(shù)據(jù)標(biāo)簽區(qū)域”，通過“選擇數(shù)據(jù)標(biāo)簽區(qū)域”文本框右邊的“拾取器”按鈕，在工作表中選中①列或②列的全部數(shù)據(jù)區(qū)域，單擊“確定”按鈕，所有數(shù)據(jù)標(biāo)簽就都進(jìn)行了修改，以此方法能快捷完成頻率和重現(xiàn)期的標(biāo)注。

4 結(jié)語

標(biāo)準(zhǔn)正態(tài)分布在常規(guī)坐標(biāo)紙上呈“S”型曲線，通過縱坐標(biāo)不變，過曲線兩端點(diǎn)連成直線，再將曲線上各點(diǎn)平移至該直線上，定出各點(diǎn)的橫坐標(biāo)位置，就形成了縱坐標(biāo)均勻分布，橫坐標(biāo)關(guān)于P=50%處對稱，越向兩側(cè)間距越大的海森概率格紙，P-III型頻率曲線繪制在海森概率格紙上有利于曲線外延。用超過制概率和上側(cè)分位數(shù)的概念刻畫了海森概率格紙橫坐標(biāo)的位置，并利用Excel軟件中的NORM.S.INV內(nèi)置函數(shù)定量給出了橫坐標(biāo)位置的表達(dá)式，同時(shí)利用其數(shù)據(jù)計(jì)算和圖表繪制功能強(qiáng)大的特點(diǎn)，解決了縱網(wǎng)格線繪制和橫坐標(biāo)標(biāo)注難題。Excel軟件操作方法簡單易懂、應(yīng)用性廣泛，通過圖表編輯可以方便快捷地輸出理想圖紙，易于進(jìn)行水文頻率曲線繪制和分析的教學(xué)活動以及工程實(shí)踐應(yīng)用。