重新启程之Excel图表

Excel催化剂有完美的IBCS图表的模板供一键完成，但图表的世界，永远有爱好者不断地挖掘出新东西，海峰老师在图表领域造诣也不浅。

---

原创：海峰

上一篇，预告了下面这张图，请大家可以思考，这张图是怎么做出来的？**（不用怀疑，这的确是用Excel做的。）

这张图所呈现的是XXX公司，从2013年到2019年的业务收入，运营利润和税后利润，其中2013年至2017年为实际发生值，2018年为当期，2019年预测值。抛开整体的布局外，对于在Excel里做出这张图，大致有3点可能给大家造成疑惑的，已在图中标记出来。

3个疑点

疑点一：收入，运营利润，税后利润三个数据系列的重叠布局
疑点二：2018年，2019年2个系列不同颜色设置如何实现
疑点三：X的高度，及其轴上2018年和2019年区域不同的颜色变更如何实现

下面就给大家解析，这张图是怎么一步一步做出来的。

疑点一：系列重叠间隔的设置

步骤1：基于这样的数据，可以很容易的得到对应的簇状柱形图

数据表

步骤2：选择其中任一一个系列，将系列重叠设置为80%

系列类重叠设置

疑点二：2018年，2019年2个系列不同颜色设置如何实现

一个比较简单的设置方法是，可以选中2018年，2019年的每个系列进行单独的颜色设置。

当然，如果您认为这是一个疑问的话，肯定是不能满足于这样的回答，另一个方案是辅助列法

步骤1：增加三个辅助列。这时，需要将2018，2019 的数据转移到3个辅助列里去，并在其他位置使用**“NA()”**来填充

步骤2：再次插入簇状柱形图，直接就得到了2018，2019的系列颜色是不一样的图形。
步骤3：重复上述系列重叠的设置后，就能得到如下

疑点三：X的高度，及其轴上2018年和2019年区域不同的颜色变更如何实现

在Excel里的确是可以单独设置X轴的颜色，及其线条的粗细

单独设置X轴的高度和颜色

但是要实现变色是几乎不可能的，所以我们需要引入一张单独的X轴图表来呈现

步骤1：直接增加另外4行辅助列

Xpre：以前年份的数据
Xcurrent：当前年份的数据
Xforecst：预测年份的数据
Xmax：X轴最大值的数据

加辅助数据

步骤2：使用这3列X的数据+年份序列，插入簇状柱形图

设置X轴

步骤3：通过系列的重叠设置100%，把3个不同的系列连接起来

设置X轴

步骤4：选中Xmax的数据区域，复制该区域，然后将其直接黏贴（Ctrl + V）到图中

设置X轴

步骤5：选中“Mmax”系列，将颜色设置为无填充

设置X轴

步骤6：去除不必要的数据，背景颜色设置为无填充，并调整大小与数据图表相一致

设置X轴

如果对X轴的色块高度不满意，可以通过调正表中的数字，和图表的对应高度进行调正，直到满意为止

步骤7：检查数据图表与X轴图表的宽度，并使其保持一致，最后完成2个图表的拼接

**看到这里，小伙伴可能已经被上述各种步骤给整的七晕八素，这么麻烦，还是算了吧，别搞得这么复杂了。**其实，通过最后几步的设置，就可以将整个图表进化成一个模板，只要以后有数据更新，自动就能出图了，是不是有点小激动啊！

步骤7：添加数据表中的判断条件，使其自动判断数据是以前的，当前的，或预测年份的数据（原始数据放在灰色区域，图表数据全部基于后面的辅助列完成）

设置X轴的高度值为3.5（可依据自己的喜好进行调整）
设置当前年份值为2018（按实际情况调整）

在收入数据的“F“列输入公式，根据当前年份自动判断当前行的数据获取

1IF($B7>=$C$3,NA(),C7)

在收入数据辅助列“I”列输入公式，解释同上

1IF($B11>=$C$3,C11,NA())

数据源公式设置

剩余数据项目可参考以上设置

设置完成，来看看成片的效果如何

动态调整

总结

谜底揭晓，原来文章开头的图表是由2张不同的图表组合而成，在这里主要运用的知识点总结如下：

图表的格式设置，文中多次将图表颜色设置为无填充
利用辅助列来完成不同数据系列的设置，避免手动调整颜色
数据系列的重叠设置，包括数据表及X轴的设置
多张图表的拼接，让成图看似为浑然天成
利用条件判断，自动获取数据值
……

最重要的是，通过这个例子，给大家带来一个在Excel里作图的全新思路，就是多张图表的拼接与组合，拓展开去，使用这种方式，可以完成很多以前看起来，在Excel不能完成图表。

**所以，只有想不到，没有做不到，凡是做不到的，都是目前还没想到的。**永远不要停止你的想象和探索……

数据分析爱好者，长期服务于500强企业，擅长自助商业智能分析 助您透视数据本质，洞察商业价值

注：这篇图文耽搁了好几天，主要是一直探索和寻找，如何在一张图表里解决的方案，奈何个人水平实在有限，请大神有思路的，友善提醒，留言告知。凭直觉，本人相信在一张图表里是可以完成的，当然多图表的组合也给你提供了一个新的选择，可以使用在类似的图表上。也曾尝试使用双轴图的次坐标轴来完成X轴的设置，然而有诸多不如意的地方，最终选择放弃。

更多资料，请欢迎前往公众号：DataPivotal

分享成就价值，愿与有相同兴趣同学互相交流，共同成长