Excel绘制指定区间的正态分布曲线下面积图

正态分布曲线下面积是很有实际应用价值的。在工程能力指数的评估、产品质量分析和教育评估分析方面都发挥了很大作用。

在正态分布的密度函数中有上述两个常数：算数平均数μ和标准差σ。正态分布的值有99.74%落在（μ-3σ，μ+3σ）区间内，也就是说落在以平均值为中心的左右各3个σ（共六个σ）的范围内，所谓管理学中的“三西格玛”或“六西格玛”就源于此。Excel中可以使用正态分布的密度函数NORMDIST（x，μ，σ，逻辑值），来表达正态分布，其中：x —“值”，是要求分布的随机变量数值；μ—“平均数”，是分布的算数平均数；σ—“标准差”，是分布的标准差；逻辑值—“积累与否”，是决定函数的逻辑值，其中取值为 “TRUE”（真），则返回累计分布函数；取“FALSE”（伪），则NORMDIST会返回正态分布函数的高度。如果为了绘制正态分布曲线，就要取“FALSE”。

Mly网友来信问起我在《Excel：正态分布函数曲线下的面积及其应用》中的图-3. μ=60，σ=15，在（-∞，55）的正态分布曲线二维面积图（蓝斜纹）如下图，是如何绘制的。

http://cda.pinggu.org/uploadfile/image/20160809/20160809120441_69120.png

这篇《Excel：正态分布函数曲线下的面积及其应用》主要是谈正态分布及其曲线下面积的应用，这张图是运用的一个例子，至于图表如何作出的具体步骤，文中就未作介绍。过后，正好有一位网友MZY来信问起了有关定积分的指定区间的曲线下面积的作图，我又写了一篇《答MZY：Excel指定区间的曲线下面积》，该文只是就Y=eX的曲线谈了分几个区间的曲线下面积的作图，因为思路是一样的，就未对分段的正态分布曲线作介绍。其实把这两篇文章结合起来就可以解决类似于上图的分区间的正态分布曲线下面积的绘制。

分区间段的面积图有多种做法，一般使用覆盖法，这样对全程控制比较方便。但是要注意要先做全程的，然后从右到左，使后者逐次叠加覆盖前者得一部分，每一系列的左端点应该是重合的，只是右端点不同，否则会出错。上图是μ=60，σ=15的正态分布曲线二维面积图，看起来是对三个不同的、但相连接的区间（-∞，55]，（55，65]和（65，+∞）分别着色，其实是对（-∞，55]，（-∞，65]和（-∞，+∞）三个互有重叠的区间实施不同的颜色填充，由于数据系列的前后顺序遮挡掩盖，而在视觉上形成是对首尾互相连接的三个区间填充内部图案的感觉。

首先，建立数据系列的表格，使用正态分布的密度函数NORMDIST（x，μ，σ，逻辑值），来表达区间（-∞，+∞）上的正态分布，其中：“平均数”μ=60；“标准差”σ=15；逻辑值“积累与否”取“FALSE”。A列设置随机变量数值，在A2单元格设为0、A3单元格设1，等差值为1，选中A2：A3后，一直拖到A112得出数值为110为止。在B列设置正态分布的密度函数NORMDIST（x，μ，σ，逻辑值），使B2单元格的内容为“=NORMDIST(A2,60,15,FALSE)”，向下拖曳复制公式到B112，如表-1所示，B列的显示的是公式：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120449_45127.png

表-1

如果使B列显示的是数值，即如表-2所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120457_27010.png
 

表-2

先作系列1：

将区间（-∞，+∞）作为系列1，也就是全程，放在最下一层，

选中单元格区间A2：B112，插入二维面积图，这就绘出μ=60，σ=15，在（-∞，+∞）的正态分布曲线图，如图-1所示。可见该中间高两头低的钟形图形是以μ=60为对称轴的轴对称图形，并以横轴为渐近线：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120505_17902.png
 

图-1

图-1完成后，在2003版的“源数据”-“系列”中可以看到系列1的“值”和“分类（x）轴标志”设置，如图-2所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120514_55723.png

图-2

在2010版中点击“图表工具”-“数据”-“选择数据”，如图-2-2010-1所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120523_53806.png

图-2-2010-1

在“选择数据源”对话框中，可以看到系列1已设置，如图-2-2010-2所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120531_58347.png

图-2-2010-2

在“编辑数据系列”对话框中可以看到“系列值”中的设置，如图-2-2010-3所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120539_57009.png

图-2-2010-3

而“轴标签”的设置，如图-2-2010-4所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120546_32520.png

图-2-2010-4

后续的数据系列必须在源数据中逐步添加。

添加系列2：

在“源数据”-“系列”-“系列”中添加系列2，将区间（-∞，65]作为系列2，“值”和“分类（x）轴标志”的设置如图-3所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120554_63182.png

图-3

图-3中“值”的具体设置方法是：点击“值”右侧的拾取折叠按钮，会弹出如图-4所示的“源数据-数值”对话框，选取系列2数据所在的单元格B2：B67，“源数据-数值”对话框会出现设置如图-4所示数据，再点击“源数据-数值”对话框右侧的拾取折叠按钮，则折叠复位到图-3。分类（x）轴标志”的设置方法也是利用右侧的拾取折叠按钮，选择的范围还是全程A2：A112.

http://cda.pinggu.org/uploadfile/image/20160809/20160809120601_11645.png
 

图-4

在2010版中，类似的设置如图-4-2010-1与图-4-2010-2所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120611_85796.png

图-4-2010-1

http://cda.pinggu.org/uploadfile/image/20160809/20160809120618_48469.png

图-4-2010-2

确定后，图表如图-5所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120627_89578.png

图-5

添加系列3：

在“源数据”-“系列”-“系列”中添加系列3，将区间（-∞，55]作为系列3，“值”和“分类（x）轴标志”的设置如图-6所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120634_32711.png

图-6

图-6中“值”的具体设置方法是：点击“值”右侧的拾取折叠按钮，在弹出的“源数据-数值”对话框，选取系列3数据所在的单元格B2：B57，再点击“源数据-数值”对话框右侧的拾取折叠按钮，则折叠复位。分类（x）轴标志”选择的范围还是全程A2：A112。

在2010版中，类似的设置如图-6-2010-1与图-6-2010-2所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120642_60741.png

图-6-2010-1

http://cda.pinggu.org/uploadfile/image/20160809/20160809120651_20448.png
 

图-6-2010-2

确定后，图表如图-7所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120658_47820.png

图-7

为了说明三个系列前后遮挡掩盖的效果，可以用三维面积图来说明。

当只有系列1时的三维图，如图-8所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120707_22223.png

图-8

当添加系列2后的三维图，如图-9所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120715_62478.png
 

图-9

当添加系列3后的三维图，如图-10所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120725_35415.png
 

图-10

将三个系列的三维图的三维变换角度调为0，相当于从侧面正视这个三维图，三维的遮挡掩盖效果就成了二维的分段切割效果，如图-11所示：

http://cda.pinggu.org/uploadfile/image/20160809/20160809120734_75417.png

图-11

最后要说明的是，这种分段区间的曲线下面积图的制作是对若干个互有重叠的区间实施不同的颜色填充，由于数据系列的前后顺序遮挡掩盖，而在视觉上形成是对首尾互相连接的三个区间填充内部图案的感觉。因此在制作中如有覆盖错误，很可能是数据系列的次序颠倒，可以在相关的数据系列次序调整的选项面板中进行调整。cda数据分析师官网