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