按：咱们的读者和粉丝都是完美控、强迫症。上次《这个动态图表竟然是用Excel做的？！》帖子发出后，读者杨小府发来了自己的做法，把柱形图做成了堆积的小方块，更接近原例。我请他写了个教程，供大家参考。谢谢小府童鞋分享！欢迎大家打赏，给小府发红包：）
上次在Excel图表之道公众号看过刘万祥老师题目为《这个动态图表居然是用 Excel 做的？！》之后，再一次被刘老师的技艺折服，翘课的同学可以再学习下，原文链接是：http://mp.weixin.qq.com/s/FtRS1bwg2yRSKHFFxqRm3Q。
下面的两张图就分别是原《财新》杂志的范例和刘老师做的模仿图。
1.《财新》杂志范例
2.Excel图表之道模仿图
是不是吊吊哒？这张图的特点我就不讲了，上次刘老师讲的已经很详尽了。但是呢，对比下来，感觉刘老师的模仿图做到了神似，形上还有些地方可以改进，这么说希望老师别介意:-)。
为什么这么说呢，细心的同学应该可以发现，《财新》原图是用不同的圆点元素代表特定单元值，所以大多数数据表的顶端是不平齐的，而刘老师的模仿图采用的是针对堆积柱形图进行图案填充，也就没有了这种特点，在精细、美观两点尚有待提高。
作为一个不逼死自己不罢休的强迫症患者，我就会想，用Excel是不是可以做到更逼真的模仿呢？经过一些尝试，确实是可以的。接下来我就跟大家分享下我的小方法——用条件格式模仿中国对外房地产投资动态图。
分享形式呢，就模仿老师的方式吧，分为作图思路、制作步骤、知识回顾三部分，一方面更容易比较两种方法的异同，一方面也是致敬刘老师。
作图思路
选择器。图表之道用的是单选按钮控件来做的，这种方法更接近原图，起初我也尝试着用这种方法，但是按钮控件位置的锁定上总是有些小问题，所以就改用了比较简单的组合框控件。
条件格式“柱形图”。要实现单元点的方式绘制柱形图表暂时没想到什么可行的方法，之前尝试过一种通过多组柱形图并列组合，针对每组柱形图采用色块图片层叠填充的方式做过类似的图，但是效果一般，有兴趣的可以自己尝试下。这里我采用的是缩小excel界面显示比例，并通过条件格式控制单元格填充的方式，做出类“柱形图”的效果。
柱形图切换。通过更改组合控件，改变作图区的值，通过条件格式控制对应单元格填充色，即可针对三种不同分类进行区别呈现。
3个柱形图的类别标签，这里采用的比较笨拙的方式，插入文本框，并对文本框进行单元格引用，这样就可以实现切换分类时显示对应标签的效果，当然也可以按照刘老师的方法使用散点图，我这里只是提供一种思路。
以上就是我们的作图思路，下面就是具体的实现方法了。
制作步骤
1. 首先，制作选择器。
我们的选择器比较简单，插入组合框控件即可，在calc!$AL$7:$AL$9分别输入按整体年份、按国家及地区、按房产类型，并将其作为控件的数据源区域，控件的单元格链接我们选择calc!$H$1。calc!$H$1这个单元格很重要，后面的数据设置基本跟它相关。
2. 第二大步骤是组织作图数据。
数据源我们采用刘老师图表中的数据，处理方法有一部分基本类似，类似的部分我偷个懒直接复制下来，版权为刘老师所有。
先把这个原数据，通过选择性粘贴、粘贴链接的方式引用到新的工作表。这样做是为了方便我们后期好更新源数据。
按国家和按房产类型的堆积柱形图，它是需要悬空以后水平对齐的，所以说我们就要在每行数据下面插入一个空行，组织占位数据。
图中，D16的公式：=$E$2+MAX($D15:$N15)-D15
公式的意思是说我们取上1行的最大值减去上一格的值，然后再加上一个留空值。这个留空的间隔$E$2，我们把它做成一个参数方便调整，暂时取值为整个分类数据里的最大值除以6。
这个公式注意写法，锁定列号，行号不锁定。向右复制到整行，然后能把这一行复制粘贴到余下的所有占位行。检查公式和结果正确。
然后，我们要根据用户的选择，分别引用这3块里面的1块，到作图数据区域。
由于作图方式不同，所以我们的作图数据区域会有所调整，见上图红框部分，这里有2个注意点：
① 合计部分为0是因为设置条件格式的作图区域使用了vlookup的模糊引用，在这里vlookup相当于“>=”的作用，具体的用法后面介绍。
② 受vlookup的影响，季度前一列的数字与左边的相比统一向上平移一个单元格，左边序列下空出的单元格采用较大的数字进行填充，这里采用的是100-。
3个区域的公式相应进行调整：
第一块区域：S9：=IF($H$1=1,SUM(D$9:D9),NA())，复制到满区域；
第二块区域：S15：=IF($H$1=2,D15,NA())，
S16：=IF($H$1=2,INT((D16+S15)/($E$1*10))*$E$1*10,NA())，
S17：=IF($H$1=2,D17+S16,NA())，
将S16、S17对应的复制到满区域；
第三块区域：S40：=IF($H$1=3,D40,NA())，
S41：=IF($H$1=3,INT((D41+S40)/($E$1*10))*$E$1*10,NA())
S42：=IF($H$1=3,D42+S41,NA())，
将S41、S42对应的复制到满区域；
3.第三步骤，填充作图区域辅助表数据及填写作图区域数据，并设置条件格式
① 填充作图区域辅助表数据
新建一个sup插页，作为作图区域的辅助内容。
其中D120=0，E120=D120+calc!$E$1，D119 =M120+calc!$E$1，calc!$E$1代表一个单元值，按照这种方式将D1：M120填满，这样就构成了10*120个单元数据，每个单元数据比相邻左方单元格大一个单元值。
这就构成了2007年图的辅助区域，将D1：M120依次向右复制10次，作图区域的辅助表数据我们就完成了。
② 填写作图区域数据
新建一个sub2插页，图片将在这个插页生成！
在sub2插页中中我们选用和上述等宽等高的区域N12：EC131（最开始其实选用的是D1：M120，只不过后期作图存放标签需要空间，进行了相应的调整），将该区域等分成10份10*120的间隔小区域。
第一块区域，N131：=IFERROR(VLOOKUP(sup!D120,IF(calc!$H$1=1,IF({1,0},calc!$S$8:$S$12,calc!$Q$8:$Q$12),IF(calc!$H$1=2,IF({1,0},calc!$S$14:$S$37,calc!$Q$14:$Q$37),IF({1,0},calc!$S$39:$S$50,calc!$Q$39:$Q$50))),2,1),0)，复制到满区域；
第二块区域，Y131：=IFERROR(VLOOKUP(sup!O120,IF(calc!$H$1=1,IF({1,0},calc!$T$8:$T$12,calc!$Q$8:$Q$12),IF(calc!$H$1=2,IF({1,0},calc!$T$14:$T$37,calc!$Q$14:$Q$37),IF({1,0},calc!$T$39:$T$50,calc!$Q$39:$Q$50))),2,1),0)，复制到满区域；
第三块区域，AJ131：=IFERROR(VLOOKUP(sup!Z120,IF(calc!$H$1=1,IF({1,0},calc!$U$8:$U$12,calc!$Q$8:$Q$12),IF(calc!$H$1=2,IF({1,0},calc!$U$14:$U$37,calc!$Q$14:$Q$37),IF({1,0},calc!$U$39:$U$50,calc!$Q$39:$Q$50))),2,1),0) ，复制到满区域；
依次对后续区域进行填充即可，最后使用自定义数字格式“;;;”将数字隐藏。
注：加粗部分代表的是之前calc插页中框住的三个区域不同日期对应的数据。
③设置条件格式
选中N131单元格，通过条件格式>>新建规则>>使用公式确定要设置格式的单元格的方法设置填充色。这里共设置了22次条件格式，举例如下：
.
.
.
具体格式见范例文件。
4.美化与收尾
经过上述步骤得到的图仅仅只有柱状部分，但是每个单元的长宽是不一样的，如果想要将每个小单元调成正方形，我们可以通过视图中选择页面布局使行高列宽均变成cm为单位，然后进行调整。
接下来我们还需要使用文本框来做标签，首先添加横坐标标签（年度），这部分恒久存在，直接在文本框中添加年份，放置相应位置码齐即可。其他部分标签因为不同分类显示不同，所以需要使用文本框的动态引用。在AI6：AJ37区域我们分别使用公式对文本的显示进行了定义，通过将文本框引用这单元格后进行字体、字号、位置调整整个图表就算完成了！
由于整个作图区占用区域过大，为了方便呈现，我们使用选择性粘贴中链接图片的方式将其粘贴至data插页，并与筛选框进行组合。
最终的动态图效果如下图：
当然，最后不得不说，这种做法的通配性相比插入式图表要差很多，调整起来会比较麻烦，在这里仅给大家提供一种以后的做图参考思路。
知识回顾
总结一下，以上一共应用了以下几个知识点：
组合框控件的应用；
vlookup模糊匹配的应用；
自定义数字格式“;;;”隐藏数字；
使用公式的方法设置单元条件格式；
调整单元格行高列宽一致；
选择性粘贴中的图表链接粘贴方法。
范例下载
仍然是在 iamExcelPro 发送 “caixin1” 获得下载链接。
有兴趣沟通的朋友可以加我微信，感谢支持！
【点评】：小府使用单元格条件格式来实现小方块堆积的条形图，更接近财新的原例，不过实现起来更麻烦，性价比不足，但仍是很好的练习材料。比如，大量练习了多条件的公式型条件格式，可以深入理解和熟练掌握。
网友评论