【动态演示】3个套路带你玩转Excel动态图表!
共 1823字,需浏览 4分钟
·
2020-08-15 14:44
在这篇教程中,为大家分享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
先看最终效果:
动态柱状图
操作步骤
(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,所以工具栏自带“开发工具”,低版本的亲们可以通过下面的步骤来导出“开发工具”功能。
【文件】→【选项】→【自定义功能区】
选择右侧自定义功能区的【开发工具】,点击【确定】
看的再多,也不如自己操作。这次的教程就到这里啦,咱们下次再见!