办公软件

WPS 表格按编号查订单:VLOOKUP、MATCH 匹配及缺失/重复检查

约 7 分钟读完
WPS 表格按编号查订单:VLOOKUP、MATCH 匹配及缺失/重复检查

摘要

本文用订单表与待核对表演示 WPS 表格的完整核对流程:VLOOKUP 精确查订单,MATCH 配合 INDEX 处理非首列编号,条件格式标记缺失记录,COUNTIF 找出重复编号,并附可直接复制的公式、标色步骤与常见注意事项。

本文目录

做订单核对时,最费时间的往往不是查单个编号,而是找出哪些订单根本不存在、哪些编号录入了两遍。本文以 WPS 表格中的“订单表”和“待核对表”为例,演示精确匹配、缺失标色和重复检查,公式可直接复制使用。

适用设备与系统版本

本文以 Windows 10/11 的 WPS Office 2019/2021/365 和当前 macOS 版为主,具备基础函数和条件格式即可。手机端可查看和计算:以小米/Redmi HyperOS 为例,进入系统“文件管理”→“内部存储”→点开 .xlsx 文件→“打开”→选择“WPS 表格”;iPhone 可在“文件”App 中将表格共享给 WPS。华为鸿蒙、OPPO/一加、vivo/iQOO、荣耀的入口名称可能不同,批量标色建议在电脑完成。

一、准备订单表和待核对表

  1. 建立两张表。订单表使用 A~D 列:A 为订单编号、B 为客户、C 为商品、D 为金额;待核对表使用 F~I 列:F 为订单编号,G~I 用于填写查到的客户、商品和金额。表头不要合并单元格。
  2. 将 A 列和 F 列的订单编号设为“文本”。选中列后按 Ctrl+1,在“数字”选项中选择“文本”,再录入或粘贴编号,避免长数字变成科学计数法、丢失前导零。已经丢失的前导零通常无法自动恢复,应回原系统核对。
  3. 删除编号前后的空格,并统一两张表的编号类型。示例数据到第 101 行,下面的 $A$2:$D$101 可替换成实际最后一行。

二、用 VLOOKUP 精确匹配订单

  1. 在待核对表 G2 输入:

    =IF($F2="","",IFERROR(VLOOKUP($F2,$A$2:$D$101,2,FALSE),"未找到"))

    回车后,G2 会显示订单表中的客户名称;编号不存在时显示“未找到”。

  2. 在 H2、I2 分别输入:

    =IF($F2="","",IFERROR(VLOOKUP($F2,$A$2:$D$101,3,FALSE),"未找到"))

    =IF($F2="","",IFERROR(VLOOKUP($F2,$A$2:$D$101,4,FALSE),"未找到"))

    两条公式分别返回商品和金额。

  3. 选中 G2:I101,按 Ctrl+D 向下填充。参数中的 2、3、4 表示返回查询区域第几列;FALSE 表示精确匹配,不能随意省略。$A$2:$D$101 是固定查询范围,$F2 则会随行号变化。

三、用 MATCH 配合 INDEX 查数据

  1. MATCH 单独使用只能找到编号的位置。在 J2 输入:

    =IFERROR(MATCH($F2,$A$2:$A$101,0),"未找到")

    返回结果 1 代表查询区域中的第一个位置,也就是工作表 A2,并非工作表第 1 行。

  2. 如果客户名称不在订单表首列,或希望结构更直观,可用 MATCH 配合 INDEX。在 G2 输入:

    =IF($F2="","",IFERROR(INDEX($B$2:$B$101,MATCH($F2,$A$2:$A$101,0)),"未找到"))

    MATCH 负责找位置,INDEX 负责返回该位置的客户名称。查询其他字段时,只需替换 INDEX 的返回区域。

  3. 若还要区分“编号不存在”和“订单存在但客户栏为空”,可把 G2 改为:

    =IF($F2="","",IFERROR(IF(INDEX($B$2:$B$101,MATCH($F2,$A$2:$A$101,0))="","未填写",INDEX($B$2:$B$101,MATCH($F2,$A$2:$A$101,0))),"未找到"))

    查找其他字段时,同步替换 B 列区域即可。

