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

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

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

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

4、數(shù)配置2縱坐標范圍最小值0最大值10003機率格紙網(wǎng)格線機率格紙頻率、重現(xiàn)期刻度4頻率至P二5吃處 水平距離X坐標丫坐標頻率至P=5兆處 水平距離X坐標Y坐標1Y坐標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注：應根據(jù)研究對象選擇恰當?shù)目v坐標最大值。(8) 算LP (如圖1所示)海森機率格紙中頻率P對應的橫坐標值LP的計算及填寫步驟如下：(1) 在【流量機率格紙數(shù)據(jù)點】的 A5、A6中分別輸入0.01 - A7、A8中分別輸入0.02 一依此類推，在A列后續(xù)單元格中輸入海森機率格紙的縱向網(wǎng)格線對應的頻率值，直至在A233、A234中分別輸入99.99 ;(2)在【流量機率格紙數(shù)據(jù)點】的 B5中輸入“ =NORM.S.INV(A5%) ” 一選中B5 ,使用填充柄向下填 充至B234 ,則得到頻率P對應的標準正

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

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

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

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

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

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

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

13、加的曲線(對應于 "縱向網(wǎng)格線”的線條)上右鍵一【設置數(shù)據(jù)系列格式】一【線條】【實線】；顏色：深灰色；寬度：0.5磅；(3)【設計】選項卡一【數(shù)據(jù)標簽】：無；5 .修改刻度值打開VBE環(huán)境(快捷鍵【Alt】+【F11，或【開發(fā)工具】選項卡一【 Visual Basic ,若沒有啟用【開發(fā)工具】 選項卡，則需要打開Excel【選項】一【自定義功能區(qū)】一【主選項卡】一勾選【開發(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