基于Excel的正态分布概率计算

背景

迄今为止，在所有的概率和统计中，最重要的连续分布模型就是正态分布(Normal distribution)，或者称为高斯分布，因为它如此经常出现并有着如此广泛的应用。

当一个概率模型中的结果是数字时，这个不确定量称做随机变量(Random Variable)，分为连续随机变量和离散随机变量两种。

如果一个X服从一个均值为和标准差为的正态分布，记为

虽然概率密度公式有点复杂，但是，在实际应用中，我们不需要概率密度函数的计算公式，就可以有效地利用正态分布，采用公式或者函数进行计算，理解其含义即可。

在下图的基础上，我们对概率密度进行累加，即可得到累积概率分布曲线，即通常我们所说的概率，代表一个区间面积。

正态分布函数有非常多的用途，累积概率在实际应用中非常广泛，比如通过计算产品尺寸落在特定公差范围内的概率、疾病的发生率和严重程度、智力测试和能力测试等结果、社会现象分布、保险事件发生的概率。

验证分布是否属于正态分布

描述正态分布，有两个参数，一个是密度函数，所以可以绘制数据的直方图，观察其形状是否接近正态分布的钟形曲线。另外一个参数是累计分布函数，将数据点按照从小到大的顺序排列，然后绘制其累积分布函数（CDF）与正态分布的CDF的对比图，如果数据点近似落在一条直线上，则数据接近正态分布，这就是正态概率图。现在以最常见的直方图进行说明。

基于A公司从2013年1月到2022年12月的数据，数据见下方。

时间	收益率（%）	时间	收益率（%）	时间	收益率（%）	时间	收益率（%）
2013年1月	3.58	2015年8月	0.24	2018年4月	-0.44	2020年11月	1.46
2013年2月	2.41	2015年9月	1.46	2018年5月	-1.56	2020年12月	0.72
2013年3月	2.47	2015年10月	1.85	2018年6月	3.69	2021年1月	7.24
2013年4月	1.99	2015年11月	2.77	2018年7月	2.28	2021年2月	-1.65
2013年5月	-1.4	2015年12月	5.72	2018年8月	-3.48	2021年3月	2.66
2013年6月	7.5	2016年1月	5.94	2018年9月	3.68	2021年4月	7.87
2013年7月	4.6	2016年2月	-1.11	2018年10月	-1.78	2021年5月	7
2013年8月	4.18	2016年3月	2.76	2018年11月	3.66	2021年7月	5.93
2013年9月	-2.13	2016年5月	2.28	2018年12月	1.12	2021年8月	0.89
2013年10月	4.84	2016年6月	2.19	2019年1月	0.03	2021年9月	6.52
2013年11月	5	2016年7月	3.33	2019年2月	2.99	2021年10月	0.76
2013年12月	3.53	2016年8月	-0.46	2019年3月	4.64	2021年11月	-1.43
2014年1月	3.22	2016年9月	0.65	2019年4月	-2.69	2021年12月	1.43
2014年2月	5.32	2016年10月	4.8	2019年5月	-0.75	2022年1月	0.56
2014年3月	-2.18	2016年11月	0.46	2019年6月	-4.83	2022年2月	-0.75
2014年4月	-1.47	2016年12月	2.03	2019年7月	0.23	2022年3月	2.08
2014年5月	-5.69	2017年1月	-0.07	2019年8月	5.06	2022年4月	-0.39
2014年6月	6.65	2017年2月	5.11	2019年10月	-0.9	2022年5月	0.29
2014年7月	8.76	2017年3月	7.68	2019年11月	1.74	2022年6月	1.47
2014年9月	-0.24	2017年4月	4.16	2019年12月	3.99	2022年7月	-0.86
2014年10月	3	2017年5月	1.89	2020年1月	2.74	2022年8月	0.06
2014年11月	-1.19	2017年6月	2.12	2020年2月	4.56	2022年9月	-1.54
2014年12月	-1.12	2017年7月	-3.51	2020年3月	-0.07	2022年10月	0.92
2015年1月	-1.63	2017年8月	2.07	2020年4月	-1.39	2022年11月	7.24
2015年2月	2.16	2017年9月	-2.38	2020年5月	5.69	2022年12月	-1.65
2015年3月	1.73	2017年10月	1.51	2020年6月	3.82
2015年4月	6.4	2017年11月	1.54	2020年7月	2.89
2015年5月	-5.59	2017年12月	2.02	2020年8月	-2.64
2015年6月	-4	2018年1月	3.35	2020年9月	-1.06
2015年7月	1.3	2018年3月	-0.72	2020年10月	3.1

对其使用Excel的图表工具进行直方图处理。

选择直方图的横坐标的数字，可以根据要求设置箱的宽度，比如这里为0.5，从0.81-1.31，当然你也可以设置为箱数，比如这里是29，另外如果你什么都不做，默认是自动。

选择直方图图表中纵条，可以设置纵条的间距，比如这里设置为50%，默认为0，也就是挨着的。

从图中可以看到-5.69–5.19出现的次数为2次，1.81-2.31出现的频次最高，达到了12次。从图形上分析，中间高，两边低，大致服从正态分布。

当然也可以采用Frequency来进行手动绘制。或者Excel数据分析工具箱的直方图进行绘制。

正态分布概率的计算

前面说过分布就是概率，是区间。对于一个函数Z的累计分布函数就是

用密度图形表示就是：

用累计概率表示就是：

为了方便，我们假设了一种最简单的正态分布，我们成为标准正态分布。也就是均值为0，标准差为1。

当数据服从正态分布的时候，我们可以使用标准正态分布表进行计算。

Excel中，你可以使用NORM.DIST函数来计算正态分布的累积概率。

函数语法：

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

• x：需要计算其累积概率的数值。
• mean：分布的均值。
• standard_dev：分布的标准差。
• cumulative：逻辑值，用于指定函数形式。如果为TRUE，则函数返回累积分布函数；如果为FALSE，则返回概率密度函数。

对于刚才的分布，你想计算随机变量小于或等于45的概率。

1. 在Excel中，你可以在任意一个单元格中输入以下公式：  =NORM.DIST(5, 1.676782609, 3.066149236, TRUE)

2. 这将返回0.86的结果。

正态分布概率逆的计算

如果你需要找到某个累积概率对应的值，你可以使用NORM.INV函数。

函数语法：

 NORM.INV(probability, mean, standard_dev)

• probability：介于0到1之间的概率值。
• mean：分布的均值。
• standard_dev：分布的标准差。

假设你想知道随机变量达到0.86078的概率对应的值是多少。

1. 在Excel中，你可以在任意一个单元格中输入以下公式：

    =NORM.INV(0.86078, 1.676782609, 3.066149236)

2. 这将返回随机变量达到90%概率的值。

小结

这就是Excel计算正态分布概率的基本原理，及其计算的相关工具和使用案例。你学会了吗？

知识点

• 直方图的使用
• 正态分布计算概率的基本原理
• Norm.dist的使用
• Norm.inv的使用