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下限看,华南客户的收货天数范围小于华北客户,说明流程更加稳定。
数据分析咨询请扫描二维码
在如今的数据驱动时代,掌握数据分析的工具和方法不仅是提高工作效率的关键,也是开拓职业机会的重要技能。数据分析涉及从数据的 ...
2024-11-08在现代商业环境中,企业正在逐步认识到数据挖掘技术在客户行为分析中的重要性。通过深度分析客户数据,这项技术不仅可以帮助企业 ...
2024-11-08数据挖掘分析是从大量数据中发现隐藏模式和有用信息的过程。尤其是在图数据挖掘中,提供了分析复杂关系和结构的独特视角。图数据 ...
2024-11-08在当今快速发展的商业环境中,提高运营效率已成为企业取得成功的关键因素。企业需要通过优化工作流程、利用技术创新和提升员工技 ...
2024-11-08Python 是一门非常适合初学者学习的编程语言。其简洁明了的语法、丰富的功能库,以及广泛的应用领域,使其成为学习编程的理想选 ...
2024-11-08在当今快速变化的商业环境中,金融数字化已经成为中小企业(SMEs)发展的关键驱动力。通过采用数字工具和技术,中小企业能够提高 ...
2024-11-08中小企业在全球经济中扮演着重要角色,然而,面对数字化浪潮,这些企业如何有效转型成为一大挑战。数字化转型不仅是技术的升级, ...
2024-11-08选择合适的数据分析方法是数据分析流程中的关键环节。它影响最终结论的准确性和可信度。在这个过程中,需要综合考虑数据的性质、 ...
2024-11-08在当今数据驱动的商业环境中,数据分析师扮演着至关重要的角色。他们帮助企业从大量数据中提取有用的洞察,从而推动决策制定和战 ...
2024-11-07在现代商业环境中,商务数据分析师扮演着至关重要的角色。作为联系业务需求与数据洞察之间的桥梁,数据分析师需要具备一系列技能 ...
2024-11-07在现代商业环境中,商务数据分析师扮演着至关重要的角色。作为联系业务需求与数据洞察之间的桥梁,数据分析师需要具备一系列技能 ...
2024-11-07在现代商业环境中,数据挖掘发挥着至关重要的作用。它不仅帮助企业从庞大的数据集中提取有价值的信息,还为企业的决策和业务运营 ...
2024-11-07数据分析可视化是一种通过图形化方式展现数据的技术,它使复杂的数据变得直观易懂,从而帮助我们更好地做出决策。在这个快速发展 ...
2024-11-07数据分析是一项至关重要的技能,尤其在当今数据驱动的世界中。Python以其强大的库和简单的语法成为了数据分析领域的佼佼者。本文 ...
2024-11-07在现代数据驱动的环境中,数据分析师扮演着至关重要的角色。他们需要掌握多种工具,以满足数据分析、处理和可视化的需求。无论是 ...
2024-11-07作为一名业务分析师,你将发现自己处于企业决策和数据驱动战略之间的桥梁位置。这个角色要求掌握一系列技能,以便有效地将数据转 ...
2024-11-07CDA中科院城市环境研究所(厦门)内训圆满成功 2017年9月12日-15日,CDA数据分析师在中科院城市环境研究所(厦门)进行了 ...
2024-11-07数据分析是现代商业和研究领域不可或缺的重要工具。无论是为了提高业务决策的准确性,还是为了发掘隐藏在数据中的潜在价值,了解 ...
2024-11-06数据分析是一个精细且有序的过程,旨在从海量数据中提取有用的信息,为决策提供有力支持。无论你是新手还是有经验的分析师,理解 ...
2024-11-06在当今竞争激烈的商业环境中,业务分析师(Business Analyst, BA)的角色变得愈加重要。随着企业对数据驱动决策的依赖加深,业务 ...
2024-11-06