如何利用EXCEL做概率曲线图

软件时空

如何利用EXCEL 做概率曲线图

How to make probability curves using EXCEL

(457001 河南中原油田勘探开发科学研究院)

=摘要>本文介绍了一种在EXCEL 中实现正态概率坐标系的方法。

关键词:EXCEL 、概率、正态分布

Abstract:T his paper intro duces y ou a way to realize prob -ability coordinate system in EX CEL. Keywords:EXC EL probability

normal distribution

王婧韫

你可以通过查标准正态分布表取得数据, 这比较麻烦, 而且不精确, EXCEL 中提供了正态分布函数的算法, 直接利用这些函数可以大大减少工作量, 提高精确度。

三概率坐标的具体实现

目前, 大家常用的是OF FICE97, 下面就以EX -CEL97为平台介绍具体的步骤。

第一步, 生成数据表。

1新建EXCEL 文件, 在A1:A17单元格内输入数字/00, 在B1:B17单元格内分别输入数字99. 99, 99. 9, 99, 95, 90, 80, 70, 60, 50, 40, 30, 20, 10, 5, 1, 0. 1, 0. 01。前者作为横坐标, 代表将来纵轴与横轴的交叉位置, 其数值以后要根据实际情况调整。后者代表纵轴以百分数表示的概率刻度值。

2选择C1单元后击粘贴函数(见图1中箭头所指位置) , 待出现粘贴函数菜单后选取统计类的N ORM-SINV 或NORM INV 函数。

3根据不同的选择做如下工作:

a 若选前者, 则在出现新的对话框后, 在proba -bility 后输入/B1/1000, 然后确定(图2) 。

b 若选后者, 则在新对话框中, 自上而下依次输入/B1/1000、/500、/10, 然后确定(图3) 。

4这时C1单元格内就有了一个函数值, 拖动C1单元格的填充柄(C1单元格右下角的小黑块) 向下填充, 则C2:C17单元格内都自动生成了函数值。

5设置C1:C17单元格格式, 将其数值的小数位数定为/20。

至此, 做图的准备工作-生成数据表就算完成了(见图4-选NORM SIN V 函数时生成的数据, 图5-选N ORM IN V 函数时生成的数据) , 生成的C1:C17数值就代表正态概率分布的区间值, 下一步我们就要用A1:A 17和C1:C17单元格的数据做图了。

第二步, 做草图。

不管前面选择什么分布函数, 这一步都是相同的。

)

一问题的提出

用过微软OFF ICE 的人都知道, 微软的EXCEL 提供了强大的制表、制图功能, 你可以很容易地制成一张美观、实用的表格, 也可以方便地利用其提供的各种图表类型做出一张形象、直观的图表, 帮你分析数据, 反映成果。这确实给大家提供了便利。

但笔者在工作中发现, 虽然EXCEL 功能强大, 既提供了普通算术平面直角坐标系, 也提供了对数坐标系, 却没有提供概率坐标, 而在实际工作当中, 尤其是统计分析中这又是必不可少的, 本文正是基于这样的现状, 以正态概率分布为例, 给大家介绍一种在EXCEL 中做概率图的方法。

二原理

见过正态概率坐标纸的人都知道, 它与普通算术坐标纸的不同就在于其横坐标轴为均匀刻度, 代表统计观察值, 而其纵坐标轴的刻度不是均匀的, 表示概率, 并且以概率50%处上下对称, 概率刻度的刻法是:选定各坐标的单位长度, 然后接标准正态分布表的数对(z, p)

(z 为区间值, p 为概率) 来定出纵坐

标的刻度, 使得标准正态分布函数在此图纸上的图形是一条直线, 由于一般正态分布函数N (u, R 2) 经过线性变换z=x -u/R 可转换为标准整套态分布函数, 因此, 一般正态分布同样也适合。

由此可见, 只要能在EXCEL 中做出概率坐标纸, 做概率图就容易了。由概率刻度的刻法可知, 概率刻度值实际上体现的是正态分布的区间值, 在EXCEL 中以z 值为数据做散点图就可实现。至于z 值的取得,

