用EXCEL绘制PⅢ频率的曲线.docVIP

用EXCEL绘制P频率曲线 摘要：利用excel强大的函数功能，经过一些简单设置，完全可以绘制p频率曲线，其方法简单、实用，有一定的推广价值。 关键词：excel 绘制 p曲线 　　日常工作中，经常要对一些样本系列进行排频并绘制p频率曲线，现有的程序在适用性和经济上都不尽如人意，而常用的excel函数功能强大，经过一些简单设置，完全可以胜任此项工作。 绘制p频率曲线的步骤：首先用矩法、权函数法等求出样本系列总体的三个统计参数 、cv、cs，然后求出与不同p对应的各个xp值，并作为一个系列，最后用xy散点图在excel图表中绘出。本文对以上过程中的一般方法不再赘述，仅就绘制图形作一简介。 频率格纸的制作p频率曲线是绘制在频率格纸上的，其x轴上的刻度为对数刻度，绘有纵向网格线，而excel缺省的图表都不具备以上功能，必须要进行一些设置。 1.1 x轴对数刻度向线性刻度的转换 我们在绘制p频率曲线的同时，也要将同一系列的经验频率数据点绘出，以检验数据点的拟合情况。虽然一些水文书中给出了频率格纸横坐标分格表，但此表不可能准确地给出对应于经验频率数据点的横坐标。下面以图1为例，说明用normsinv函数求线性横坐标的方法。 图1 在单元格b2中输入“=normsinv(a2%)”，在单元格c2中输入“=-$b$2+b2”，对于b、c两列其它单元格可通过向下填充完成设置。1.2 纵向网格线的绘制 纵向网格线的绘制是通过向图表中添加一个系列的xy散点图来完成的，见图1中c、d两列。在（0.000，0）→（0.000，7000）→（0.179，7000）→（0.179，0）过程线中，第1和第2点、第3和第4点间构成纵向网格线，第2和第3点间与次横坐标轴重合。不同样本系列的y轴起始和终止刻度是不同的，为了增强其适应性，我们在单元格d2、d5中输入“=$e$2”，在单元格d3、d4中输入“=$f$2”，见图1。 1.3 x轴对数刻度的标注 向2中的数据系列添加数据标签，通过移动标签位置，修改标签内容，完成主次x轴对数刻度的标注。用gammainv函数计算对应于p的xp值 gammadist为γ分布函数，其反函数gammainv可返回具有给定概率的γ累积分布的区间点。但是我们并不能直接应用gammainv，因为gammadist与p曲线两者的γ分布函数关系式不尽相同。 1）p曲线数学上常称γ分布，其概率密度函数为 f(x)= (x-a0)α-1e-β（x-a ） （1） 　式中 γ（α）——α的γ函数； α，β，a0——三个参数。 这三个参数与总体的三个统计参数 、cv、cs具有下列关系： α= ；β= ；a0= （1- ） （2） 2）p与xp间的关系式为 p=p（x≥xp）= (x-a0)α-1e-β（x-a ） （3） 3）excel中γ概率密度函数的计算公式如下： f(x，α，β)= xα-1e- （4） 通过对比，可以看出（1）式与（4）式的参数与变量间有如下对应关系： α—α；β— ；x-a0—x 总结以上分析，可得求xp的方法和步骤：首先由 、cv、cs根据（2）式求出α，β，a0后代人（5）式即可。 xp=gammainv（1-p，α， ）+ a0 （5） 样本系列容量加大后图表的自动更新 一般情况下，不同的样本系列其容量也不相同，当数据量加大后，就要修改图表的数据范围。下面介绍的方法，可在工作表中的数据量加大后，自动更新图表的数据范围，步骤如下。 1）创建如图2所示的工作表。2）选择“插入”→“名称”→“定义”命令，弹出“定义名称”对话框，在“当前工作簿中的名称”框中输入x，在“引用位置”框中输入公式：=offset(频率曲线表！$c$2,0,0,count(频率曲线表！$c:$c)) 3）单击“添加”按钮。请注意，offset函数引用第一个数据点（单元格c2），并且使用count函数得到列中数据点的数量。 4）在“当前工作簿中的名称”框中输入ya，在“引用位置”框中输入公式：=offset(频率曲线表！$d$2,0,0,count(频率曲线表！$c:$c)) 5) 单击“添加”按钮和“确认”按钮关闭对话框。 6) 激活图表选择系列数据。 7) 使用步骤2和步骤4定义的名称重新设置范围引用。公式为：=series(“ya”,p频率曲线.xls!x,p频率曲线.xls!ya,3) 执行完以上步骤后，当c列和d列的数据量加大后，图表将自动更新。 1