这个动态图表居然是用 Excel 做的？！

本文转载自刘万祥老师

来源：Excel图表之道

向商业媒体学习图表和可视化是非常好的方式，因为他们的图表与可视化都是经过专业的设计人员、美术人员制作出来，有很高的专业性和前沿性。很多国外专家也都是向媒体学习，练习用 Excel 来实现媒体的图表。在很久以前，我也是通过购买财经杂志获得案例，来琢磨如何用 Excel 来实现它上面的图表。

今天我们看一则《财新》杂志的动态图表范例，叫 中国近十年境外大额房产投资的数据分析 。网页链接是：http://datanews.caixin.com/2017-05-25/101094588.html，感谢动图作者韦梦的好作品。

这个动态图表非常酷，默认显示一个类似于封面一样的页面，有个堆积柱形图，显示10年来每年分季度累计的投资额，可以看总体的变化趋势。然后有两个选择按钮，你可以选择按国家或者按房产类型来看细分的数据，这时候堆积柱形图变成了垂直方向悬空、水平底边对齐的形式。这种把堆积柱形图改成分组水平对齐的方式，可以克服堆积柱形图不容易看出各分项变化趋势的缺陷问题。还有一个刷新还原的按钮，点击又回到这个封面。

这个交互方式很有新意，尤其是切换时沙盒流动的感觉，非常灵动轻盈。在我们的实际工作中也可以借鉴应用这种交互方式，比如说对你的近十年的销售额数据的分析，分时间、分地区、分产品、分销售渠道等，来进行总体趋势和构成比较分析，都是很合适的。

如果我们用 Excel 来做这个动态图表，可能做不到那么灵动的效果，但是交互的方式和界面是可以做到差不多的。今天我们就尝试用 Excel 来实现这样一个动态交互图表。

首先，让我们看一下做图思路。

1、选择器。

这里财新用的是扁平化风格的两个选择按钮和一个刷新按钮，Excel的控件本身无法做到。我们在《向经济学人学图表》第2季里面介绍过利用超链接和自定义函数，把单元格变成一个选择器的做法，很巧妙很高级，但是要用到宏。这里为了简单起见，我们使用窗体控件里面的单选按钮来做，但要想办法让用户看不出来。

2、悬空柱形图。

选择后的3个柱形图，其中有两个是细分柱形图要悬空水平对齐的形式，我们可以使用多行占位数据，把细分的柱形图给垫起来，形成水平方向对齐。

3、柱形图切换。

这3个切换的堆积柱形图，我们其实可以把它做成一个堆积柱形图，只是当你选择某一个分类后，另外两个的数据全部取值为0，你就看不见了。

4、3个柱形图的类别名称标签

可以使用1组散点图来智能化地标记。

好，这个就是我们的作图思路，下面可以开始制作了。

1、 第一步，是制作这个扁平化的选择器。

我们直接利用单元格写上“按国家和地区”、“按房产类型”，和一个表示刷新的特殊符号“↺”，这个圆弧箭头，你需要在那个插入符号里面找一找。然后，我们画3个空白的单选按钮放在这3个单元格的上方，锚定对齐好。他们的选择结果链接到 control工作表的 C3 单元格，D3 顺便翻出选择结果备用。

当用户选择之后，前面两个格子要做出格式反馈，可以使用条件格式。 选中前面两个格子，条件格式，使用公式的条件格式，=C6=control!$D$3，即如果其值等于选择结果对应的内容，那么就把这个单元格设置为白色填充黑色字体，表示被选中了。

2、 第二大步骤，是组织作图数据。

这是我们的数据源，10年的数据，有3个细分维度：

先把这个原数据，通过选择性粘贴、粘贴链接的方式引用到新的工作表。这样做是为了 方便我们后期好更新源数据 。

按国家和按房产类型的堆积柱形图，它是需要悬空以后水平对齐的，所以说我们就要在每行数据下面插入一个空行，组织占位数据。

图中，D16的公式：=MAX($D15:$N15)-D15+vgap_2

公式的意思是说我们取上1行的最大值减去上一格的值，然后再加上一个留空值。这个留空的间隔vgap_2，我们把它做成一个参数方便调整，暂时取值为整个分类数据里的最大值除以5。

