叫你作图对比数据,你看你都做了个啥?【Excel教程】

全套Excel视频教程,扫码观看

编按:
哈喽,大家好!提到excel中“箱线图”,相信大多数人都是第一次听说,虽然它的名声不大,可是功能确不容小觑。当我们需要对多组数据进行比较、分析时,例如比较几组数据中的最大值、最小值、中位数等数值,由于数据量过大,如果采用常规的图表,势必会使图表看起来混乱无比,而“箱线图”就能完美的解决这一难题,赶紧来看看吧!
箱线图(Boxplot)也称箱须图,它是用一组数据中的下限值、下四分位数、中位数、上四分位数和上限值来反映数据分布的中心位置和散布范围,它也可以粗略地看出数据是否具有对称性。通过将多组数据的箱线图画在同一坐标上,则可以清晰地显示出各组数据的分布差异,为发现问题、改进流程提供突破点。箱线图是用作显示一组数据分散情况资料的一种统计图,因形状如箱子而得名。在各种领域中也经常被使用,常见于品质管理。它主要用于反映原始数据分布的特征,其次还可以进行多组数据分布特征的比较。
(注意,以下示例数据均已排异常值,所以我们做了简单化处理,上限值等于该组数据的最大值,下限值等于该组数据的最小值。如果数据有异常值,或者无法确定数据是否有异常值,则不能用上限值等于最大值,下限值等于最小值的方式来绘制箱线图。)

1

箱线图介绍

箱线图需要用到统计学的四分位数的概念,所谓四分位数,就是把一组数中的数据由小到大排列并分成四等份,处于三个分割点位置的数字就是四分位数。
最小值:等于该样本中所有数值的最小值。
最大值:等于该样本中所有数值的最大值。
中位数:等于该样本中所有数值由小到大排列后第50%的数字。
下四分位数:等于该样本中所有数值由小到大排列后第25%的数字。
上四分位数:等于该样本中所有数值由小到大排列后第75%的数字。
图形的主体是一个矩形,就像一个箱子一样,其上下两端分别对应数据的上四分位数和下四分位数,矩形内部的横杠称为中位线,对应的是数据的中位线。在箱体外的上端和下端各有两条线段,分别对应数据的最大值和最小值。
温馨提示:加入下面QQ群:1043683754,下载教程配套的课件练习操作。

2

箱线图制作方法

下面我们以员工的综合测评表为例说明箱线图的做法。已知某公司年底员工的综合测评结果,分析以下员工各项能力数据的分布差异。
原始表格:
生成图形:
要制作箱线图,首先需要对基础数据进行简单的处理,然后利用处理后的数据制作带数据标记的折线图,再对折线图进行添加涨/跌柱线、高低点连线等细节上的操作,就OK啦!具体步骤如下:
① 在K2:O7单元格中新建表格数据,并按照下图输入对应数据。
在L3单元格输入公式“=QUARTILE.EXC(B3:B11,1)”计算“技术能力”的下四分位数;
在L4单元格输入公式“=MAX(B3:B11)”计算“技术能力”的最大值;
在L5单元格输入公式“=MIN(B3:B11)”计算“技术能力”最小值;
在L6单元格输入公式“=MEDIAN(B3:B11)”计算“技术能力”中位数;
在L7单元格输入公式“=QUARTILE.EXC(B3:B11)”计算“技术能力”上四分位数。
QUARTILE.EXC(array, quart)函数用于返回数据集的四分位数,其中,Array为要求得四分位数值的数组或数字型单元格区域,quart为指定返回哪一个值。当quart为1时,返回数据集的下四分位,当quart为3时,返回数据集的上四分位。MEDIAN()函数用于返回数据集的中位数。
② 选中L3:L7单元格,向右拖动填充柄复制公式至O7单元格。我们将基于L3:O7单元格的数据制作箱线图。
③ 选中“K2:O7”单元格区域,在功能区中单击“插入”菜单-图表功能区-“折线图”-“带数据标记的折线图”,单击“确定”按钮关闭对话框。
④ 选择“最大值”数据系列,在“设置数据系列格式”对话框中,将数据标记的类型设置为条形,大小为20,填充颜色为黑色,无边框。同时将最大值数据系列的线条设置为无线条。
⑤ 使用与步骤4一样的方法,设置“最小值”“中位数”的数据系列。
⑥ 在图表中选择“上四分位数”的数据系列,将其数据标记设置为“无”,线条设置为“无线条”。按照同样的方法设置“下四分位数”的数据系列。
⑦ 为图表添加涨跌/柱线,并在“设置涨注线格式”对话框中设置柱线边框的颜色为“黑色”,线宽为“1磅”。
⑧ 为图表添加高低点连线。选中图表,在“设计”选项卡中的“添加图表元素”组中点击“线条”选项,在下级列表中选择“高低点连线”。然后在“设置高低点连线格式”对话框中将线条颜色设置为“黑色”,线宽为“1磅”。
⑨ 美化图形,删除网格线、图例,为数据系列添加“图表标题”,设置纵坐标轴的刻度间隔为“2”,并将图表及箱型内部填充为“淡蓝色”。

3

箱线图分析

在箱线图中,箱体的高度表示上四分位和下四分位的间距,间距越小,说明数据越集中,间距越大说明数据越分散。从上图来看,代表员工“学习能力”的箱体矩形高度较其它三个偏低,说明数据较集中。而箱体中间的线,表示数据的中位数,代表了样本数据的平均水平,其中“技术水平”和“学习能力”的中位数位置基本相同,且高于另两个指标,说明员工“技术能力”和“学习能力”较好。综上,员工的整体学习能力较好,但技术能力和团队协作能力差异明显。
OK,一个箱线图的制作及分析就完成了,再强调一句,当只有一个连续型变量时,并不适合画箱线图,直方图是更适合的选择,箱线图最有效的使用途径是作比较,配合一个或者多个定性数据。聪明的你有什么别的想法,欢迎留言。
参考资料:
来源:贾俊平、何晓群、金勇.统计学(第四版):中国人民大学出版社,2009年:66-67
扫二维码免费学Excel等视频

Excel教程相关推荐

帮我把这10W行数据,从第3行开始,隔7行求和,我现在就要!【excel教程】

520,最辛酸excel技术直男追女日记曝光:扎心了!【excel教程】

做聪明自动化表格,减少重复轻松自己,非它不可【Excel教程】

让工作提速百倍的「Excel极速贯通班」

(0)

相关推荐