【动态演示】3个套路带你玩转Excel动态图表!

数据分析1480

共 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


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,所以工具栏自带“开发工具”,低版本的亲们可以通过下面的步骤来导出“开发工具”功能。


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



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



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

浏览 8
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报