5微计算机信息6 1998年第14卷第6期

选中A1:A17和C1:C17单元格后, 选择图表向导, 按照提示进行:

1在4步骤之1中, 选择X 、Y 散点图类型中的第一子类型, 后单击下一步;

2在4步骤之2中, 确认系列产生在列(点中/列0前面的小圈) , 后单击下一步;

3在4步骤之3中, 可暂不做选择, 直接单击下一步;

4在4步骤之4, 根据喜好选图表位置, 本文中选/新工作表0, 单击/完成0后草图就算做成了(图6-选NO RM SI NV 函数时生成的草图、图7-选NORM INV 函数时生成的草图) 。

现在还看不出一点概率图的样子, 别着急, 经过下一步的/整容0工作, 来了。

第三步, 修图

1首先先去掉图例框、绘图区背景色以及网格线, 便于下面的修改;

2选择数值(Y) 轴, 按鼠标右键弹出菜单后选/坐标轴格式0, 在/坐标轴格式0菜单中做如下设置:¹在/图案0项中将主、次刻度线类型及刻度线标志均选/无0;

º在/刻度0项中, 根据第一步中

所选函数的不同作如下设定:a) 若选/NOR M -SIN V 0, 则最小值选/-3. 720, 最大值选/3. 720, 数值(X ) 轴交叉于(C ) :选/-3. 720; b) 若选/NO RM IN V 0, 则最小值选/46. 280, 最大值选/53. 720, 数值(X) 轴交叉于(C) :选/46. 280;

现在概率坐标轴已初具形态了, 还需进一步/美化0。

3选择数据系列, 按鼠标右键弹出菜单后选/数据系列格式0, 在该菜单作如下设置:

¹将图案中的/数据标记样式0选为短横线作为纵轴刻度线; º在/数据标志0项

中选/显示值0, 然后确定。

4选中数据系列点的/数据标志0, 自上而下依次将各标志值改为99. 99、99. 9. .. .. . 0. 01(即B1:B17的值) 作为刻度值;

5在/数据标志格式0菜单中将文本对齐方式选为水平/靠左0, 垂直/居中0, 标志位置选/靠左0, 然后确定。

6将绘图区框向右拉小一些使刻度值清楚可见。至此, 概率坐标纸就做好了(见图8) , 此时你可以将/sheet10改名为/数据表0,

/图表10改名为

/图表0后将文件以模板的形式保存起来, 在实际应)

)

/庐山真面目0就会暴露出

用当中作具体概率图时调用。

四应用举例

前面介绍了概率坐标系的实现, 下面举例说明模板的具体应用。

例1. 在20天内, 从维尼纶正常生产时生产报表上看到的维尼纶纤度(表示纤维粗细程度的一个量) 的情况, 有如下100个数据, 试判断纤度是否服从正态分布:

1. 361. 491. 431. 411. 371. 401. 321. 421. 471. 391. 411. 361. 401. 341. 421. 421. 451. 351. 421. 391. 441. 421. 391. 421. 421. 301. 341. 421. 371. 361. 371. 341. 371. 371. 441. 451. 321. 481. 401. 451. 391. 461. 391. 531. 361. 481. 401. 391. 381. 401. 361. 451. 501. 431. 381. 431. 411. 481. 391. 451. 371. 371. 391. 451. 311. 411. 441. 441. 421. 471. 351. 361. 391. 401. 381. 351. 421. 431. 421. 421. 421. 401. 411. 371. 461. 361. 371. 271. 371. 381. 421. 341. 431. 421. 411. 411. 441. 481. 551. 37

为解决此题, 我们先将这100个数按等间隔分为10组:1. 265~1. 295, 1. 295~1. 325, .. ... . 1. 535~1. 565, 每组间隔0. 03, 然后统计出落在每个组内观察值的个数, 记作频数fi, 得到上面的频数分布表:

组限

