(完整版)Excel繪制海森機(jī)率格紙

1、Excel繪制海森機(jī)率格紙注：現(xiàn)成的Excel文檔，在這里。本文主要介紹利用Excel內(nèi)置函數(shù)和圖表功能繪制海森機(jī)率格紙的方法。水文頻率計(jì)算(適線法)中，采用的海森機(jī)率格紙應(yīng)用的是一種特殊的坐標(biāo)系統(tǒng)一一縱坐標(biāo)為等間距 數(shù)學(xué)坐標(biāo)，橫坐標(biāo)為與頻率值(下側(cè)概率)的標(biāo)準(zhǔn)正態(tài)分布分位數(shù)有關(guān)。標(biāo)準(zhǔn)正態(tài)分布分位數(shù)在？?= 50%處為零，而海森機(jī)率格紙?jiān)?？?= 0.01%時(shí)橫坐標(biāo)為零。因此，海森機(jī)率格紙橫坐標(biāo)值計(jì)算公式可表示為：?= ?- ?.01%其中，？?為海森機(jī)率格紙中的頻率 P對應(yīng)的橫坐標(biāo)值；？?內(nèi)頻率P對應(yīng)的標(biāo)準(zhǔn)正態(tài)分布分位數(shù)；？?).01%為頻率？?= 0.01%對應(yīng)的標(biāo)準(zhǔn)正態(tài)分布分位數(shù)。標(biāo)準(zhǔn)正

2、態(tài)分布分位數(shù)可用 Excel內(nèi)置函數(shù)NORM.S.INV(P)(返回標(biāo)準(zhǔn)正態(tài)累積分布函數(shù)的反函數(shù)，精度 ±3X10-7)直接計(jì)算。在Excel中繪制海森機(jī)率格紙的要點(diǎn)：海森機(jī)率格紙的橫向網(wǎng)格線(即縱坐標(biāo))均勻分布，可直接由Excel的圖表功能自動生成，而縱向網(wǎng)格線(橫坐標(biāo))需要向圖表中添加2或3個(gè)系列的XY散點(diǎn)圖來完成一一實(shí)際是使用該系列的值產(chǎn)生矩形波形圖，即利用該波形圖的“上升沿”和“下降沿”作為縱向網(wǎng)格線，而“高電平”和“低電平”部分與圖形 邊框線重合來實(shí)現(xiàn)的。下面以某站流量頻率計(jì)算為例，介紹海森機(jī)率格紙?jiān)贓xcel中的繪制方法。、數(shù)據(jù)準(zhǔn)備(1) .新建Excel工作簿一將工作表

3、【Sheet1】重命名為【流量機(jī)率格紙數(shù)據(jù)點(diǎn)】一按以下格式布局表 格：(1)合并A1:N1區(qū)域，并輸入標(biāo)題：“海森機(jī)率格紙參數(shù)配置”；(2) 合并A2:B2區(qū)域，并輸入欄目：“縱坐標(biāo)范圍”；(3) C2 中輸入欄目：“最小值”，D2中輸入?yún)?shù)：0;(4) F2中輸入欄目：“最大值”，G2中輸入?yún)?shù)：1000 ;(5)合并A3:D3區(qū)域，并輸入主列標(biāo)題：“機(jī)率格紙網(wǎng)格線”；(6) 在A4、B4、C4、D4中分別輸入子列標(biāo)題：“頻率”、“至P=50%處水平距離”、“X坐標(biāo)”、“丫坐標(biāo)”；(7) 按照(5)、(6)的操作在設(shè)置F3:K4的格式及參數(shù)，如圖1所示。ABCDE FGH1K1海森機(jī)率格紙參

4、數(shù)配置2縱坐標(biāo)范圍最小值0最大值10003機(jī)率格紙網(wǎng)格線機(jī)率格紙頻率、重現(xiàn)期刻度4頻率至P二5吃處 水平距離X坐標(biāo)丫坐標(biāo)頻率至P=5兆處 水平距離X坐標(biāo)Y坐標(biāo)1Y坐標(biāo)2重現(xiàn)期50.01-3.7190 0000 0.01-3.7190.000010001000060 01-3.7190.0001000f 0.05-3.2910.42801000200070 02-3.5400.1791000 1T o.i-3.0900.62901000100080.02-3.5400.1790 102-2.8780.SA10100050090.03-3.43202670 105-2.5761.143010002

5、00100 03-3.4320.2B710001-2.3261.39301000100圖1注：應(yīng)根據(jù)研究對象選擇恰當(dāng)?shù)目v坐標(biāo)最大值。(8) 算LP (如圖1所示)海森機(jī)率格紙中頻率P對應(yīng)的橫坐標(biāo)值LP的計(jì)算及填寫步驟如下：(1) 在【流量機(jī)率格紙數(shù)據(jù)點(diǎn)】的 A5、A6中分別輸入0.01 - A7、A8中分別輸入0.02 一依此類推，在A列后續(xù)單元格中輸入海森機(jī)率格紙的縱向網(wǎng)格線對應(yīng)的頻率值，直至在A233、A234中分別輸入99.99 ;(2)在【流量機(jī)率格紙數(shù)據(jù)點(diǎn)】的 B5中輸入“ =NORM.S.INV(A5%) ” 一選中B5 ,使用填充柄向下填 充至B234 ,則得到頻率P對應(yīng)的標(biāo)準(zhǔn)正

