向商业媒体学习图表和可视化是非常好的方式，因为他们的图表与可视化都是经过专业的设计人员、美术人员制作出来，有很高的专业性和前沿性。很多国外专家也都是向媒体学习，练习用 Excel 来实现媒体的图表。在很久以前，我也是通过购买财经杂志获得案例，来琢磨如何用 Excel 来实现它上面的图表。
今天我们看一则《财新》杂志的动态图表范例，这是他们昨天在微信公众号推出的一篇文章和动态图表，叫中国近十年境外大额房产投资的数据分析。网页链接是：http://datanews.caixin.com/2017-05-25/101094588.html，感谢动图作者韦梦的好作品。
例图分析
这个动态图表非常酷，默认显示一个类似于封面一样的页面，有个堆积柱形图，显示10年来每年分季度累计的投资额，可以看总体的变化趋势。然后有两个选择按钮，你可以选择按国家或者按房产类型来看细分的数据，这时候堆积柱形图变成了垂直方向悬空、水平底边对齐的形式。这种把堆积柱形图改成分组水平对齐的方式，可以克服堆积柱形图不容易看出各分项变化趋势的缺陷问题。还有一个刷新还原的按钮，点击又回到这个封面。
这个交互方式很有新意，尤其是切换时沙盒流动的感觉，非常灵动轻盈。在我们的实际工作中也可以借鉴应用这种交互方式，比如说对你的近十年的销售额数据的分析，分时间、分地区、分产品、分销售渠道等，来进行总体趋势和构成比较分析，都是很合适的。
如果我们用 Excel 来做这个动态图表，可能做不到那么灵动的效果，但是交互的方式和界面是可以做到差不多的。今天我们就尝试用 Excel 来实现这样一个动态交互图表。
作图思路
首先，让我们看一下做图思路。
选择器。这里财新用的是扁平化风格的两个选择按钮和一个刷新按钮，Excel的控件本身无法做到。我们在《向经济学人学图表》第2季里面介绍过利用超链接和自定义函数，把单元格变成一个选择器的做法，很巧妙很高级，但是要用到宏。这里为了简单起见，我们使用窗体控件里面的单选按钮来做，但要想办法让用户看不出来。
悬空柱形图。选择后的3个柱形图，其中有两个是细分柱形图要悬空水平对齐的形式，我们可以使用多行占位数据，把细分的柱形图给垫起来，形成水平方向对齐。
柱形图切换。这3个切换的堆积柱形图，我们其实可以把它做成一个堆积柱形图，只是当你选择某一个分类后，另外两个的数据全部取值为0，你就看不见了。
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，就可以动态链接的文字出现与否。
现在图表的基本完成，我们可以进行收尾了。我们还可以做个事情，把单选按钮的那个圆点点，用一个形状把它遮住，让用户看不出来这是个单选按钮。因为这两个单元格底色可能会发生变化，所以我们用来遮挡的形状来要利用一个条件格式单元格的链接图片，这个单元格设置条件格式，和前面选择器单元格类似。那么这个遮挡的图片就会根据用户的选择是显示为白色还是黑色，来智能化地遮住那个圆形按钮。
最后的动态图表效果如下图：
最后呢，我们可以把文件保护起来，避免用户不小心拖动了这些图表和图片。保护之前，要把这三个单选按钮的属性里面有个锁定给取消。整个图表就完成了，你可以进行一些调试检测，确认模型的制作正确性。
知识回顾
本例综合性比较强，回顾一下，我们用到了哪些知识点呢？
首先是这个扁平化选择器的DIY制作。我们是把文字放在单元格里面，单选按钮做成隐藏的、透明的，看起来是没有 Excel 痕迹的。通过条件格式来对用户的选择作出反馈。
数据组织上，这种悬空水平对齐的堆积柱形图，我们是用了一些辅助占位数据，把它垫起来。
然后这三个柱形图，事实上我们是用的一个柱形图，只是你选择某一个分类的时候这个分类的柱子才全部出来，其他分类都不出现。
然后呢，给这个图表配色，我们是把它拷到ppt里面，利用ppt里面的取色器来快速填充颜色。以及设置了填充图案。取色器移出PPT外 的技巧。
动态的标签，是使用了散点图去智能化标记，柱形图+散点图的组合图表。
还有呢，我们复制这个条件格式单元格，粘贴为链接的图片，也就是拍照方式的图片，去遮挡一个单选按钮的那个圆形按钮，智能变化黑色还是白色。
运用场景
这种形式的动态图表很实用，在我们日常工作中的有很多的应用场景，比如说你公司近12个月或者近10年的销售数据，你可以呢，按时间总数来看，也可以按地域维度、产品类型来分类比较，可以使用这样一个动态图表来切换，既可以看他们的时间趋势，也可以看分类构成。
范例下载
难度系数：★★★★★
实用系数：★★★★★
如何获得范例下载？先转发本帖到朋友圈，然后在微信公众号 iamExcelPro 发送 “caixin1”下载范例文件，动手练习。如果不想从头制作，直接在data表里填入你的数据，直接套用也是可以的。
（本文为语音输入写作，个别地方略有口语风格，望见谅）
想要成为能做出这样动态图表的高手吗？参加我们的《让你的图表动起来》课程，史上最全最体系化最高阶的动态图表技术与应用大全，由浅入深，晋身顶尖高手，不容错过，点击【阅读原文】立即前往。
网友评论