做订单核对时,最费时间的往往不是查单个编号,而是找出哪些订单根本不存在、哪些编号录入了两遍。本文以 WPS 表格中的“订单表”和“待核对表”为例,演示精确匹配、缺失标色和重复检查,公式可直接复制使用。
适用设备与系统版本
本文以 Windows 10/11 的 WPS Office 2019/2021/365 和当前 macOS 版为主,具备基础函数和条件格式即可。手机端可查看和计算:以小米/Redmi HyperOS 为例,进入系统“文件管理”→“内部存储”→点开 .xlsx 文件→“打开”→选择“WPS 表格”;iPhone 可在“文件”App 中将表格共享给 WPS。华为鸿蒙、OPPO/一加、vivo/iQOO、荣耀的入口名称可能不同,批量标色建议在电脑完成。
一、准备订单表和待核对表
- 建立两张表。订单表使用 A~D 列:A 为订单编号、B 为客户、C 为商品、D 为金额;待核对表使用 F~I 列:F 为订单编号,G~I 用于填写查到的客户、商品和金额。表头不要合并单元格。
- 将 A 列和 F 列的订单编号设为“文本”。选中列后按
Ctrl+1,在“数字”选项中选择“文本”,再录入或粘贴编号,避免长数字变成科学计数法、丢失前导零。已经丢失的前导零通常无法自动恢复,应回原系统核对。 - 删除编号前后的空格,并统一两张表的编号类型。示例数据到第 101 行,下面的
$A$2:$D$101可替换成实际最后一行。
二、用 VLOOKUP 精确匹配订单
-
在待核对表 G2 输入:
=IF($F2="","",IFERROR(VLOOKUP($F2,$A$2:$D$101,2,FALSE),"未找到"))回车后,G2 会显示订单表中的客户名称;编号不存在时显示“未找到”。
-
在 H2、I2 分别输入:
=IF($F2="","",IFERROR(VLOOKUP($F2,$A$2:$D$101,3,FALSE),"未找到"))=IF($F2="","",IFERROR(VLOOKUP($F2,$A$2:$D$101,4,FALSE),"未找到"))两条公式分别返回商品和金额。
-
选中 G2:I101,按
Ctrl+D向下填充。参数中的2、3、4表示返回查询区域第几列;FALSE表示精确匹配,不能随意省略。$A$2:$D$101是固定查询范围,$F2则会随行号变化。
三、用 MATCH 配合 INDEX 查数据
-
MATCH 单独使用只能找到编号的位置。在 J2 输入:
=IFERROR(MATCH($F2,$A$2:$A$101,0),"未找到")返回结果 1 代表查询区域中的第一个位置,也就是工作表 A2,并非工作表第 1 行。
-
如果客户名称不在订单表首列,或希望结构更直观,可用 MATCH 配合 INDEX。在 G2 输入:
=IF($F2="","",IFERROR(INDEX($B$2:$B$101,MATCH($F2,$A$2:$A$101,0)),"未找到"))MATCH 负责找位置,INDEX 负责返回该位置的客户名称。查询其他字段时,只需替换 INDEX 的返回区域。
-
若还要区分“编号不存在”和“订单存在但客户栏为空”,可把 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 列区域即可。
四、把缺失编号和缺失内容标出来
-
选中待核对表的 F2:F101,进入“开始”→“条件格式”→“新建规则”,选择“使用公式确定要格式化的单元格”,有的 WPS 版本显示为“仅对公式的值进行格式化”。输入:
=AND($F2<>"",ISNA(MATCH($F2,$A$2:$A$101,0))) -
在格式设置中选择红色底纹或红色字体,再应用到 F2:F101。这样,订单表中不存在的编号会立即标红。
-
对 G:I 列使用
=G2="未找到"可把查不到的查询结果标红;若使用上一节的“未填写”公式,则用=AND($G2<>"",$G2="未填写")标成橙色。 -
如果找不到条件格式入口,可在 J2 建立备用状态列:
=IF($F2="","待录入",IF(ISNA(MATCH($F2,$A$2:$A$101,0)),"编号缺失",IF(COUNTIF($A$2:$A$101,$F2)>1,"源表编号重复","正常")))填充到 J101 后,选中 F1:J101,在“数据”→“筛选”中查看“编号缺失”。
五、查出重复订单编号
-
在订单表 E1 输入“重复检查”,E2 输入:
=IF(COUNTIF($A$2:$A$101,A2)>1,"重复","")填充到 E101 后,所有出现两次以上的编号都会标为“重复”。
-
选中 A1:E101,进入“数据”→“筛选”或“自动筛选”,点击 E1 的下拉箭头,只保留“重复”,即可查看重复编号。
-
如果只想标出第二次及以后出现的记录,E2 可改用:
=IF(COUNTIF($A$2:A2,A2)>1,"重复","") -
待核对表也要检查时,可在 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 则能快速发现重复。正式处理前保留原表备份,并先确认重复订单是否属于正常的明细记录。






