办公软件

Excel/WPS 表格按编号匹配资料:XLOOKUP、VLOOKUP 与缺失值处理

约 6 分钟读完
Excel/WPS 表格按编号匹配资料:XLOOKUP、VLOOKUP 与缺失值处理

摘要

编号匹配是人员、订单、库存表最常见的整理操作。本文以 Excel 和 WPS 表格为例,演示如何整理编号、用 XLOOKUP 或 VLOOKUP 批量带出资料,并通过 IFNA 与自定义提示处理找不到、空值和重复编号,附上可直接改写的公式。

本文目录

简要引言

处理人员名单、订单明细或库存资料时,经常会遇到两张表:一张有待补充的编号,另一张有完整资料。编号一致时,可以用查找函数自动带出姓名、部门、状态等信息,不必反复复制粘贴。Excel 和 WPS 表格都支持这类操作,主要区别在于是否使用 XLOOKUP,以及缺失资料如何显示。

适用设备与系统版本

  1. 推荐在 Windows 或 macOS 电脑端使用最新版 Excel、Microsoft 365 或 WPS 表格。Excel 2021、Microsoft 365 支持 XLOOKUP;较新的 WPS 桌面版通常也已加入该函数,但不同版本和平台可能存在差异。
  2. 如果在 WPS 中输入 =XLOOKUP( 没有函数提示,或运行后出现 #NAME?,先通过 WPS 官方更新;仍不可用时,直接使用本文的 VLOOKUP 方案。手机版适合查看和少量补录,大批量匹配建议在电脑端完成。
  3. 下列公式使用英文函数名和英文逗号。若公式报语法错误,可检查 Excel 或 WPS 的公式分隔符设置,必要时把英文逗号改为分号。

一、先把两张表整理成可匹配的结构

  1. 建立“待匹配”和“基础资料”两个工作表,并使用一致的列布局。

    工作表 列布局
    待匹配 A列编号、B列姓名、C列部门
    基础资料 A列编号、B列姓名、C列部门

    示例数据可设为:待匹配表中有编号 1001、1002;基础资料表中对应 1001、张伟、财务,以及 1002、李娜、运营。

  2. 两边的编号必须使用同一种数据格式。带前导零的编号,如 00123,应先把单元格设为“文本”再录入;已经丢失的前导零应从原始资料重新导入,不要用公式随意补零。

  3. 基础资料中的编号应尽量唯一。如果编号重复,XLOOKUP 和 VLOOKUP 都会返回从上往下遇到的第一个结果,可能不是业务上想要的那一条。

二、用 XLOOKUP 按编号批量匹配

  1. 在“待匹配”表的 B2 单元格输入:

    =IFNA(XLOOKUP(A2,'基础资料'!$A$2:$A$1000,'基础资料'!$B$2:$B$1000,"未找到"),"未找到")

  2. 这条公式中,A2 是待查编号;'基础资料'!$A$2:$A$1000 是编号所在列;B 列是要带出的姓名;第四个参数 "未找到" 是找不到编号时的显示内容;外层 IFNA 继续处理其他 #N/A 错误。

  3. 如果还要带出部门,只需把返回区域改为 C 列:

    =IFNA(XLOOKUP(A2,'基础资料'!$A$2:$A$1000,'基础资料'!$C$2:$C$1000,"未找到"),"未找到")

  4. 选中 B2,拖动右下角填充柄向下填充,公式中的 $ 会锁定资料范围。XLOOKUP 默认按精确方式匹配,因此不需要先按编号排序;同时,它不要求编号必须位于资料表第一列。

三、用 VLOOKUP 兼容更多版本

  1. 如果 WPS 版本不支持 XLOOKUP,可在 B2 使用:

    =IFNA(VLOOKUP(A2,'基础资料'!$A$2:$C$1000,2,FALSE),"未找到")

  2. 第三个参数 2 表示返回查找区域中的第 2 列,也就是姓名;返回部门时改成 3。这个序号是从查找区域最左侧的 A 列开始计算,不是从工作表 A 列开始计算。

  3. 第四个参数必须写 FALSE,表示按编号精确匹配。不要省略它或写成 TRUE,否则可能返回编号相近但不相同的资料。

  4. VLOOKUP 有一个重要限制:查找编号必须位于资料区域的第一列。如果编号在 B 列、目标资料在 C 列,可以调整“基础资料”的列顺序,或改用 XLOOKUP。

四、处理找不到、空值和其他错误

  1. 如果当前行本身没有编号,可避免显示多余的“未找到”:

    =IF(A2="","",IFNA(XLOOKUP(A2,'基础资料'!$A$2:$A$1000,'基础资料'!$B$2:$B$1000,"未找到"),"未找到"))

  2. "未找到" 可以换成“编号不存在”“待补充”等文字。若希望找不到时保持空白,可使用:

    =IFNA(XLOOKUP(A2,'基础资料'!$A$2:$A$1000,'基础资料'!$B$2:$B$1000,""),"")

  3. “编号不存在”和“编号存在但目标字段为空”是两种情况。后者建议先在基础资料中把空字段明确填写为“资料为空”;某些表格版本会把空单元格结果显示为 0,但 0 也可能是合法内容,不宜统一批量替换。

  4. 优先使用 IFNA,因为它只处理 #N/A。IFERROR 会连同 #REF!、#VALUE! 等错误一起隐藏,容易掩盖列号写错、区域选错等真实问题。

五、批量填充后检查结果

  1. 填充完成后,先抽查几行,再统计 B 列中显示“未找到”的数量:

    =COUNTIF(B2:B1000,"未找到")

  2. 为发现基础资料中的重复编号,可在辅助列输入并向下填充:

    =IF(COUNTIF('基础资料'!$A$2:$A$1000,A2)>1,"编号重复","")

  3. 核对无误后,再复制结果列并选择性粘贴为“数值”,避免后续修改基础资料时公式结果跟着变化。原始表格建议先保留备份。

注意事项

  • 编号前后若有空格,可用 =TRIM(A2) 建立辅助列,再对基础资料的编号列做同样清理;清理后将辅助列作为新的查找列。
  • 数字型编号和文本型编号不要混用。复制资料后,先检查是否存在格式不一致或长数字显示为科学计数法。
  • 复制公式时保留 $,否则向下填充后查找范围可能发生偏移。工作表名含空格或特殊符号时,应使用单引号包住名称。
  • 正式批量处理前保留原始文件。若出现 #NAME?,先确认函数版本;若出现 #N/A,重点检查编号类型、空格和重复问题。

小结

新版本 Excel 或支持 XLOOKUP 的 WPS,优先使用 XLOOKUP,它既精确又不受“编号必须在第一列”的限制;旧版 WPS 或需要兼容其他表格软件时,使用带 FALSE 参数的 VLOOKUP。再配合 IFNA、明确的缺失提示和重复编号检查,就能完成稳定、可核验的批量资料匹配。

常见问题

WPS 表格里提示 XLOOKUP 无效或出现 #NAME? 怎么办?

先确认 WPS 表格版本并从官方更新。仍不支持时,把公式中的 XLOOKUP 改成文中的 VLOOKUP,并注意返回列序号和 FALSE 参数。

为什么 VLOOKUP 明明有编号,却还是提示找不到?

常见原因是编号前后有空格、一边是数字而另一边是文本,或者使用了 TRUE 进行近似匹配。清理空格、统一编号格式,并使用 FALSE 精确匹配。

基础资料里出现重复编号,会返回哪一条?

XLOOKUP 和精确匹配的 VLOOKUP 通常都返回从上往下找到的第一条。建议先用 COUNTIF 找出重复编号,清理后再进行正式匹配。

参考来源