Excel 奇招：三种公式核对两列不同的数据

在当今快节奏的工作与生活中，高效处理数据成为了许多人的迫切需求。今天，我们将为你揭秘 2 个至关重要的 Excel 数据核对公式，即使你再忙碌，也绝对不能错过这些能够极大提升工作效率和数据准确性的法宝。

如下图所示：左侧是excel里面登记的库存，右侧是实际盘点库存，需要用公式判断库存是否一致。

方法一：在单元格输入公式：=IF(COUNTIFS($B$4:$B$100,F4,$C$4:$C$100,G4,$D$4:$D$100,H4)>0,"一致","不一致")

以下是对公式的解说：

COUNTIFS函数：这是一个多条件计数函数。它用于统计同时满足多个条件的单元格数量。

$B$4:$B$100,F4：第一个条件，表示在 B4:B100 这个绝对引用的范围中，统计等于 F4 单元格内容的单元格数量。使用绝对引用是为了在向下填充公式时，这个范围不会发生改变。

$C$4:$C$100,G4：第二个条件，在 C4:C100 绝对引用的范围内，统计等于 G4 单元格内容的单元格数量。

$D$4:$D$100,H4：第三个条件，在 D4:D100 绝对引用的范围内，统计等于 H4 单元格内容的单元格数量。

>0：判断COUNTIFS函数的结果是否大于 0。如果大于 0，说明在指定范围内存在同时满足三个条件（产品、型号、库存分别与 F4、G4、H4 对应相同）的情况。

"一致","不一致"：如果前面的条件判断为真，即找到了满足条件的记录，就返回 “一致”；如果判断为假，即没有找到满足条件的记录，就返回 “不一致”。

方法二：=IF(SUMPRODUCT(($B$4:$B$100=F4)*($C$4:$C$100=G4)*($D$4:$D$100=H4))>0,"一致","不一致")

以下是对公式的解说：

SUMPRODUCT函数：这个函数可以在给定的几组数组中，将数组间对应的元素相乘并求和。

($B$4:$B$100=F4)：这部分判断 B4:B100 这个绝对引用的范围中的每个单元格是否等于 F4 的值。如果相等，返回TRUE，在 Excel 中TRUE会被当作 1 来处理；如果不相等，返回FALSE，在 Excel 中FALSE会被当作 0 来处理。

($C$4:$C$100=G4)和($D$4:$D$100=H4)同理，分别判断 C4:C100 范围中的值是否等于 G4，以及 D4:D100 范围中的值是否等于 H4。

这三个部分用乘法连接起来，只有当三个条件都为真（即产品、型号、库存分别与 F4、G4、H4 对应相同）时，结果才为 1，否则为 0。然后SUMPRODUCT函数对这些结果进行求和。

>0：判断SUMPRODUCT函数的结果是否大于 0。如果大于 0，说明至少有一组数据满足三个条件。

"一致","不一致"：如果前面的条件判断为真，即找到了满足条件的记录，就返回 “一致”；如果判断为假，即没有找到满足条件的记录，就返回 “不一致”。

方法三：在单元格输入公式=IF(ISERROR(INDEX($D$4:$D$100,MATCH(F4&G4,$B$4:$B$100&$C$4:$C$100,0))),"不一致",IF(INDEX($D$4:$D$100,MATCH(F4&G4,$B$4:$B$100&$C$4:$C$100,0))=H4,"一致","不一致"))

以下是对方法三公式解读

一、整体结构

这个公式使用了嵌套的IF函数来进行判断。先判断是否存在错误，如果有错误则返回 “不一致”，如果没有错误再进一步判断库存是否一致，从而确定最终结果是 “一致” 还是 “不一致”。

二、内部函数解析

MATCH(F4&G4,$B$4:$B$100&$C$4:$C$100,0)：

F4&G4：将 F4 单元格的产品和 G4 单元格的型号进行合并，形成一个唯一的查找值。

$B$4:$B$100&$C$4:$C$100：将 B4:B100 范围的产品列和 C4:C100 范围的型号列进行合并，形成一个合并后的区域用于查找。

0表示精确匹配。这个函数的作用是在合并后的区域中查找与合并后的查找值（产品和型号）完全匹配的位置，并返回其在合并区域中的相对位置序号。

INDEX($D$4:$D$100,MATCH(F4&G4,$B$4:$B$100&$C$4:$C$100,0))：

$D$4:$D$100是库存所在的列范围。

MATCH(F4&G4,$B$4:$B$100&$C$4:$C$100,0)返回的位置序号作为INDEX函数的参数。INDEX函数根据这个序号从库存列中提取相应的库存值。

ISERROR(INDEX($D$4:$D$100,MATCH(F4&G4,$B$4:$B$100&$C$4:$C$100,0)))：

检查INDEX函数提取的值是否为错误值。如果在查找过程中没有找到匹配的产品和型号组合，INDEX函数会返回错误值，此时这个条件判断为真。

IF(INDEX($D$4:$D$100,MATCH(F4&G4,$B$4:$B$100&$C$4:$C$100,0))=H4,"一致","不一致")：

如果前面没有错误值，即找到了匹配的产品和型号组合，那么进一步判断提取的库存值（来自记录的数据）是否等于实际盘点的库存值（H4）。如果相等，返回 “一致”，否则返回 “不一致”。