Excel绘制海森机率格纸

Excel绘制海森机率格纸注：现成的Excel文档，在这里。本文主要介绍利用Excel内置函数和图表功能绘制海森机率格纸的方法。水文频率计算（适线法）中，采用的海森机率格纸应用的是一种特殊的坐标系统——纵坐标为等间距数学坐标，横坐标为与频率值（下侧概率）的标准正态分布分位数有关。标准正态分布分位数在𝑃=50%处为零，而海森机率格纸在𝑃=0.01%时横坐标为零。因此，海森机率格纸横坐标值计算公式可表示为：𝐿𝑃=𝑈𝑃−𝑈0.01%(1)其中，𝐿𝑃为海森机率格纸中的频率P对应的横坐标值；𝑈𝑃为频率P对应的标准正态分布分位数；𝑈0.01%为频率𝑃=0.01%对应的标准正态分布分位数。标准正态分布分位数可用Excel内置函数NORM.S.INV(P)（返回标准正态累积分布函数的反函数，精度±3×10−7）直接计算。在Excel中绘制海森机率格纸的要点：海森机率格纸的横向网格线（即纵坐标）均匀分布，可直接由Excel的图表功能自动生成，而纵向网格线（横坐标）需要向图表中添加2或3个系列的XY散点图来完成——实际是使用该系列的值产生矩形波形图，即利用该波形图的“上升沿”和“下降沿”作为纵向网格线，而“高电平”和“低电平”部分与图形边框线重合来实现的。下面以某站流量频率计算为例，介绍海森机率格纸在Excel中的绘制方法。一、数据准备1．新建Excel工作簿→将工作表【Sheet1】重命名为【流量机率格纸数据点】→按以下格式布局表格：(1)合并A1:N1区域，并输入标题：“海森机率格纸参数配置”；(2)合并A2:B2区域，并输入栏目：“纵坐标范围”；(3)C2中输入栏目：“最小值”，D2中输入参数：0；(4)F2中输入栏目：“最大值”，G2中输入参数：1000；(5)合并A3:D3区域，并输入主列标题：“机率格纸网格线”；(6)在A4、B4、C4、D4中分别输入子列标题：“频率”、“至P=50%处水平距离”、“X坐标”、“Y坐标”；(7)按照(5)、(6)的操作在设置F3:K4的格式及参数，如图1所示。图1注：应根据研究对象选择恰当的纵坐标最大值。2．计算LP（如图1所示）海森机率格纸中频率P对应的横坐标值LP的计算及填写步骤如下：(1)在【流量机率格纸数据点】的A5、A6中分别输入0.01→A7、A8中分别输入0.02→依此类推，在A列后续单元格中输入海森机率格纸的纵向网格线对应的频率值，直至在A233、A234中分别输入99.99；第2页共3页(2)在【流量机率格纸数据点】的B5中输入“=NORM.S.INV(A5%)”→选中B5，使用填充柄向下填充至B234，则得到频率P对应的标准正态分布分位数；(3)在【流量机率格纸数据点】的C5中输入“=B5-$B$5”→选中C5，使用填充柄向下填至C234，则得到海森机率格纸中频率P对应的横坐标值LP（即网格线的横坐标值）；(4)在【流量机率格纸数据点】的D5:D8中分别输入“=$D$2”、“=$G$2”、“=$G$2”、“=$D$2”→选中D5:D8，使用填充柄向下填充至D234，则得到海森机率格纸网格线的纵坐标值。3．刻度计算按以下步骤进行海森机率格纸频率刻度的计算：(1)在【流量机率格纸数据点】的$F$5:$F$36中分别输入0.01，0.05，…→依此类推，在F列后续单元格中输入海森机率格纸频率刻度对应的频率值，直至在F36单元格中输入99.99；(2)根据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各单元格中暂不填写值，后续由程序按以下公式计算：重现期标签值由以下公式计算：N={1P(P≤50%)11−P(P≥50%)(2)其中，N为重现期，P为【流量机率格纸数据点】的$K$5:$K$36中各单元格的值（频率）。二、绘制网格线在工作簿（不是工作表）中插入图表并重命名为【流量频率曲线】。1．图表样式设置(1)坐标轴：仅显示【主要纵坐标轴】→【更多轴选项】：【线条】：颜色——深灰色，宽度——0.5磅；【坐标轴选项】：最小值——0，最大值——1000，单位——（主要100，次要20）；【数字】：小数位数——0；(2)轴标题：显示【主要横坐标轴】和【主要纵坐标轴】，标题分别修改为：“频率P(%)”、“流量(单位)”（根据实际情况命名）；(3)图表标题：选择【图表上方】，并重命名为“海森机率格纸”（根据实际情况命名）；(4)网格线：选择【主轴主要水平网格线】→【更多网格线选项】→【线条】：颜色——深灰色，宽度——0.5磅；(5)图例：选择【无】。(5)在图表区右键→【设置图表区域格式】颜色：黑色；线宽：0.5磅；(6)在绘图区（即图形区，选择时边界出现圆形控制点）右键→【设置绘制区格式】→边框：无线条绘图区格式：边框——黑色，选择合适的线型样式、粗细，区域【无】；(2)纵坐标轴格式：刻度最大值——1800，刻度最小值——0，主要刻度单位——100，次要刻度单位——20，字号——6（选择纵坐标轴刻度标签后设置字号）。注：应根据研究对象选择恰当的坐标轴格式设置值；(3)横向次要网格线格式：选择合适的线型样式、颜色与粗细；(4)横坐标轴格式：坐标轴与刻度线的标签——无，刻度最大值——7.438，刻度最小值——0。在2．设置频率刻度(1)在图表区右键→【选择数据】→【选择数据源】对话框→图例项（系列）【添加】→【编辑数据系列】对话框：系列名称：=频率刻度X轴系列值：=流量机率格纸数据点!$H$5:$H$36Y轴系列值：=流量机率格纸数据点!$I$5:$I$36→【确定】第3页共3页→【选择数据源】对话框→【确定】(2)在刚才添加的曲线（即与X轴重叠的曲线，对应于频率刻度的线条，选择时会出现许多带4个圆角点的方框——即数据点）上右键→【设置数据系列格式】→【线条】：【实线】；颜色：深灰色；宽度：0.5磅；(3)选择数据标签（即“频率刻度”的数据点）右键→【设置数据标签格式】：【标签包括】：X值；【标签位置】：靠下；【对齐方式】：垂直对齐方式——中部剧中，自定义角度——-45度（以避免标签——刻度重叠）；→【开始】选项卡→字号：6；3．设置重现期刻度(1)在图表区右键→【选择数据】→【选择数据源】对话框→图例项（系列）【添加】→【编辑数据系列】对话框：系列名称：=重现期刻度X轴系列值：=流量机率格纸数据点!$H$5:$H$36Y轴系列值：=流量机率格纸数据点!$J$5:$J$36→【确定】(2)在刚才添加的曲线（即与绘图区上边框线重叠的曲线，对应于重现期刻度的线条）上右键→【设置数据系列格式】→【线条】：【实线】；颜色：深灰色；宽度：0.5磅；(3)选择数据标签（即“频率刻度”的数据点）右键→【设置数据标签格式】：【标签包括】：X值；【标签位置】：靠上；【对齐方式】：垂直对齐方式——中部剧中，自定义角度——-45度；→【开始】选项卡→字号：6；4．绘制网格线(1)在图表区右键→【选择数据】→【选择数据源】对话框→图例项（系列）【添加】→【编辑数据系列】对话框：系列名称：=纵向网格线X轴系列值：=流量机率格纸数据点!$C$5:$C$234Y轴系列值：=流量机率格纸数据点!$D$5:$D$234→【确定】(2)在刚才添加的曲线（对应于纵向网格线的线条）上右键→【设置数据系列格式】→【线条】：【实线】；颜色：深灰色；宽度：0.5磅；(3)【设计】选项卡→【数据标签】：无；5．修改刻度值打开VBE环境（快捷键【Alt】+【F11】，或【开发工具】选项卡→【VisualBasic】，若没有启用【开发工具】选项卡，则需要打开Excel【选项】→【自定义功能区】→【主选项卡】→勾选【开发工具】即可），添加如下代码：PrivateSubChart_Activate()Dimi%,k#Fori=5To36k=Sheet1.Cells(i,F)Ifi21Thenk=100/kElsek=1/(1-k/100)'1/P%Ifk-Int(k)0ThenSheet1.Cells(i,K)=Format(k,0.##0)Else第4页共3页Sheet1.Cells(i,K)=Format(k,0)EndIfNextFori=1ToSeriesCollection(2).DataLabels.CountSeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange.Characters.Text=Sheet1.Cells(i+4,F)SeriesCollection(2).DataLabels(i).Format.TextFrame2.TextRange.Characters.Text=Sheet1.Cells(i+4,K)NextEndSub完后关闭VBE窗口，重新激活图表即可自动完成刻度的更新（激活方法：切换到其它工作表，再切换回图表即可）。最终完成图如图2所示。图2提示：根据具体要求，对【流量频率曲线】进一步美化，即可得到美观、实用的海森机率格纸。应用效果：01002003004005006007008009001000流量(单位)频率P(%)海森机率格纸第5页共3页01002003004005006007008009001000流量(单位)频率P(%)海森机率格纸