Excel繪制海森機率格紙

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

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

3、heet1】重命名為【流量機率格紙數(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坐標”、“Y坐標”；(7) 按照(5)、(6)的操作在設置F3:K4的格式及參數(shù)，如Error! Reference source not

4、found.所示。圖1注：應根據(jù)研究對象選擇恰當?shù)目v坐標最大值。2計算LP（如Error! Reference source not found.所示）海森機率格紙中頻率P對應的橫坐標值LP的計算及填寫步驟如下：(1) 在【流量機率格紙數(shù)據(jù)點】的A5、A6中分別輸入0.01A7、A8中分別輸入0.02依此類推，在A列后續(xù)單元格中輸入海森機率格紙的縱向網(wǎng)格線對應的頻率值，直至在A233、A234中分別輸入99.99；(2) 在【流量機率格紙數(shù)據(jù)點】的B5中輸入“=NORM.S.INV(A5%)”選中B5，使用填充柄向下填充至B234，則得到頻率P對應的標準正態(tài)分布分位數(shù)；(3) 在【流量機率格紙

5、數(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ù)點】的$F$5:$F$36中分別輸入0.01，0.05，依此類推，在F列后續(xù)單元格中輸入海森機率格紙頻率刻度對應的頻率值，直至在F36單元格中輸入99.99；(2) 根據(jù)LP

6、的計算方法計算$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)期標簽值由以下公式計算：N=&1P (P50%)&11-P (P50%)(2)其中，N為重現(xiàn)期，P為【流量機率格紙數(shù)據(jù)點】的$K$5:$K$36中各單元格的值（頻率）。二、繪制網(wǎng)格線在工作簿（不是工作表）中插入圖表并重命名為【流量頻率曲線】。1圖表樣式設置(1)

7、坐標軸：僅顯示【主要縱坐標軸】【更多軸選項】：【線條】：顏色深灰色，寬度0.5磅；【坐標軸選項】：最小值0，最大值1000，單位（主要100，次要20）；【數(shù)字】：小數(shù)位數(shù)0；(2) 軸標題：顯示【主要橫坐標軸】和【主要縱坐標軸】，標題分別修改為：“頻率P(%)”、“流量(單位)”（根據(jù)實際情況命名）；(3) 圖表標題：選擇【圖表上方】，并重命名為“海森機率格紙”（根據(jù)實際情況命名）；(4) 網(wǎng)格線：選擇【主軸主要水平網(wǎng)格線】【更多網(wǎng)格線選項】【線條】：顏色深灰色，寬度0.5磅；(5) 圖例：選擇【無】。(5) 在圖表區(qū)右鍵【設置圖表區(qū)域格式】顏色：黑色；線寬：0.5磅；(6) 在繪圖區(qū)（即圖

8、形區(qū)，選擇時邊界出現(xiàn)圓形控制點）右鍵【設置繪制區(qū)格式】邊框：無線條繪圖區(qū)格式：邊框黑色，選擇合適的線型樣式、粗細，區(qū)域【無】；(2) 縱坐標軸格式：刻度最大值1800，刻度最小值0，主要刻度單位100，次要刻度單位20，字號6（選擇縱坐標軸刻度標簽后設置字號）。注：應根據(jù)研究對象選擇恰當?shù)淖鴺溯S格式設置值；(3) 橫向次要網(wǎng)格線格式：選擇合適的線型樣式、顏色與粗細；(4) 橫坐標軸格式：坐標軸與刻度線的標簽無，刻度最大值7.438，刻度最小值0。在2設置頻率刻度(1) 在圖表區(qū)右鍵【選擇數(shù)據(jù)】【選擇數(shù)據(jù)源】對話框圖例項（系列）【添加】【編輯數(shù)據(jù)系列】對話框：系列名稱：="頻率刻度&q

9、uot;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磅；(3) 選擇數(shù)據(jù)標簽（即“頻率刻度”的數(shù)據(jù)點）右鍵【設置數(shù)據(jù)標簽格式】：【標簽包括】：X值；【標簽位置】：靠下；【對齊方式】：垂直對齊方式中部劇中，自定義角度-45度（以避免標簽刻度重疊）；【開始】選項卡字號：6；3設置重現(xiàn)期刻度(1)

10、 在圖表區(qū)右鍵【選擇數(shù)據(jù)】【選擇數(shù)據(jù)源】對話框圖例項（系列）【添加】【編輯數(shù)據(jù)系列】對話框：系列名稱：="重現(xiàn)期刻度"X軸系列值：=流量機率格紙數(shù)據(jù)點!$H$5:$H$36Y軸系列值：=流量機率格紙數(shù)據(jù)點!$J$5:$J$36【確定】(2) 在剛才添加的曲線（即與繪圖區(qū)上邊框線重疊的曲線，對應于"重現(xiàn)期刻度"的線條）上右鍵【設置數(shù)據(jù)系列格式】【線條】：【實線】；顏色：深灰色；寬度：0.5磅；(3) 選擇數(shù)據(jù)標簽（即“頻率刻度”的數(shù)據(jù)點）右鍵【設置數(shù)據(jù)標簽格式】：【標簽包括】：X值；【標簽位置】：靠上；【對齊方式】：垂直對齊方式中部劇中，自定義角度-45度

11、；【開始】選項卡字號：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) 在剛才添加的曲線（對應于"縱向網(wǎng)格線"的線條）上右鍵【設置數(shù)據(jù)系列格式】【線條】：【實線】；顏色：深灰色；寬度：0.5磅；(3) 【設計】選項卡【數(shù)據(jù)標簽】：無；5修改刻度值打開VBE環(huán)境（快捷鍵【Alt】+【F11】，或【開發(fā)工具】選項卡【Visual Basic】，

12、若沒有啟用【開發(fā)工具】選項卡，則需要打開Excel【選項】【自定義功能區(qū)】【主選項卡】勾選【開發(fā)工具】即可），添加如下代碼：Private Sub Chart_Activate() Dim i%, k# For i = 5 To 36 k = Sheet1.Cells(i, "F") If i < 21 Then k = 100 / k Else k = 1 / (1 - k / 100) '1/P% If k - Int(k) > 0 Then Sheet1.Cells(i, "K") = Format(k, "0.#0&

13、quot;) Else Sheet1.Cells(i, "K") = Format(k, "0") End If Next For i = 1 To SeriesCollection(2).DataLabels.Count SeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange.Characters.Text = Sheet1.Cells(i + 4, "F") SeriesCollection(2).DataLabels(i).Format.TextFrame2.TextRange.Characters