表格设计课 第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

我相信你肯定也做出来的：

1. 添加“高亮”辅助列
2. 使用IF函数，把负数提取到辅助列中
3. 把辅助列数据添加到图表中

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

知识点汇总

本期的知识点包括：

1. 添加“高亮”辅助行
2. 使用IF、INDEX、OFFSET等函数，提取高亮数据
3. 图表中添加辅助行对应的元素

哪个知识点你不会？请通过投票的形式告诉我，下期我会讲解投票最高的知识点。

6

案例下载

表格设计课，学习3步曲

1. 公众号后台回复0927获取本节练习文件，以及【答案兑换卡】。
2. 转发【答案兑换卡】到朋友圈，回复截图，换取答案。

footer

本文首发公众号【拉小登】
我是拉小登，好的表格是设计出来的，源于Excel，不只Excel。