这个图表竟然是用Excel做的？！--续：刘老师，我做的比你更好！
按：咱们的读者和粉丝都是完美控、强迫症。上次《这个动态图表竟然是用Excel做的？！》帖子发出后，读者杨小府发来了自己的做法，把柱形图做成了堆积的小方块，更接近原例。我请他写了个教程，供大家参考。谢谢小府童鞋分享！欢迎大家打赏，给小府发红包：）
上次在Excel图表之道公众号看过刘万祥老师题目为《这个动态图表居然是用 Excel 做的？！》之后，再一次被刘老师的技艺折服，翘课的同学可以再学习下，原文链接是：http://mp./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” 获得下载链接。
有兴趣沟通的朋友可以加我微信，感谢支持！
【点评】：小府使用单元格条件格式来实现小方块堆积的条形图，更接近财新的原例，不过实现起来更麻烦，性价比不足，但仍是很好的练习材料。比如，大量练习了多条件的公式型条件格式，可以深入理解和熟练掌握。
【友情提示】网易618活动火热进行中，20周年，年度最惠，5重优惠，不容错过！点击【阅读原文】立即参加 ↓↓↓
责任编辑：
声明：本文由入驻搜狐号的作者撰写，除搜狐官方账号外，观点仅代表作者本人，不代表搜狐立场。
今日搜狐热点这个动态图表居然是用 Excel 做的？！
本文转载自刘万祥老师来源：Excel图表之道向商业媒体学习图表和可视化是非常好的方式，因为他们的图表与可视化都是经过专业的设计人员、美术人员制作出来，有很高的专业性和前沿性。很多国外专家也都是向媒体学习，练习用 Excel 来实现媒体的图表。在很久以前，我也是通过购买财经杂志获得案例，来琢磨如何用 Excel 来实现它上面的图表。今天我们看一则《财新》杂志的动态图表范例，叫中国近十年境外大额房产投资的数据分析。网页链接是：//.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制作出高逼格的动态图表，可以加入我的课程《让你的图表动起来》。课程准备了对应的练习范例，按要求完成作业，即可掌握课程的方法和技巧。现在这门课程正在参加超级砍价参与即可享受底价269点赞额外再减10元还可使&#x的平台优惠券仅&#x！ 课程大纲（部分） 章节1:开始之前章节2:动态图表导论章节3:选择器的常见形式章节4:常用抽数函数章节5:常见交互形式 - 下拉框、列表框、有效性章节6:常见交互形式 - 单选按钮章节7:常见交互形式 - 复选框章节8:常见交互形式 - 滚动条章节9:常见交互形式 - 微调按钮章节10:综合运用与高级技巧章节11: 综合动态仪表板章&#x个建议章节13:课程收尾只此一次扫码进入疯狂砍价点击阅读原文，跟刘老师学图表点击“阅读原文”，跟刘老师学图表