什么是Excel动态图表交互,如下动图所示,选择不同的日期,出现相应的数据和环比分析,同时折线图跟随日期动态延展等效果。
这种动图交互的图表对Excel综合技能的应用考验的比较多,尤其还涉及图表盘设计、颜色搭配、数据与交互之间的逻辑选择等,其实并不难,但想做好也不简单,如果大家对这种交互图表感兴趣,后续会开一个系列专门写写它,今天先简单介绍一下。(文末有本节内容的讲解视频,后台回复“动态交互”,可获得本节内容的练习数据。)
01 原理
整个动态图表交互的原理可以从4个方面理解:
首先要有数据源,这个就不多说了
第二步是制作选择器,根据实际需求选取【开发工具】里面的组合框、复选框、列表框等控件,或数据透视表中的切片器,或数据有效性都可以作为选择器
第三步从数据源中抽取随控件的选择而动态变化的数据,需要用到vlookup、index、match、offset等函数的组合应用
将第三步抽取出来的数制作静态图表,选择控件中不同的值,图表自然而然动态变化
结合条件格式、配色以及花哨的静态图表综合运用,可以得到类似power Bi的效果。
02 实例演示
光说不练假把式,我们来实际演示一个案例做做看
数据源1是用户侧数据,从9月1日到9月29日的新关注人数、取消关注人数、净增关注人数和累积关注人数。
注:以上数据纯属瞎编
Step1:做选择器
本例中用到的选择器有组合框和列表框,这些可以称之为控件,控件在【开发工具】——【插入】选框中插入,如果没有开发工具选项,可以从Excel【文件】——【选项】——【自定义功能区】中将开发工具前的空白栏勾上,再回到页面中就可以看到开发工具的功能项了。
这里先做一个日趋选择的组合框,插入组合框完成后右键【设置控件格式】,在弹出的设置对象格式对话框中,数据源区域选择$A$3:$A$31区域,也就是数据源1的日期列,单元格链接选择H1区域。
组合框选中一个值,会呈现该值在这组数据中是第几个,因此显示的是一个数字,如组合框选择9月3日,在相应的单元格链接中会显示3的值。
Step2:取数
Step2.1 第一个动态交互效果
接下来我们要做第一个动态交互效果,随着组合框选择不同的日期,4个关键指标的数字跟着相应变化。
还记得动态交互图表的原理吗,4个步骤,选择器做好下一步就是取数,做一个数据源1的取数表,我们要抽取4个指标下当日和前日的数据,以及算出其环比,这将在第二个展示区中呈现。
当日的数据用到index函数,H4单元格输入公式,=INDEX(B$3:B$31,$H$1),该公式的意思是取B$3:B$31这组数据中的第$H$1个值,H1是日期组合框的值。
当日数据抽取出来后,前日的数据就好懂许多,同样也是用到index函数,只需把第几个值改成日期组合框-1即可,=INDEX(B$3:B$31,$H$1-1)
环比就是用(当日-前日)/前日得到的值
回到图表展示区,在新增关注人数下方输入“=数据源!H4”,即把数据源1取数表中H4当日新关注人数的值链接过来,其余同理,这样,就实现了开头的随日期变化4个指标也变的效果。
Step2.2 第二个动态交互效果
接下来做第二个动态交互效果,随着组合框日期选择的变化,用颜色和箭头显示4个指标的环比,先说结论,用到的是条件格式。
和上一步一样,在新增关注人数旁边输入“=数据源!J4”,即把数据源1取数表中J4单元格新关注人数的环比的值链接过来,其余同理
用条件格式实现大于0的值标记为绿色向上箭头,小于0的值红色向下箭头,等于0的值黄色向右箭头。
选中要进行条件格式的区域,【开始】——【条件格式】——【新建规则】,选择基于各值设置所有单元格的格式,图标样式选择3箭头的类型,值的设置区域就按大于0,等于0,小于0设置。
Step2.3 第三个动态交互效果
第3个动态交互效果也是重中之重,就是这个图表了,它有两个交互模式,一个是根据4个指标的选择绘制折线图,另一个是根据日期组合框的选择,在图表中随时间动态延展。
对数据源1插入透视表,并插入切片器,这样就能做到随着切片器选择不同的指标,值相应变化的效果。
对相应的值插入折线图,这样随着切片器选择的不同,图表就动态变化了。
再做第二个交互效果,随时间动态延展,需要用到动态名称和offset函数。在【公式】——【名称管理器】中新建一个名称,这里命名为t_data,引用位置处输入公式“=OFFSET(数据源!$G$11,0,1,1,数据源!$H$1)”,表示随日期选择框选项的不同,选择不同日期下的指标。
再回到图表展示区,将图表的数据引用区改成刚刚命名的t_data即可。
这一部分文字要想讲解透彻实在有限,所以特意录制了视频,在文末。
Step3:静态图表
我们用第二个数据源性别数据来做这种有图片填充的“小人”条形图。
其实很简单,刚刚也已经说明白了,是填充进去的,准备好数据和男女小人的图标图片。
对用户数插入条形图
再把小人的图片直接复制到一条图中,填充模式选择层叠,男小人同理。
最后一个交互效果,大家可以自己研究下。
文字讲解透彻有限,如有对这种动态交互图表感兴趣的小伙伴,可直接观看下方讲解视频,后台回复“动态交互”,可获得本节内容的练习数据。
提供入门级数据分析的学习路线规划,分享从Excel到统计学的干货。数据分析是一项技能,希望人人都能分析数据。
相关内容:
SQL学习:MySQL入门 | 库/表/记录的增查删改 | 对行和列的操作 | 计算字段 | 分组和子查询
Excel分析方法:RFM分析 | 综合指标分析 | 平均和交叉 | 分组 | 对比 | 时间序列分析 | 回归分析 | 描述性统计分析 | 相关性分析
Excel图表:数据地图 | 数据透视表 | 5个基本图 | 13个进阶图 | 直方图 | 控制图 | 排列图
Excel函数:日期文本函数 | 查找引用函数 | if函数 | 统计函数
用Excel进行数据分析:数据获取 | 数据处理
方法论:如何系统地学习Excel | 数据分析学习 | Excel相见恨晚的技巧
码字不易,喜欢就点个右下角 在看 呗