四、把缺失编号和缺失内容标出来

  1. 选中待核对表的 F2:F101,进入“开始”→“条件格式”→“新建规则”,选择“使用公式确定要格式化的单元格”,有的 WPS 版本显示为“仅对公式的值进行格式化”。输入:

    =AND($F2<>"",ISNA(MATCH($F2,$A$2:$A$101,0)))

  2. 在格式设置中选择红色底纹或红色字体,再应用到 F2:F101。这样,订单表中不存在的编号会立即标红。

  3. 对 G:I 列使用 =G2="未找到" 可把查不到的查询结果标红;若使用上一节的“未填写”公式,则用 =AND($G2<>"",$G2="未填写") 标成橙色。

  4. 如果找不到条件格式入口,可在 J2 建立备用状态列:

    =IF($F2="","待录入",IF(ISNA(MATCH($F2,$A$2:$A$101,0)),"编号缺失",IF(COUNTIF($A$2:$A$101,$F2)>1,"源表编号重复","正常")))

    填充到 J101 后,选中 F1:J101,在“数据”→“筛选”中查看“编号缺失”。

五、查出重复订单编号

  1. 在订单表 E1 输入“重复检查”,E2 输入:

    =IF(COUNTIF($A$2:$A$101,A2)>1,"重复","")

    填充到 E101 后,所有出现两次以上的编号都会标为“重复”。

  2. 选中 A1:E101,进入“数据”→“筛选”或“自动筛选”,点击 E1 的下拉箭头,只保留“重复”,即可查看重复编号。

  3. 如果只想标出第二次及以后出现的记录,E2 可改用:

    =IF(COUNTIF($A$2:A2,A2)>1,"重复","")

  4. 待核对表也要检查时,可在 K2 输入:

    =IF(COUNTIF($F$2:$F$101,F2)>1,"待核对表重复","")

    同一订单出现多行不一定是错误,可能对应多个商品。确认重复原因前不要直接删除记录;确实需要合计金额时,可用 =SUMIFS($D$2:$D$101,$A$2:$A$101,F2)。

注意事项

  • 订单编号尽量只用数字、字母和短横线,并统一按文本保存;前导零和超长数字尤其需要检查。
  • VLOOKUP 要求查询键位于查询区域首列,精确查找时保留 FALSE;需要从任意列查找时,优先使用 MATCH 配合 INDEX。
  • 源表编号重复时,VLOOKUP 和 MATCH 都只会返回第一个匹配结果,应先处理重复,再核对金额或数量。
  • 若公式被当作文字显示,请把单元格格式设为“常规”后重新输入首条公式,再向下填充。

小结

实际核对可按“整理编号→精确查询→标出缺失→筛选重复”的顺序完成。VLOOKUP 适合首列查询,MATCH 配合 INDEX 更灵活,COUNTIF 则能快速发现重复。正式处理前保留原表备份,并先确认重复订单是否属于正常的明细记录。

常见问题

VLOOKUP 返回 #N/A 或查不到数据怎么办?

先检查订单编号是否前后有空格、两张表的编号是否都按文本保存,再确认查询区域包含该行,并保留 FALSE 进行精确匹配。外层加入 IFERROR 后,可以把错误统一显示为“未找到”。

MATCH 和 VLOOKUP 应该怎么选?

VLOOKUP 要求订单编号位于查询区域第一列,使用简单;MATCH 只返回位置,通常要配合 INDEX 取值,但查询键不在首列时更方便,也能避免因插入列导致的列序号错位。

发现订单编号重复后,可以直接删掉吗?

不要立即删除。同一订单可能对应多个商品或多条明细,应先查看客户、商品、金额等字段;确认是重复录入后,再保留正确记录,或使用 SUMIFS 按订单编号汇总金额。

参考来源