easyui 动态设置单元格控件_插入控件->引用变量->实现动态图表纵横筛选

欢迎关注我的微信公众号：HR爱玩儿Excel和PPT，分享有趣又有逼格的Excel和PPT创意和技巧，emmm...不关注也没有关系...哼

昨晚没睡好，因为睡前老友跟我说了句话：

我们总部做的表没有你做的这么复杂！

你可以侮辱我的人品，但你不能侮辱我的Excel！

我忍住了翻身起来穿城前去打她的冲动，她说第二天到办公室就把总部的表发给我看一下，然后我就一直盼着天快亮，等我看完了再打她也不迟...

故事的一开始：
老友：你能不能做出这样的图表（如下）

我：你问这问题就是对我的侮辱

老友：那你2小时内给我整好，搞快，我老板等着

我：...蠢材你凭啥觉得我要2小时？！

然后她把源表发我，我傻眼了，这时我才看到图表中两个筛选框分别是...↓

针对列和行（标题行）的纵横双向筛选

>针对列的筛选非常常见，如上图对A列进行筛选，将会产生3个筛选项：成都、上海、北京，这是纵向的筛选，提取的是横向数据，常见应用如通过花名册查看某一条员工信息；（Excel自带的筛选设置正是如此）↓

常见列筛选工具：数据透视或者切片器（这两个工具都可以直接控制图表变化，不同于源表上的筛选下拉菜单），效果如下：

这两个工具适用于有很大的重复性可以进行二次筛选的纵列数据，如“城市”；如果对“苹果销量“这样不具有重复性的数据进行筛选，嗯，可以说把筛选用成了ctrl+f...

>针对行（标题行）的筛选在数据统计分析中更加普遍，比如单独分析苹果、梨子全年销量变化，但是体现在图表中我们常常是一个筛选项建立一张单独的静态图表，创建和更新数据量大且麻烦，如何在一张图表中实现多个标题行筛选项的动态切换？OK，今天的topic:

插入控件->引用变量->实现动态图表纵横筛选

先看成品

两个筛选复选框分别针对首列首行 ↑

以下内容为延伸，可略过

思路：动态图表的核心是数据的动态提取，提取数据的办法有很多；

lookup,match,index,offset都是常见的提取数据的公式；

lookup函数就不多说，不了解lookup函数的话，看这篇文章都没啥意义..

match和index函数常常配套使用，经典的比喻就是图书馆借书，match在电脑上先找出这本书在书架上所在的行或列，index就去现场使用相同的相对位置把这本书找出来；

offset函数本身很强大，但是也有个很大的缺陷，比起lookup函数最大的区别在于，lookup是识别数值，而offset是识别位置，

举个栗子

前方排队的人里面，头戴大红花的那个就是你对象，你的对象站在队列的第三个；

lookup能把头戴大红花的那个人找出来，你对象换了位置，lookup还是能根据她戴的大红花把她找出来；

offset能把排在第三个的人找出来，你对象要是换了位置，那就呵呵了...

我咋能想出这么聪明的比喻...

OK，下面正式说步骤

第一步：创建复选框

为啥要创建复选框？因为动态筛选十之八九要使用控件（复选框是一种控件）...

复选框设置：

1）数据源就是你的筛选项集合（复选框的数据源只能识别纵列值，所以来自标题行的筛选项需提前转换为纵向排列...）

2）单元格链接就是...复选框的筛选项对应了不同的默认值，链接就是把这些默认值放到同一个单元格里面去，当你的筛选项变化的时候，这个链接单元格里的数值就对应着变化...

来一波动图感受下复选框选项和链接单元格的关联，细看黄色单元格的变化，把黄色单元格（链接单元格）想象成一把钥匙，钥匙1打开的是“苹果销量”，钥匙2打开的是“梨子销量”，简单直白不...

第二步：根据链接单元格创建/提取对应数据

这个是重点，你钥匙有了，然后你还得把东西放到钥匙对应的屋里去啊

关于这个，有两个思路和解决方式，大家自行选择

1、方法一：在源表基础上生成一块新的区域，用来提取并存放需要展示的数据，打个比方，客户A要苹果，客户B要梨子，你就单独把苹果和梨子从仓库（源表）拿出来放到另外两个单独的房间，

好处：操作简单，低调奢华有内涵...

