月薪 3 千和月薪 3 万的差距很多时候不在会不会用 Excel而在同样的录入工作交给谁做。一个工号敲进去姓名、部门、岗位、入职日期、手机号全自动带出来一张原始表改一行汇总表、人员清单、拆分后的明细同步刷新——这套动作靠的就是 4 个函数的组合XLOOKUP、FILTER、TEXTJOIN、TEXTSPLIT。它们分别解决查得到筛得出合得起来拆得开四件事覆盖了日常录入工作里 80% 的机械操作。这篇文章不讲函数语法手册而是按先建一张测试表 → 逐个函数验证 → 组合成一套自动录入模板的顺序走一遍。你会看到每个函数解决的具体痛点、可复制的完整公式、老版本 Excel 和 WPS 的降级写法以及公式做好之后容易踩的坑比如 #SPILL! 溢出报错、文本型数字匹配不上、整列引用把表拖慢。需要提前说明的是这 4 个函数里 XLOOKUP 和 FILTER 属于动态数组函数Excel 365、Excel 2021 及较新的 WPS 表格版本支持较好TEXTSPLIT 推出更晚兼容性最差很多环境需要降级到分列或快速填充。所以本文每个函数都会给一套新版写法 老版替代方案你按自己电脑上的版本挑着用。适合读这篇的人很明确每天要在 Excel 里重复录入、重复核对、重复整理名单的运营、人事、财务、教务、仓储岗以及需要给同事做模板、又不想天天被人问这个格子怎么又错了的人。全文公式都可以直接复制改一下工作表名和区域就能跑。1. 四个函数核心能力速览先把这 4 个函数的定位摆清楚。很多人学函数失败不是因为记不住参数而是不知道什么场景该拿哪个出来。下面这张表是本文后面所有实战的总纲建议先扫一眼。能力项说明函数组合XLOOKUP查找匹配、FILTER条件筛选、TEXTJOIN多值合并、TEXTSPLIT文本拆分解决的核心问题重复录入、重复核对、重复整理名单、重复拆分单元格版本门槛XLOOKUP / FILTER 需 Excel 365、Excel 2021 或较新 WPSTEXTSPLIT 需更新版本兼容性最差老版本替代VLOOKUP / INDEXMATCH、高级筛选 / 数据透视表、 连接符 / PHONETIC、分列 / 快速填充 CtrlE是否支持批量支持公式向下填充即可批量处理超大数据量建议配合 Power Query是否需要编程不需要本文另附 VBA 与 Python 对照写法供需要做批量任务调度的人参考典型输出自动填表、动态看板、合并后的标签文本、拆分后的规范明细适合场景员工信息录入、订单明细整理、学生名单汇总、库存标签生成、问卷多选拆分不适合场景十万行以上的高频重算、跨多工作簿的复杂关联、需要审计留痕的正式报表这张表里最值得注意的一行是版本门槛。XLOOKUP 是微软在动态数组体系里推出的查找函数参数比 VLOOKUP 少一个列序号多了未找到时返回什么用起来顺手很多FILTER 同样是动态数组函数一次返回一整个结果区域。但这两个函数在 Excel 2016 / 2019 上是不存在的敲进去只会得到 #NAME?。所以动手之前先确认版本比背参数重要得多。第二件要注意的事这 4 个函数不是并列关系而是有主次的。XLOOKUP 和 FILTER 负责把数据取过来TEXTJOIN 负责把取过来的多条结果拼成一句话TEXTSPLIT 负责把别人给我的、已经拼好的一坨文本重新拆开。理解了这个数据流向组合公式就不会写成一锅粥。2. 适用场景与使用边界先说适合的场景。只要你的工作里出现同一份信息要填两遍以上这 4 个函数就有用武之地。典型如拿着工号去员工表里反查姓名和部门拿着部门名把所有成员筛出来做通讯录把筛选结果拼成张三、李四、王五这种一行文本塞进邮件把别人发来的姓名-部门-工号混排字符串拆成三列。这些动作手动做一次两分钟做一百次就是三个多小时而公式做一次之后就永久免费。再说边界这部分很多人不爱看但恰恰决定了模板能不能长期用。第一种边界是数据量。公式是在内存里逐行计算的几千行、一两万行的规模完全没压力但如果你有一张二十万行的流水表还挂了几百个整列引用的数组公式每次改一个格子整表重算体验会非常糟糕。这种场景应该交给 Power Query 或数据透视表而不是硬堆函数。第二种边界是公式能解决逻辑解决不了脏数据。比如员工表里工号存成了文本左上角带绿色小三角录入表里你手敲的是数字格式XLOOKUP 就会告诉你找不到。这不是函数的问题是数据规范的问题。所以后面第 8 节会专门列一张排查表把明明有数据却匹配不上这类现象拆开讲。第三种边界是合规。人事信息、客户手机号、身份证号这类数据放进公式模板里很容易被随手转发出去。做模板时建议把明细表放在单独工作表并加保护录入表只暴露必要字段导出的文件在传阅前先脱敏。手机号可以只保留后四位身份证号用REPLACE处理掉中间段。这不是洁癖是基本的职业习惯。3. 环境准备与版本兼容这一节讲清楚你手上的软件到底能不能用。版本问题不查清楚后面所有公式都是白写。3.1 先确认自己的版本打开 Excel点文件 → 账户 → 关于 Excel能看到具体版本号。判断标准很粗但够用能看到Microsoft 365字样的四个函数基本都有显示 Excel 2021 的XLOOKUP 和 FILTER 有TEXTSPLIT 不一定有显示 Excel 2019 / 2016 的一个都没有直接看降级方案。WPS 表格的判断方法类似在设置 → 关于 WPS里看版本较新的个人版已经支持 XLOOKUP 和 FILTERTEXTSPLIT 支持情况以本机实测为准。最靠谱的验证方式是直接在一个空白单元格里敲函数名加左括号比如输入XLOOKUP(如果软件自动弹出参数提示说明支持如果敲完回车报 #NAME?说明不支持。三种函数各试一次一分钟就能把环境摸清楚比查文档快。XLOOKUP( FILTER( TEXTSPLIT(上面三行不是完整公式就是把函数名敲进去看有没有提示。这一步千万别省尤其是用公司统一装的 Office 时版本往往比你想的旧。3.2 需要提前打开的设置动态数组函数有一个前提结果区域必须是空的否则会报 #SPILL!。所以做模板时公式右侧和下方的区域要提前留空不要在同一片区域塞别的数据。另外建议在公式 → 计算选项里确认是自动重算如果是手动公式改了不会自动刷新很容易误判成公式写错。还有一个容易被忽略的点区域引用尽量写成固定范围加绝对引用比如员工明细!$A$2:$A$5000而不是员工明细!$A:$A。新版 Excel 对整列引用做了优化但数组公式叠加整列引用时重算开销会明显变大尤其是在老机器上。养成写固定范围的习惯模板换到别人电脑上也不容易卡。4. 从手动录入到自动匹配XLOOKUP 与 FILTER 实战准备工作做完开始上真东西。这一节先解决查得到和筛得出两个动作用的是一份员工明细表和一份录入表。4.1 搭建一份最小可用的测试数据新建一个工作簿第一张表命名为员工明细从 A1 开始录入表头内容如下工号姓名部门岗位入职日期手机号A001张伟销售部销售主管2021/3/113800000001A002李娜销售部销售专员2022/7/1513800000002A003王强技术部前端工程师2020/11/213800000003A004赵敏技术部后端工程师2023/1/913800000004A005陈晨人事部招聘专员2022/5/2013800000005再新建第二张表命名为录入表A1 写请输入工号B1 到 F1 分别写姓名部门岗位入职日期手机号。这张录入表就是最终交付给同事用的界面后面所有公式都写在这里。这份测试数据只有 5 行但足够验证全部逻辑。真实使用时行数随便加只要把公式里的区域范围改掉就行。测试阶段不要用真实的人事数据先用假数据把流程跑通确认没问题再接入正式表。4.2 XLOOKUP一次带出整行信息在录入表的 B2 单元格写入下面这条公式XLOOKUP($A2,员工明细!$A$2:$A$5000,员工明细!$B$2:$B$5000,未找到该工号)公式里四个参数依次是找什么A2 里输入的工号、在哪找员工明细的工号列、返回什么员工明细的姓名列、找不到时显示什么。在 A2 里输入 A003B2 立刻显示王强输入 A999显示未找到该工号不会甩一个 #N/A 出来吓人。这就是 XLOOKUP 比 VLOOKUP 舒服的地方——错误兜底被做成了参数不用再套一层 IFERROR。多列信息当然不用写五遍。C2 到 F2 可以合成一条公式直接返回姓名到手机号这一整片区域XLOOKUP($A2,员工明细!$A$2:$A$5000,员工明细!$B$2:$F$5000,未找到该工号)在支持动态数组的版本里这一条公式写进 C2 就会自动向右溢出把四列结果一次铺开。如果版本不支持溢出选中 C2:F2 再输入公式按 CtrlShiftEnter 作为数组公式确认效果一样只是以后修改时要整片改。XLOOKUP 还有两个进阶用法值得记一下。一个是反向查找VLOOKUP 时代查左边的数据要靠 INDEXMATCH 或者调列序现在只需要把返回区域写到前面去方向完全无所谓。另一个是区间匹配把第五个参数设为 -1 或 1可以做等级判定比如按分数查档位、按金额查折扣率比嵌套 IF 干净得多。XLOOKUP($B2,员工明细!$E$2:$E$5000,员工明细!$B$2:$B$5000,无记录)上面这条是按入职日期反查姓名演示返回区域可以在查找区域左边这件事实际业务里用得不多但理解它有助于你摆脱 VLOOKUP 时代养成的被查列必须在最左边的思维定式。老版本没有 XLOOKUP 怎么办两条路。第一条用 INDEX MATCH 组合INDEX(员工明细!$B$2:$B$5000,MATCH($A2,员工明细!$A$2:$A$5000,0))效果等价只是没有兜底参数外面要再包一层 IFERROR。第二条就是老老实实用 VLOOKUP但要求被查列在返回列的左边且插入新列时列序号会错位维护成本明显更高。IFERROR(INDEX(员工明细!$B$2:$B$5000,MATCH($A2,员工明细!$A$2:$A$5000,0)),未找到该工号)这条是老版本环境下的首选写法虽然长一点但稳定性和兼容性都更好。4.3 FILTER把符合条件的行整片筛出来录入表解决的是单条查询FILTER 解决的是批量筛选。假设部门经理要看销售部的全部人员把部门名写在 H1 单元格里然后在一个空白区域写入FILTER(员工明细!$A$2:$F$5000,员工明细!$C$2:$C$5000$H1,该部门暂无人员)回车之后销售部的所有行会整片铺出来包含工号、姓名、部门、岗位、入职日期、手机号。把 H1 改成技术部结果立刻切换不用重新点筛选、不用重新勾选复选框。这就是动态两个字的价值——数据源改了结果跟着改。多条件筛选也简单条件之间用乘号表示并且用加号表示或者FILTER(员工明细!$A$2:$F$5000,(员工明细!$C$2:$C$5000$H1)*(员工明细!$D$2:$D$5000$I1),无匹配记录)这条公式的意思是部门等于 H1 且岗位等于 I1。注意每个条件都要用括号包起来否则运算优先级会让结果出错。至于或者关系写成(条件1)(条件2)即可返回的是去重后的并集。FILTER 最容易遇到的报错是 #SPILL!原因几乎都是公式下方或右方已经有内容溢出区域被占用。处理方法很简单选中公式单元格下方的区域清空内容公式立刻恢复正常。这一点在别人发来的模板里尤其常见看到 #SPILL! 先在公式周围找找有没有残留的旧数据。老版本没有 FILTER替代方案是数据 → 筛选配合高级筛选或者直接上数据透视表。高级筛选能做条件区域但结果是静态的数据源更新后要重新执行一次数据透视表刷新一下就行但输出的字段布局受限于透视结构。如果你的版本确实太老建议优先用数据透视表比反复点筛选省事。5. TEXTJOIN 与 TEXTSPLIT合并与拆分查得到、筛得出之后接下来的两个动作是把多条结果拼成一句话和把一坨文本拆成多列。这两个动作看起来方向相反实际业务里往往成对出现。5.1 TEXTJOIN把筛选结果拼成一行文本继续用上面那张员工明细表。现在想在 H3 单元格里输出一句话销售部成员张伟、李娜。公式这样写销售部成员TEXTJOIN(、,TRUE,FILTER(员工明细!$B$2:$B$5000,员工明细!$C$2:$C$5000销售部,))三个参数分别是分隔符顿号、是否忽略空值TRUE、要连接的文本区域。中间嵌套的 FILTER 负责先把销售部的姓名筛出来TEXTJOIN 再把它们串成一串。如果没有 TEXTJOIN这件事要么用一长串 硬拼要么写 VBA都很难维护。改成动态部门更实用直接把部门名指向单元格$H1成员TEXTJOIN(、,TRUE,FILTER(员工明细!$B$2:$B$5000,员工明细!$C$2:$C$5000$H1,无人员))这样 H1 换成哪个部门下面这句话就自动变成哪个部门的名单。做周报、做部门通讯录、做群发消息的收件人列表这一条公式能省掉大量复制粘贴。TEXTJOIN 还有一个不那么常见但很好用的场景把一个单元格区域里的多行内容合并成一行。比如别人发来一列地址你想拼成一行用逗号隔开TEXTJOIN(,,TRUE,A2:A100)一条就够不用再一个一个点。TEXTJOIN(,,TRUE,A2:A100)注意第二个参数写 TRUE 表示跳过空单元格如果写成 FALSE区域里的空白会变成连续的逗号输出会很难看。这个参数写错的概率相当高检查公式时优先看这里。老版本没有 TEXTJOIN退而求其次可以用手动连接但列数一多就没法维护也可以用 PHONETIC 函数但它只对文本格式生效数字会被忽略适用面很窄。比较现实的做法是把合并工作放到导出之后用 Python 处理或者升级软件版本。5.2 TEXTSPLIT把混排文本重新拆成列TEXTSPLIT 解决的是反方向的问题别人给你一坨文本你要拆开。比如 A2 单元格里是张伟、李娜、王强要拆成三列TEXTSPLIT(A2,、)回车后三个名字自动占据 B2、C2、D2 三个格子。多个分隔符一起处理也没问题用花括号把分隔符列出来TEXTSPLIT(A2,{、,,,;,})这条公式能同时应对中文顿号、英文逗号、中英文分号处理别人发来的格式不统一的名单时特别省事。再加一个参数可以控制是否忽略连续分隔符产生的空值TEXTSPLIT(A2,、,,TRUE)第四个参数写 TRUE遇到张伟、、李娜这种连续分隔符时不会多出一个空列。如果只需要取分隔符前面或后面的一段用 TEXTBEFORE 和 TEXTAFTER 更直接。比如 A2 里是张伟-销售部-A001想拆成三段TEXTBEFORE(A2,-) TEXTBEFORE(TEXTAFTER(A2,-),-) TEXTAFTER(A2,-,2)第一条取第一个分隔符之前的内容得到张伟第三条从第二个分隔符之后取得到A001中间那条是嵌套写法先取第一个分隔符之后的销售部-A001再取这个结果里第一个分隔符之前的销售部。三条公式组合起来就是完整的三段拆分而且比 TEXTSPLIT 更适合只取其中某一段的场景。实际工作中更常见的是带标签的混排文本比如姓名张伟手机号13800000001部门销售部。这种用 TEXTBEFORE / TEXTAFTER 按标签切最稳TEXTBEFORE(TEXTAFTER(A2,姓名),) TEXTAFTER(A2,手机号)第一条先定位到姓名之后再取到下一个逗号之前第二条直接取手机号之后的全部内容。这种写法不依赖固定位置文本顺序变了也能取对比 LEFT/MID 按字符数截取可靠得多。老版本没有这几个函数替代方案是数据 → 分列按分隔符拆缺点是拆完之后数据源变了要重新拆一次不是动态的。另一个替代是快速填充 CtrlE手动敲出第一行的结果让 Excel 猜规律适合格式高度一致的场景但规律一复杂就容易猜错谨慎使用。6. 组合实战一套自动录入模板单个函数会用之后真正省时间的是把它们串起来。这一节拼一个完整的小模板目标是输入工号 → 自动带出全部信息 → 自动生成部门人员名单 → 自动把多值单元格拆成明细。第一步录入表 A2 输入工号B2 到 F2 用前面那条 XLOOKUP 多列公式一次性带出姓名、部门、岗位、入职日期、手机号。这一层解决不用手动敲。第二步在录入表下方做一个部门汇总区。H1 填部门名H2 输出名单文本H3 输出人数COUNTA(FILTER(员工明细!$B$2:$B$5000,员工明细!$C$2:$C$5000$H1,))这一层解决不用临时找数据。COUNTA 统计 FILTER 返回的非空单元格数量就是该部门的人数FILTER 第三参数写空字符串避免没匹配到时 COUNTA 把错误值也数进去。第三步做一块批量拆分区。假设 J 列是别人发来的一列混排字符串每条里包含多个用顿号隔开的标签想把每条拆成多行。这一步在纯公式里做比较绕实际更推荐两条路一条是用 TEXTSPLIT 拆成多列横向适合后续按列处理另一条是用 Power Query 的按分隔符拆分到行适合拆完之后继续做透视统计。TEXTSPLIT(J2,、)横向拆是最省事的做法一行拆成几列就用几个辅助列不需要写数组公式。如果一定要纵向拆成多行新版可以用TOCOL配合但函数组合会明显变复杂日常业务里性价比不高。第四步给模板加保护。选中明细表右键设置单元格格式 → 保护 → 锁定再在审阅 → 保护工作表里设密码录入表保持可编辑。这样交出去之后别人只能填工号改不动底层数据公式也不会被误删。做完这一步一套能给同事直接用的录入模板就算完工了。7. 批量任务与性能观察公式做完了还得知道它跑得快不快、什么时候会撑不住。这一节讲观察方法和批量处理思路。7.1 什么时候该换工具判断标准很简单如果改一个单元格之后状态栏的计算提示要转好几秒才消失说明公式量已经过大。常见原因是整列引用$A:$A叠加数组公式或者同一片区域被几十条公式反复引用。优化顺序是先把整列引用改成固定范围再看能不能用辅助列把重复计算的部分先算一次最后考虑把明细表压缩成只保留必要字段。另一个观察点是文件体积。XLOOKUP 和 FILTER 本身不占空间但如果公式里引用了整列Excel 会把整列纳入计算范围文件会莫名变大。做完模板后看一眼文件大小如果几十 KB 的数据变成好几 MB多半是引用范围写太宽了。7.2 用 VBA 做批量填充公式是逐行拖的如果明细表有几千行、几十列都要填公式手动拖也烦。这时候可以用一段很短的宏一次性铺完Sub FillFormulas() Dim ws As Worksheet Dim lastRow As Long Set ws ThisWorkbook.Sheets(员工明细) lastRow ws.Cells(ws.Rows.Count, A).End(xlUp).Row With ThisWorkbook.Sheets(录入表) .Range(B2:B lastRow).Formula _ XLOOKUP($A2,员工明细!$A$2:$A$5000,员工明细!$B$2:$B$5000,未找到该工号) End With MsgBox 已填充到第 lastRow 行, vbInformation End Sub这段宏做的事情很朴素算出明细表最后一行然后把公式一次性写进整片区域。注意公式里的双引号在 VBA 字符串中要写成两个双引号转义这是最容易写错的地方。运行前先备份文件宏是不可撤销操作。7.3 用 Python 做同一件事如果数据量大到 Excel 已经不好受可以把这一步搬到 Python 里。pandas 处理这类查找匹配几乎是一行的事import pandas as pd detail pd.read_excel(员工明细.xlsx, sheet_name员工明细) input_df pd.read_excel(录入表.xlsx, sheet_name录入表) result input_df.merge( detail[[工号, 姓名, 部门, 岗位]], on工号, howleft ) result[姓名] result[姓名].fillna(未找到该工号) result.to_excel(录入结果.xlsx, indexFalse) print(f处理完成共 {len(result)} 行)这段脚本对应 XLOOKUP 的逻辑以录入表的工号为主键去明细表里做左连接。没匹配上的行会自动变成空值再用 fillna 兜底成未找到该工号。十万行级别的数据这个脚本通常几秒就能跑完比在 Excel 里等重算要稳。8. 常见问题与排查方法公式模板出问题九成集中在下面这几种现象里。遇到报错先查表比乱改公式效率高。问题现象可能原因排查方式解决方案公式报 #NAME?当前版本不支持该函数单独敲函数名看有无提示换老版本写法或升级软件公式报 #SPILL!溢出区域已有内容看公式右下方有无数据清空溢出区域明明有数据却匹配不上文本型数字与数值型不一致检查单元格左上角绿色小三角用 VALUE 或 TEXT 统一格式结果显示 #N/A查找区域确实没有该值用 CtrlF 在明细表里搜一遍给 XLOOKUP 加第四参数兜底FILTER 返回空白没写第三参数且无匹配临时把条件改成必然成立的值测试补上第三参数TEXTJOIN 输出多余逗号第二参数写成了 FALSE检查参数顺序改成 TRUE 跳过空值TEXTSPLIT 拆出的列数不对分隔符中英文混用逐个检查原文分隔符用花括号列出多种分隔符改了数据但公式不更新计算选项被设为手动看公式选项卡里的计算选项切换为自动重算模板发给别人后公式全错工作表名被改过检查公式里的表名引用统一表名或用命名区域打开文件非常卡大量整列引用叠加数组公式观察状态栏计算耗时改成固定范围引用表格里最值得展开的是文本型数字这一条。它表现为你肉眼看到工号 A001 在两张表里都有但就是匹配不上或者明明两边都是 13800000001 却查不到。原因是其中一边被存成了文本通常是从系统导出时带过来的另一边是数值。解决办法是用--或者 VALUE 函数转一下或者在 XLOOKUP 里把查找值包一层 TEXT。XLOOKUP(TEXT($A2,),员工明细!$A$2:$A$5000,员工明细!$B$2:$B$5000,未找到该工号)把查找值统一按文本处理两边就对齐了。反过来如果明细表是数值而录入是文本就把TEXT(...,)换成VALUE(...)注意 VALUE 遇到带字母的工号会报错所以工号这类字段建议全表统一存成文本。另一类高频问题是公式复制到别的行就错位。这几乎都是绝对引用没写对$A2锁列不锁行、A$2锁行不锁列含义完全不同。复制公式前先想清楚哪些部分应该跟着行变化哪些必须固定不动。这个习惯养成之后模板的复用率会高一大截。9. 最佳实践与下一步把上面这些拼起来一套能长期用的模板大概有这么几个特征。第一数据层和展示层分开。明细数据放一张表录入表、汇总表、看板各放一张公式只从明细表取数绝不在明细表里写公式。这样明细表可以整表替换展示层不用动。第二区域引用全部写成固定范围加绝对引用命名区域是加分项员工明细!$A$2:$A$5000这种长串改成工号列会好读很多。第三先小范围验证再铺开。写一条公式拿三五行数据验证正确性确认没问题再往下拖几千行。反过来先拖满再检查一旦逻辑错了就是几千个错误值排查成本翻好几倍。第四模板交付前做一次破坏性测试故意输入不存在的工号、故意留空、故意把部门名改错看公式是不是都能给出可读的提示而不是甩一堆 #N/A 和 #VALUE!。第五涉及手机号、身份证号、薪资这类字段模板传阅前先脱敏明细表加保护导出的文件走内部渠道。技术上的便利不能替代数据安全意识。如果这 4 个函数已经用熟了下一步可以往三个方向走一是学 Power Query它能把每月导一次表、清洗一遍、合并一次的整套动作做成可刷新的流程比公式更适合处理重复性的数据准备二是学数据透视表配合切片器做交互式看板比堆公式省力三是把稳定的处理逻辑搬到 Python用脚本定时跑彻底摆脱手工打开文件的动作。回到最初那个对比。真正拉开差距的不是记住了几个函数名而是遇到重复劳动时第一反应是这段能不能交给公式而不是今晚加个班弄完。XLOOKUP 解决查得到FILTER 解决筛得出TEXTJOIN 解决合得起来TEXTSPLIT 解决拆得开四个动作串起来一张录入表就能自己长出来。建议先拿手头最烦的那张表练一遍从 XLOOKUP 开始一条公式跑通剩下的都是水到渠成。 
长河君 · 2026/10/24 2:39:47 · 阅读 1.2k XLOOKUP+FILTER+TEXTJOIN+TEXTSPLIT自动录入模板
月薪 3 千和月薪 3 万的差距很多时候不在会不会用 Excel而在同样的录入工作交给谁做。一个工号敲进去姓名、部门、岗位、入职日期、手机号全自动带出来一张原始表改一行汇总表、人员清单、拆分后的明细同步刷新——这套动作靠的就是 4 个函数的组合XLOOKUP、FILTER、TEXTJOIN、TEXTSPLIT。它们分别解决查得到筛得出合得起来拆得开四件事覆盖了日常录入工作里 80% 的机械操作。这篇文章不讲函数语法手册而是按先建一张测试表 → 逐个函数验证 → 组合成一套自动录入模板的顺序走一遍。你会看到每个函数解决的具体痛点、可复制的完整公式、老版本 Excel 和 WPS 的降级写法以及公式做好之后容易踩的坑比如 #SPILL! 溢出报错、文本型数字匹配不上、整列引用把表拖慢。需要提前说明的是这 4 个函数里 XLOOKUP 和 FILTER 属于动态数组函数Excel 365、Excel 2021 及较新的 WPS 表格版本支持较好TEXTSPLIT 推出更晚兼容性最差很多环境需要降级到分列或快速填充。所以本文每个函数都会给一套新版写法 老版替代方案你按自己电脑上的版本挑着用。适合读这篇的人很明确每天要在 Excel 里重复录入、重复核对、重复整理名单的运营、人事、财务、教务、仓储岗以及需要给同事做模板、又不想天天被人问这个格子怎么又错了的人。全文公式都可以直接复制改一下工作表名和区域就能跑。1. 四个函数核心能力速览先把这 4 个函数的定位摆清楚。很多人学函数失败不是因为记不住参数而是不知道什么场景该拿哪个出来。下面这张表是本文后面所有实战的总纲建议先扫一眼。能力项说明函数组合XLOOKUP查找匹配、FILTER条件筛选、TEXTJOIN多值合并、TEXTSPLIT文本拆分解决的核心问题重复录入、重复核对、重复整理名单、重复拆分单元格版本门槛XLOOKUP / FILTER 需 Excel 365、Excel 2021 或较新 WPSTEXTSPLIT 需更新版本兼容性最差老版本替代VLOOKUP / INDEXMATCH、高级筛选 / 数据透视表、 连接符 / PHONETIC、分列 / 快速填充 CtrlE是否支持批量支持公式向下填充即可批量处理超大数据量建议配合 Power Query是否需要编程不需要本文另附 VBA 与 Python 对照写法供需要做批量任务调度的人参考典型输出自动填表、动态看板、合并后的标签文本、拆分后的规范明细适合场景员工信息录入、订单明细整理、学生名单汇总、库存标签生成、问卷多选拆分不适合场景十万行以上的高频重算、跨多工作簿的复杂关联、需要审计留痕的正式报表这张表里最值得注意的一行是版本门槛。XLOOKUP 是微软在动态数组体系里推出的查找函数参数比 VLOOKUP 少一个列序号多了未找到时返回什么用起来顺手很多FILTER 同样是动态数组函数一次返回一整个结果区域。但这两个函数在 Excel 2016 / 2019 上是不存在的敲进去只会得到 #NAME?。所以动手之前先确认版本比背参数重要得多。第二件要注意的事这 4 个函数不是并列关系而是有主次的。XLOOKUP 和 FILTER 负责把数据取过来TEXTJOIN 负责把取过来的多条结果拼成一句话TEXTSPLIT 负责把别人给我的、已经拼好的一坨文本重新拆开。理解了这个数据流向组合公式就不会写成一锅粥。2. 适用场景与使用边界先说适合的场景。只要你的工作里出现同一份信息要填两遍以上这 4 个函数就有用武之地。典型如拿着工号去员工表里反查姓名和部门拿着部门名把所有成员筛出来做通讯录把筛选结果拼成张三、李四、王五这种一行文本塞进邮件把别人发来的姓名-部门-工号混排字符串拆成三列。这些动作手动做一次两分钟做一百次就是三个多小时而公式做一次之后就永久免费。再说边界这部分很多人不爱看但恰恰决定了模板能不能长期用。第一种边界是数据量。公式是在内存里逐行计算的几千行、一两万行的规模完全没压力但如果你有一张二十万行的流水表还挂了几百个整列引用的数组公式每次改一个格子整表重算体验会非常糟糕。这种场景应该交给 Power Query 或数据透视表而不是硬堆函数。第二种边界是公式能解决逻辑解决不了脏数据。比如员工表里工号存成了文本左上角带绿色小三角录入表里你手敲的是数字格式XLOOKUP 就会告诉你找不到。这不是函数的问题是数据规范的问题。所以后面第 8 节会专门列一张排查表把明明有数据却匹配不上这类现象拆开讲。第三种边界是合规。人事信息、客户手机号、身份证号这类数据放进公式模板里很容易被随手转发出去。做模板时建议把明细表放在单独工作表并加保护录入表只暴露必要字段导出的文件在传阅前先脱敏。手机号可以只保留后四位身份证号用REPLACE处理掉中间段。这不是洁癖是基本的职业习惯。3. 环境准备与版本兼容这一节讲清楚你手上的软件到底能不能用。版本问题不查清楚后面所有公式都是白写。3.1 先确认自己的版本打开 Excel点文件 → 账户 → 关于 Excel能看到具体版本号。判断标准很粗但够用能看到Microsoft 365字样的四个函数基本都有显示 Excel 2021 的XLOOKUP 和 FILTER 有TEXTSPLIT 不一定有显示 Excel 2019 / 2016 的一个都没有直接看降级方案。WPS 表格的判断方法类似在设置 → 关于 WPS里看版本较新的个人版已经支持 XLOOKUP 和 FILTERTEXTSPLIT 支持情况以本机实测为准。最靠谱的验证方式是直接在一个空白单元格里敲函数名加左括号比如输入XLOOKUP(如果软件自动弹出参数提示说明支持如果敲完回车报 #NAME?说明不支持。三种函数各试一次一分钟就能把环境摸清楚比查文档快。XLOOKUP( FILTER( TEXTSPLIT(上面三行不是完整公式就是把函数名敲进去看有没有提示。这一步千万别省尤其是用公司统一装的 Office 时版本往往比你想的旧。3.2 需要提前打开的设置动态数组函数有一个前提结果区域必须是空的否则会报 #SPILL!。所以做模板时公式右侧和下方的区域要提前留空不要在同一片区域塞别的数据。另外建议在公式 → 计算选项里确认是自动重算如果是手动公式改了不会自动刷新很容易误判成公式写错。还有一个容易被忽略的点区域引用尽量写成固定范围加绝对引用比如员工明细!$A$2:$A$5000而不是员工明细!$A:$A。新版 Excel 对整列引用做了优化但数组公式叠加整列引用时重算开销会明显变大尤其是在老机器上。养成写固定范围的习惯模板换到别人电脑上也不容易卡。4. 从手动录入到自动匹配XLOOKUP 与 FILTER 实战准备工作做完开始上真东西。这一节先解决查得到和筛得出两个动作用的是一份员工明细表和一份录入表。4.1 搭建一份最小可用的测试数据新建一个工作簿第一张表命名为员工明细从 A1 开始录入表头内容如下工号姓名部门岗位入职日期手机号A001张伟销售部销售主管2021/3/113800000001A002李娜销售部销售专员2022/7/1513800000002A003王强技术部前端工程师2020/11/213800000003A004赵敏技术部后端工程师2023/1/913800000004A005陈晨人事部招聘专员2022/5/2013800000005再新建第二张表命名为录入表A1 写请输入工号B1 到 F1 分别写姓名部门岗位入职日期手机号。这张录入表就是最终交付给同事用的界面后面所有公式都写在这里。这份测试数据只有 5 行但足够验证全部逻辑。真实使用时行数随便加只要把公式里的区域范围改掉就行。测试阶段不要用真实的人事数据先用假数据把流程跑通确认没问题再接入正式表。4.2 XLOOKUP一次带出整行信息在录入表的 B2 单元格写入下面这条公式XLOOKUP($A2,员工明细!$A$2:$A$5000,员工明细!$B$2:$B$5000,未找到该工号)公式里四个参数依次是找什么A2 里输入的工号、在哪找员工明细的工号列、返回什么员工明细的姓名列、找不到时显示什么。在 A2 里输入 A003B2 立刻显示王强输入 A999显示未找到该工号不会甩一个 #N/A 出来吓人。这就是 XLOOKUP 比 VLOOKUP 舒服的地方——错误兜底被做成了参数不用再套一层 IFERROR。多列信息当然不用写五遍。C2 到 F2 可以合成一条公式直接返回姓名到手机号这一整片区域XLOOKUP($A2,员工明细!$A$2:$A$5000,员工明细!$B$2:$F$5000,未找到该工号)在支持动态数组的版本里这一条公式写进 C2 就会自动向右溢出把四列结果一次铺开。如果版本不支持溢出选中 C2:F2 再输入公式按 CtrlShiftEnter 作为数组公式确认效果一样只是以后修改时要整片改。XLOOKUP 还有两个进阶用法值得记一下。一个是反向查找VLOOKUP 时代查左边的数据要靠 INDEXMATCH 或者调列序现在只需要把返回区域写到前面去方向完全无所谓。另一个是区间匹配把第五个参数设为 -1 或 1可以做等级判定比如按分数查档位、按金额查折扣率比嵌套 IF 干净得多。XLOOKUP($B2,员工明细!$E$2:$E$5000,员工明细!$B$2:$B$5000,无记录)上面这条是按入职日期反查姓名演示返回区域可以在查找区域左边这件事实际业务里用得不多但理解它有助于你摆脱 VLOOKUP 时代养成的被查列必须在最左边的思维定式。老版本没有 XLOOKUP 怎么办两条路。第一条用 INDEX MATCH 组合INDEX(员工明细!$B$2:$B$5000,MATCH($A2,员工明细!$A$2:$A$5000,0))效果等价只是没有兜底参数外面要再包一层 IFERROR。第二条就是老老实实用 VLOOKUP但要求被查列在返回列的左边且插入新列时列序号会错位维护成本明显更高。IFERROR(INDEX(员工明细!$B$2:$B$5000,MATCH($A2,员工明细!$A$2:$A$5000,0)),未找到该工号)这条是老版本环境下的首选写法虽然长一点但稳定性和兼容性都更好。4.3 FILTER把符合条件的行整片筛出来录入表解决的是单条查询FILTER 解决的是批量筛选。假设部门经理要看销售部的全部人员把部门名写在 H1 单元格里然后在一个空白区域写入FILTER(员工明细!$A$2:$F$5000,员工明细!$C$2:$C$5000$H1,该部门暂无人员)回车之后销售部的所有行会整片铺出来包含工号、姓名、部门、岗位、入职日期、手机号。把 H1 改成技术部结果立刻切换不用重新点筛选、不用重新勾选复选框。这就是动态两个字的价值——数据源改了结果跟着改。多条件筛选也简单条件之间用乘号表示并且用加号表示或者FILTER(员工明细!$A$2:$F$5000,(员工明细!$C$2:$C$5000$H1)*(员工明细!$D$2:$D$5000$I1),无匹配记录)这条公式的意思是部门等于 H1 且岗位等于 I1。注意每个条件都要用括号包起来否则运算优先级会让结果出错。至于或者关系写成(条件1)(条件2)即可返回的是去重后的并集。FILTER 最容易遇到的报错是 #SPILL!原因几乎都是公式下方或右方已经有内容溢出区域被占用。处理方法很简单选中公式单元格下方的区域清空内容公式立刻恢复正常。这一点在别人发来的模板里尤其常见看到 #SPILL! 先在公式周围找找有没有残留的旧数据。老版本没有 FILTER替代方案是数据 → 筛选配合高级筛选或者直接上数据透视表。高级筛选能做条件区域但结果是静态的数据源更新后要重新执行一次数据透视表刷新一下就行但输出的字段布局受限于透视结构。如果你的版本确实太老建议优先用数据透视表比反复点筛选省事。5. TEXTJOIN 与 TEXTSPLIT合并与拆分查得到、筛得出之后接下来的两个动作是把多条结果拼成一句话和把一坨文本拆成多列。这两个动作看起来方向相反实际业务里往往成对出现。5.1 TEXTJOIN把筛选结果拼成一行文本继续用上面那张员工明细表。现在想在 H3 单元格里输出一句话销售部成员张伟、李娜。公式这样写销售部成员TEXTJOIN(、,TRUE,FILTER(员工明细!$B$2:$B$5000,员工明细!$C$2:$C$5000销售部,))三个参数分别是分隔符顿号、是否忽略空值TRUE、要连接的文本区域。中间嵌套的 FILTER 负责先把销售部的姓名筛出来TEXTJOIN 再把它们串成一串。如果没有 TEXTJOIN这件事要么用一长串 硬拼要么写 VBA都很难维护。改成动态部门更实用直接把部门名指向单元格$H1成员TEXTJOIN(、,TRUE,FILTER(员工明细!$B$2:$B$5000,员工明细!$C$2:$C$5000$H1,无人员))这样 H1 换成哪个部门下面这句话就自动变成哪个部门的名单。做周报、做部门通讯录、做群发消息的收件人列表这一条公式能省掉大量复制粘贴。TEXTJOIN 还有一个不那么常见但很好用的场景把一个单元格区域里的多行内容合并成一行。比如别人发来一列地址你想拼成一行用逗号隔开TEXTJOIN(,,TRUE,A2:A100)一条就够不用再一个一个点。TEXTJOIN(,,TRUE,A2:A100)注意第二个参数写 TRUE 表示跳过空单元格如果写成 FALSE区域里的空白会变成连续的逗号输出会很难看。这个参数写错的概率相当高检查公式时优先看这里。老版本没有 TEXTJOIN退而求其次可以用手动连接但列数一多就没法维护也可以用 PHONETIC 函数但它只对文本格式生效数字会被忽略适用面很窄。比较现实的做法是把合并工作放到导出之后用 Python 处理或者升级软件版本。5.2 TEXTSPLIT把混排文本重新拆成列TEXTSPLIT 解决的是反方向的问题别人给你一坨文本你要拆开。比如 A2 单元格里是张伟、李娜、王强要拆成三列TEXTSPLIT(A2,、)回车后三个名字自动占据 B2、C2、D2 三个格子。多个分隔符一起处理也没问题用花括号把分隔符列出来TEXTSPLIT(A2,{、,,,;,})这条公式能同时应对中文顿号、英文逗号、中英文分号处理别人发来的格式不统一的名单时特别省事。再加一个参数可以控制是否忽略连续分隔符产生的空值TEXTSPLIT(A2,、,,TRUE)第四个参数写 TRUE遇到张伟、、李娜这种连续分隔符时不会多出一个空列。如果只需要取分隔符前面或后面的一段用 TEXTBEFORE 和 TEXTAFTER 更直接。比如 A2 里是张伟-销售部-A001想拆成三段TEXTBEFORE(A2,-) TEXTBEFORE(TEXTAFTER(A2,-),-) TEXTAFTER(A2,-,2)第一条取第一个分隔符之前的内容得到张伟第三条从第二个分隔符之后取得到A001中间那条是嵌套写法先取第一个分隔符之后的销售部-A001再取这个结果里第一个分隔符之前的销售部。三条公式组合起来就是完整的三段拆分而且比 TEXTSPLIT 更适合只取其中某一段的场景。实际工作中更常见的是带标签的混排文本比如姓名张伟手机号13800000001部门销售部。这种用 TEXTBEFORE / TEXTAFTER 按标签切最稳TEXTBEFORE(TEXTAFTER(A2,姓名),) TEXTAFTER(A2,手机号)第一条先定位到姓名之后再取到下一个逗号之前第二条直接取手机号之后的全部内容。这种写法不依赖固定位置文本顺序变了也能取对比 LEFT/MID 按字符数截取可靠得多。老版本没有这几个函数替代方案是数据 → 分列按分隔符拆缺点是拆完之后数据源变了要重新拆一次不是动态的。另一个替代是快速填充 CtrlE手动敲出第一行的结果让 Excel 猜规律适合格式高度一致的场景但规律一复杂就容易猜错谨慎使用。6. 组合实战一套自动录入模板单个函数会用之后真正省时间的是把它们串起来。这一节拼一个完整的小模板目标是输入工号 → 自动带出全部信息 → 自动生成部门人员名单 → 自动把多值单元格拆成明细。第一步录入表 A2 输入工号B2 到 F2 用前面那条 XLOOKUP 多列公式一次性带出姓名、部门、岗位、入职日期、手机号。这一层解决不用手动敲。第二步在录入表下方做一个部门汇总区。H1 填部门名H2 输出名单文本H3 输出人数COUNTA(FILTER(员工明细!$B$2:$B$5000,员工明细!$C$2:$C$5000$H1,))这一层解决不用临时找数据。COUNTA 统计 FILTER 返回的非空单元格数量就是该部门的人数FILTER 第三参数写空字符串避免没匹配到时 COUNTA 把错误值也数进去。第三步做一块批量拆分区。假设 J 列是别人发来的一列混排字符串每条里包含多个用顿号隔开的标签想把每条拆成多行。这一步在纯公式里做比较绕实际更推荐两条路一条是用 TEXTSPLIT 拆成多列横向适合后续按列处理另一条是用 Power Query 的按分隔符拆分到行适合拆完之后继续做透视统计。TEXTSPLIT(J2,、)横向拆是最省事的做法一行拆成几列就用几个辅助列不需要写数组公式。如果一定要纵向拆成多行新版可以用TOCOL配合但函数组合会明显变复杂日常业务里性价比不高。第四步给模板加保护。选中明细表右键设置单元格格式 → 保护 → 锁定再在审阅 → 保护工作表里设密码录入表保持可编辑。这样交出去之后别人只能填工号改不动底层数据公式也不会被误删。做完这一步一套能给同事直接用的录入模板就算完工了。7. 批量任务与性能观察公式做完了还得知道它跑得快不快、什么时候会撑不住。这一节讲观察方法和批量处理思路。7.1 什么时候该换工具判断标准很简单如果改一个单元格之后状态栏的计算提示要转好几秒才消失说明公式量已经过大。常见原因是整列引用$A:$A叠加数组公式或者同一片区域被几十条公式反复引用。优化顺序是先把整列引用改成固定范围再看能不能用辅助列把重复计算的部分先算一次最后考虑把明细表压缩成只保留必要字段。另一个观察点是文件体积。XLOOKUP 和 FILTER 本身不占空间但如果公式里引用了整列Excel 会把整列纳入计算范围文件会莫名变大。做完模板后看一眼文件大小如果几十 KB 的数据变成好几 MB多半是引用范围写太宽了。7.2 用 VBA 做批量填充公式是逐行拖的如果明细表有几千行、几十列都要填公式手动拖也烦。这时候可以用一段很短的宏一次性铺完Sub FillFormulas() Dim ws As Worksheet Dim lastRow As Long Set ws ThisWorkbook.Sheets(员工明细) lastRow ws.Cells(ws.Rows.Count, A).End(xlUp).Row With ThisWorkbook.Sheets(录入表) .Range(B2:B lastRow).Formula _ XLOOKUP($A2,员工明细!$A$2:$A$5000,员工明细!$B$2:$B$5000,未找到该工号) End With MsgBox 已填充到第 lastRow 行, vbInformation End Sub这段宏做的事情很朴素算出明细表最后一行然后把公式一次性写进整片区域。注意公式里的双引号在 VBA 字符串中要写成两个双引号转义这是最容易写错的地方。运行前先备份文件宏是不可撤销操作。7.3 用 Python 做同一件事如果数据量大到 Excel 已经不好受可以把这一步搬到 Python 里。pandas 处理这类查找匹配几乎是一行的事import pandas as pd detail pd.read_excel(员工明细.xlsx, sheet_name员工明细) input_df pd.read_excel(录入表.xlsx, sheet_name录入表) result input_df.merge( detail[[工号, 姓名, 部门, 岗位]], on工号, howleft ) result[姓名] result[姓名].fillna(未找到该工号) result.to_excel(录入结果.xlsx, indexFalse) print(f处理完成共 {len(result)} 行)这段脚本对应 XLOOKUP 的逻辑以录入表的工号为主键去明细表里做左连接。没匹配上的行会自动变成空值再用 fillna 兜底成未找到该工号。十万行级别的数据这个脚本通常几秒就能跑完比在 Excel 里等重算要稳。8. 常见问题与排查方法公式模板出问题九成集中在下面这几种现象里。遇到报错先查表比乱改公式效率高。问题现象可能原因排查方式解决方案公式报 #NAME?当前版本不支持该函数单独敲函数名看有无提示换老版本写法或升级软件公式报 #SPILL!溢出区域已有内容看公式右下方有无数据清空溢出区域明明有数据却匹配不上文本型数字与数值型不一致检查单元格左上角绿色小三角用 VALUE 或 TEXT 统一格式结果显示 #N/A查找区域确实没有该值用 CtrlF 在明细表里搜一遍给 XLOOKUP 加第四参数兜底FILTER 返回空白没写第三参数且无匹配临时把条件改成必然成立的值测试补上第三参数TEXTJOIN 输出多余逗号第二参数写成了 FALSE检查参数顺序改成 TRUE 跳过空值TEXTSPLIT 拆出的列数不对分隔符中英文混用逐个检查原文分隔符用花括号列出多种分隔符改了数据但公式不更新计算选项被设为手动看公式选项卡里的计算选项切换为自动重算模板发给别人后公式全错工作表名被改过检查公式里的表名引用统一表名或用命名区域打开文件非常卡大量整列引用叠加数组公式观察状态栏计算耗时改成固定范围引用表格里最值得展开的是文本型数字这一条。它表现为你肉眼看到工号 A001 在两张表里都有但就是匹配不上或者明明两边都是 13800000001 却查不到。原因是其中一边被存成了文本通常是从系统导出时带过来的另一边是数值。解决办法是用--或者 VALUE 函数转一下或者在 XLOOKUP 里把查找值包一层 TEXT。XLOOKUP(TEXT($A2,),员工明细!$A$2:$A$5000,员工明细!$B$2:$B$5000,未找到该工号)把查找值统一按文本处理两边就对齐了。反过来如果明细表是数值而录入是文本就把TEXT(...,)换成VALUE(...)注意 VALUE 遇到带字母的工号会报错所以工号这类字段建议全表统一存成文本。另一类高频问题是公式复制到别的行就错位。这几乎都是绝对引用没写对$A2锁列不锁行、A$2锁行不锁列含义完全不同。复制公式前先想清楚哪些部分应该跟着行变化哪些必须固定不动。这个习惯养成之后模板的复用率会高一大截。9. 最佳实践与下一步把上面这些拼起来一套能长期用的模板大概有这么几个特征。第一数据层和展示层分开。明细数据放一张表录入表、汇总表、看板各放一张公式只从明细表取数绝不在明细表里写公式。这样明细表可以整表替换展示层不用动。第二区域引用全部写成固定范围加绝对引用命名区域是加分项员工明细!$A$2:$A$5000这种长串改成工号列会好读很多。第三先小范围验证再铺开。写一条公式拿三五行数据验证正确性确认没问题再往下拖几千行。反过来先拖满再检查一旦逻辑错了就是几千个错误值排查成本翻好几倍。第四模板交付前做一次破坏性测试故意输入不存在的工号、故意留空、故意把部门名改错看公式是不是都能给出可读的提示而不是甩一堆 #N/A 和 #VALUE!。第五涉及手机号、身份证号、薪资这类字段模板传阅前先脱敏明细表加保护导出的文件走内部渠道。技术上的便利不能替代数据安全意识。如果这 4 个函数已经用熟了下一步可以往三个方向走一是学 Power Query它能把每月导一次表、清洗一遍、合并一次的整套动作做成可刷新的流程比公式更适合处理重复性的数据准备二是学数据透视表配合切片器做交互式看板比堆公式省力三是把稳定的处理逻辑搬到 Python用脚本定时跑彻底摆脱手工打开文件的动作。回到最初那个对比。真正拉开差距的不是记住了几个函数名而是遇到重复劳动时第一反应是这段能不能交给公式而不是今晚加个班弄完。XLOOKUP 解决查得到FILTER 解决筛得出TEXTJOIN 解决合得起来TEXTSPLIT 解决拆得开四个动作串起来一张录入表就能自己长出来。建议先拿手头最烦的那张表练一遍从 XLOOKUP 开始一条公式跑通剩下的都是水到渠成。