1. 265~1. 2951. 295~1. 3251. 325~1. 3551. 355~1. 3851. 385~1. 4151. 415~1. 4451. 445~1. 4751. 475~1. 5051. 505~1. 5351. 535~1. 565

6

频数fi [1**********]611n=100

频数fi/n 0. 010. 040. 070. 220. 230. 250. 100. 060. 010. 011. 00

累积频率0. 010. 050. 120. 340. 570. 820. 920. 980. 991. 00

要判断是否服从正态分布, 实际上就是看这些累积频数点在概率坐标纸上的分布是否近似于一条直线, 因此我们需要根据组限值和累积频率值做概率图。

1首先打开上面所做的模板, 在数据表中, 自A18向下输入1. 295, 1. 325, 1. 355.. . 1. 535, 自B18向下依次输入对应的累积频数值(转成百分数形式) /1、5、12、34、57、82、92、98、990, 拉动C17单元格的填充柄向下填充, 这时原始数据就准备好了。

2然后选中模板中的图表, 在/图表0菜单中选/添加数据0, 在出现对话框后选中要添加的数据区域(此处选A18:A26和C18:C26) , 在下一对话框中选

软件时空

添加单元格为/新系列0, 数值(Y ) 轴在/列0, 并选中/首列为分类X 值0前面的方框, 然后确定, 对这些统计点所做的累积频率曲线草图就做好了。

3调整坐标轴格式, 使图看起来直观些:在坐标轴格式菜单中将横轴刻度值范围调整为1. 265~1. 565, 刻度间隔为0. 03, 这时纵轴又不谐调了, 你可以把原始数据中A1~A 17单元内的数据/00都改为/1. 2650, 这样纵轴就落在11265处, 看起来比较直观了(见图9-例1统计点概率图) 。

此时你可以目测判断点的分布是否为直线, 也可以为该数据系列添加线性趋势线。经过判断发现, 这些点的分布近于一条直线, 说明其符合正态分布。

将做过改动的文件以普通EXCEL 文件的形式另存, 保持模板不变, 供以后再用。

例2. 在石油地质研究中, 常常需要对砂样的粒度资料进行分析, 绘制成图, 其中常见的一种图叫概率值累积曲线, 借助于本文中介绍的方法, 在EXCEL 中就可以实现。

例如根据某砂样的粒度资料得到以下孔隙半径U 与累积重量百分比值的数据表:5值

-1

1100

2100

3100

4100

5100

5102

616

累积百分比11005124

入/B1/100

0就可以为/B10, 如果你不想以本文中的数据标示刻度值, 也可以根据需要重组数据表, 对图表还可做许多修饰工作, 这里就不再多说, 请参看EXCEL 有关内容。另外, 对本文中提到的EXCEL 操作有不明白的, 也请查阅EXCEL 手册中的有关内容。

本文只介绍了正态分布的情况, 类似地, 你也可以生成其它分布的模板。

(收稿日期:98, 9, 10)

[***********][1**********]2

需要对以上数据做概率值累积曲线。

同样, 我们打开模板, 自A18向下输入U 值, 自B18向下输入累积重量百分比值, 在C 列填充得到各百分比值对应的区间值, 然后以同样的方法将新的数据系列(A18:A25和C18和C25) 添加到图表中, 则该砂样的概率值累积曲线草图就做好了, 然后再调整横轴坐标范围为-2~7, 刻度间隔为1, 并将A1~A 17内的数据改为/-20, 这样就得到了一张直观的概率图(图10-例2砂样粒度概率累积曲线) , 地质人员可据此分析沉积成因等。

五结束语

在EXCEL 中做图, 可以很方便地对图做各种修饰、改动, 如添加背景, 修改字体、颜色、添加文本等等, 因此, 采用此方法你可以轻而易举地做出一张漂亮、直观、实用的概率图, 这是其它手段所无法达到的。

本文主要是介绍方法, 具体实现当中完全可以灵活应用, 比如原始数据的排列方式也可以以行的形式, B1:B17的数据也可以写成小数, 这样后面的输

)