最终效果图:
我们最终要实现的效果就是:
1,滚动条控制开始与结束时间
2,随着滚动条时间的改变,图表区域自动更新为选择时间内的数据
3,下拉菜单字段改变时,数据区域自动改变为选中字段数据
第一步,选择数据源一个数据区域(拥有数据源的子表命名为数据源),插入折线图,如下:
为了不破坏源数据,我们把图表剪贴到一个新的子表,子表命名为图表区
第二步,设置控制区域
调整图表大小,并完成如下后续需要用到的设置(单元格填充色可以不管)
关键点:
监控字段选择这里我们使用定义名称来配合数据有效性,实现。为了不破坏源数据,我们新建一个辅助数据子表(我们现在有三张子表,数据源,图表区,副主数据。),来把所有需要监控的字段都命名为 监控字段这个名称。演示如下:
定义名称:简单解释,是为了用一个名称词来代表一个引用区域。这样的好处是简化操作,而且更容易把不同公式、功能组合使用
注意,我们需要在监控字段后面写上监控字段对应的所在数据源的列数,后面会解释为何需要这个列数。现在结合数据有效性,做好监控字段下拉列表,同时把选中的监控字段所在数据源列匹配过来。
接下来,我们设置滚动条参数(右键单击滚动条),并与对应单元格关联起来,演示如下:
滚动条最大值设置成365,代表一年天数。关联单元格会显示滚动条滚动位置的数字,注意,这个数字是非常有价值的,我们需要这个数字与日期关联起来,这个时候,我们在辅助数据子表中创造一列和数据源中日期列一样的数字,我们直接用=号即可,这里注意,我们数据源中的日期只到了7月9日,如果后续添加日期怎么办?很简单,我们把数据辅助子表中的日期列拉到365行即可。同时我们在辅助数据子表日期列前插入一列序号列,表示每个日期对应的数字,这样方便把滚动条的数字与对应日期匹配关联起来。演示如下:
把滚动条代表的数字对应日期匹配到图表区的P2和P3单元格。如图:
第三步,设置图表区域数据
我们右键图表-选择数据,看到如图的画面:
这个图标由XY轴以及两个数据区域字段组成,每一个字段都可以被编辑
1,PV
2,UV
3,日期字段(X轴)
这些字段背后的本质都是对于特定引用数据区域的图形化展示,也就是说,你看到的曲线图柱形图背后引用的都是特定的数据区域。我们编辑一个字段看看是不是这样子
可以看到,字段的名字以及数据区域都是可以编辑更改的。这些字段的引用区域是可以被公式定义的。这里我们选择OFFSET函数。我们在使用offset函数定义字段数据引用区域前,我们先实现字段名字随着下拉菜单而改变,这个很简单,只需要选中字段-编辑-把名字指向字段下拉菜单即可。演示如下:
现在已经实现了字段的名字随着下拉菜单的变化而变化。我们现在来改变日期。让日期随着滚动条而改变。这里就要用到OFFSET函数。使用之前,先科普一下,百度百科解释为:以指定的引用为参照系,通过给定偏移量得到新的引用。公式为:OFFSET(reference,rows,cols,height,width),翻译成汉字参数:=OFFSET(基准位置,向下或上偏移几行,向右或左偏移几列,返回引用区域的高度,返回引用区域的宽度)。
我们先让时间轴,随滚动条变化。可以发现,滚动条绑定了O2和O3单元格后,滚动条变化,O2和O3的数字也会变化,那么对应的P2和P3的日期也会变化,这就是我们X轴日期变化的基础,我们用这个公式:
=OFFSET(数据源!$A$1,图表区!$O$2,0,图表区!$O$3-图表区!$OO$2+1,1)
公式解读:
数据源!$A$1:偏移基准点
图表区!$O$2 : 偏移行数
数图表区!$O$3-图表区!$O$2+1:偏移后返回的引用区域的高度,+1的意思是把尾巴也包含在内
1: 偏移后返回的引用区域的宽度,1即为1列
我们把此公式定义名称为:动态日期, 演示如图
然后,我们编辑日期字段,把定义好的公式设置成图表的日期字段的引用区域,演示如图:
注意把定义好名称的 动态日期 编辑入水平轴的时候,前面要加上带后缀的工作簿的全称,然后再加一个英文的感叹号。感叹号后写上定义好的名称。
大家发现第一个滚动条滚动的时候图表日期是改变的,第二个滚动条不变。这是因为图表展示的折线图数据区域并未改变。所以时间改变并未体现出来。我们现在开始编辑折线图的字段,解决这个字段动态变化问题。
思路:根据监控字段顺序,来控制引用区域列便宜量,比如UV字段在数据源第3列,那么函数中便宜的列数,就应该是3-1=2,即基于偏移基准点,偏移2列就可以达到第三列引用,行偏移数与日期开始数字一致,偏移后引用的数据区域宽度高度与日期一致
形成公式如下
=OFFSET(数据源!$A$1,图表区!$O$2,图表区!$D$2-1,图表区!$O$3-图表区!$O$2+1,1)
公式解读:
数据源!$A$1:偏移基准点
图表区!$O$2 : 偏移行数
图表区!$D$2-1: 偏移列数 图表区D2所在的单元格,即为选中的监控字段1所在数据源的列数。
图表区!$Q$3-图表区!$Q$2+1:偏移后返回的引用区域的高度,+1的意思是把尾巴也包含在内
1: 偏移后返回的引用区域的宽度,1即为1列
同样,我们可以得到监控字段2的公式,仅仅需要把代表监控字段1所在数据源列的数字D2单元格改为D3:
=OFFSET(数据源!$A$1,图表区!$O$2,图表区!$D$3-1,图表区!$O$3-图表区!$O$2+1,1)
我们把这两个公式定义名称为:图表监控字段1 和 图表监控字段2, 演示如图:
最后,编辑图表数据区域。
右键图表-选择数据-编辑左边的两个字段,你可以先把要编辑的内容写好,直接复制,比如:
=动态图表演示表.xlsx!图表监控字段1
=动态图表演示表.xlsx!图表监控字段2
演示如图:
至此,基本已经完成所有核心操作,拉滚动条与选择字段,数据图表自动切换。
但是为了美观,我们一般隐藏不需要让其他人看到的辅助单元格或者数据,你可以隐藏字体设置成与背景色一样的颜色。 图表也可以美化,最终效果如图:
另注意,如果选择字段1为UV,字段2为转化率的情况下,会发现转化率直接是一条贴近0的直线。这是因为两个字段数值差异过大,可以采用转化率利用次坐标轴来展示的技巧来规避这个问题。
第一次知乎发文,不知如何上传附件源数据,感兴趣可以私信我发送源数据。
感谢查阅,感兴趣可收藏!