在Excel用数据验证制作下拉列表很简单,可以参考之前的文章
也常有学员抱怨,数据源内容太多,数据验证下拉列表太麻烦了,找个客户名称也很不方便。
![dc58d3568c625e6ddee21b0dc6d4cfdb.gif](https://i-blog.csdnimg.cn/blog_migrate/d77afad61273384b0782a9abc8ccf9c9.gif)
问题是:如何制作可以带模糊匹配筛选的下拉列表?比如下图的效果。
![afe2fc7db67792ce5f4bd07f713423ce.png](https://i-blog.csdnimg.cn/blog_migrate/601e2344f1e0e4aac0ba1356f396be22.jpeg)
在以前的版本中要实现这个功能是 难 上 加 难!!
现在呢,用Microsoft 365(以前的Office 365)可以轻松实现。
今天营长给你来演示,主要用到两个动态数组函数FILTER和UNIQUE。
01. 动态数组函数
使用动态数组,返回值将自动“输出”到相邻单元格中(未使用的)。使用动态数组函数,只需要编写一个简单的公式,而不是编写复杂的数组公式来解决多单元格问题。Microsoft 365新增了7个动态数组函数:FILTER,UNIQUE,SORT,SORTBY,SEQUENCE,SINGLE和RANDARRAY。
FILTER函数可以实现一对多和多对多的查询,见下图。
![db673b4d727ccfac754ada31f1e3facf.png](https://i-blog.csdnimg.cn/blog_migrate/eb8818cec2899d08c78e5bb8bf0423a8.jpeg)
返回多个条件的 FILTER
使用乘法运算符 (*),以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值:
=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")。
![f91473a77be88a16029ae7672935e9bd.png](https://i-blog.csdnimg.cn/blog_migrate/627861027a80101168e870601e68348b.jpeg)
UNIQUE 函数返回列表或范围中的一系列唯一值。
返回一系列值或名称中的唯一值
![eb22037bb5db41316320e9f626c08c29.png](https://i-blog.csdnimg.cn/blog_migrate/483703027c3754bb6d6dd3578138216b.jpeg)
请注意,这些函数都不需要绝对引用,因为它们仅存在于一个单元格中,并将其结果溢出到相邻单元格。
02. FILTER+UNIQUE 筛选不重复值
用FILTER、ISNUMBER和FIND函数,提取包含F3单元格(目前是“贸易”)的客户名称,会是重复的。
![4f9a0400f2a17a04eebe264b3c0367a7.png](https://i-blog.csdnimg.cn/blog_migrate/1f1ebeb6b5e1c1f3f91ad3224de87937.jpeg)
H3单元格输入公式
=FILTER(B2:B831,ISNUMBER(FIND(F3,B2:B831)),"")
再用UNIQUE+FILTER这个经典实用的组合,提取包含“贸易”的不重复客户名称。
![a491617245569c47c92ba9e63eb0ed92.png](https://i-blog.csdnimg.cn/blog_migrate/799f547a38966a8678ef70e0b2a6f566.jpeg)
H3单元格输入公式
=UNIQUE(FILTER(B2:B831,ISNUMBER(FIND(F3,B2:B831)),""))
03. 设置数据验证序列
在设置数据验证序列时,不同之处在于引用来源只选择上述公式所在的单元格H3,最后一定要加上 # ,表示引用动态数组结果。
![2db6af23f888c2786bdaad0246bfa874.png](https://i-blog.csdnimg.cn/blog_migrate/4ebff9f6e51654aeb22eee81a0d8224e.jpeg)
注意,一定要取消出错警告的默认选项。
![05b64da5690682f59a5e669d1dc58fa6.png](https://i-blog.csdnimg.cn/blog_migrate/9582441a22fada91d6e2a1da489f0f3c.jpeg)
可以做出根据单元格输入内容模糊匹配的下拉列表。
![ace2255413fbc0a480c5e4b977d41e45.gif](https://i-blog.csdnimg.cn/blog_migrate/507496d8be3dea9bbcc2be0cdbe3bfc9.gif)
用FILTER+UNIQUE+数据验证同样可以做出多对多查询效果,如下图。
![c0ac013845d82c6bf16d85e7fc7a1111.png](https://i-blog.csdnimg.cn/blog_migrate/f8f2100cf174471626aac22aa49f592c.jpeg)
B6单元格中输入公式
=FILTER(订单明细!B2:D831,(订单明细!B2:B831=订单筛选!B2)*(YEAR(订单明细!C2:C831)=订单筛选!B3),"")
![678802deefa47ffe7c5be0b0cfef6d4a.gif](https://i-blog.csdnimg.cn/blog_migrate/bf82617286d18ab98ace9caf52c04fb4.gif)