在业务表录入场景中最浪费时间的事情往往不是敲字而是从一份几百行的下拉列表里准确找到那一条记录。填商品、选客户、录供应商Excel 自带的数据验证下拉只能固定显示全部选项列表一长就只能滚动找或者回到数据源里搜索再复制回来。等有同事问“能不能输入关键字下拉菜单自动过滤”传统方案基本只有两条路加一堆辅助列写复杂公式或者直接上 VBA。VBA 在老版本 Office 里会被安全策略拦截在 WPS 个人版里还不一定自带 VBA 环境交付给别人时更是一堆兼容性问题。其实现在有第三条路用 FILTER BYROW 这两个动态数组函数在不写一行 VBA 的前提下实现“输入关键字下拉备选自动全表模糊匹配”的录入体验。整套方案由纯函数驱动数据源变了自动更新公式逻辑透明Office 365 / Excel 2021 以及已经支持动态数组的 WPS 最新版本都可以运行。这篇文章会把原理、公式、名称管理器和数据验证配置一步步讲清楚还会分析 WPS 与 Office 的版本差异、性能瓶颈和最容易踩的坑。读完你就能自己搭建一个带关键字下拉菜单的 Excel 录入模板。1. 传统下拉菜单的痛点与本文技术选型先还原一个真实场景。你在做一个“商品出库单”模板需要在 C 列选择商品。数据源“商品表”里有 1000 行商品记录每行包含商品名称、类别、规格、供应商。传统做法是在数据验证里直接选择“序列”来源指向商品名称列。看起来能用实际使用中问题非常多第一列表过长。1000 个商品名称一屏显示不完用户要在下拉框里滚动很久才能找到目标。遇到名称相似的“苹果”和“苹果汁”眼睛容易看花。第二数据源变动后下拉列表不会自动扩展。往商品表末尾添加新商品后如果数据验证来源写死为$A$2:$A$100新数据就永远不会出现在下拉里。工作中经常需要手动调整引用范围维护成本很高。第三VBA 方案并不友好。很多人第一反应是写 VBA 实现关键词过滤。但 VBA 有两个现实问题Excel 默认会禁用带宏的文件用户每次打开都要手动启用WPS 个人版默认不包含 VBA 环境需要单独安装第三方 VBA 插件。对于要分发给他人的模板VBA 意味着大量解释成本和安全隐患提示。第四个问题是“数据验证来源跨表引用”限制。Excel 的数据验证序列不能直接引用另一个工作表的区域常见解法是用名称管理器包一层。如果数据源和录入表不在同一个 Sheet新建名称是绕不开的步骤。所以本文选择的方案是方案是否写宏是否依赖动态数组是否支持模糊匹配维护难度固定列表数据验证否否否低辅助列 INDIRECT 动态列表否一般不需要有限支持中VBA 表单控件是否是高FILTER BYROW 纯函数否是是中低结论很明确如果你手头的 Office 或 WPS 版本已经支持动态数组函数FILTER BYROW 是兼顾体验、可维护性和交付便捷度的最优解。2. 核心函数与原理解读这一节先不讲完整公式而是把用到的函数拆开讲清楚。这些函数单独看都不复杂组合起来才是完整方案。2.1 FILTER按条件筛选数组FILTER 是 Excel 365 引入的动态数组函数之一作用是按照条件筛选数组。FILTER(要筛选的区域, 筛选条件, 无结果时返回的值)条件部分要求是一组与筛选区域行数相同的 TRUE/FALSE 值。TRUE 对应保留的行FALSE 对应剔除的行。第三个参数可选当结果为 0 行时不写会返回#CALC!建议统一写一个提示文本。2.2 BYROW LAMBDA按行处理数组BYROW 的功能是把一个二维数组按行传给 LAMBDA 函数每一行都执行相同的运算最终返回一个纵向的数组结果。BYROW(二维数组, LAMBDA(当前行, 对当前行执行的运算))在“多字段模糊匹配”场景里BYROW 的价值非常明显它让我们可以逐行判断“商品名称和类别这两个字段中只要有一个包含关键字就算命中”。如果没有 BYROW你要写两套 ISNUMBER SEARCH再用加法或 OR 合并公式会冗长且难以扩展。2.3 SEARCH、ISNUMBER 与 SUMPRODUCTSEARCH 在文本中查找指定关键字返回第一次出现的位置数字找不到时返回#VALUE!错误。它不区分大小写这是匹配英文字母时比较友好的特性。ISNUMBER 把 SEARCH 的结果转成 TRUE/FALSE找到关键字是 TRUE找不到是 FALSE。但 BYROW 内部处理的是一行多列数组SEARCH 对一行中多个字段会返回多个结果所以需要 SUMPRODUCT 来汇总SUMPRODUCT(--ISNUMBER(SEARCH($B$2, r))) 0其中--把 TRUE/FALSE 转为 1/0SUMPRODUCT 对这行多个字段的命中结果求和。只要和大于 0就说明这一行的某个字段包含关键字。如果不用 SUMPRODUCT也可以写成OR(ISNUMBER(...))只是部分 WPS 版本对 OR 处理数组的兼容性不如 SUMPRODUCT 稳定。所以这里推荐 SUMPRODUCT 写法。2.4 动态数组的“#”溢出引用与名称管理器FILTER 返回的结果是动态数组会从公式所在单元格向下自动扩展。在公式引用中使用区域左上角单元格#可以代表整个溢出区域。例如录入表!$D$2#表示 D2 以及 D2 下方所有溢出结果。但数据验证的“序列来源”并不能直接输入一个动态数组公式它要求输入一个区域或名称。标准做法是把 FILTER 公式放在一个单元格中让结果溢出然后用名称管理器新建一个名称引用这个溢出区域最后数据验证的序列来源填入名称。这一步是整个方案最绕的环节也是很多人看过公式却做不出来的原因。2.5 组合效果的本质把上面几个函数串起来逻辑是这样的用户在某一个单元格里输入关键字。FILTER 从商品表中筛选包含关键字的商品。FILTER 结果成为一个动态数组作为候选池。名称管理器把候选池变成数据验证可引用的名称。数据验证下拉菜单显示这个动态候选池。整个过程没有任何宏不需要启用内容也没有辅助列在界面上一堆堆地出现。相比 VBA纯函数方案在协作交付时更容易被同事接受。3. 版本检查与环境准备使用这套方案的前提是你使用的软件支持动态数组函数特别是 FILTER、BYROW 和 LAMBDA。这几个函数不是所有 Excel 版本都支持务必先确认环境。支持情况如下Office 365 / Microsoft 365完整支持 FILTER、BYROW、LAMBDA、SORT、UNIQUE。Excel 2021支持主要动态数组函数。Excel 2019 及更早版本基本不支持动态数组不建议使用本方案。WPS 表格较新版本已逐步支持动态数组函数但版本差异较大LAMBDA 和 BYROW 的支持程度需要实际检测。最简单的检测方法是打开一个空白工作表在 A1 输入FILTER(A1:A3, {1;0;1}, 无)如果 A1 返回“无”说明 FILTER 可用。再在 B1 输入BYROW(A1:C1, LAMBDA(r, SUM(r)))如果返回计算结果说明 BYROW 和 LAMBDA 都可用如果返回#NAME?说明当前版本不支持或函数名称解析有问题。在 WPS 中如果检测发现#NAME?可以尝试升级到最新版本。如果新版仍不支持也可以使用本文后面介绍的兼容方案用 INDEX IFERROR 把动态数组结果转换为固定辅助区域避免直接依赖 # 溢出引用。4. 第一步全表模糊匹配核心公式先用一个最小化的例子理解完整实现。假设有两个工作表“商品表”存放数据源A 列是商品名称B 列是类别AB商品名称类别红富士苹果水果陕西苹果汁饮品香蕉水果苹果味酸奶乳品梨水果“录入表”里设计录入界面。B2 是关键字输入单元格D2 是候选列表输出位置。现在我们要实现的效果是在 B2 输入“苹果”D 列自动列出所有商品名称或类别中包含“苹果”的行点击录入列的下拉菜单选项就是这个候选池。核心公式放在 D2FILTER(商品表!$A$2:$A$200, BYROW(商品表!$A$2:$B$200, LAMBDA(r, SUMPRODUCT(--ISNUMBER(SEARCH($B$2, r))) 0)), 无匹配)逐层拆解SEARCH($B$2, r)在每一行的 r 数组里查找关键字返回位置数组或错误。ISNUMBER(...)把位置转为 TRUE把错误转为 FALSE。--ISNUMBER(...)把布尔值变成 1 和 0。SUMPRODUCT(...) 0只要该行任意一个字段包含关键字就返回 TRUE。BYROW(...)对每一行执行上面的判断返回整个数据源的 TRUE/FALSE 数组。FILTER(...)从商品名称列中筛出所有匹配行。如果在 B2 输入“苹果”D 列会动态显示“红富士苹果”“陕西苹果汁”“苹果味酸奶”。如果输入“水果”也会显示“红富士苹果”和“香蕉”因为 B 列类别字段命中了。如果你的商品表只有“商品名称”一列其实不需要 BYROWFILTER 加单列判断即可FILTER(商品表!$A$2:$A$200, ISNUMBER(SEARCH($B$2, 商品表!$A$2:$A$200)), 无匹配)多字段匹配是标题里 BYROW 存在的意义。注意FILTER 是动态数组函数在 Office 365 或 Excel 2021 中直接回车即可不需要按 CtrlShiftEnter。老版本如果出现#NAME?说明不支持动态数组。5. 第二步把候选列表接入下拉菜单公式生成候选池后剩下的事是让数据验证下拉菜单引用它。这里有两种做法。5.1 方法一动态数组溢出引用 名称管理器适合 Office 365、Excel 2021 以及支持 # 溢出引用的 WPS 新版。第一步确认 D2 的 FILTER 公式已经返回结果。D2 下方会出现同色的溢出区域边框。第二步打开名称管理器。Excel菜单“公式” - “名称管理器” - “新建”。WPS菜单“公式” - “名称管理器” - “新建”。名称可以写DropList引用位置输入录入表!$D$2#这里#表示引用 D2 及其所有溢出结果。第三步设置数据验证。选中录入表中需要下拉录入的单元格区域例如 C2:C100然后Excel数据 - 数据验证 - 设置。WPS数据 - 有效性 - 设置。允许条件选择“序列”来源输入DropList注意“DropList”前面的等号不能省略输入的就是名称而不是区域。第四步去掉“输入无效数据时显示出错警告”的勾选这样当候选中没有匹配项时用户可以手动输入其他值不会被拦截。完成后在 B2 修改关键字再点 C 列单元格的下拉箭头就能看到与关键字匹配的候选列表。5.2 方法二INDEX IFERROR 固定辅助区域部分 WPS 版本或企业旧版 Excel 虽然支持 FILTER但不一定支持数据验证直接引用带 # 的名称。此时可以用固定辅助区域来承接 FILTER 的结果再用名称引用这个固定区域。在录入表 E2 输入IFERROR(INDEX(FILTER(商品表!$A$2:$A$200, BYROW(商品表!$A$2:$B$200, LAMBDA(r, SUMPRODUCT(--ISNUMBER(SEARCH($B$2, r))) 0)), ), ROWS(E$2:E2)), )然后将 E2 向下填充到 E201给候选结果预留 200 行。这段公式的作用是每次从 FILTER 结果中取第一行、第二行……如果取不到对应的行IFERROR 返回空文本。此时再打开名称管理器新建名称DropList引用位置改为录入表!$E$2:$E$201数据验证的区域来源依然填写DropList。这个方法不依赖 # 溢出引用兼容性更高。代价是辅助区域占用了 200 行固定空间而且如果实际匹配数超过 200 行结果会被截断。数据量不大时优先用方法一环境不支持时再用方法二。6. 进阶多字段匹配、独立备选与去重排序基础方案已经可以用但真实业务里还有三个高频需求多字段匹配、每行独立关键字、去重和排序。6.1 多字段匹配与“全表”的含义标题里的“全表模糊匹配”意思是匹配范围是整个数据源表的所有相关字段而不只是某一列。前面的公式已经把 A 列商品名称和 B 列类别都纳入匹配如果商品表还有规格、供应商、品牌列只需要把 BYROW 的第一参数区域扩展到对应列即可。例如要把规格也纳入匹配FILTER(商品表!$A$2:$A$200, BYROW(商品表!$A$2:$D$200, LAMBDA(r, SUMPRODUCT(--ISNUMBER(SEARCH($B$2, r))) 0)), 无匹配)这里把$A$2:$B$200改为$A$2:$D$200筛选区域仍是 A 列商品名称。全表匹配的价值在于用户不记得完整商品名只记得供应商或类别关键词也能把候选选出来。6.2 每行独立关键字与独立备选还有一类场景是“每条记录的关键字不同”。例如在销售明细表中每一行要录入不同分类下的商品你希望每一行的下拉候选都不同。思路是在每一行放一个关键字输入列并在该行右侧放一个独立的 FILTER 公式为这一行生成独立候选池。例如A 列该行关键字B 列录入商品名称D 列该行候选池在 D2 输入FILTER(商品表!$A$2:$A$200, ISNUMBER(SEARCH($A2, 商品表!$A$2:$A$200)), 无匹配)D2 会向下溢出形成这一行的独立备选列表。然后为每一行单独建立名称例如Row2Drop 录入表!$D$2#数据验证来源分别填Row2Drop、Row3Drop。如果只有少数几行这种做法效率很高。如果录入表有几十行逐行建名称不现实建议改用顶部一个关键字单元格 整列共用一个下拉候选池的结构这也是大多数录入模板更合理的交互方式。6.3 去重与排序商品表里如果存在重复记录下拉列表也会出现重复项。可以在 FILTER 外面再包一层 UNIQUE 和 SORTSORT(UNIQUE(FILTER(商品表!$A$2:$A$200, BYROW(商品表!$A$2:$B$200, LAMBDA(r, SUMPRODUCT(--ISNUMBER(SEARCH($B$2, r))) 0)), 无匹配)))这样候选池会自动去重并按字母顺序排序。注意 SORT 和 UNIQUE 同样依赖动态数组版本要求与 FILTER 一致。7. 运行结果与效果验证公式写完后建议按下面步骤做一轮完整验证确认下拉菜单真的生效。第一步在“录入表”的 B2 输入“苹果”看 D 列候选池是否显示“红富士苹果”“陕西苹果汁”“苹果味酸奶”。第二步点 C2 单元格按下拉箭头检查下拉列表中显示的内容是否和 D 列一致。第三步把 B2 改成“水果”确认候选池和下拉列表同时变化出现“红富士苹果”和“香蕉”。第四步点击某个下拉选项确认单元格正常填入不报“输入值非法”。第五步把 B2 改成不存在的内容例如“火星”确认候选池显示“无匹配”。如果数据验证勾选了“输入无效数据时显示出错警告”手动输入一个不在列表内的值会被拦截这是预期行为。如果某一步没有达到预期优先检查三个方面B2 是否真的是你期望的引用单元格名称管理器里的引用区域是否指向 D2# 或 E2:E201数据验证的“序列来源”是否写成了DropList而不是DropList。这三处是最容易出问题的地方。8. 常见问题与排查思路问题现象可能原因排查方式解决方案输入公式后显示#NAME?当前 Excel/WPS 版本不支持 FILTER、BYROW 或 LAMBDA用最简公式单独测试 FILTER 和 BYROW升级到支持动态数组的版本或改用 VBA / 传统辅助列方案数据验证来源报“源目前包含错误”名称引用的区域不存在或区域中包含错误值、区域大小为 0打开名称管理器检查引用位置是否可定位修正名称引用如果候选池在另一个 Sheet确认名称没有写错工作表名下拉菜单不更新数据验证来源写成了固定区域而不是名称或名称没有指向动态数组重新检查数据验证的序列来源改为DropList这种名称引用形式FILTER 公式在 D2 返回“无匹配”B2 关键字在数据源中确实不存在直接检查商品表对应字段是否包含该关键词修改关键字如果数据源是文本格式确认没有隐藏字符或全角半角差异下拉选项出现空白项辅助区域范围太大E 列填充到 200 行空字符串占位取消隐藏辅助列观察真实情况改用动态名称 OFFSET(录入表!$E$2,0,0,COUNTA(录入表!$E$2:$E$201),1)商品表新增数据后下拉不显示名称或公式中引用了固定区域$A$2:$A$100检查公式和名称是否只到 100 行把公式范围扩大或用整列引用注意性能公式计算卡顿数据源行数过多BYROW 逐行执行 LAMBDA且引用了整列检查公式是否引用 A:A 整列指定 200-2000 行左右的有界范围避免整列引用关键字输入“A*B”时匹配结果