3个套路带你玩转Excel动态图表

罂粟姐姐 | 作者

简书 | 来源

---

在这篇教程中，为大家分享Excel动态图表的三个套路，毕竟自古套路得人心啊。

1

辅助数据和复选框的结合

先看最终效果：

动态折线图

 操作步骤 

（1）新建辅助数据表，在B18单元格输入公式=IF(B$30,B3,NA())

公式解读：当B30为ture的时候，B18单元格的取值为B3，当B30为false的时候，B18单元格显示错误值。

输入公式

（2）添加并美化表单控件

有11个产品，需要添加11个表单控件。

开发工具-插入-表单控件-复选框，复制粘贴10次，并修改复选框名称。（按住CTRL键单击复选框可以进入编辑状态）

添加表单控件

全选所有控件（同样按住Ctrl键），利用对齐-左对齐-纵向对齐完成美化表单控件的工作。

美化表单控件

（3）将控件与辅助数据链接

选择控件-单击右键-设置控件格式-单元格链接-导入链接的单元格，冰箱链接B30，洗衣机链接B31，依次类推，完成11个控件的链接。

这个时候，我们会发现勾选复选框，其对应的单元格显示为ture，产品1-12月所有的数据均正常显示，不勾选的复选框，其对应的单元格依旧为#N/A，代表该列数据不显示。（结合第一步的公式来理解）

控件与数据链接

（4）根据辅助数据制作折线图

插入-折线图-选择数据，完成作图。

最终效果

公式还有一种写法，=IF(OR($A$30,B$30),B3,NA())，区别就是增加了一个“全选”复选框，链接数据A30，当勾选“全选”复选框时，A30为ture，可以一次性选择所有产品的折线图。

OR公式效果

此外，还可以用OFFSET函数加复选框来实现此功能，而且不需要辅助数据，但是过程比较繁琐，不如辅助数据简单方便易懂。

2

INDIRECT函数与数据有效性的结合

先看最终效果：

动态柱状图

 操作步骤 

（1）整理原始数据

1月-12月原始数据表格式尽量保持一致，产品类型统一排序，既整齐美观，又方便公式的批量应用。

整理原始数据

（2）建立年度汇总表

利用数据有效性实现月份的动态选择。

月份动态选择

制作年度汇总表，使用INDIRECT函数实现各月份数据的引用。

INDIRECT(ref_text,[a1])可以实现对单元格的引用，如果只有1月，那么B3='1月'!B3，现有1月-12月，那么B3='1月'!B3、'2月'!B3、……，C3='1月'!C3、'2月'!C3、……，其中1月、2月、……为A1单元格。

根据规律，第一个参数ref_text就应该为$A$1&"!B"&ROW()，这样B3就为A1单元格选择的月份所在的sheet表里的B3单元格的数据，以此类推：

• B列最终公式=INDIRECT($A$1&"!B"&ROW())  

• C列最终公式=INDIRECT($A$1&"!C"&ROW())

年度汇总表

（3）制作柱状图

将辅助数据月份隐藏，插入柱状图，修改图表类型，构建组合图表，实际与计划两列数据为主坐标轴，差异为次坐标轴。

制作柱状图

（4）美化图表

将差异变为柱状图，插入数据标签，字体、排版优化。

美化图表

3

数据透视图与切片器的结合

2010及以上版本的Excel中有一个非常强大的人性化工具，就是切片器，当切片器与数据透视图在一起时，产生了非常惊艳的动态图表效果，非常简单方便。

 操作步骤 

（1）插入数据透视图

插入-插入数据透视图，可以制作出基本的可筛选的动态图表。

插入数据透视图

（2）插入切片器

插入-切片器-插入切片器选择筛选的字段。

插入切片器

（3）切片器使用

切片器可以多选，也可以按住Ctrl多选，还可以调整切片器大小，当不同字段有包含关系时，选择其中1个，另外一个切片器中不属于它的内容全部变为灰色。

切片器使用

Excel动态图表有很多类型，也有很多可以实现的方法。因此，大家在学习的时候多总结多探索，有时候不是我们做不到，只是我们想不到哦~

 注意 

我用的是2016版Office，所以工具栏自带“开发工具”，低版本的亲们可以通过下面的步骤来导出“开发工具”功能。

【文件】→【选项】→【自定义功能区】

选择右侧自定义功能区的【开发工具】，点击【确定】

看的再多，也不如自己操作。这次的教程就到这里啦，咱们下次再见！

●Python实现常用的假设检验 ！

●12000+字超详细 SQL 语法速成！

后台回复“入群”即可加入小z干货交流群

Get????