EXCEL进阶课堂成长过程中,得到了各位粉丝关注和支持,给了进阶君持续更新的动力,谢谢大家。通过私信,陆续有小伙伴提出了工作当中的实际问题,进阶君从中选取了一些有典型代表意义的问题进行发文回复,希望能给大家带来一定的启示,举一反三。
看了进阶君多篇关于下拉列表的教程,许多小伙伴都索取素材后,纷纷练习,于是有新的问题不断出现。其中,智能下拉列表的问题相对集中,于是在这里做一个与智能下列拉表的探讨。
大家对于智能下拉列表兴趣浓厚原因在于实际工作的当中的痛点。对于某些单元格,通过数据据有效性做成下拉列表的形式后,对于数据输入效率有所提升。但是,如果有一个下拉列表里面有50项,要去找其中的选项,是非常麻烦和低效的。
有这样的案例:某地某部门组织工程招标,参加竞标的公司名单如下图所示。
工作人员小明,在招标结束后,需要做出数据报表,为了准备和方便,他把公司名单设置为了工作表当中 中标公司 列下单元格的下拉列表,如下图所示。
很快,小明发现在这么多公司选项里面去找一个公司,真是太麻烦了。能不能输入公司的关键字,下拉列表以此为依据,将相关数据项显示,而将无关数据项清除倒呢?
在EXCEL中当然是可以实现的。如果用VBA代码实现,效果好,但是学习成本高;如果用函数实现,效果不完美,但是学习成本低。在这里,进阶君采取函数来实现。
智能下拉列表,很多小伙伴都见过。最常见的类似样例,就是在百度里面进行搜索内容时输入效果。如:当我们分别输入重庆、北京、上海时,下拉列表会自动变换相应的数据选项,相较而言,EXCEL里面通过数据有性做出的下拉列表就显得笨、呆、傻了。
所谓的智能下拉列表:根据输入的关键字,动态变化下拉列表中的可选项,减少无关选项。
从这个含义描述来看,我们需要做三步事情:
第一步: 需要将所有的可选项放入下拉列表。
第二步: 输入查找关键字
第三步: 根据输入的查找关键字,动态变化下拉列表内容。
第一步:建立数据源,明确输入查找关键字单元格和下拉列表的之间的关系。
根据案例描述,进阶君建立了如下图所示的数据源。特别强调的是,竞标公司名单是按照公司名称降序排序的。
输入查找关键字的单元格与下拉列表之间的关系大体有两种:其一,输入查找关键字的单元格与下拉列表在同一个单元格;其二,输入查找关键字的单元格与下拉列表在不同的单元格。两种做法稍有差异,但是做在同一个单元格内更显高端与简洁,进阶君就来实现此种效果。
第二步:明确下拉列表查询的关键字
通过观察,我们可以发现,下拉列表中的公司名称开头代表公司区域归属,分为重庆、四川、北京、上海四类,每类下面的可选项数目很少。于是我们可以将重庆、四川、北京、上海四类作为下拉列表查询的关键字。
第三步:弄明白查询中通配符的作用
在查询当中,经常会使用到通配符。
所谓的通配符是指,在查询当中使用一个特殊符号来代表一个或多个字符。
通配符常用的有"*"和"?"两类符号(注意,这两个符号都是在英文输入法状态下输入),分别代表的含义是:
①?:代表一个字符。
比如:查找姓名为 "刘星" 的人,我们直接查找“刘星”;如果查找姓名以“刘”开头,共计2个字的,我们应该查找“刘?”; 如果查找姓名以“刘”开头,共计3个字的,我们应该查找“刘??”。 再次强调:? 一定是在英文输入法下输入的。 查找效果如下动图所示:
②*:代表任意个字符。
比如:查找姓名中以“西”字开头,我们可以查找“西*”; 查找姓为“欧阳”的,我们可以查找“欧阳*”;查找姓名当中含有“飞”字的,我们可以查找“*飞*”。 再次强调:*一定是在英文输入法下输入的。 查找效果如下动图所示:
第四步:运用数据有效性,建立下拉列表
(1)选择单元格区域: 选中数据源中的C3:C7单元格
(2)设定数据有效性
依次点击数据菜单、数据有效性设置,在设置窗口中作如图所示的设置:
其中,来源=OFFSET($E$3,MATCH(C3&"*",$E$3:$E$14,0)-1,0,COUNTIF($E$3:$E$14,C3&"*"),1)
这个公式在做多级菜单联动时经常使用,在这里不做详讲。如果有遗忘的小伙伴,请查看进阶君前面的教程。
「函数说24」多级下拉菜单联动,名称太多容易晕?高级招数:一个公式轻松搞定
具体操作过程及效果如下动图所示:
(3)解决单元格不能随意输入问题
通过第2步操作,我们可以实现单元格的下拉列表了,但是现在下拉列表里面显示的是所有可选项。我们需要输入关键字来动态更新可选项,但是到目前为目这样做是有问题的。如动图所示。
为什么会出现这种情况呢?首先必须说明,出现这种情况是正确的。因为刚才做的数据有效性,说明这些单元格里面只能选择或输入参加竞标公司的名称,而重庆、北京、四川、上海四项不是公司名称,当然会出错。
如何解决?修改一下这些单元格的数据有效性设置。在弹出的有效性设置窗口中,进行如下操作:
(4)验证效果
对相关单元格数据有效性进行修改后,在单元格里面输入查询关键字,会发现下拉列表里面的选项动态调整了,我们成功实现了效果。具体效果如下动图所示:
做到这一步后,我们发现智能下拉列表基本功能已经实现了,但是存在着一些不完美,明显的地方有两点:
①下拉列表不能自动显示:下拉列表必须手动点击单元格右侧的按钮才会出现。
②下拉列表内容不能随着输入变化:下拉列表的内容只要在输入结束后才会变化,不能一边输入一边变化。
进阶君需要说明的是,智能下拉列表不靠VBA编程和控件,要实现如百度搜索效果基本不可能,而这些内容绝大多数小伙伴很陌生,所以进阶君就在这里不去展开,后续进阶君会推出VBA专题系列教程,敬请大家关注。
在这里展示一个输入查询关键字,回车后,下拉列表自动展示的例子。
源代码在此:
智能下拉列表,实现的 基本思路就是在进行数据有效性设置时,将输入项和通配符联合进行进查询,使得下拉列表选项动态变化,减少无关项。 再这里需要再次强调,通配符的输入一定是在英文输入法状态进行的。
为方便小伙伴们学习,进阶君将原始素材共享出来,获取素材的方法:
第一步:关注 Excel进阶课堂。
第二步:私信 Excel进阶课堂,因为设定的是自动回复,所以内容一定要准确
私信内容: 练一练
第三步:根据得到信息打开网盘,找到 答粉03智能下拉列表 工作簿 自行下载
评论留言