最近在工作中遇到一个问题，需要校验 Excel 表中的两列数据是否相互匹配，但是默认 Excel 没有相关函数可以校验，只能使用现有函数做判断了。最终通过下面一条公式完成功能：

=IF(B7<>"",IF(COUNTIF(B:B,B7)=COUNTIFS(B:B,B7,C:C,C7),"TRUE","FALSE"),"")

最终实现效果如下：

目前是判断有一行数据错误的就对所有原始数据报错。下面对公式做个简单的说明。

主要使用了两个固有函数 COUNTIF 与COUNTIFS ，关于这两个函数的使用方法如下：

COUNTIF(range，criteria) ：计算满足条件的单元格数目，range 是区域，criteria 是条件。

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…) ：计算满足各自条件的单元格数据，最后取并集，也就是统计满足所有条件的多个区域中单元格数目。

简单说下思路：

  • 首先，计算原始数据项目（左侧）中包含当前校验项目的单元格个数 COUNTIF(B:B,B7) ；
  • 然后，以原始数据项目为条件筛选出需要计算的单元格 COUNTIFS(B:B,B7) ，这里主要为了避免直接统计匹配数据项目（右侧）时出现的错误（将不属于原始数据的相同项目也误统计了）。在筛选的结果基础上再统计匹配数据项目（右侧）在统计区域中出现的次数；
  • 最后，判断两次统计出的数据是否一致，如果一致则判定为 TRUE （两列数据匹配），否则为FALSE（两列数据不匹配）。

以上。