|
之前一篇讲过动态图表主要有三种形式：数据透视表+切片器，vlookup+match+数据验证，以及控件+offset，之前是觉得数据透视表是最简单灵活的，但是近几天深入研究了几个控件的案例，发现控件也是有其不可比拟的优势的，且听我娓娓道来~
一、组合框+OFFSET——单一筛选利器
① 控件制作：点击开发工具-插入-组合框，在空白单元格画出一个框，然后右键设置格式，数据源选择数据中的5月-8月，然后数据链接任意选一空白单元格，确定后点击控件即可出现5-8月的筛选。
② 动态数据制作：按住CTRL+F3，调出名称管理器，新建，名字输入月份，在引用位置上输入
=OFFSET(Sheet2!$A$121,Sheet2!$F$129,1,1,4)，第1个是月份单元格，第2个是控件链接的单元格，意思是根据控件筛选的值，将单元格由月份往下移动多少，然后再往右一列，取一行四列的值，按确定。
③图表制作：插入-簇状柱形图，得到一个空白的图形，然后右键-选择数据-添加，在弹出的数据系列中的数据值输入刚才定义的名称，最好加上所在工作表，不然容易混淆，确定后就得到一组柱形图了，然后将组合框与图表组合一起，美化下就可以啦
二、多个选项按钮+OFFSET——选项式筛选
①控件制作：还是刚才的数据，点击开发工具-插入-选项按钮，链接随意任意一个空单元格，这里不用选择数据源，确定后，双击控件，修改名字为5月，然后复制1个，改名为6月，再复制2个，分别改名为7月和8月，然后你在这四个控件切换，会发现链接的单元格会按你复制的顺序依次+1。
②动态数据制作：因为数据源没变，可以直接用上一个例子定义的月份即可
③图表制作：插入-圆环图，选择数据-添加-系列值输入月份，确定，就可以得到一个可多个选项筛选的动态圆环图，然后将四个选项与圆环图组合一起，美化下就可以啦！
三、滚动条+OFFSET——拖拽筛选的精灵，折线图的绝佳搭档
数据是2019年1月至3月某电商平台的月下单用户（月活）
①控件制作：开发工具-插入-滚动条，点击设置控件格式，这里有几个参数需要设置，当前步长可以不用设置，最小，最大和步长可以根据你的数据量的需求设定，一般默认最小和步长为1，最大为数据的行数。确定后拖拽滚动条，链接单元格的值就会变
② 动态数据制作：这里与前两个例子不同的点是，取的数据是链接单元格的值以及之前所有的，所以函数=OFFSET(Sheet2!$B$128,Sheet2!$D$203,,-Sheet2!$D$203,1),第四个参数是-链接单元格，负号意思是往前取链接单元格的行数，而不是往后取
③图表制作：选择数据-插入折线图，得到普通的折线图，然后点击图表-选择数据-添加，在系列值中输入刚才制作的月活，点击确定，就得到另一条折线图，此时拖拽滚动条，就可以看到一条滚动的折线图，有点像贪吃蛇，哈哈。
④图表美化：本来这里已经结束了，但是还想在运动的折线增加日期显示，让图表更生动一些。
在控件单元格右边输入=INDEX(B129:B218,D203)，意思是查找链接单元格对应的月活，日期也是类似。然后选择D203:E203,复制，点击图表，左上角粘贴，选择新建系列和首列为系列名称，确定。然后点击图表-格式，选择刚才新建的系列3，这时图表上会出现1个很奇怪的点，然后点击图表-更改系列图表类型，将系列3调整为散点图，并改为主坐标轴，此时这个点就会乖乖地出现在折线头部啦，然后于对其进行美化就可以啦
四、选项与组合框并用——轻松实现多维度筛选
还是沿用案例一的数据
①控件制作：
选项控件——开发工具-插入-选项，链接一个空白单元格，修改名字为“按月份筛选”，再复制一个修改名字为“按城市筛选”，然后发现在两个选项之间切换，链接单元格值会变化（一般是从1开始，我这里由于之前建立了4个选项，于是是从5开始）
组合框控件——建立辅助区域，新键一个名称管理器，按CRTR+F3，新建一个名称为data，函数为=IF(Sheet2!$C$230=5,Sheet2!$A$231:$A$234,Sheet2!$B$231:$B$234)，意思是如果链接单元格是5，就取辅助列中月份那一列，如果是6，则取城市
开发工具-插入-组合框，右键设置控件格式，数据源不再像之前那样框选一个数据区域，而是输入刚建立的data,链接一个新的单元格即可
② 动态数据制作：按CRTR+F3，新建名称“chartdata",函数为=IF(Sheet2!$C$230=5,OFFSET(Sheet2!$A$224,Sheet2!$C$231,1,1,4),OFFSET(Sheet2!$A$224,1,Sheet2!$C$231,4,1))，意思是如果选项链接单元格是5，则是按月份筛选，按组合框链接单元格数值调整选取范围；如果是6，则按城市筛选
③ 图表制作：插入-簇状柱形图，选择数据，添加系列值为 chartdata，则可以根据月份和城市进行筛选了，但是纵坐标标签不能动态变化，别怕，按CTRL+F3，新建axis,函数=IF(Sheet2!$C$230=5,Sheet2!$B$231:$B$234,Sheet2!$A$231:$A$234)，意思是如果是按月份，则取辅助区域中的城市，如果是城市则取月份，再点击图表-选择数据，右边的类
点击学习其它图表的制作吧，后续会一直更新此系列，感兴趣的小伙伴可以关注下我喔
Excel数据分析高级技巧②——数据透视表（组合/切片器/计算字段/数据透视图/条件格式）
https://blog.csdn.net/cindy407/article/details/90574429
Excel高级图表制作②——帕累托图
https://blog.csdn.net/cindy407/article/details/90648837
Excel高级图表制作①——电池图/KPI完成情况对比图/重合柱形图
https://blog.csdn.net/cindy407/article/details/90613513
Excel数据分析高级技巧①——动态图表制作（offset,vlookup,控件…）
https://blog.csdn.net/cindy407/article/details/90415007
Excel高级图表制作③——漏斗图/转化路径图
https://blog.csdn.net/cindy407/article/details/90706990
|
