下图工作表中，用公式判断单元格区域A1:A3和C1:C3是否有重复值。

从工作表中可以明显看出，两个区域都包含“Excel”，因此存在重复值。

如何用公式来判断？

先不看答案，自己试试。

公式思维

将单元格区域A1:A3中的每个值与C1:C3中的每个值进行比较。如果它们不同，则不存在重复值，否则存在重复值。

公式分析

在单元格E1中输入以下数组公式：

=或(转置(A1:A3)=C1:C3)

该值为真，表示这两个区域中有重复的值。如下图所示。

在公式中，使用转置函数将A1:A3转置为水平数组，即{“excel”、“excel perfect”、“excel”}，然后，C1:C3中的值{“office”；“Excel”；“word”}与转置数组一一对比，即C1的值“Office”与{“Excel”、“excelperfect”、“Excel”}对比，C2的值“Excel”与{“Excel”、“excelperfect”、“Excel”}对比，C3的值与{“Excel”对比。如果值相等，则为真；否则为假。最后，得到一个3行3列的数组：

{假，假，假；假，假，真；假，假，假}

该数组用作OR函数的参数，以获得比较后的结果。只要数组中的一个值为真，或函数的返回值就是真。在本例中，数组中的一个值为真，因此如果返回真，则表明存在重复值。

应用程序扩展

扩展1:判断重复值的单元格区域方向不同。

在上图中，具有重复值的单元格区域都在列中。但是，如果要判断列中是否有一个区域重复值，行中是否有一个区域重复值，如下图工作表所示，公式怎么写？

在这种情况下，公式更简单！在单元格C3中输入数组公式：

=OR(A1:A3=C1:G1)

由于这两个区域中没有重复的值，因此结果为FALSE，如下图所示。

将单元格区域A1:A3中的每个值与单元格区域C1:G1中的每个值逐一进行比较，总共进行15次比较，得到一个数组：

{假，假，假，假，假，假；假，假，假，假，假；假，假，假，假，假}

作为或函数的一个参数。因为所有数组都为假，所以结果为假，这表明这两个区域中没有重复的值。

扩展2:要比较的单元格区域有更多的行和列。

上述比较判断两个单元格区域是否有重复值的方法，要求两个区域的方向不同，一个是逐行，一个是逐列。但是，对于Excel 2003，工作表列数为256，对于Excel 2007及更高版本，工作表列数为16384。如果数据量超过这些列，则不能使用上述方法。

此时可以使用MATCH函数和COUNT函数的组合来判断两个单元格区域是否有重复值。

例如，对于前面的示例，您可以使用数组公式：

=COUNT(MATCH(A1:A3，C1:G1，0))

如果结果为0，则意味着两个单元格区域中没有重复的值。如果结果是一个数字，该数字表示这两个区域中重复值的数量。

注意：更灵活的做法是命名两个要比较的区域，比如Data1和Data2，然后用名称来指代公式中的区域，比如=COUNT(MATCH(Data1，Data2，0))。

MATCH函数在区域Data1中查找区域Data2中的值。如果没有找到，将生成错误值#N/A，如果找到，将获得该值的列号。这样，就生成了一个由#N/A或数字组成的数组。将数组作为COUNT函数的参数，对数组中的数字进行计数。如果没有数字，表示没有重复值，则为0。如果有数字，返回数字的个数，即重复值的个数。

总结

要比较两个区域的数据，两个区域的方向应该不同。如果两个区域的方向相同，应该使用转置函数转置其中一个区域。转置功能的使用细节见《Excel函数学习23：TRANSPOSE函数》。

通过上述方法比较的次数，也就是说，生成的数组中元素的数量取决于这两个区域所占的行数和列数。例如，第一示例中的比较次数为33=9次，第二示例中的比较次数为35=15次。

Excel工作表列中的数据代表数组中的每一行，即每一个数据后面都有一个分号；工作表中的行数据代表数组中的每一列，即每个数据后面有一个逗号，后面有一个分号。

函数COUNT不计算#N/A值，只返回非#N/A值的个数。