让你的Excel图表动起来
有读者提了这样一个问题，想要制作如下的动态图表，要怎么实现？
可以看到，这个动态效果里有4种选择器，左上方的组合框呈现出选择不同的方案展示相应柱形图的效果；
中间的复选框勾上显示差额的图表，不勾就不显示；
右上角的滚动条则实现每页显示多少人，就有多少个柱子的功能；
下方的滚动条实现数据的滚动，因为这个数据是有100多条，一个柱形图显然无法全部展示，所以要用滚动条去认为地滚动展示。
下面我们就来实战一下要怎么实现。上面演示的是从网上找的模板，我们要做的真实数据是这样的：
选择器的制作
记得动态图表是怎么做的吗？首先要有数据源，然后制作选择器，接着取数，最后画图。如果你没有动态图表基础，可以参考以下文章：
这样用Excel动态交互图表，还要什么前端？
下面我们就来制作选择器。
滚动条
总共122条数据，每个人们对应方案1和方案2的值，差异是方案1减去方案2，如果想要把122条数据放在一张图里，就需要用到滚动条了，所以我们先来做滚动条。
点击【开发工具】-【插入】，找到【滚动条】的选项，在表格空白处画一个滚动条。
这样，当我们移动滚动条时，G2单元格中的数值就会跟着变化。
数值调节钮
原模板右上角的滚动条我们用数值调剂钮来代替，同样点击【开发工具】中的【插入】，选择【数值调节钮】，在空白处画一个调节钮。
这样，我们点一下数值调节钮，G3单元格中的数字也会跟着变。
组合框
同样在【开发工具】中插入【组合框】。
右键【设置控件格式】，数据源区域选择提前写好的I2:I4区域，也就是方案一、方案二和方案对比，结果链接到G4单元格中。
复选框
同样我们做一个复选框的按钮，把它链接到G5单元格中。
做好这4个选择器以后，我们要开始做动态图表了，大家要明确一个思路，图表之所以会动，是因为数据在动，数据动态变化，图表自然而然就跟着变了。所以我们的关键在于让数据动起来。
取数：定义动态名称
之前我们一贯的思路是做了选择器之后，做一个取数的区域，让这个区域里的数随着选择器里数值的改变而变化，但是这里我们不能固定一个动态区域了，因为这个区域本身就不是固定的了，它会随着数值调节钮的改变而增加或减少区域，会随着滚动条的改变选择不同区域的数值，因此，我们需要用offset定义动态名称来解决这个问题。
X轴
点击【公式】里的【定义名称】，起个名字，命名为x_data，意思是我们这里要选择的是图表中的X轴。在引用位置处输入公式
=OFFSET(动态图表!$A$1,动态图表!$G$2,0,动态图表!$G$3,1)
这个公式的意思是，以A1单元格为参考下，向下偏移G2单元格数值个行，也就是滚动条变化的值，向右偏移0列，选择G3单元格数值个行，也就是数值调节钮变化的值，选择1列。
这里滚动条的值是1，数值调节钮的值是5，也就是以A1单元格为参考系，向下偏移1行，向右偏移0列，这时就选中了A2单元格，再选择5行，1列，这样就选中了A2:A6区域，也就是图里虚线选中的部分。
Offset函数是实现通过给定参考系，按照偏移量返回特定引用区域的功能，也不复杂，只要想明白它是怎么用的就好。
Offset(以什么为参考系，向下偏移几行，向右偏移几列，选择几行，选择几列)
我们试着改变一下滚动条和数值调剂钮的值，发现x_data区域也跟着变化了，要想实现的就是这效果。
方案1
做好了X轴的动态变化，我们再来做方案1，也就是Y轴的动态变化，同样用到offset动态名称。命名为y1_data，输入公式
=OFFSET(动态图表!$A$1,动态图表!$G$2,1,动态图表!$G$3,1)
可以看到虚线框选到了方案1列所在的区域，这个公式和X轴公式的唯一区别在于向右偏移几列这个参数，这里是1，X轴公式是0.
方案2
同样给出方案2的动态变化，命名为y2_data，输入公式
=OFFSET(动态图表!$A$1,动态图表!$G$2,2,动态图表!$G$3,1)
和方案1公式不同的还是在于向右偏移几列这个参数，这里是2，意思是向右偏移2列。
作图
随便选择ABC3列数据中的区域做一个柱形图，得到如图所示的图表，选中柱子右键【选择数据】，在弹出的对话框中点击右边的水平轴标签中的【编辑】按钮。
把轴标签里的值替换成我们刚刚写好的X轴也就是x_data，注意感叹号！前面的不要动，这是表名。
同样对方案1和方案2进行编辑
将方案1里的系列值替换成y1_data，方案2替换成y2_data
这样我们在调整滚动条和数值调节钮的时候，图表就会变化了，雏形差不多出来了。
方案1&2再细化
细心的你可能发现了，我们最初想要实现的是用组合框控制方案1、2的图形变换，现在这样是两种方案同时呈现的图，和组合框好像没什么关系。
对了，因此需要对方案1和方案2的offset函数再优化，在G6单元格写一个判断函数来告诉offset向右偏移几列这个参数，如果G4单元格，也就是组合框的值为1，也就是选择了方案1的话，if的值为1；否则的话再进行判断，如果组合框的值为3，也就是选择了方案对比的话，if的值也为1，否则为0。这个意思是如果组合框选了方案1和方案3，都向右偏移1列，如果选择了方案2，就偏移0列。（大家细细琢磨一下，得自己理解）
同样给方案2一个if判断，如果组合框选了方案2和方案对比，就向右偏移2列，否则偏移0列。
我们再来重新写一下方案1的offset偏移公式，命名为y11_data，输入公式
=OFFSET(动态图表!$A$1,动态图表!$G$2,动态图表!$G$6,动态图表!$G$3,1)”
和y1_data公式唯一的区别在于向右偏移几列这个参数，这里要改成G6单元格，也就是我们刚if函数去判断的那个单元格。
同样把方案2的公式也改一下，命名为y22_data，输入公式
=OFFSET(动态图表!$A$1,动态图表!$G$2,动态图表!$G$7,动态图表!$G$3,1)
这里也是向右偏移几列写成了G7单元格。
组合框动态图表
我们选中图表中的蓝色柱子，将公式栏中的y1_data，改为刚刚写好的y11_data
选择橙色的柱子，把y2_data改成刚刚写好的y22_data。
这时我们改变组合框的选项，就实现我们最初想要的功能了。
复选框动态图表
快完成了，别忘了还有一个复选框控件，要实现的功能是点击“显示差额”复选框，就输出方案1和方案2的差额对比的图表，数据源就是D列差额。思路还是用offset动态名称加上条件判断的方法。
先在G8单元格给定向右偏移几列这个参数的条件判断，如果G5单元格为真，if判断的值就为3，否则为0，意思是如果选择了复选框，就向右偏移3列，否则就不偏移。
再来给定动态名称，命名为yc_data，输入公式“=OFFSET(动态图表!2,动态图表!2,动态图表!8,动态图表!3,1)”
同样地，随便选择区域数据插入图表，点击柱子，将x轴部分改为x_data，Y轴部分改为yc_data。
这样，当我们点击显示差额，这个图就跟着显示或不显示了。
图表美化
把组合框、复选框和数值调节钮拖到第二行，并把这一片区域填充为橙色。
把 差额 做的这个表拉过来和大表对齐。
把后面那一片背景填充为金色。
把两个图表设置成无颜色填充无框线，再给这片区域加上粗线框，最后得到如下结果：
猜你喜欢:
什么是好的数据指标：精益数据分析
为什么要学统计学：赤裸裸的统计学
成为数据分析师的第三年，我写了10W字
@ 作者：可乐
@ 公众号/知乎专栏/头条/简书：可乐的数据分析之路
@加个人微信：data_cola，备注：进群，拉你入 可乐的数据分析群 和各行各业的小伙伴交流探讨数据分析相关内容