搜索
利用Excel的NORMSDIST函数建立正态分布表董大钧，乔莉沈阳理工大学应用技术学院、信息与控制分院，辽宁抚顺113122摘要：利用Excel办公软件特有的NORMSDIST函数可以很准确方便的建立正态分布表、查找某分位数点的正态分布概率值,极大的提高了数理统计的效率。该函数可返回指定平均值和标准偏差的正态分布函数，将其引入到统计及数据分析处理过程中，代替原有的手工查找正态分布表，除具有直观、形象、易用等特点外,更增加了动态功能,极大提高了工作效率及准确性。关键词：Excel；正态分布；函数；统计引言正态分布是应用最广泛的连续概率分布，生产与科学实验中很多随机变量的概率分布都可以近似地用正态分布来描述。例如，在生产条件不变的情况下，某种产品的张力、抗压强度、口径、长度等指标；同一种生物体的身长、体重等指标；同一种种子的重量；测量同一物体的误差；弹着点沿某一方向的偏差；某个地区的年降水量；以及理想气体分子的速度分量等等。一般来说，如果一个量是由许多微小的独立随机因素影响的结果，那么就可以认为这个量具有正态分布。从理论上看，正态分布具有很多良好的性质，许多概率分布可以用它来近似；还有一些常用的概率分布是由它直接导出的，例如对数正态分布、t分布、F分布等。在科学研究及数理统计计算过程中，人们往往要通过某本概率统计教材附录中的正态分布表去查找，非常麻烦。若手头有计算机，并安装有Excel软件，就可以利用Excel的NORMSDIST(x)函数进行计算某分位数点的正态分布概率值，或建立一个正态分布表，准确又方便。1正态分布及其应用正态分布（normaldistribution）又名高斯分布（Gaussiandistribution），是一个在数学、物理及工程等领域都非常重要的概率分布，在统计学的许多方面有着重大的影响力。若随机变量X服从一个数学期望为μ、标准方差为σ2的高斯分布，记为N(μ，σ2)。则其概率密度函数为正态分布的期望值μ决定了其位置，其标准差σ决定了分布的幅度。因其曲线呈钟形，因此人们又经常称之为钟形曲线。我们通常所说的标准正态分布是μ=0,σ=1的正态分布。服从正态分布的随机变量的概率规律为取与μ邻近的值的概率大，而取离μ越远的值的概率越小；σ越小，分布越集中在μ附近，σ越大，分布越分散。在统计学和数据处理中都能看到正态分布的影子。统计与正态分布的关系，就像物理与牛顿第一定律的关系。统计是研究样本数据的数学特性的一门学科。而正态分布，是样本数据的一种分布方式。统计学中有平均数方差标准差等等很多概念，正态分布只是其中之一。一般来说，自然界中的很多现象都可以用正态分布解释。比如说子弹的弹着点散布，学生的考试成绩等等。子弹在中心位置着弹最多，而两旁则逐渐减少。考试成绩，平均分数的最多，而满分和低分的很少。一般实验中的随机误差，大多数呈现为正态或近似正态分布；有些指标（变量）虽服从偏态分布，但经数据转换后的新变量可服从正态或近似正态分布，可按正态分布规律处理。[1]2利用Excel软件计算正态分布函数2.1Excel软件中的NORMDIST()函数利用Excel软件中的NORMDIST()函数可以返回指定平均值和标准偏差的正态分布函数。该函数的语法格式为：NORMDIST(x,mean,standard_dev,cumulative)。其中各参数功能如下：X为需要计算其分布的数值。Mean分布的算术平均值。Standard_dev分布的标准偏差。Cumulative为一逻辑值，指明函数的形式。如果cumulative为TRUE，函数NORMDIST返回累积分布函数；如果为FALSE，返回概率密度函数。[2]例如利用NORMDIST(x,mean,standard_dev,cumulative)计算服从某正态分布函数的累积分布函数值及概率密度函数值，如图1所示。图1NORMSDIST函数使用示例2.2利用NORMSDIST()函数计算标准正态分布表在Excel中，使用NORMSDIST()函数可以得到标准正态分布（μ=0，σ=1）表，其意义为从标准正态分布的左边开始，累加到z值处的总面积（z)（概率），如图2所示。图2NORMSDIST函数意义函数格式：NORMSDIST(z)如已知z，求（z)，即仅需计算一个值，比如z=1.96，可在Excel的任一单元格中使用公式进行计算，在单元格中输入：“=NORMSDIST(1.96)”，回车后即可得到0.9750（此单元格的格式保留小数位为4）。若想生成生成z值为0-4.99间的标准正态分布表。行间按0.1增加，列间按0.01增加。可在A3单元格输入0，A4单元格输入0.1，选中A3、A4两单元格，向下拖动填充柄，自动填充值至4.9。同理在B2单元格输入0，C2单元格输入0.01，选中B2、C2两单元格，向右拖动填充柄，至K2单元格。下面各单元格中，z值=行值+列值。为保证A列值都具有一位小数，需要统一设定该列单元格的格式。选中第一列，单击“格式”/“单元格”，在单元格对话框中选择“数值”，小数位数为1，如图3所示，以保证所有的数都是1位小数。同样方法设定第2行都有2位小数。[3]图3单元格格式对话框在B3单元格中，输入公式为=NORMSDIST($A3+B$2)这里，B3单元格公式中使用了混合地址引用，$A3中，$A表示锚定了A列，以保证不管该单元格的公式复制到那里，都使用A列的值，因为行前没有$符号，表示行号随着行的变化而变化；B$2锚定了第2行，列号没有锚定，表示随着单元格横向的移动能相应地改变列号，而行号始终为2。即不论该公式复制到哪里，其值都为该行A列值同时加上该列第2行的值。回车后显示函数值为0.5。选中B3单元格，向右拖动填充柄，至K列，再向下拖动填充柄，则能得到最大z值为4.99的函数值。[4]这样得到的函数值小数位数不统一，需要统一设定单元格的格式。选中B3为左上角的整块区域，单击“格式”/“单元格”，在单元格对话框中选择“数值”，小数位数设为4，以保证所有的数都是4位小数。标准正态分布表如图4所示。图4标准正态分布表（仅列出z值为0-1.59范围内的函数值）如果想得到从-5到5区间的标准正态分布表，只要A列值由-5开始即可。[5]若已知概率值（z)，求z值，可以使用正态分布反函数NORMSINV(概率值)求得。例如查概率为0.975的z值，在单元格中输入“=NORMSINV(0.975)”，回车后得到1.959964，移动单元格的列边线，改变有效数字位数，取2位小数既是1.96。利用这个函数可以极容易地查找到任何教科书中正态分布表列出和没列出来的值，也不会因为看错行而出错。3结语Excel软件包含丰富的函数，灵活准确的使用其中的函数可以更有效的完成数据处理。本文主要针对数据处理和统计过程中常用的正态分布表的查找方法提出了一种新的思路，即采用NORMSDIST()函数查找标准或非标准正态分布累积分布函数值及概率密度函数值。此方法较传统的手工查找分布表更快速，更准确，只要计算机中安装有常用的Excel软件，在进行常规的文字处理中还可以完成数据处理及统计分析的工作，值得推广，也让Excel软件体现了更高的价值。参考文献：董大钧SAS统计分析应用第1版北京电子工业出版社2010杨世莹.Excel2002函数、统计与分析应用范例第1版北京：中国青年出版社2003董大钧计算机应用基础第1版北京人民卫生出版社1998李继兵Excel公式与函数应用范例第2版北京：中国青年出版社2006陶永德杨庆霄数理统计第1版沈阳辽宁科技出版社1993董大钧SAS统计分析软件包应用指南第1版北京电子工业出版社1993TocreatethenormdistributionwiththeNORMSDIST(X)functioninEXCELDONGDa-jun1,QIAOLi2(PolytechnicSchoolofShenyangTechnologyUniversity,InformationAndControlCollege,Fushun113122,P.R.China)Abstract:WiththeNORMSDIST(X)functioninEXCEL,normdistributioncaneasilybecreated.Aslothenormdistributionprobitbeyondsomenumbercanbesearched.Itcanraisethemathematicalstatisticalefficiencyinthisway.TheNORMSDIST(X)functioncouldreturnthevalueofnormdistributionwithspecifymediumandstandarddeviation.Usethisfunctioninsteadofmanualsearchinmathematicalstatisticsanddataprocessing,canbemoreeasilyandimagic.Keywords:excel;normdistribution;function;mathematicalstatistics