老生常谈，判断两个区域是否具有相同的值

标签：Excel公式练习

这个问题似乎很常见，如下图1所示，有两个区域，你能够使用公式判断它们是否包含相同的值吗？假设单元格区域B3:B7命名为“range1”，D3:d7命名为“range2”。

图1

注：示例数据来自于chandoo.org。

如果两个区域包含的值相同，则公式返回TRUE，否则返回FALSE。

关键是要双向比较，即不仅要以range1为基础和range2相比，还要以range2为基础和range1相比。

最简洁的公式是：

=AND(COUNTIF(range1,range2),COUNTIF(range2,range1))

这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。

还有下面的一系列数组公式：

=IF(ISERROR(SUM(MATCH(range1,range2,0)*MATCH(range2,range1,0))),FALSE,TRUE)

或者：

=IF(ISERROR(SUM(1/(MMULT({1,1,1,1,1},--(range1=TRANSPOSE(range2)))*TRANSPOSE(MMULT(--(range1=TRANSPOSE(range2)),{1;1;1;1;1}))))),FALSE,TRUE)

或者：

=IFERROR(AND(SUMPRODUCT(--(MATCH(range1,range2,0)>0))=COUNTA(range1),SUMPRODUCT(--(MATCH(range2,range1,0)>0))=COUNTA(range2)),FALSE)

或者：

=AND(NOT(ISNA(MATCH(range1,range2,0))),NOT(ISNA(MATCH(range2,range1,0))))

或者：

=AND(COUNTIF(range1,range2)=IF(range1=range1,1,0),COUNTIF(range2,range1)=IF(range2=range2,1,0))

或者：

=IF(ISERROR(SUM(1/(COUNTIFS(range2,range1)*COUNTIFS(range1,range2)))),FALSE,TRUE)

或者：

=AND(COUNT(range1)=COUNT(range2),COUNTIF(range2,range1)=1,COUNTIF(range1,range2)=1)

都可以得到正确的结果。

看到了吧，同样的问题，各种函数各显神通，都可以得到想要的结果。仔细体味一下上述各个公式，相信对于编写公式的水平会大有裨益。

当然，或许你有更好的公式？欢迎留言。

注：有兴趣的朋友可以到知识星球完美Excel社群下载本文配套示例工作簿。