使用Google Sheets/Excel对比两组库存数据排查差异

核心逻辑 #

先通过SKU确认商品在两组数据中都存在，再同时对比价格和库存数量：

• 两者完全一致：单元格标绿色
• 任一不一致：单元格标红色
• SKU仅在会计软件中存在：不做格式标记（可自行调整）

条件格式设置步骤（基于已创建的命名区域） #

假设你已命名的区域：

• 网店数据：Shop_SKU（SKU列）、Shop_Price（价格列）、Shop_Stock（库存列）
• 会计软件数据：Account_SKU（SKU列）、Account_Price（价格列）、Account_Stock（库存列）

1. 处理会计软件的价格列 #

选中会计软件价格列的所有数据单元格（对应Account_Price区域），打开「条件格式」→「新建规则」→选择「使用公式确定要设置格式的单元格」：

• 绿色规则：输入公式，设置填充色为绿色

  =AND(COUNTIF(Shop_SKU, $A2)>0, $B2=VLOOKUP($A2, Shop_SKU:Shop_Price, 2, FALSE))
  

  （$A2是当前行的SKU单元格，$B2是当前行的价格单元格，需根据你的实际列位调整；Shop_SKU:Shop_Price是网店SKU到价格的连续区域）
• 红色规则：再新建一个规则，输入公式，设置填充色为红色

  =AND(COUNTIF(Shop_SKU, $A2)>0, $B2<>VLOOKUP($A2, Shop_SKU:Shop_Price, 2, FALSE))
  

2. 处理会计软件的库存列 #

选中会计软件库存列的所有数据单元格（对应Account_Stock区域），重复新建规则操作：

• 绿色规则公式：

  =AND(COUNTIF(Shop_SKU, $A2)>0, $C2=VLOOKUP($A2, Shop_SKU:Shop_Stock, 3, FALSE))
  

  （$C2是当前行的库存单元格）
• 红色规则公式：

  =AND(COUNTIF(Shop_SKU, $A2)>0, $C2<>VLOOKUP($A2, Shop_SKU:Shop_Stock, 3, FALSE))
  

3. （可选）反向标记网店数据 #

如果需要在网店数据中也标注和会计软件不一致的项，把公式里的命名区域互换即可。比如网店价格列的绿色规则：

=AND(COUNTIF(Account_SKU, $A2)>0, $B2=VLOOKUP($A2, Account_SKU:Account_Price, 2, FALSE))

关键注意点 #

• 确保SKU格式完全一致（无多余空格、大小写统一），否则匹配会失败，可先用TRIM($A2)清理SKU再匹配
• 若用Excel 365/2021，推荐用XLOOKUP替代VLOOKUP，稳定性更强：XLOOKUP($A2, Shop_SKU, Shop_Price, "")
• 公式里的单元格引用要注意绝对/相对引用（比如$A2锁定列，行随单元格自动变化）

内容的提问来源于stack exchange，提问作者Genmai