「表格设计课」第10期 堆积图的正确使用方法

表格设计课 第10期.jpg

首先，我对这个栏目做个介绍。

• 什么是「表格设计课」。我们在做Excel时遇到的问题，多半是表格没有设计好，那些各种复杂的公式，都是在弥补表格设计上的错误。「表格设计课」就是教你设计好表格，从源头上避免问题发生。
• 「表格设计课」的思想：好的表格是设计出来的。
• 每期一个表格问题。原始文稿，来自于「读者投稿」或者「网络」，投稿邮箱是laxiaodeng2017@163.com，如果你也有问题，可以给我投稿。
• 每周三更新

以下是本期内容

---

1- 问题描述

上一期的《表格设计课》中，我们学习了班特图，知道它可以在一个图表对象中，使用折线图、柱形图和堆积图，3种不同图表类型，来呈现月、周、天的统计结果，非常的直观。

image

灯丝们都表示非常的喜欢。

image

不过也有不足的地方，比如心比醋酸同学，就发来了疑问：

折线图、柱形图，都非常的直观，但是堆积图，看着还是乱乱的，能做一个高亮吗？

image

答案肯定是可以的。不过在讲解之前，我想给这位，学习后自己动手实践的心比醋酸同学，一个大大赞！

image

2- 问题分析

为什么堆积图会看着乱呢？还是因为数据对比维度太多了。

image

1. 当天线别之间对比。即对比同一根柱子中，不同色块的大小。
2. 同一线别每天的趋势。即对比不同柱子中，相同色块的大小。

所以解决问题的方法就很直接的了：降低对比维度。

image

还记得前面的课程中，我们讲过的，降低对比维度的两个方法吗？

• 减少对比数量。然而，数据量少了，对比的差异性也会削减，不推荐这个方法。
• 减少对比的类型。把数据分类，分为“鲜花”和“绿叶”，把鲜花数据，用不同的颜色标记的醒目一些，数据也会很清晰，比如第3期中，我们制作的这个折线图。

image

所以我们还是，通过减少对比类型，把数据分为“鲜花”和“绿叶”，来让图表更加的直观。

针对这一节的堆积图，我们可以在关(xian)注(hua)的色块上，画一个红色的框框，会让对比变的更加的清晰。

image

3- 解决方案

这个效果的实现原理，和第3期中的“双色柱形图”是一样的。即：使用相同的数据，添加两个堆积图，进行前后叠加。

image

不过，上层图表中，我们希望把关注的色块（鲜花色块）高亮显示，所以要对数据进行处理。由原来的6个色块，变成两个色块：

1. 关注色块（鲜花）
2. 其他色块（绿叶）

image image

接下来，我们以高亮L04线的数据为例，讲解具体的实现步骤：

第1步

在第8_{9行，添加两个辅助行数据，`辅助2(鲜花)`对应着L04线的数据，`辅助1(绿叶)`是L01}L03的数据的加总。

辅助1(绿叶)的目的，是把 辅助2(鲜花)的色块“顶”起来，覆盖住后层堆积图的色块

image

第2步

在堆积图图表中，添加辅助行的数据，注意，辅助1(绿叶)的数据，要在辅助2(鲜花)的上方。

image image

第3步

在图表中设置，辅助1、2对应的色块，使用次坐标轴，使辅助行的色快，叠加在原图表的上方。

image image

第4步

把辅助1(绿叶)对应的色块，设置为透明色，无边框，因为它的作用就是，把鲜花顶起来，不需要显示出来。

设置辅助2(鲜花)对应色块的样式，把L04线对应的色块，高亮显示出来。案例种，我使用了一个红色的变化，你可以根据自己的喜好，随意设置。

image

这样，第4条线的数据，就被高亮标记出来了，我们可以尝试着修改L04的数据，同时红色的矩形的大小、位置，也会随着变化，非常的直观。

[图片上传失败...(image-ce0112-1529995874173)]

4- 动态图表

当然了，只是针对L04线，添加高亮标记是不够的。我们希望，选择哪条线，就把哪条线高亮显示出来。所以要创建一个动态的图表。

[图片上传失败...(image-231f67-1529995874173)]

在上面的动态图表中，红色的矩形，是随着我们选择的线别，对应的进行标记的。具体的实现步骤如下：

第1步

修改第9行辅助2(鲜花)对应的数据，使用INDEX函数，根据J1单元格中的数据，自动获取高亮线别数据。

image

第2步

修改第8行辅助1(绿叶)对应的数据，使用SUM和OFFSET函数，获取高亮线别之前的数据之和。比如，要高亮L04的数据，这里就计算L01~L03的数据之和。

记住，绿叶数据的作用，是把鲜花数据顶起来，覆盖住后面原始的堆积图色块。所以，要计算“鲜花”下方的数据之和。

image

公式修改完成后，我们修改J1单元格的数值，随意输入1~6之间的数字，高亮的红色边框，就会框住对应线别的色块。

[图片上传失败...(image-f90ae3-1529995874173)]

5- 改善输出

动态高亮堆积图是实现了，但是手动的修改数字，来切换高亮线别，显的很low。

我们可以在【开发工具】选项卡中，找到并插入几个选项控件，设置控件链接的单元格，是J1单元格。

image

这样通过点击控件，就可以快速切换线别。

[图片上传失败...(image-23ed44-1529995874173)]

选项控件看上去还是有点单调，不是吗？

如果把每条线的数据，做一个加总，然后对应的创建一个柱形图，快速对比每条线的总不良率。我们就可以轻松的发现，不良率最好，或者最差的线别。

image

最有意思的是，把选项控件放在对应柱子的下方，然柱形图作为选项控件的标签，会让这个空间，更加的炫酷。

[图片上传失败...(image-3e7c1-1529995874174)]

6- 知识点汇总

1. 双色图制作，可以参考表格设计课，第3期
2. INDEX函数，动态获取对应线别的数据。
3. OFFSET函数，动态调整求和选区的大小。
4. 选项控件的使用，控件与单元格的链接设置。

7- 案例下载

案例下载包含两个部分

1- 问题表格

1. 公众号后台回复【0705】获取本节练习文件，自己动手修改表格吧。
2. 同时你会获得一张【答案兑换卡】。

2- 答案表格

如果你改不出来，可以通过下面两种方式，获取答案表格。

1. 转发【答案兑换卡】或文章到朋友圈，回复截图，就可以获取答案表格。
2. 扫描下面的二维码，加入我的小密圈，获取每一期「表格设计课」的答案。

image

来吧，我在圈儿里等你来

footer

我是拉小登，如果你喜欢我的文章，请转发或者打赏，有你的支持，我才能继续写出更多，更好的教程，咱们明天见。