6、態(tài)分布分位數(shù)；(3)在【流量機(jī)率格紙數(shù)據(jù)點(diǎn)】的C5中輸入“ =B5-$B$5 ” 一選中C5,使用填充柄向下填至 C234 ,則得到海森機(jī)率格紙中頻率P對應(yīng)的橫坐標(biāo)值LP (即網(wǎng)格線的橫坐標(biāo)值)；(4) 在【流量機(jī)率格紙數(shù)據(jù)點(diǎn)】的 D5:D8中分別輸入“ 二$D$2 "、“二$G$2 "、“二$G$2 "、“二$D$2 ” 一 選中D5:D8 ,使用填充柄向下填充至D234 ,則得到海森機(jī)率格紙網(wǎng)格線的縱坐標(biāo)值。3.刻度計(jì)算按以下步驟進(jìn)行海森機(jī)率格紙頻率刻度的計(jì)算：(1)在【流量機(jī)率格紙數(shù)據(jù)點(diǎn)】的肝$5:$F$36 中分別輸入0.01 , 0.05 ,一依此類推，

7、在 F列后續(xù)單元格中輸入海森機(jī)率格紙頻率刻度對應(yīng)的頻率值，直至在 F36單元格中輸入99.99 ;(2)根據(jù)Lp的計(jì)算方法計(jì)算$G$5:$H$36中各單元格的值(即計(jì)算 X坐標(biāo)值)；(3) 在$I$5:$I$36各單元格中輸入0 (即Y1坐標(biāo)，以使曲線與 X軸重疊)；(4)在$J$5:$J$36各單元格中輸入1000 (即Y2坐標(biāo)，以使曲線與邊框線重疊)；(5) $K$5:$K$36各單元格中暫不填寫值，后續(xù)由程序按以下公式計(jì)算：重現(xiàn)期標(biāo)簽值由以下公式計(jì)算：1 一 ，P (P < 50%)N = P1(2)市(P > 50%)其中，N為重現(xiàn)期，P為【流量機(jī)率格紙數(shù)據(jù)點(diǎn)】的 $K$5

8、:$K$36中各單元格的值(頻率)。、繪制網(wǎng)格線在工作簿(不是工作表)中插入圖表并重命名為【流量頻率曲線】。1 .圖表樣式設(shè)置(1) 坐標(biāo)軸：僅顯示【主要縱坐標(biāo)軸】一【更多軸選項(xiàng)】:【線條】：顏色一一深灰色，寬度一一 0.5磅；【坐標(biāo)軸選項(xiàng)】：最小彳1 0：最大彳1 1000 ：單位一一(主要100 ,次要20);【數(shù)字】：小數(shù)位數(shù)一一0;(2)軸標(biāo)題：顯示【主要橫坐標(biāo)軸】和【主要縱坐標(biāo)軸】，標(biāo)題分別修改為：“頻率P(%) ”、“流量(單位)”(根據(jù)實(shí)際情況命名)；(3)圖表標(biāo)題：選擇【圖表上方】，并重命名為“海森機(jī)率格紙”(根據(jù)實(shí)際情況命名)；(4)網(wǎng)格線：選擇【主軸主要水平網(wǎng)格線】一【更多

9、網(wǎng)格線選項(xiàng)】線條】 ：顏色一一深灰色，寬度一一0.5磅；(5)圖例：選擇【無】。(5) 在圖表區(qū)右鍵一【設(shè)置圖表區(qū)域格式】顏色：黑色；線寬：0.5磅；(6)在繪圖區(qū)(即圖形區(qū)，選擇時(shí)邊界出現(xiàn)圓形控制點(diǎn))右鍵一【設(shè)置繪制區(qū)格式】一邊框：無線條繪圖區(qū)格式：邊框一一黑色，選擇合適的線型樣式、粗細(xì)，區(qū)域【無】；(2)縱坐標(biāo)軸格式：刻度最大值一一1800 ：刻度最小值一一0：主要刻度單位一一100 ,次要刻度單位一一20 ：字號一一6 (選擇縱坐標(biāo)軸刻度標(biāo)簽后設(shè)置字號)。注：應(yīng)根據(jù)研究對象選擇恰當(dāng)?shù)淖鴺?biāo)軸格式設(shè)置值；(3)橫向次要網(wǎng)格線格式：選擇合適的線型樣式、顏色與粗細(xì)；(4)橫坐標(biāo)軸格式：坐標(biāo)軸與刻

