我有一张excel表。
在E列下,我有425个单元格包含数据。我想检查相同的数据(即单元格内的文本)是否在e列下剩余的424个单元格中的任何其他地方重复。我该怎么做?
例如,在E54中,我有Hello Jack
我该如何检查这个值,看看它是否在这些单元格中的任何其他单元格中?
您可以使用
=SUMPRODUCT(1/COUNTIF(E1:E425,E1:E425))
计算E1:425 中唯一细胞的数量
425的答案意味着所有的值都是唯一的
421的答案意味着4个值与其他值重复。
对所有将基于以下公式高亮显示的单元格使用条件格式:
COUNTIF(E:E,E1)<>1
这是基于列为E,从E1开始,否则进行修改。
在Excel2010中,它甚至更容易,只需进入条件格式并选择
仅格式化唯一或重复的值
如果必须补偿空白单元格,请使用@brettdj和提供的公式
- 调整计数的唯一分子以检查是否为非空格
-
将零长度字符串添加到COUNTIFS的条件参数中。
=SUMPRODUCT((E1:E425<>")/COUNTIF(E1:E4 25,E1:E425&"))
检查分子中的非空白单元格意味着任何空白单元格都将返回零。任何分子为零的分数,无论分母是什么,都将为零。添加到COUNTIF
的标准部分的空字符串足以避免#DIV/0!
错误。
有关更多信息,请访问Count Unique with SUMPRODUCT()Breakdown。
此公式输出"唯一"或"重复",具体取决于列值是否都是唯一的:
{=IF(
SUM(IF(ISBLANK(E1:E425),0,ROW(E1:E425)))
=
SUM(IF(ISBLANK(E1:E425),0,MATCH(E1:E425,E1:E425,0)))
,"unique","duplicates")}
这是一个数组公式。您没有显式键入封闭的{}。相反,您可以输入不带{}的公式,然后按cmd回车键(或者其他键,如果不是Mac,请去查找!)如果您想将公式拆分为多行以提高可读性,请在Mac上使用cmd ctrl回车键。
该公式通过比较两个SUM()结果来工作。如果相等,则所有非空条目(数字或文本)都是唯一的。如果它们不相等,就会有一些重复。公式不会告诉你重复项在哪里。
第一个和是通过将每个非空白条目的行号相加得到的。
第二个和使用MATCH()查找每个非空条目。如果所有条目都是唯一的,MATCH()会在其自己的位置找到每个条目,结果与第一个和相同。但是,如果存在重复条目,则后面的重复条目将与前面的重复条目相匹配,后面的重复项将为总和贡献不同的值,并且总和将不匹配。
你可能需要调整这个公式:
如果你想让包含"的单元格算作空白,那么对ISBLANK(…)使用LEN(…)=0。如果你愿意,我想你可以在其中放入其他测试,但我没有尝试过。
如果你想测试一个不是从第1行开始的数组,那么你应该从row(…)中减去一个常数。
如果你有一个巨大的单元格列,在计算这个和时可能会出现整数溢出。我没有解决办法。
很遗憾Excel没有ISUNIQUE()函数!
这可能是一个更简单的解决方案。假设列A包含有问题的数据。按该列排序。然后,从B2(或第一个非空白单元格)开始,使用以下公式:=如果(A2=A1,1,0)。
比那一列的总和。当sum=0时,所有值都是唯一的。
突出显示E,然后在主页选项卡上选择条件格式>突出显示单元格规则>重复值。。。然后,它将突出显示重复的所有内容。