EXCEL 2017实现模糊查询下拉框

haipali / 2023-08-10 / 原文

测试小姐姐给了我一个活，因为他懒得一个个根据表输入名字，所以要实现输入姓氏，然后出现下拉框，其中展示所有含该姓氏的人名，基础模板如下

可以看出数据量还是有点多的，小六千条

实现步骤

第一步我肯定是去请问CHATGPT大佬，大佬说使用Filter函数，但是我查了发现find函数仅Excel365可以使用，这对于限制了excel版本的我来说，走不通
我接着追问，后面了解到VBA，即Visual Basic for Applications，是 VisualBasic 的一种宏语言，是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。但我向chat要了代码，却怎么也跑不起来，报各种错，最后放弃了
我觉得这方法应该是可行的，是我能力问题，这里放上VBA的教学CSDN
EXCEL VBA 入门与实用例子
经过多次碰壁，我上了B站大学，找到了一个宝藏视频

[Excel模糊搜索下拉菜单数据验证]
("https://www.bilibili.com/video/BV1GP4y1E7Pe/?spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=0558e58483e033c4f566abbb302c07f8")

下面是视频中的详细步骤，我也是复现成功了，要是懒得看也可以直接点连接看视频

其中用到的函数

CELL（"content"）
：不加第二个参数，会提示有循环引用风险，这不用管，该函数返回你在任意单元格最后一次输入的值
FIND（x,Array）
：返回Array中含有x的具体位置，若不含则报#Value
ISNUMBER（Array,y）
：字面意思，判断是否为数字，不是数字返回y
IF(a,x,y)
：若为true返回x，为false返回y
ROW()
：返回行号
SMALL(Array,a,b)
：返回array中
INDEX(Array，x，y)
：返回array中第x列第y行
Offset(原始区域，偏移几行，偏移几列，[扩展为几行]，[扩展为几列])

其实就是用到上面这些函数，经过组合得到
辅助列函数
=INDEX(人员档案!B:B,SMALL(IF(ISNUMBER(FIND(CELL("contents"),人员档案!$B$2:$B$5913)),ROW($2:$5913),10000),ROW()-1))
数据校验函数
=OFFSET($X$2,0,0,5912-COUNT($X$2:$X$5913),1)

实际上逻辑就是：

用cell函数获取输入的姓氏
用find函数在对应人名范围中，找到含该姓氏的数据，并返回有具体数字
用isnumber函数筛选出这些数据，返回值为true或者false
用if函数筛选为true的数据，返回数据的行号，为false的数据就设置一个很大很大的值
用small函数从小到大排序，就能把目标数据的行号都显示出来了，后面非目标行号的数据都是你自己设置的很大很大的值
用index函数，在人名范围中定位，获取到含该姓氏的人名
7.第六步返回的结果，除了目标人名外，后面会有很多0，表示非目标人名，所以要使用offset函数进行偏移，只截取想要的数据
主要过程就是用count函数计算出非目标人名返回的0的个数，然后用人名总数-该值，就完成啦！

其中的"人员档案!B:B"，"人员档案!$B$2:$B$5913","$2:$5913"可以根据实际情况修改
写完，辅助列因为是数组函数，所以记得按CTRL+SHIFT+ENTER噢
然后选中要出现下拉框的目标列，

然后按照顺序点击，弹出数据校验界面

将函数填进去

记得把出错警告勾选去掉，不然会一直报错用不了
最后点击确定，就搞定啦！！