分享文章:重新启程之Excel图表

上一篇,预告了下面这张图,请大家可以思考,这张图是怎么做出来的?**(不用怀疑,这的确是用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轴的设置,然而有诸多不如意的地方,最终选择放弃。

原文发布于微信公众号 - PowerBI大师(PowerBIMaster)

原文发表时间:2019-08-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券