今天有个大佬私信问我如何画下面这张图,主要有两个问题:
(1)竖虚线怎么添加?
(2)横坐标的标签怎么改成文字?
今天这期,我们就利用Excel散点图中的误差线来解决这个问题。
本文附件:
https://pan.baidu.com/s/1Ry7cra_uvvW-mZP1914l4Q
提取码:44zn
散点图/折线图
在解决问题前,先说一个关于散点图/折线图的易错点。
PS1:折线图其实就是散点图连条线而已。
假设有上图所示原始数据,要想用一条线刻画出x与y之间的关系,既不能选择A1:C5的整个区域来画图,也不能选择B1:C5的区域来直接画图。而需要先清除B1单元格的内容,再选中B1:C5的区域才可以。
方法一:选择A1:C5区域内的数据来画图,得到两个系列的数据
方法二:选择B1:C5区域内的数据画图,得到的也是两个系列的数据
方法三:先清除B1单元格的内容,再选中B1:C5区域内的数据画图,仅得到一个系列的数据
PS2:本文使用的是Excel2016版本,在Excel2013版本中,无需清除B1单元格的内容,也可以得到正确的图表。(我之前的笔记就是可以直接添加的,换了版本之后不行了)
上面这一点需要大家记清楚,很容易出错,因为我以前就出过这样的错误,画了半天画不出来。
至于本文做的图,当然是直接选中插入图表即可。
本文使用的原始数据
本文所使用的原始数据是8行4列的数据,分别给出了8个不同组别的最大值、最小值、中位数和现值数据。
需要画出包括4个系列,并带有最大值和最小值之间连线的图表,同时将横坐标以组别来命名。
问题解决
第一步:插入图表打基础
根据画图经验可以知道,直接选中数据,然后插入图表就自动生成最大值、最小值、中位数和现值4个系列的散点图了。
有待解决的问题:
虽然这个图看起来很寒碜,但是已经有了基本的框架,我们只需要在这张图的基础上做一些调整即可。
图表格式方面的修改主要有,删除标题与网格线、删除图表外边框、将图例移至上方、对各个系列的散点重设格式、将横轴标签的0-9删除(并将横轴标签更改为组别)等。
为什么要在“并将横轴标签更改为组别”这几个字外面加括号呢,那是因为从表面上看,这只需要稍微调整格式就可以达成目标,但是这就错了,为了这一“显而易见”的目标,我们需要新增一个辅助系列,然后通过特殊操作才能完成目标。
除此之外,还需要将每一个组别的最大值与最小值之间的两个点用线连起来,在Excel中能达到这一效果的就只有误差线这一个选项。
第二步:更改散点图中每个点的样式,并调整部分格式
在这个过程中,最难的操作,如何将这个点改成一个X或者改成一条横线。在『多图』Excel图表元素格式化一文中,我提到过形状填充、形状轮廓的概念,在Excel2016版本中,将这两个词分别改成了“填充”与“边框”,其实本质都是一样的。
对于散点图来说,每一个点都是可以进行格式化。
单个点在Excel中称为“标记”,这个标记其实就是一个小点,这个点可以填充颜色,还可以有不同的边框/轮廓,而且点可以有大小,边框可以有粗细,边框甚至还可以拥有不同的颜色。显然,将点改成X或者横线并不是填充不同颜色的范畴,而是边框的范畴。
为了实现将点更改为X或横线的目标,我们需要先在散点图中选中某个需要更改的系列,利用Ctrl + 1快捷键或右键设置数据系列格式,调到系列选项的标记设置选择框。先将“边框”改为“实线”,并选择为所需的颜色,然后在“标记选项”处选择“内置”,在“类型”下拉框中选择X或横线。
这个时候,我们发现X外面还有一层底色,那就是所谓“标记”的“填充颜色”,此时只需要在“填充”处修改为“无填充”,就可以得到所需要的一个红叉的标记了。同理,也可以画出横线。
第三步:添加误差线
误差线可以在单个散点上下左右四个方面加一条线,依据误差量来确定误差线的长度。也就是说,只要设置得当,就可以将最大值和最小值两个点连起来。
误差线的官方介绍:
为了达到本文的目标,既可以在“最小值系列”上添加误差线,也可以在“最大值系列”添加误差线。为了方便起见,本文只介绍在“最大值系列”的散点上添加误差线的方法。两者选择其一,均可达到目标。
下面介绍具体的方法:
(1)选中“最大值系列”然后在“图表设计”选项卡中“添加图表元素” → “误差线” → “标准误差”。
(2)添加误差线之后,选中误差线,Ctrl + 1或右键调出图表元素格式化的对话框。通过修改水平误差线(系列“最大值”X误差线)和垂直误差线(系列“最大值”Y误差线)的格式即可实现目标。
(3)本文中是不需要水平误差线(系列“最大值”X误差线)的,因此需要将水平误差线的误差量改为固定值0,并取消左右两个末端。
(4)本文需要使用的是垂直误差线(系列“最大值”Y误差线),而且是负偏差的垂直误差线。(对于垂直误差线来说,正偏差是向上的线,负偏差是向下的线;水平误差线就是左和右了。)
此处的核心在于通过自定义误差量,来达到每一组都能恰好实现最大值到最小值之间的连线,而不会太长或太短。恰好有一个自定义误差量的功能,只需要在对话框内将“正错误值”设置为0,将“负错误值”设置为最大值减最小值的结果即可。因此,在原始数据处添加一个辅助系列即可。
添加辅助系列,并设置误差量(设置的时候先将里面的值删掉再选择,不容易出错):
可以得到长度刚刚好的误差线,然后对误差线的“线条”设置格式,将“线条”改为“实线”,更改颜色,设置“短划线类型”就可以得到结果。
第四步:修改横轴的标签为组别
前文说过“将横轴标签更改为组别”这几个字从表面上看,只需要稍微调整格式就可以了,但是这是实现不了的,需要添加辅助系列来实现目标。
这个方法在刘万祥老师的《Excel图表之道》一书中详细说明过,推荐一看,也可以去网上找电子版。
(1)具体来说,先将横坐标的0-8的标签删掉,需要通过“设置坐标轴格式”,将坐标轴的“标签”的“标签位置”更改为无来实现,而不能直接按delete键删除横坐标,那样的话,横坐标那条线就没了。
(2)添加一个y值为0,x值为组别的辅助系列,将这个系列的散点图加入原图中。
1、在原始数据后添加一个辅助列,值全为0;
2、选中图表,“图表设计”选项卡 → “选择数据” → “添加”系列 → 选择辅助列的数据,将新数据加入原图中;
3、给散点添加位于下方的数据标签,并将数据标签的值设置为仅包括“X值”,也就是我们所需要展示的组别;
4、将辅助系列的点隐藏:设置为“无标记”;
5、将图例中多余的“辅助列”的图例,多点几次选中这一个,注意仅删除这一个,不要把全部图例都删了。然后将图例位置调到上方。
至于最后的这张结果图,其实是单元格+文字的组合,已经不仅仅包括图表了,做法与思路详见《Excel图表之道》一书。
以上。
相关推荐
第一,推荐一下我自己写的Excel画图相关的文章。
(1)图表基础:
『多图』Excel图表元素
『多图』Excel图表元素格式化
(2)特殊图表:
『多图』再论Excel图表中的数据系列之双X轴图表
『多图』如何将日度数据和月度数据绘制于同一个图中
『多图』柱形阴影图
(3)动态图表:
『多图』一文详解Excel中的动态图表
『多图』数据地图框架中的动态图表实现
『多图』数据有效性下的动态图表实现
第二,强烈推荐刘万祥老师的这本《Excel图表之道》,完整阅读、上手操作,将会快速提升你的Excel画图水平。当年我就是读了这本书,后来才写出了那么些关于画图的文章。
京东:
当当:谢谢支持。
2019.7.24凌晨