下拉式模糊查询,烧脑慎入
利用Excel的【数据有效性】功能制作下拉菜单,应是表哥表姐耳熟能详的一个技能了。
当然,有些小伙伴可能还不会,不会的小伙伴估计是E界新人,是轻稚可爱的表弟表妹,是早晨八九点钟的太阳,是祖国——咳咳,作为过来人,星光还是诚挚建议您翻阅下EH公众号的往期文章,认真学习下【数据有效性】的相关知识——毕竟这部分知识是非常炫酷实用的。
咱们这期讲的内容是也和数据有效性有关,是利用上周咱们讲过的CELL函数,制作具有模糊查询效果的动态下拉菜单。
这话到底啥意思呢,还是用动态图说话吧。
目标
在A列某区域输入某个关键词,在数据有效性的下拉列表里,显示出包含该关键词的数据。
材料
一份名单表:
制作过程
E2单元格输入数组公式,并向下填充到E10单元格区域:
=INDEX(D:D,SMALL(IF(ISNUMBER(FIND(CELL('contents'),D$2:D$10)),ROW($2:$10),4^8),ROW(A1)))&''
这公式看起来很复杂的模样,但倘若您学习并掌握了我们之前推送的INDEX+SMALL+IF函数套路的文章,理解起来就简单多了。
CELL('contents')
CELL函数省略了第二参数,获得最后更改单元格的值。
FIND(CELL('contents'),D$2:D$10)
FIND函数查询CELL函数的结果,是否在D2:D10单元格区域存在,如存在则返回一个位置数值,相反则返回错误值,生成一个内存数组,比如:
{#VALUE!;4;4;2;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。
IF(ISNUMBER(FIND(CELL('contents'),D$2:D$10)),ROW($2:$10),4^8)
ISNUMBER函数判断FIND函数的结果是否为数值,如为数值,则IF函数判断为真,返回相关值所对应的行号,如否,则返回值4^8,即65536。
=INDEX(D:D,SMALL(IF(ISNUMBER(FIND(CELL('contents'),D$2:D$10)),ROW($2:$10),4^8),ROW(A1)))&''
SMALL函数对IF函数的结果进行从小到大取数,随着公式的向下填充,依次提取第1、2、3、4……N个最小值,由此依次得到符合条件——包含最后更改单元格值的单元格的行号。
INDEX函数根据SMALL函数返回的索引值,得出结果。
由于CELL('contents')得到的是最后更改单元格的值,而编辑上述公式时的单元格即为最后更改内容的单元格,此时会造成循环引用,但不必理会。
公式填充至E10单元格后结果如下:
选取设置下拉菜单的单元格区域A2:A9,点击【数据】选项卡中的【数据验证】(13版本之前名为【数据有效性】)。
在弹出的数据验证对话框中,单击【设计】选项卡【允许】输入框右侧的按钮,在下拉列表中选择【序列】。
单击【来源】右侧的选取按钮选择工作表的数据区:$E$2:$E$10
单击【出错警告】选项卡,去掉【输入无效数据时显示出错警告(S)】的勾选。
最后【确定】之,大功告成了。
结束语
挠头……那个……对于大部分表弟表妹来说,今天分享的内容难度系数确实有些偏高,想必有不少人云里雾里的,难点之处在于那条长长的裹脚布函数。
关于INDEX+SMALL+IF的函数套路,坊间里戏称万金油套路,由此可见它作用之强大——作为早晨八九点钟的太阳,E界未来的希望,怎能不掌握它呢?
——新的一天,兄弟姐妹们努力学习吧!
图文作者:看见星光