「表格设计课」第13期 双色图的魅力和原理
首先,我对这个栏目做个介绍。
什么是「表格设计课」。我们在做Excel时遇到的问题,多半是表格没有设计好,那些各种复杂的公式,都是在弥补表格设计上的错误。「表格设计课」就是教你设计好表格,从源头上避免问题发生。
「表格设计课」的思想:
好的表格是设计出来的
。每期一个表格问题。原始文稿,来自于「读者投稿」或者「网络」,投稿邮箱是laxiaodeng2017@163.com,如果你也有问题,可以给我投稿。
每周三更新
以下是本期内容
— — —
前两天,有一个同学问我,下面这个图表中,怎么把坐标轴下方的柱子,变成红色?
这个问题,是一个典型的双色图问题,因为比较忙,我没有回答制作的过程,而是直接做好发给了她,并承诺在13期的课程里讲解。她拿到图表和许诺后,像得到一颗棒棒糖的孩子一样,高兴的干活去了。
今天我就兑现诺言,跟大家讲讲双色图的魅力和原理。
什么是双色图
这里我所说的双色图呢,并不说,在图表中出现两种颜色。而是使用不同的颜色,把需要强调的图表元素,高亮显示出来,让信息传递的更高效。所以这里的双色,严格来讲应该是指:
需要强调的颜色
一般数据的颜色
在前面的课程中,也大量的应用了双色图的原理,我稍微总结了一下,双色图大致可以分为下面几种类型:
n对1型
1对0.n型
1对0型
都是什么意思呢?继续往下看。
n对1型
n对1型,意思就是说,如果图表有很多的元素,可以把最重要的,或者我们当前正在看的数据,使用不同的颜色,高亮显示出来。
n的图表元素是一种颜色,高亮出来的图表元素,是另一种颜色。
比如在《第3期 动态图表,你想不到的美》 中,把当前正在看的线条设置红色,既可以看到它的趋势,也可以轻松对比不同线条之间的高度差异,让原本乱糟糟的一堆线条,变得清晰。
再比如《第10期 如何正确使用堆积图》中,我们常用的柱状堆积图,因为颜色非常的多,让图表变得眼花缭乱。给当前正在看的色块,添加一个红色的边框,让我们的视觉有了焦点,减少了一些凌乱的感觉。
1对0.n型
所谓1对0.n型,就是把原本一个整体的数据中,部分的数据,使用不同的颜色,高亮显示出来。
原始数据是一种颜色,高亮部分的数据,是另一种颜色。
比如在《第11期 我和组长就差一个“未达成率”》中,把折线中目标值以下的线条,用红色标记出来,这样不合格的数据就一目了然了。
在比如《第12期 如何用直方图漂亮的呈现达成率》中,我们把合格、不合格的柱子合并成1个,设置成一个边框、无填充色。然后再把合格的柱子填充上颜色,形成占比;这样留下来的空白,就是不合格的数据。简单明了。
1对0型
1对0型,相当于给图表添加了一个开关,可以显示或者隐藏图表中的某个元素。
显示图表是一种颜色,不显示是另一种个颜色。
目前课程中还没有涉及到这个类型的双色图,不过我们做了一个简单的demo,你可以感受一下:
双色图的原理
看过这么多的双色图的案例,我相信你对它应该有了一定的认识,也产生了一些兴趣,那么要怎么制作双色图呢?
方法有很多,但是你要记住一个不变的中心思想:把高亮数据单独提取出来,作为图表的数据源。这样既方便了我们给图表设置颜色,也可以轻松的扩展成动态图表。
提取高亮数据的方法就各不相同了,不过大致都是使用下面几个函数实现的:
INDEX
OFFSET
VLOOKUP
IF
下面我列举几个案例:
VLOOKUP提取高亮数据
在《第3期 动态图表,你想不到的美》 中,高亮的红色线条就是使用VLOOKUP函数提取出来的。
在上面的表格中可以看到,我在第9行添加了一个辅助行,然后在辅助行里,使用VLOOKUP函数根据指定的城市名称,获取对应行的数据。
然后把这个数据添加到图表中,并设置对应的线条为红色,再加上一个下拉菜单来更改城市名称,就实现了动态高亮的效果。
IF提取高亮数据
在《第11期 我和组长就差一个“未达成率”》中,只把不合格的数据,提取出来并标记成红色,就是使用IF函数实现的。
同样的,我添加了一个“高亮”辅助行,然后使用IF函数,判断实际值是否小于目标值,如果是就提取到辅助行中,如果不是就显示空或者NA(),进而实现了部分数据的高亮。
实战练习
双色图的魅力看了,原理呢,我们也讲,咱们再回到开始的问题:如何把下图中,坐标轴下方的柱子,变成红色?你有答案了吗?
对了,它的数据表格是这样的。
我相信你肯定也做出来的:
添加“高亮”辅助列
使用IF函数,把负数提取到辅助列中
把辅助列数据添加到图表中
1- 添加辅助列
在数据表格的下方,添加一个“高亮”辅助列。
2- IF函数提取数据
在辅助列中,添加IF函数公式,把负数提取出来。公式如下:
=IF(D2-C2<0,D2-C2,0)
另外,正数列中,也是用IF函数,把负数值过滤掉,公式的判断条件刚好相反:
=IF(D2-C2>0,D2-C2,0)
3- 更新图表
最后,把辅助列的数据,添加到图表当中。
有一个小技巧,直接复制辅助列的数据,然后选择图表,按下ctrl+v粘贴,快速添加数据。完成之后的效果如下:
改善输出
更新后的图表,蓝色和红色柱子完全独立,修改任何部分的数据,图表都可以完美的更新。
知识点汇总
本期的知识点包括:
添加“高亮”辅助行
使用IF、INDEX、OFFSET等函数,提取高亮数据
图表中添加辅助行对应的元素
哪个知识点你不会?请通过投票的形式告诉我,下期我会讲解投票最高的知识点。
案例下载
表格设计课,学习3步曲
公众号后台回复
0927
获取本节练习文件,以及【答案兑换卡】。转发【答案兑换卡】到朋友圈,回复截图,换取答案。
更详细的操作步骤,可以点击
阅读原文
,观看《表格设计课》讲解视频。
我是拉小登
点赞、评论、转发
评论区点赞最高的同学
可以获得本节对应视频课程