Excel正态分布函数简介

引言：Excel提供了几个工作表函数来处理正态分布或“钟形曲线”，这里介绍Excel的正态分布函数为统计上的挑战所提供的帮助。本文学习整理自exceluser.com，供有兴趣的朋友参考。

关于正态曲线的一件有趣的事情是它经常出现在许多不同的环境中：

• 人口中按性别的身高呈正态分布。
• 成人中低密度脂蛋白胆固醇的测量值呈正态分布。
• 斑马上条纹的宽度据说是正态分布的。
• 大多数测量误差被假定为正态分布。
• 许多六西格玛计算假设是正态分布。

等等。

最后一个例子，这里有一个令人惊讶的正态曲线：取任何人口，无论它是否呈正态分布，从该群体中随机选择至少30名成员，测量他们的某些特征，然后找到这些测量值的平均值，该平均值是一个数据点。现在选择相同数量的另一个随机样本，并找到它们的测量值的平均值。一次又一次地做同样的事情，中心极限定理说这些平均值往往服从正态分布。

正态分布无处不在，让我们尽可能轻松地使用Excel仔细看看如何使用它们。

简单的定义

我们需要了解一些简单的概念，以便可以开始使用Excel函数来描述数据。

从胆固醇到斑马条纹，正态概率分布描述了具有特定属性值范围的总体比例。大多数成员的指标接近平均水平；有些与平均值相差较远；有些与平均值相差更远。

例如，总体可能是世界上所有斑马的所有条纹，正态曲线将显示具有不同宽度的条纹的比例。

样本的标准偏差是样本与其平均值的分布的度量。（当然，我们在一个“样本”中取了很多项目，而不仅仅是一个项目。）在正态分布中，大约68%的样本在均值的一个标准偏差内，大约95%在两个标准偏差内，大约99.7%在三个标准偏差内。图1中的数字表示与平均值的标准偏差。

图1

z值是一个值与以标准差表示的平均值之间的距离。在图2中，每个数字都是一个z值。

图2

计算或估计标准偏差

以下几个函数需要标准偏差值，至少有两种方法可以找到该值。

首先，如果有数据样本，Excel原先提供有STDEV函数，但在Excel 2010中，被STDEV.S函数取代：

=STDEV.S(range_of_values)

另一方面，如果使用的是整个总体，则可以使用STDEV.P函数计算标准偏差：

=STDEV.P(range_of_values)

然而，如果要进行粗略估计，则必须采用不同的方法，因为没有实际数据来支持你的估计。

在这种情况下，首先计算范围，这是从最大可能值中减去最小可能值。很可能，让我们假设所有可能的值在大约95%都在该范围内。

记住，大约95%的样本在均值两侧的两个标准偏差内。（当然，这是总共四个标准差。）因此，如果我们将范围除以四，应该得到近似的标准差。

仅仅将范围除以四似乎是一种草率的方法，但要考虑这种计算经常使用的方式。

假设要预测下一年的销售额，你认为销售额将约为1000，但该数字可能高达1200，也可能低至800。有了这些信息，你可以在估计的销售额周围绘制一条正态曲线，并开始生成各种利润和现金流预测.

需要强调的是，这些数字只是你的最佳估计。因此，使用估计的标准偏差似乎并不像其他方式那样草率。

根据这些估计，平均销售额将约为1000，而标准差约为(1200 –800) / 4 = 100。有了这些信息，你可以使用以下函数来执行需要的许多计算分析。

NORM.DIST(x, mean, standard_dev,cumulative)

NORM.DIST函数给出一个数字落在或低于正态分布的给定值的概率，其中：

• x：想要测试的值。
• mean：分布的平均值。
• standard_dev：分布的标准差。
• cumulative：如果为FALSE或零，则返回x发生的概率；如果为TRUE或非零，则返回该值小于或等于x的概率。

示例：美国18至24岁女性的身高分布近似正态分布，平均值为65.5英寸（166.37厘米），标准差为2.5 英寸（6.35 厘米），这些女性中有多少比例高于5英尺8英寸，即68英寸（172.72厘米）？

身高小于或等于68英寸的女性百分比是：

=NORM.DIST(68,65.5,2.5,TRUE)=84.13%

因此，身高超过68英寸的女性比例为1 –84.13%，即大约15.87%，该值由下图3中的阴影区域表示。

图3

NORM.S.DIST(z, cumulative)

NORM.S.DIST函数将标准差(z)的数量转换为累积概率，其中：

• z：想要的分布的值。
• cumulative：确定函数形式的逻辑值。如果cumulative为TRUE，则NORM.S.DIST返回累积分布函数；如果为FALSE，则返回概率质量函数。

（概率质量函数PMF提供离散（即非连续）随机变量恰好等于某个值的概率。）

示例：

=NORM.S.DIST(1,TRUE)=84.13%

=NORM.S.DIST(-1,TRUE)=15.87%

因此，某个值在平均值的一个标准偏差内的概率是这些值之间的差值，即68.27%，此范围由如下图4所示图表的阴影区域表示。

图4

NORM.INV(probability, mean, standard_dev)

NORM.INV函数是NORM.DIST函数的反函数，它计算给定概率的x变量。

为了说明这一点，考虑上文中NORM.DIST函数的说明中使用的美国女性的身高，如果一个女人想成为75%最高的美国女性之一，她需要有多高？

使用NORM.INV，她会知道她的身高至少需要63.81英寸，如以下公式所示：

=NORM.INV(0.25,65.5,2.5)=63.81英寸

下图5显示了25%的美国女性比这个身高更矮所代表的区域。

图5

NORM.S.INV(probability)

NORM.S.INV函数是NORM.S.DIST函数的反函数，给定变量在均值一定距离内的概率，它会找到z值。

为了说明这一点，假设你关心最接近均值的一半样本。以下两个公式提供了-.674和+.674的边界，

=NORM.S.INV(0.25)

=NORM.S.INV(0.75)

如下图6所示。

图6

STANDARDIZE(x, mean, standard_dev)

STANDARDIZE函数返回指定值、均值和标准差的z值。

为了说明这一点，在上文的NORM.INV示例中，我们发现女性至少需要63.81英寸高才能避开人口中最矮25%的身高（按身高计算）。STANDARDIZE函数告诉我们 63.81 英寸的z值是：

=STANDARDIZE(63.81,65.5,2.5)=-0.676

可以使用NORM.S.DIST函数检查这个数字：

=NORM.S.DIST(-0.676,TRUE)=25%

也就是说，z值为-.6745的概率为25%。

如何从正态分布计算随机数

记住，NORM.INV函数返回给定概率的值。在Excel2007及更高版本中，其语法是：

NORM.INV(probability,mean,standard_dev)

此外，RAND函数返回一个介于0和1之间的随机数。也就是说，RAND生成随机概率。因此，可以使用NORM.INV函数从正态分布计算随机数，在Excel2007及更高版本中使用此公式：

=NORM.INV(RAND(),mean,standard_dev)