这个公式注意写法，锁定列号，行号不锁定。向右复制到整行，然后能把这一行复制粘贴到余下的所有占位行。检查公式和结果正确。

然后，我们要根据用户的选择，分别引用这3块里面的1块，到作图数据区域，

第1块区域，Q9：=IF(control!$C$3=3,calc!D9,NA())，复制到满区域；

第2块区域，Q15：=IF(control!$C$3=1,calc!D15,NA())，复制到满区域；

第3块区域，Q40：=IF(control!$C$3=2,calc!D40,NA())，复制到满区域；

3、第三步骤，做图表。

现在我们以P8：AA50 数据区域插入堆积柱形图，并对所有的占位行数据序列的柱形图设置为无填充色。这一步你要分别通过选择器选择 按国家和地区、按房产类型，和刷新复原情况下的图表，把所有占位数据的柱形图都设置为无填充色。

现在你测试选择，应该可以看到动态图表的变化，检查确认正确。下面我们来给它加上那个类别标签。

我们需要准备一列辅助数据，是一个散点图的序列xy，也是分3块分别取值：

第1块区域，显示季度数据时，标签放右侧，x，AC9：=IF(control!$C$3=3,11,NA())，y，AD10：=SUM($Z$9:Z10)-Z10/2。这里第1行有一点特别处理，需要看看。

第2块区域，显示国家数据时，标签放左侧，x，AC15：=IF(control!$C$3=1,0.5,NA())，y，AD15：=SUM($Q$15:Q15)-Q15。往下复制到满区域。

第3块区域，显示类型数据时，标签放左侧，x，AC40：=IF(control!$C$3=2,0.5,NA())，y，AD40：=SUM($Q$40:Q40)-Q40。往下复制到满区域。

数据准备好之后，我们往图表里面添加一个新的序列，数据源暂时不管，确定，然后把这个序列更改图表类型为散点图，确认，再选择数据，给他的数据源指定xy序列，分别是AC、AD列，确定之后，图表里应该出现一组散点图，给它添加数据标签，指定为P列的的类别名称，位置靠左。

现在我们通过选择按钮来切换图表，检查标签的位置，发现当用户选择刷新复原后，那个1234季度的标签位置靠左是不合适的，单独把这四个标签的位置分别调整为居中，以及第1季度的标签靠右。

4、第四步，美化与收尾。

现在动态图表模型已经完成，切换图表确认是正确的，然后让我们来做一些格式美化。为了实现类似财新杂志的风格，我们可以把这个图表拷到ppt里面去，利用ppt里面的取色器，从财新例图取色，来对柱形图快速进行逐一的填色。

我们还可以通过柱形图的图案填充，选择一种方块格的图案，比较接近于财新的这个圆点风格。这两步都是重复操作，略微有些繁琐。

在点刷新复原按钮选的时候，封面图里面有一些文字说明，我们也可以利用一个文本框来动态引用。先在 control表的B12 写好说明文字，然后在B15：=IF(cur_sel=3,B12,"")，根据用户的选择，如果等于3，就引用这个说明文字，否则为空。然后在图表区域里面插入一个文本框，在公式栏里链接等于这个B15，就可以动态链接的文字出现与否。

现在图表的基本完成，我们可以进行收尾了。我们还可以做个事情，把单选按钮的那个圆点点，用一个形状把它遮住，让用户看不出来这是个单选按钮。因为这两个单元格底色可能会发生变化，所以我们用来遮挡的形状来要利用一个条件格式单元格的链接图片，这个单元格设置条件格式，和前面选择器单元格类似。那么这个遮挡的图片就会根据用户的选择是显示为白色还是黑色，来智能化地遮住那个圆形按钮。

最后的动态图表效果如下图：

最后呢，我们可以把文件保护起来，避免用户不小心拖动了这些图表和图片。保护之前，要把这三个单选按钮的属性里面有个锁定给取消。整个图表就完成了，你可以进行一些调试检测，确认模型的制作正确性。

如果大家也想用Excel制作出高逼格的动态图表，可以加入我的课程 《让你的图表动起来》 。