Excel绘制指定区间的正态分布曲线下面积图
正态分布曲线下面积是很有实际应用价值的。在工程能力指数的评估、产品质量分析和教育评估分析方面都发挥了很大作用。
在正态分布的密度函数中有上述两个常数:算数平均数μ和标准差σ。正态分布的值有99.74%落在(μ-3σ,μ+3σ)区间内,也就是说落在以平均值为中心的左右各3个σ(共六个σ)的范围内,所谓管理学中的“三西格玛”或“六西格玛”就源于此。Excel中可以使用正态分布的密度函数NORMDIST(x,μ,σ,逻辑值),来表达正态分布,其中:x —“值”,是要求分布的随机变量数值;μ—“平均数”,是分布的算数平均数;σ—“标准差”,是分布的标准差;逻辑值—“积累与否”,是决定函数的逻辑值,其中取值为 “TRUE”(真),则返回累计分布函数;取“FALSE”(伪),则NORMDIST会返回正态分布函数的高度。如果为了绘制正态分布曲线,就要取“FALSE”。
Mly网友来信问起我在《Excel:正态分布函数曲线下的面积及其应用》中的图-3. μ=60,σ=15,在(-∞,55)的正态分布曲线二维面积图(蓝斜纹)如下图,是如何绘制的。
这篇《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列的显示的是公式:
表-1
如果使B列显示的是数值,即如表-2所示:
表-2
先作系列1:
将区间(-∞,+∞)作为系列1,也就是全程,放在最下一层,
选中单元格区间A2:B112,插入二维面积图,这就绘出μ=60,σ=15,在(-∞,+∞)的正态分布曲线图,如图-1所示。可见该中间高两头低的钟形图形是以μ=60为对称轴的轴对称图形,并以横轴为渐近线:
图-1
图-1完成后,在2003版的“源数据”-“系列”中可以看到系列1的“值”和“分类(x)轴标志”设置,如图-2所示:
图-2
在2010版中点击“图表工具”-“数据”-“选择数据”,如图-2-2010-1所示:
图-2-2010-1
在“选择数据源”对话框中,可以看到系列1已设置,如图-2-2010-2所示:
图-2-2010-2
在“编辑数据系列”对话框中可以看到“系列值”中的设置,如图-2-2010-3所示:
图-2-2010-3
而“轴标签”的设置,如图-2-2010-4所示:
图-2-2010-4
后续的数据系列必须在源数据中逐步添加。
添加系列2:
在“源数据”-“系列”-“系列”中添加系列2,将区间(-∞,65]作为系列2,“值”和“分类(x)轴标志”的设置如图-3所示:
图-3
图-3中“值”的具体设置方法是:点击“值”右侧的拾取折叠按钮,会弹出如图-4所示的“源数据-数值”对话框,选取系列2数据所在的单元格B2:B67,“源数据-数值”对话框会出现设置如图-4所示数据,再点击“源数据-数值”对话框右侧的拾取折叠按钮,则折叠复位到图-3。分类(x)轴标志”的设置方法也是利用右侧的拾取折叠按钮,选择的范围还是全程A2:A112.
图-4
在2010版中,类似的设置如图-4-2010-1与图-4-2010-2所示:
图-4-2010-1
图-4-2010-2
确定后,图表如图-5所示:
图-5
添加系列3:
在“源数据”-“系列”-“系列”中添加系列3,将区间(-∞,55]作为系列3,“值”和“分类(x)轴标志”的设置如图-6所示:
图-6
图-6中“值”的具体设置方法是:点击“值”右侧的拾取折叠按钮,在弹出的“源数据-数值”对话框,选取系列3数据所在的单元格B2:B57,再点击“源数据-数值”对话框右侧的拾取折叠按钮,则折叠复位。分类(x)轴标志”选择的范围还是全程A2:A112。
在2010版中,类似的设置如图-6-2010-1与图-6-2010-2所示:
图-6-2010-1
图-6-2010-2
确定后,图表如图-7所示:
图-7
为了说明三个系列前后遮挡掩盖的效果,可以用三维面积图来说明。
当只有系列1时的三维图,如图-8所示:
图-8
当添加系列2后的三维图,如图-9所示:
图-9
当添加系列3后的三维图,如图-10所示:
图-10
将三个系列的三维图的三维变换角度调为0,相当于从侧面正视这个三维图,三维的遮挡掩盖效果就成了二维的分段切割效果,如图-11所示:
图-11
最后要说明的是,这种分段区间的曲线下面积图的制作是对若干个互有重叠的区间实施不同的颜色填充,由于数据系列的前后顺序遮挡掩盖,而在视觉上形成是对首尾互相连接的三个区间填充内部图案的感觉。因此在制作中如有覆盖错误,很可能是数据系列的次序颠倒,可以在相关的数据系列次序调整的选项面板中进行调整。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
CDA持证人简介: 居瑜 ,CDA一级持证人国企财务经理,13年财务管理运营经验,在数据分析就业和实践经验方面有着丰富的积累和经 ...
2025-04-25在当今数字化时代,数据分析师的重要性与日俱增。但许多人在踏上这条职业道路时,往往充满疑惑: 如何成为一名数据分析师?成为 ...
2025-04-24以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《刘静:10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda ...
2025-04-23大咖简介: 刘凯,CDA大咖汇特邀讲师,DAMA中国分会理事,香港金管局特聘数据管理专家,拥有丰富的行业经验。本文将从数据要素 ...
2025-04-22CDA持证人简介 刘伟,美国 NAU 大学计算机信息技术硕士, CDA数据分析师三级持证人,现任职于江苏宝应农商银行数据治理岗。 学 ...
2025-04-21持证人简介:贺渲雯 ,CDA 数据分析师一级持证人,互联网行业数据分析师 今天我将为大家带来一个关于用户私域用户质量数据分析 ...
2025-04-18一、CDA持证人介绍 在数字化浪潮席卷商业领域的当下,数据分析已成为企业发展的关键驱动力。为助力大家深入了解数据分析在电商行 ...
2025-04-17CDA持证人简介:居瑜 ,CDA一级持证人,国企财务经理,13年财务管理运营经验,在数据分析实践方面积累了丰富的行业经验。 一、 ...
2025-04-16持证人简介: CDA持证人刘凌峰,CDA L1持证人,微软认证讲师(MCT)金山办公最有价值专家(KVP),工信部高级项目管理师,拥有 ...
2025-04-15持证人简介:CDA持证人黄葛英,ICF国际教练联盟认证教练,前字节跳动销售主管,拥有丰富的行业经验。在实际生活中,我们可能会 ...
2025-04-14在 Python 编程学习与实践中,Anaconda 是一款极为重要的工具。它作为一个开源的 Python 发行版本,集成了众多常用的科学计算库 ...
2025-04-14随着大数据时代的深入发展,数据运营成为企业不可或缺的岗位之一。这个职位的核心是通过收集、整理和分析数据,帮助企业做出科 ...
2025-04-11持证人简介:CDA持证人黄葛英,ICF国际教练联盟认证教练,前字节跳动销售主管,拥有丰富的行业经验。 本次分享我将以教培行业为 ...
2025-04-11近日《2025中国城市长租市场发展蓝皮书》(下称《蓝皮书》)正式发布。《蓝皮书》指出,当前我国城市住房正经历从“增量扩张”向 ...
2025-04-10在数字化时代的浪潮中,数据已经成为企业决策和运营的核心。每一位客户,每一次交易,都承载着丰富的信息和价值。 如何在海量客 ...
2025-04-09数据是数字化的基础。随着工业4.0的推进,企业生产运作过程中的在线数据变得更加丰富;而互联网、新零售等C端应用的丰富多彩,产 ...
2025-04-094月7日,美国关税政策对全球金融市场的冲击仍在肆虐,周一亚市早盘,美股股指、原油期货、加密货币、贵金属等资产齐齐重挫,市场 ...
2025-04-08背景 3月26日,科技圈迎来一则重磅消息,苹果公司宣布向浙江大学捐赠 3000 万元人民币,用于支持编程教育。 这一举措并非偶然, ...
2025-04-07在当今数据驱动的时代,数据分析能力备受青睐,数据分析能力频繁出现在岗位需求的描述中,不分岗位的任职要求中,会特意标出“熟 ...
2025-04-03在当今数字化时代,数据分析师的重要性与日俱增。但许多人在踏上这条职业道路时,往往充满疑惑: 如何成为一名数据分析师?成为 ...
2025-04-02