专栏首页完美ExcelExcel正态分布函数简介

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)

本文分享自微信公众号 - 完美Excel(excelperfect),作者:fanjy

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-08-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Excel数据分析案例:正态分布运用实例

    正态分布,是统计学中最重要的分布之一,它是由两个参数:均值和方差决定的。在excel中可以用NORMDIST和NORMSDIST两个函数来计算给定的均值和标准差...

    沉默的白面书生
  • 统计简单学_正态分布

    用户1147754
  • 随机数函数

    今天给大家分享几种常用的随机数函数! ▼ 在excel中生成随机数虽然不是很频繁的需求,但是简单了解几个随机数生成方式,偶尔还是很有帮助的。因为我们时常需要使用...

    数据小磨坊
  • 向Excel说再见,神级编辑器统一表格与Python

    很多开发者说自从有了 Python/Pandas,Excel 都不怎么用了,用它来处理与可视化表格非常快速。但是这样还是有一大缺陷,操作不是可视化的表格,因此对...

    Python数据科学
  • np.random.rand均匀分布随机数和np.random.randn正态分布随机数函数使用方法

    , 可以使用语句sigma * np.random.randn(...) + mu

    演化计算与人工智能
  • 向Excel说再见,神级编辑器统一表格与Python!

    是的,在一个界面上同时展示可视化表格与代码,而且同时通过表格与代码修改数据,这不就是 Python 与 Excel 的结合吗?

    1480
  • 这个神级编辑器给 Excel 加上了 Python 功能!

    很多开发者说自从有了 Python/Pandas,Excel 都不怎么用了,用它来处理与可视化表格非常快速。但是这样还是有一大缺陷,操作不是可视化的表格,因此对...

    统计学家
  • 向Excel说再见,神级编辑器统一表格与Python

    很多开发者说自从有了 Python/Pandas,Excel 都不怎么用了,用它来处理与可视化表格非常快速。但是这样还是有一大缺陷,操作不是可视化的表格,因此对...

    诸葛青云
  • 推荐一款数据处理的神级工具,完全结合了Python和Excel的优势

    是的,在一个界面上同时展示可视化表格与代码,而且同时通过表格与代码修改数据,这不就是 Python 与 Excel 的结合吗?

    double
  • 向Excel说再见,神级编辑器统一表格与Python

    是的,在一个界面上同时展示可视化表格与代码,而且同时通过表格与代码修改数据,这不就是 Python 与 Excel 的结合吗?

    机器之心
  • 神级编辑器Grid Studio统一Excel表格与Python

    是的,在一个界面上同时展示可视化表格与代码,而且同时通过表格与代码修改数据,这不就是 Python 与 Excel 的结合吗?

    小草AI
  • 向Excel说再见,神级编辑器统一表格与Python

    是的,在一个界面上同时展示可视化表格与代码,而且同时通过表格与代码修改数据,这不就是 Python 与 Excel 的结合吗?

    CDA数据分析师
  • Excel催化剂功能第4波-一大波自定义函数高级应用,重新定义Excel函数的学习和使用方法

    原文在简书上发表,再同步到Excel催化剂微信公众号或其他平台上,文章后续有修改和更新将在简书上操作, 其他平台不作同步修改更新,因此建议阅读其他出处的文章时,...

    Excel催化剂
  • 向Excel说再见,神级编辑器统一表格与Python

    是的,在一个界面上同时展示可视化表格与代码,而且同时通过表格与代码修改数据,这不就是 Python 与 Excel 的结合吗?

    小小詹同学
  • 向Excel说再见,神级编辑器统一表格与Python

    近日,开发者构建了名为 Grid studio 的开源项目,它是一个基于网页的表格应用,完全结合了 Python 和 Excel 的优势。

    华章科技
  • 向Excel说再见,神级编辑器统一表格与Python

    很多开发者说自从有了 Python/Pandas,Excel 都不怎么用了,用它来处理与可视化表格非常快速。但是这样还是有一大缺陷,操作不是可视化的表格,因此对...

    朱小五
  • 个人永久性免费-Excel催化剂功能第43波-文本处理类函数增强

    原文在简书上发表,再同步到Excel催化剂微信公众号或其他平台上,文章后续有修改和更新将在简书上操作, 其他平台不作同步修改更新,因此建议阅读其他出处的文章时,...

    Excel催化剂
  • Python与Excel的结合 Grid Studio

    很多开发者说自从有了 Python/Pandas,Excel 都不怎么用了,用它来处理与可视化表格非常快速。但是这样还是有一大缺陷,操作不是可视化的表格,因此对...

    KEVINGUO_CN
  • 向 Excel 说再见,神级编辑器统一表格与 Python

    是的,在一个界面上同时展示可视化表格与代码,而且同时通过表格与代码修改数据,这不就是 Python 与 Excel 的结合吗?

    用户1737318

扫码关注云+社区

领取腾讯云代金券