坏处：第一是占地方...第二是逼格不够...这是主因...o(*￣︶￣*)o

2、方法二：不创建单独的区域，而且在库房设置一个隐藏的抓斗，客户要苹果，我就用抓斗把苹果给你抓出来，你要梨子，我直接从抓斗把梨子给你抓出来；

好处：不占地方，高端大气上档次...

坏处：需要在后台定义公式，emmm...在前台页面都把公式写不转的童鞋，在后台定义公式再引用，比较容易出错...

方法一操作如下：

公式：={OFFSET(A1,11*($J$1-1)+1,0,11,1)}，

以下内容为延伸，可略过

公式说明：

在前面讲公式差别的时候，已提到offset是识别位置，所以这就限制了源表的数据排列，必须要按照顺序来排列，其次，公式里的11，表示一次性抓取连续的11个数据，因为源表里每一个筛选项（城市）对应的数据就是11条...根据前面找对象的那个例子，你就会明白，源表也是不能对非筛选列随意进行排序的（因为会打乱筛选列的顺序），如何弥补这个bug，嗯，你点文末“喜欢作者”我就告诉你...

上图演示了列筛选（城市）的生成，标题行筛选逻辑和公式写法一样，只是参数设置稍微复杂一丢丢：

附赠offset手工笔记一张，免得大家看下面的解释吃力

几个城市筛选项始终在同一列，所以offset的第三个参数写0即可，即reference（参照位置）同列，第五个参数写1，即返回1列数据，总之就是死活都在同一列...

行筛选不一样的地方在于，以A1为参照位置，几个筛选项的位置有差异：“苹果销量”在A1右边第3位，“梨子销量”在A1右侧第4位，以此类推...相对位置即第三个参数cols，如何让cols值跟随筛选项点击的变化相应改变（提取相应值），答案大写加粗的答案，5个字：

链接单元格！

以下内容为延伸，可略过

逻辑概括如下：复选框3个选项对应1、2、3, 这3个值是3把钥匙放在同一个盒子“链接单元格G1”，123三把钥匙又分别对应“苹果销量”、“梨子销量”各个筛选项...通过在offset函数中引用变量G1去提取不同列的数据，从而实现复选框、链接单元格、源表被提取数据三者的关联...

觉得绕口的话请再多读几遍...毕竟方法二比这更复杂...
提取出来的数据单独建立/存放在一个区域，并根据这个区域的数据生成图表，因为区域数据是根据复选框选项动态变化的，则图表也同步实现动态变化。右键图表可以查看图表数存储位置。

方法二操作如下：

1、按下图红框提示定义公式，公式名称叫啥都行，氮素！如果你真的给公式起的名字叫“叫啥都行”，你一定要记住这个名字，因为后面引用数据都是用的这个名字，当你引用这个名字的时候，并不是敲了几个汉字这么简单，而是会直接引用到这个名字背后定义的数据；

公式名称：“叫啥都行”

公式定义：OFFSET(offset提取区域!$B$1,(offset提取区域!$J$1-1)*11+1,offset提取区域!$G$1,11,1)

2、插入图表，右键选择数据，编辑数据，引用刚刚自己定义的那个名称↓

that's all :)

以下内容为延伸，可略过

之前还写过另外一篇动态图表的制作，感兴趣的小伙伴可以去翻一下

分享2种相对高大上的图表...拒绝系统自带

以下三个图表都是通过控件实现了动态变化，感兴趣的童鞋可以多了解一下控件；

以上所有注明“可略过”内容建议都不要略过，因为知识并非一次性使用产品。

公众号所有表格、图表、表格及图表动图皆来自个人笔记，转载请注明出处。

每次更公众号我都更很长，很多触类旁通的内容，感觉不写都不完整，加之写这个纯属兴趣，无关利益，所以也不会策略性的将一块内容拆分成多次来更新，如果感觉读起来吃力可以留言或私信。也有朋友问是不是需要通过买教材来系统学习，我自己是一本都没买过。Excel这个东西，不感兴趣的话放十本书在手边上你都不会翻完，感兴趣吧，自然就会比别人更花心思和时间。书本、培训、公众号分享并不一定会让你Excel提高，但至少让你了解曾经面临的问题其实还有其他答案。你不需要一定记住它，但是下次面临同样的问题至少知道通过搜索去找到它。所见大于所得。

撒花~Mua~~~