这次我们来看一个 Excel 实战技巧如何用纯函数实现一个带关键字的下拉菜单。效果是你在下拉框里输入“张”候选列表里就只剩姓名中包含“张”的人输入“产品”候选列表自动变成包含“产品”关键字的条目。全程不用 VBA、不用辅助插件核心就是FILTER BYROW这套动态数组函数组合WPS 和 Office 都能用。先回答最关心的问题要不要装插件不用纯函数实现。要不要写 VBA不用全程公式 数据验证。老版本 Excel 能不能用FILTER和BYROW需要 Excel 365 / 2021 及以上版本WPS 新版也已支持老版本请在结尾看兼容替代思路。能不能批量用可以。公式会自动扩展下拉菜单候选会自动更新。会不会卡数据量在几千行以内基本无感超过几万行需要优化匹配范围。这篇文章会带你把公式拆清楚再手把手做一遍下拉菜单。内容包括核心函数原理、单关键字模糊匹配、多关键字 OR 匹配、数据验证下拉设置、动态数组下拉兼容做法、常见报错排查以及在 WPS 和 Office 里的差异。1. 核心能力速览能力项说明项目类型Excel 函数公式技巧纯函数实现无 VBA核心函数FILTER、BYROW、LAMBDA、ISNUMBER、FIND、SEARCH主要功能下拉菜单根据输入关键字实时筛选候选、全表多列模糊匹配、多关键字独立备选支持平台Microsoft Excel 365 / Excel 2021WPS 新版需支持动态数组函数是否需要插件否是否需要 VBA否是否支持批量是公式自动扩展适用场景员工信息录入、产品名称选择、报销项目检索、物料编码匹配等不适合场景超大表数据匹配、跨工作簿实时联动、多人协同高并发录入这个方案的核心价值是把传统下拉菜单从“固定候选列表”升级成“搜索式候选列表”。你不需要在几百个选项里翻输入一个字或几个字候选范围就缩小到几条。这种交互方式在 Excel 里完全可以靠公式支撑起来。2. 适用场景与使用边界先看它适合解决什么问题。最典型的是信息录入场景。比如人事表里要录入员工姓名员工有 2000 人。如果做普通下拉菜单每次选人都要从头翻效率低还容易选错。用这个方案输入“王”下拉框里只剩姓王的候选人输入“王五”候选基本就一两条。第二类场景是产品名称规范化录入。业务团队经常把产品名写成“A产品100”和“产品A-100”导致后续汇总对不上。通过全表模糊匹配只要候选库里有标准名称输入关键字就能检索到标准写法从源头减少脏数据。第三类是物料编码、部门名称、项目名称这类格式要求严格、选项量大的字段。凡是“必须从已有数据里选但选项又很多”的场景都适合用这个方案。那不适合什么场景数据量特别大时比如几万行以上FIND遍历每个单元格的开销会明显增加输入响应变慢。跨工作簿实时联动不推荐。公式引用其他未打开的工作簿时刷新时机不可控下拉候选容易显示#REF!或空值。多人同时在线编辑时动态数组的扩展区域可能和别人输入的数据冲突。还有一个边界需要提醒如果下拉菜单里的数据涉及员工姓名、手机号、身份证号等个人信息要做好隐私管控。不要把敏感字段随工作簿随意分发建议只保留“用于检索的字段必要的回填字段”无关的个人隐私字段不要带进表里。3. 公式原理拆解FILTER 和 BYROW 到底在干什么这段是整篇文章的地基。先看FILTER的语法FILTER(要返回的数据区域, 筛选条件, 匹配不到时的返回值)第一个参数是“结果往哪取”第二个参数是“保留哪些行”第三个参数可写可不写但强烈建议写因为不写的话匹配不到会直接报#CALC!。第二参数是核心。如果筛选条件只有一列可以直接写FILTER(A2:C100, ISNUMBER(FIND(张, A2:A100)), 无匹配)意思是在A2:A100中找包含“张”的单元格FIND找到返回位置数字找不到返回#VALUE!外面套一层ISNUMBER就把“找到”变成TRUE“找不到”变成FALSE。FILTER根据这个TRUE/FALSE数组决定保留哪些行。但如果要在整行多列里模糊匹配呢比如姓名在 A 列部门在 B 列岗位在 C 列用户输入关键字希望只要这行里任意一列包含关键字就保留。这时你需要的是一行一行的判断。BYROW的作用就是“按行扫描”。BYROW(A2:C100, LAMBDA(当前行, OR(ISNUMBER(FIND(张, 当前行)))))拆开讲A2:C100是逐行扫描的范围。LAMBDA(当前行, ...)中的“当前行”是指每次传入的一整行比如A2:C2这一行数据。FIND(张, 当前行)会返回一个数组对三个单元格分别判断是否包含“张”。ISNUMBER把它们转成TRUE/FALSE数组。OR判断这三个布尔值里有没有任意一个为TRUE。然后把这个BYROW结果直接塞进FILTER的第二参数FILTER(A2:C100, BYROW(A2:C100, LAMBDA(行, OR(ISNUMBER(FIND(张, 行))))), 无匹配)这就是“全表模糊匹配”的原理。这里提一个细节FIND是区分大小写的如果你希望不区分大小写可以把FIND换成SEARCHFILTER(A2:C100, BYROW(A2:C100, LAMBDA(行, OR(ISNUMBER(SEARCH(关键字, 行))))), 无匹配)SEARCH不区分大小写还支持通配符。一般文本匹配建议用SEARCH英文和编码类匹配建议用FIND要根据业务需求来。4. 方案一单关键字全表模糊匹配这个方案适用场景是用户只输入一个关键字然后从整表中筛选包含该关键字的行。业务场景有一个员工信息表员工信息数据区域是A2:C200列分别是“姓名、部门、岗位”。在单元格E1输入关键字希望在F2开始的位置自动显示所有匹配的员工记录。公式写法在F2单元格输入FILTER(A2:C200, BYROW(A2:C200, LAMBDA(行, OR(ISNUMBER(SEARCH($E$1, 行))))), 无匹配)注意$E$1的绝对引用。如果关键字单元格留空SEARCH(, 行)会返回1表示所有行都匹配E1留空时显示全部数据。如果你希望留空时不显示任何数据可以在前面加一个判断IF($E$1, 请输入关键字, FILTER(A2:C200, BYROW(A2:C200, LAMBDA(行, OR(ISNUMBER(SEARCH($E$1, 行))))), 无匹配))操作步骤在E1输入“张”。观察F2开始返回的结果。修改E1为“产品”。结果自动更新。判断成功的标准输入“张”后返回结果中只要任意一列包含“张”都被保留。输入不存在的关键字F2显示“无匹配”而不是#CALC!错误。修改关键字后结果区域自动扩展或收缩不需要手动拖动公式。常见失败原因报错#NAME?当前 Excel/WPS 版本不支持FILTER或LAMBDA。报错#VALUE!数据区域里包含错误值比如单元格里是#N/AFIND/SEARCH会直接传递错误。提示“数组结果无法扩展”结果区域右侧或下方已有内容删除遮挡的单元格即可。5. 方案二多关键字 OR 匹配BYROW 的核心优势很多时候用户希望一次输入多个关键字只要匹配其中任意一个就算命中。比如仓库表里有“电脑、显示器、键盘”你想一次筛出“电脑”和“键盘”相关的记录。普通写法要写多条OR很啰嗦而且关键字数量变了公式就要重写。用BYROW LAMBDA可以写一个通用的多关键字 OR 匹配。业务场景在G1:G3区域输入关键字列表每个单元格一个关键字然后在F2输出所有匹配的行只要任意一列包含G1:G3中任意一个关键字即可。公式写法FILTER(A2:C200, BYROW(A2:C200, LAMBDA(行, OR(ISNUMBER(SEARCH($G$1:$G$3, 行))))), 无匹配)这里SEARCH($G$1:$G$3, 行)的匹配逻辑是把G1:G3三个关键字逐一放到当前行里查找返回的结果是“3 个关键字 x 3 列”的二维数组再经ISNUMBER转成布尔值最后OR判断是否至少一个位置命中。为什么要用 BYROW如果不用BYROW直接在FILTER第二参数里写多关键字筛选需要手动把G1、G2、G3分别套进去再拼接ORFILTER(A2:C200, (ISNUMBER(SEARCH(G1, A2:A200)) ISNUMBER(SEARCH(G2, A2:A200)) ISNUMBER(SEARCH(G3, A2:A200))) 0, 无匹配)这样写有两个问题关键字多了公式非常长。关键字区域的大小变了公式不会自动适配。BYROW方法不需要关心有几个关键字。G1:G3区域是动态的只要表达式引用整个区域新增一个关键字就自动纳入匹配。操作步骤在G1:G3分别输入“电脑”“键盘”“显示器”。观察F2返回结果。删除G3的关键字结果立即减少。在G4输入“鼠标”结果自动增加。判断成功的标准关键字区域非空时返回结果包含所有在任意列命中的行。关键字区域使用时不会因为区域里有空单元格而报错。匹配到的记录不会重复出现。注意点SEARCH的关键字区域如果包含空单元格空值会被当成空字符串处理也就是“匹配所有行”。所以如果关键字区域没用满建议把引用范围控制到实际使用的范围不要整列引用。6. 下拉菜单设置数据验证 动态数组区域公式能实时筛选出候选了接下来一步很关键把筛选结果变成真正的“带关键字下拉菜单”。这里有个坑Excel 的数据验证旧称“数据有效性”里“序列”来源不能直接填一个动态数组公式也就是不能直接写FILTER(...)。它需要一个“引用”。解决方案是用名称管理器把动态数组结果定义成一个名称然后在数据验证里引用这个名称。6.1 第一步确认结果区域是动态数组按方案一或方案二的公式在F2得到筛选结果。以 Excel 365 为例F2单元格右下角会出现一个“溢出”标识表示这是一个动态数组结果会按需扩展到周边单元格。6.2 第二步定义名称打开“公式”选项卡 -“名称管理器”-“新建”。名称下拉候选引用位置员工信息!$F$2#这里的#是动态数组引用运算符表示“从F2开始包含整个动态数组扩展区域”。如果 WPS 或当前版本不支持#运算符可以在名称管理器里用偏移函数构造动态区域写成OFFSET(员工信息!$F$2, 0, 0, COUNTA(员工信息!$F:$F), 1)COUNTA统计 F 列非空单元格数量作为返回区域的高度。这个方法兼容性更好但要求F列没有其他不相干的数据。6.3 第三步设置数据验证下拉选中需要录数据的单元格区域比如H2:H100。打开“数据”选项卡 -“数据验证”WPS 里叫“数据有效性”。允许条件选择“序列”。在“来源”输入下拉候选勾选“提供下拉箭头”。设置完成后点击H2单元格右侧会出现下拉箭头。点开箭头看到的就是根据E1或G1:G3关键字筛选出来的候选列表。6.4 第四步关键字联动效果把关键字输入单元格E1放在数据录入区域旁边。用户在E1里输入“张”然后点开H2的下拉箭头看到的候选列表就只剩姓“张”的人了。这就是“带关键字的下拉菜单”。这里有一个使用习惯问题用户需要先改关键字、再点下拉属于两段式操作。如果希望录入时更顺手可以把关键字输入单元格放在录入行的同一行并设置成不同的填充颜色方便用户识别。7. 数据验证下拉菜单的完整示例假设我们要做一个“报销项目录入表”原始项目库在Sheet1的A2:A500。要求录入时输入“差旅”下拉列表里只显示包含“差旅”的项目。具体操作流程如下。第 1 步在Sheet1!D1设置关键字输入单元格填“差旅”。第 2 步在Sheet1!E1写入公式FILTER(A2:A500, ISNUMBER(SEARCH(D1, A2:A500)), 无匹配)这里只有一列不需要BYROW直接匹配即可。第 3 步名称管理器新建名称名称: 项目候选 引用: Sheet1!$E$1#如果版本不支持#就用OFFSET(Sheet1!$E$1, 0, 0, COUNTA(Sheet1!$E:$E), 1)第 4 步选中录入区域B2:B200数据验证 - 序列 - 来源项目候选第 5 步实际测试。在D1输入“住宿”点开B2下拉候选里只有含“住宿”的项目。在D1输入“快递”候选列表变为含“快递”的项目。在D1清空或输入一个不存在的词候选列表显示“无匹配”。判断成功的标准很简单下拉列表内容会随着D1的内容变化不需要重新设置数据验证也不需要修改公式。这里补充一个细节数据验证引用名称时如果名称指向的是一个动态数组Excel 365 里下拉列表的候选会自动从数组中取全部值。如果数组结果超过 256 项部分老版本可能显示不全但 Office 365 和 WPS 新版一般能正常显示。如果遇到“下拉菜单是空的”大概率不是公式的问题而是名称定义里的区域引用没有对上。8. 效果演示与验证流程按下面的验证流程可以比较完整地测试整套方案是否可靠。8.1 基础功能测试测试项操作预期结果单关键字匹配关键字输入“张”下拉候选只剩包含“张”的记录多关键字 OR 匹配关键字区域输入“电脑”“键盘”下拉候选同时包含“电脑”和“键盘”相关记录无匹配提示关键字输入“不存在的内容”结果区显示“无匹配”下拉候选显示“无匹配”清空关键字关键字单元格留空显示全部数据下拉候选为全部选项8.2 联动性测试修改关键字后结果区是否立即更新。下拉菜单是否自动更新不需要重新设置数据验证。新增一条数据到原始数据表结果区是否自动包含新增记录。8.3 边界测试数据源中包含空格、全角字符、英文大小写。关键字输入了通配符*或?SEARCH会将其视为通配符如果不想通配需要换成FIND。数据区域中有空行空行不会因为SEARCH找不到关键字而不报错FILTER通常不会把空行显示出来但建议原始数据不要留无意义的空行。8.4 失败排查清单问题现象可能原因排查方式解决方案公式报#NAME?当前版本不支持 FILTER/BYROW/LAMBDA查看函数是否在函数库中升级到 Excel 365 或 WPS 新版公式报#CALC!没有写第三参数且匹配不到去掉第三参数测试补上无匹配下拉菜单空白名称定义引用错误在名称管理器里检查引用区域重新定义名称确认#运算符可用下拉菜单不更新数据验证来源写死为固定区域检查数据验证来源改为引用名称大小写不敏感导致结果异常使用了SEARCH改用FIND按业务需求确定数据区域有错误值#N/A等错误值影响SEARCH检查数据源清理数据源错误值9. WPS 与 Office 的兼容性差异这里单独说一下WPS和Office的差异因为这个话题提问率很高。从公式语法上讲FILTER、BYROW、LAMBDA在支持动态数组的 WPS 新版本里已经可以使用。但要注意几个细节动态数组溢出运算符#的兼容性。Excel 里写$E$1#表示从E1开始的整个扩展区域。WPS 对#运算符的支持程度在不同版本下不一样稳妥做法是用OFFSET COUNTA构造动态引用兼容性更好。名称管理器行为。Excel 和 WPS 都支持名称管理器但对动态数组引用名称的处理方式略有差异。如果直接下拉空白先测试名称返回的区域是否正常。FILTER第三参数。在部分 WPS 版本中FILTER未找到结果时可能不执行第三参数直接显示错误。原因通常是数据源本身包含错误值。建议公式里加上IFERROR兜底。数组自动扩展。Excel 365 里公式结果显示区域会自动扩展WPS 新版也支持但偶尔会出现“无法更改数组公式的一部分”的提示通常是因为结果区域和已有数据有交集清理一下周边单元格即可。如果你的环境是老版本 Excel2016、2019 等不支持FILTER可以考虑用辅助列 INDEXSMALL实现等效逻辑但公式复杂度会明显上升。更简单的替代方案是给原始数据添加“筛选辅助列”用ISNUMBER(SEARCH(关键字, 某列))算出 0/1再通过 Excel 自带“高级筛选”功能生成唯一值列表作为下拉菜单的数据源。这种做法的缺点是无法实时联动需要手动刷新。总之同一份公式建议先在 Excel 365 里搭好、测试通再复制到 WPS 里验证。因为两者对动态数组的容错能力不一样暴露出来的报错也不一样。10. 性能观察与数据量优化这个方案的性能瓶颈主要在SEARCH或FIND的逐个遍历上。当原始数据只有几百行时公式秒出结果下拉菜单更新几乎无感。当数据达到 1 万行以上BYROW会逐行处理三个甚至更多列每次输入关键字都会重新计算一遍延迟就比较明显了。几个优化方向限定搜索范围。不要直接引用整列比如A:A而应该引用具体的有限区域比如A2:A5000。整列引用会让FILTER扫描整个工作表计算量剧增。减少搜索列数。比如数据表有 10 列但实际需要匹配的只有“名称”和“分类”两列那就只对这两列做BYROW别把备注、创建时间这些不需要搜索的列包含进来。启用手动计算。如果单次重算耗时较长把工作簿设置为手动计算。用户输入关键字后按F9触发重算避免每次敲字都全表刷新。去重提升响应速度。如果原始数据里有很多重复项可以先用UNIQUE把候选列表去重再基于唯一值做模糊匹配。下拉菜单可选项目变少用户查找更快公式计算压力也小。配合表格结构化引用。如果数据在 Excel“表格”里建议把范围写成结构化引用比如表1[姓名]。这样数据增加行时公式范围自动扩展但计算效率可能不如显式区域引用要根据实际情况权衡。11. 最佳实践与使用建议这里给几条工程化建议避免实际使用的时候踩坑。第一模板和工作簿分开。不要把动态数组公式和下拉菜单直接做在原始数据表上建议单独做一个“录入模板”模板里只保留“关键字输入区 筛选结果区 数据录入区”原始数据单独放一个工作表。第二给关键字输入单元格加提示。比如设置输入提示“请输入关键字筛选下拉候选”。这能明显降低使用门槛尤其是给同事用的时候。第三下拉菜单的数据源要干净。如果原始数据有前后空格、全角半角不统一筛选结果可能对不上。建议在公式里加TRIM清洗或者提前把数据源做一次清洗。第四结果区不要手工填内容。动态数组的结果区是公式自动占用的如果被人手动塞了数据公式就会报“无法扩展”。再遇到这个报错不是公式错了是结果区被占了。第五数据验证引用名称时不要直接选区域。数据验证的来源如果直接选区域没有起到“根据关键字动态变化”的效果。一定要通过名称管理器定义再在来源里输入名称这种方式。第六涉及敏感数据的场景比如员工列表做下拉要注意授权范围。不要把包含完整手机号、身份证等敏感字段的工作簿随意共享如果只是需要姓名作为候选可以单放一列姓名不要让下拉菜单直接读取含敏感信息的整表区域。第七批量应用时先小范围试点。可以先在 10 行数据里验证公式和下拉都正常再扩展到几百行。不要一上来就设置 1 万行的动态数组出了问题排查成本高。12. 总结与下一步这套方案的核心价值是用纯函数实现搜索式下拉菜单让 Excel 自带的数据验证从“固定列表”变成“动态候选 关键字联动”覆盖了单关键字、多关键字、全表模糊匹配三种最常见需求。最值得先验证的功能是FILTER BYROW的公式组合能否在你这台电脑的 Excel/WPS 版本上正常跑通。只有这一步通了后续的名称管理器下拉方案才有意义。最容易踩的坑有三个一是老版本不支持动态数组函数二是数据验证直接引用FILTER公式而不是名称三是结果区域被其他数据占用导致“无法扩展”。这三个坑在文章里都写了排查方法遇到问题可以对着逐个检查。后续如果想继续扩展方向还有几个把关键字拆成“首字母缩写”匹配用辅助列生成拼音首字母然后下拉按首字母筛选或者把结果区从“仅候选列表”扩展到“选中后自动回填多个字段”比如选完项目名称自动带出项目编号和负责人这需要结合XLOOKUP或INDEX MATCH再做一层联动。这里最稳妥的做法是先拿一个真实的小表跑通流程再逐步增加数据量。模板一旦稳定可以直接复用在自己的日常表格里也可以整理成发给同事的共享模板。建议收藏备用下次做下拉菜单时可以直接照着公式配置。