10、度線的標(biāo)簽一一無，刻度最大值一一7.438 ：刻度最小值一一0。在2.設(shè)置頻率刻度【添加】一【編輯數(shù)據(jù)(1)在圖表區(qū)右鍵一【選擇數(shù)據(jù)】一【選擇數(shù)據(jù)源】對話框一圖例項(xiàng)(系列) 系列】對話框：系列名稱：="頻率刻度"X軸系列值：=流量機(jī)率格紙數(shù)據(jù)點(diǎn)！$H$5:$H$36Y軸系列值：=流量機(jī)率格紙數(shù)據(jù)點(diǎn)！$I$5:$I$36一【確定】一【選擇數(shù)據(jù)源】對話框一【確定】(2) 在剛才添加的曲線(即與 X軸重疊的曲線，對應(yīng)于 "頻率刻度”的線條，選擇時(shí)會出現(xiàn)許多帶 4個(gè) 圓角點(diǎn)的方框一一即數(shù)據(jù)點(diǎn))上右鍵一【設(shè)置數(shù)據(jù)系列格式】一【線條】：【實(shí)線】；顏色：深灰色；寬度：0.5磅；

11、(3) 選擇數(shù)據(jù)標(biāo)簽(即“頻率刻度”的數(shù)據(jù)點(diǎn))右鍵一【設(shè)置數(shù)據(jù)標(biāo)簽格式】：【標(biāo)簽包括】：X值；【標(biāo)簽位置】：靠下；【對齊方式】：垂直對齊方式一一中部劇中，自定義角度一一-45度(以避免標(biāo)簽一一刻度重疊)；一【開始】選項(xiàng)卡一字號：6;(4) 置重現(xiàn)期刻度(1) 在圖表區(qū)右鍵一【選擇數(shù)據(jù)】一【選擇數(shù)據(jù)源】對話框一圖例項(xiàng)(系列)【添加】一【編輯數(shù)據(jù)系列】對話框：系列名稱：="重現(xiàn)期刻度"X軸系列值：=流量機(jī)率格紙數(shù)據(jù)點(diǎn)！$H$5:$H$36Y軸系列值：=流量機(jī)率格紙數(shù)據(jù)點(diǎn)！$J$5:$J$36一【確定】(2) 在剛才添加的曲線(即與繪圖區(qū)上邊框線重疊的曲線，對應(yīng)于 "重

12、現(xiàn)期刻度”的線條)上右鍵一【設(shè)置數(shù)據(jù)系列格式】一【線條】：【實(shí)線】；顏色：深灰色；寬度：0.5磅；(3) 選擇數(shù)據(jù)標(biāo)簽(即“頻率刻度”的數(shù)據(jù)點(diǎn))右鍵一【設(shè)置數(shù)據(jù)標(biāo)簽格式】：【標(biāo)簽包括】：X值；【標(biāo)簽位置】：靠上；【對齊方式】：垂直對齊方式一一中部劇中，自定義角度一一-45度；一【開始】選項(xiàng)卡一字號：6;4 .繪制網(wǎng)格線(1) 在圖表區(qū)右鍵一【選擇數(shù)據(jù)】一【選擇數(shù)據(jù)源】對話框一圖例項(xiàng)(系列)【添加】一【編輯數(shù)據(jù)系列】對話框：系列名稱：="縱向網(wǎng)格線”X軸系列值：=流量機(jī)率格紙數(shù)據(jù)點(diǎn)！$C$5:$C$234Y軸系列值：=流量機(jī)率格紙數(shù)據(jù)點(diǎn)！$D$5:$D$234一【確定】(2) 在剛才添

13、加的曲線(對應(yīng)于 "縱向網(wǎng)格線”的線條)上右鍵一【設(shè)置數(shù)據(jù)系列格式】一【線條】【實(shí)線】；顏色：深灰色；寬度：0.5磅；(3)【設(shè)計(jì)】選項(xiàng)卡一【數(shù)據(jù)標(biāo)簽】：無；5 .修改刻度值打開VBE環(huán)境(快捷鍵【Alt】+【F11，或【開發(fā)工具】選項(xiàng)卡一【 Visual Basic ,若沒有啟用【開發(fā)工具】 選項(xiàng)卡，則需要打開Excel【選項(xiàng)】一【自定義功能區(qū)】一【主選項(xiàng)卡】一勾選【開發(fā)工具】即可)，添加如下代碼：Private Sub Chart_Activate()Dim i%, k# 一For i = 5 To 36k = Sheet1.Cells(i, "F")If i

14、 < 21 Then k = 100 / k Else k = 1 / (1 - k / 100)'1/P%If k - Int(k) > 0 ThenSheet1.Cells(i, "K") = Format(k, "0.#0") ElseSheet1.Cells(i, "K") = Format(k, "0") End If NextFor i = 1 To SeriesCollection(2).DataLabels.CountSeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange.Characters.TextSheet1.Cells(i + 4, "F")SeriesCollection(2).DataLabels(i).Format.TextFrame2.TextRange.Characters.TextSheet1.Cells(i