Excel-箱线图(数据分布)分析
箱线图(Boxplot)也称箱须图(Box-whisker Plot),它是用一组数据中的最小值、第一四分位数、中位数、第三四分位数和最大值来反映数据分布的中心位置和散布范围,可以粗略地看出数据是否具有对称性。通过将多组数据的箱线图画在同一坐标上,则可以清晰地显示各组数据的分布差异,为发现问题、改进流程提供线索。
1.什么是四分位数
箱线图需要用到统计学的四分位数(Quartile)的概念,所谓四分位数,就是把组中所有数据由小到大排列并分成四等份,处于三个分割点位置的数字就是四分位数。
第一四分位数(Q1),又称“较小四分位数”或“下四分位数”,等于该样本中所有数值由小到大排列后第25%的数字。
第二四分位数(Q2),又称“中位数”,等于该样本中所有数值由小到大排列后第50%的数字。
第三四分位数(Q3),又称“较大四分位数”或“上四分位数”,等于该样本中所有数值由小到大排列后第75%的数字。
第三四分位数与第一四分位数的差距又称四分位间距(InterQuartile Range,IQR)。
计算四分位数首先要确定Q1、Q2、Q3的位置(n表示数字的总个数):
Q1的位置=(n+1)/4
Q2的位置=(n+1)/2
Q3的位置=3(n+1)/4
对于数字个数为奇数的,其四分位数比较容易确定。例如,数字“5、47、48、15、42、41、7、39、45、40、35”共有11项,由小到大排列的结果为“5、7、15、35、39、40、41、42、45、47、48”,计算结果如下:
Q1的位置=(11+1)/4=3,该位置的数字是15。
Q2的位置=(11+1)/2=6,该位置的数字是40。
Q3的位置=3(11+1)/4=9,该位置的数字是45。
而对于数字个数为偶数的,其四分位数确定起来稍微繁琐一点。例如,数字“8、17、38、39、42、44”共有6项,位置计算结果如下:
Q1的位置=(6+1)/4=1.75
Q2的位置=(6+1)/2=3.5
Q3的位置=3(6+1)/4=5.25
这时的数字以数据连续为前提,由所确定位置的前后两个数字共同确定。例如,Q2的位置为3.5,则由第3个数字38和第4个数字39共同确定,计算方法是:38+(39-38)×3.5的小数部分,即38+1×0.5=38.5。该结果实际上是38和39的平均数。
同理,Q1、Q3的计算结果如下:
Q1 = 8+(17-8)×0.75=14.75
Q3 = 42+(44-42)×0.25=42.5
Excel为计算四分位数提供了QUARTILE(array,quart)函数,其中array参数用于指定要计算四分位数值的数组或数值型单元格区域,quart指定返回哪一个四分位值,可用值如下:
0,返回最小值;
1,返回第一个四分位数;
2,返回第二个四分位数,即中位数;
3,返回第三个四分位数;
4,返回最大值。
图9-51箱线图的结构
四分位间距框的顶部线条是第三四分位数的位置,即Q3,表示有75%的数据小于等于此值。底部线条是第一四分位数的位置,即Q1,表示有25%的数据小于此值。则整个四分位间距框所代表的是数据集中50%(即75%-25%)的数据,四分位间距框的高度就是这些数据涉及的范围,能够表现出数据的集中程度。Q2是数据中位数的位置。
Whisker上限是延伸至距框顶部1.5倍框高范围内的最大数据点,Whisker下限是延伸至距框底部1.5倍框高范围内的最小数据点,超出Whisker上限或下限的数值将使用星号“*”表示。但是,在Excel中绘制箱线图需要借助股价图来实现,因此无法展现异常值,Whisker上限将延伸至数据最大值的位置,Whisker下限将延伸至数据最小值的位置。
3.绘制箱线图
图9-52中的A2:F8区域和H2:M8区域分别是华北和华南是某段时间客户订单收货天数的统计结果,C11:C15和J11:J15是利用QUARTILE函数计算的华北、华南收货天数的四分位数结果。
图9-52收货天数的四分位数计算结果
在Excel中绘制箱线图需要借助股价图的“开盘-盘高-盘底-收盘”图来实现。根据Excel绘图时放置数据系列的位置,开盘、盘高、盘底、收盘应分别对应Q1、Q0、Q2、Q4。下面是绘图步骤:
准备图表数据。根据对应关系,在表格的B18:E18区域分别输入华北客户的Q1、Q0、Q2、Q4统计数字,将Q3输入到最后的F18单元格中,在A18中输入一个日期型数据(注意,必须为日期型),如“2013/1/1”。然后在第19行中输入华南客户的数据,A19中的日期递增1天,最终结果如图9-53所示。
图9-53准备图表数据
插入图表。选定A18:E19区域,在“插入”功能区的“图表”模块中单击“其他图表”,选择股价图部分的“开盘-盘高-盘底-收盘图”按钮,即可看到绘制的股价图,如图9-54所示。
图9-54插入股价图
添加Q3数据系列。由图9-54可以看出,四分位间距框的顶部线条使用的是Q4(最大值)位置,而是不是箱线图要求的Q3位置。右击绘图区,在弹出的快捷菜单中选择“选择数据”命令,打开“选择数据源”对话框。单击“添加”按钮打开“编辑数据系列”对话框,在“系列名称”折叠框中输入“Q3”,在系列值折叠框中选择F18:F19区域,单击“确定”按钮即可看到股价图变成了箱线图,如图9-55所示。四分位间距框的高度小了很多,单击顶部线条与Whisker上限交汇处,可以看到使用的是Q3数据。
图9-55添加Q3数据系列
显示中位数线。至此,四分位间距框虽然已经绘制正确了,但是还缺少中位数线,即Q2。选择图例中的“系列3”标签,然后单击鼠标右键,在弹出的快捷菜单中选择“设置数据系列格式”命令,打开“设置数据系列格式”对话框。在“数据标记选项”中将标记类型设置为内置的“-”形状,单击“关闭”按钮即可看到中位线显示了出来,如图9-56所示。
图9-56显示中位数线
美化图表。首先要修改分类轴(横轴)标签,由于插入股价图时的限制在A18和A19单元格中输入了日期型数据,但是在图表插入后,可以将其修改为其他数据类型的值,因此在A18和A19单元格分别输入“华北”、“华南”。其次是删除图例栏,对于箱线图而言这并不需要。最后,可以为图表添加一个标题。最终美化后结果如图9-57所示。
图9-57美化后的图表
由图9-57可以看出,华北和华南客户的中位数位置、四分位间距框的位置与高度基本相同,说明两区域的客户收货天数基本相同。但是,从Whisker上限和Whisker下限看,华南客户的收货天数范围小于华北客户,说明流程更加稳定。
数据分析咨询请扫描二维码
CDA数据分析师在中国航信高科技产业园进行了面向测试度量的数据分析培训课程,培训人数近2 ...
2024-05-01CDA数据分析师走进深圳迈瑞生物医疗电子股份有限公司,在迈瑞总部展开了为期两天的培训,本次课程参训人员线上及线下近百人, ...
2024-05-01CDA数据分析师在合肥市对合肥阳光新能源科技有限公司开展了为期8天的企业内训。 合肥阳光新能源科技 ...
2024-05-01CDA数据分析师走进海尔大学,进行了《数据治理与数据中台建设的道与术》专题培训,培训现场爆满,近百人参加了此次培训。 ...
2024-05-01在中国银行苏州分行培训中心开始数据分析师培训,此次培训课程共10天内容,包括Excel、MySQL、概率论与数理统计、SPSS等内容, ...
2024-05-01从实际的业务需求出发,结合行业的典型应用特点,围绕实际的商业问题,探讨数据挖掘、机器学习模型在金融领域的应用,包括获客、信用评分、细分画像、交叉销售、反欺诈、违规识别、时序预测、运筹优化、流程挖掘九个方面,形成 ...
2024-05-01本次培训课程为线上+线下的模式,由于学员编程能力不一、部分学员没有编程基础,故提供统计学、python基 ...
2024-05-01华夏银行信用卡中心-机器学习培训 1、课程亮点 取材于业界一流企业和顶级咨询公司的行业实践;已经被证明是人人 ...
2024-05-01主 题:数据中台建设及数据分析应用主题分享 1. 数据中台市场洞察 2. 主流数据中台产品比较 3. 某企业数据中 ...
2024-05-01围绕“数据驱动”战略,全力打造我行 300 人数字化人才梯队,着力培养数字化管理人才、大数据专业团队 ...
2024-05-01在当今数据驱动的商业环境中,数据分析成为了企业决策的重要依据。通过对大量数据的收集、处理和分析,企业能够更好地理解市场 ...
2024-04-29在人工智能(AI)的世界里,提示词(Prompt)是一种强大的工具,它能够引导AI按照用户的需求产生特定的输出。本文将深入探讨AI ...
2024-04-29CDA立足未来职场,拓展前沿视野——对外经贸大学保险学院举办“三全育人大讲堂”分享行业最新动态。 ...
2024-04-294月2日,CDA数据分析师创始发起人兼协会理事长赵坚毅博士受邀在浙江万里学院举办了一场以“数字化能力在职场中的作用” ...
2024-04-29随机森林(Random Forests)现在机器学习中比较火的一个算法,是一种基于Bagging的集成学习方法,能够很好地处理分类和回归的问 ...
2022-12-23方差分析是数据分析中常用的一种统计分析方法,接下来让我们简单了解一下方差分析的基本思想和原理吧。 方差分析(Analysis ...
2022-12-23来源:关于数据分析与可视化 关于streamlit-aggrid 数据排序 表格样式的调整 数据 ...
2022-08-03作者:麦叔 定义 「把上面晦涩的概念汇成一句话就是:」 ❝ 回调函数就是一个被作为参 ...
2022-08-03现今,高学历人群日益增多,物以稀为贵的高学历光环淡去。无论本科生还是研究生,甚至博士生,求职竞争力都大不如前,就业压力越来越大。
2022-06-01某家企业10个人面试,有9个本科生……如何脱颖而出,除得体的举止和良好的沟通力外,证书成重要筹码,这也是很多人考证的关键所在。
2022-04-14