Excel两张表怎么核对差异?金额、数量对账方法(WPS适用)
- 2026-11-09 15:55:59
供应商发来一张明细，系统里导出另一张。两边的行数不同，顺序也不同，你想知道：哪些编号少了记录，哪些数量或金额对不上？
这时别直接拿两张表的同行相减。先按同一编号汇总，再比较数量和金额，最后检查双方是否都有记录。
尤其要注意重复编号：同一个编号在一张表里拆成两行，在另一张表里合成一行，是常见的数据结构差异。单次查找返回一个匹配项，不能替你把全部明细加起来。
下面用一组虚构数据，做一张可以逐项检查的核对表。Excel 和 WPS 都能按这个思路操作。
一、对账前，先统一“按什么核对”
本文核对的是相同结算编号下，数量和金额的合计，不是逐笔明细完全一致。
先确认两边是同一期间、同一币种、同一金额含税口径，数量单位和退货正负号也一致。如果必须区分“编号＋供应商＋月份”，只用编号就不够，需要按这些字段共同分组，不能直接套用本文单条件示例。
也不要随手删掉编号后缀。D001 与 D001-1 能否归为同一笔，必须先确认业务含义。本文的编号均为不含通配符的短文本。
二、把原始数据放进同一个工作簿
新建两个工作表，分别命名为 S1 和 S2。S1代表甲表，S2代表乙表，英文表名是为了让下面的公式便于原样复制。
两张表的 A1、B1、C1 都写“编号、数量、金额”。S1 数据在 A2:C6，S2 数据在 A2:C4；数量与金额须为真正数值，不是文本数字。
S1里的 D001 分两行，数量分别为3和2，金额分别为150和100；S2里的 D001 只有一行，数量5、金额250。两边汇总后其实一致。
VLOOKUP 精确匹配遇到重复编号时只返回首个匹配项，XLOOKUP 默认也从首个匹配项返回，不能把单次返回值当成该编号的合计。这里要做的是条件汇总。
三、先做完整编号清单，别漏掉乙表独有项
新建第三张工作表 Check。把 S1、S2 的编号列依次复制到 Check 的 A 列，去掉表头和空白，然后用“数据”里的删除重复项/删除重复值功能，仅对这份编号清单去重。
不要对原始明细去重。重复编号可能是合法分批记录，删掉会改变合计。原表各自保留，核对清单才只留每个编号一次。
本例 Check 的 A2:A6 依次为 D001、D002、D003、D004、D005。必须取两张表编号的并集；只从 S1 建清单，就会漏掉 S2 独有的 D004。
Check 的 B1:I1 依次写：甲数量、乙数量、数量差、甲金额、乙金额、金额差、甲记录数、乙记录数。
四、分别汇总数量，再计算数量差
第1步：在 Check 的 B2 输入，汇总 S1 中与 A2 编号一致的数量：
=SUMIF(S1!$A$2:$A$6,A2,S1!$B$2:$B$6)第2步：在 C2 输入，汇总 S2 的数量：
=SUMIF(S2!$A$2:$A$4,A2,S2!$B$2:$B$4)第3步：在 D2 输入数量差。本文统一使用“甲减乙”：
=B2-C2本例 D001 在 B2、C2 都得到5，数量差为0。SUMIF 的三个参数依次是：编号区域、当前编号、要汇总的数量区域。
公式中的 $ 锁住原始区域；向下填充时，A2会变成A3、A4，源数据区域不移动。
五、金额同样汇总，精度按业务规则处理
在 E2 输入甲金额：
=SUMIF(S1!$A$2:$A$6,A2,S1!$C$2:$C$6)在 F2 输入乙金额：
=SUMIF(S2!$A$2:$A$4,A2,S2!$C$2:$C$4)本例按“汇总差额保留2位小数”检查，在 G2 输入：
=ROUND(E2-F2,2)如果你的业务要求逐笔金额先舍入、再汇总，或保留更多小数，应按约定调整原始金额与核对规则。这里只演示本例口径；数量差不额外舍入。
差额为正，表示甲合计更大；为负，表示乙合计更大。显示两位小数与真正舍入计算不是同一个动作。
六、合计为0，不等于“找到了且一致”
SUMIF 没有匹配记录时也会返回0，而有匹配记录、正负抵消后也可能为0。要区分这两种情况，再统计双方记录数。
H2检查甲表有多少条匹配记录：
=COUNTIF(S1!$A$2:$A$6,A2)I2检查乙表：
=COUNTIF(S2!$A$2:$A$4,A2)将 B2:I2 向下填充到第6行。再选中 Check 的 A1:I6，打开筛选，分别检查 D列数量差、G列金额差不为0的记录，以及 H列或I列记录数为0的记录。
这些是不同检查条件，分别筛选并记录结果；不要同时把全部非零条件都加上，否则可能只剩交集，漏掉“只有金额不一致”等情况。
七、用这组结果，核对你有没有算对
D001：数量5对5、金额250对250，甲有2条记录、乙有1条。合计一致，不代表逐笔明细完全一致。
D002：数量4对3，差1；金额320对240，差80，需要回查明细。
D003、D005：仅甲表有记录；D004：仅乙表有记录。这些编号都应进入待核查清单。
即使全表总差额为0，也可能存在不同编号之间的正负差额抵消，因此不能只看总计判断对账完成。
八、应用到自己的表，最后检查这些前提
范围要完整：示例只到第6行和第4行，新增明细后必须同步扩大每个对应范围，保持编号区域与求和区域等长且逐行对应。
类型要正确：编号按同一文本规则处理，检查多余空格和前导零；数量、金额中的文本值可能被 SUMIF 忽略，不要把漏加误判为真实差额。
特殊编号要另查：SUMIF、COUNTIF 的条件支持 *、? 通配符，~ 可用于转义，且这些文本匹配通常不区分大小写。编号含这类符号、需严格区分大小写或属于超长文本时，不能直接把本例当成严格匹配方案。
本文用到的 SUMIF、COUNTIF、ROUND 适用于 Windows 桌面版 Excel 2016、2019、2021、2024 和 Microsoft 365；WPS Office 2019 PC 版官方资料也列有对应功能。本文不依赖 XLOOKUP、动态数组或 Power Query；不同版本的按钮名称以实际界面为准，手机与网页版不直接套用桌面路径。
最后记住：统一口径、合并编号清单、双方分别汇总、检查差额与缺失，再回查明细。
