如何检测唯一值并计算具有重复数据的单元格数量 [英] How to detect uniques and count the number of cells with duplicate data

问题描述

我有一列数据，其中包含一些相同的值.我想检测的是该行是否唯一.如果是，请回显唯一"(或其他)，但如果不是，请计算列中重复项的数量并回显该数字.我想要的例子:

I have a column of data that has the some of the same values in it. What I want to detect is if that row is unique. If it is, echo 'unique' (or something) but if it's not, count the number of duplicates in the column and echo that number. Example of what I want:

COL A     RESULT COLUMN
Apple     Unique
Banana    Unique
Banana    3
Banana    3
Cherry    Unique
Date      Unique
Date      2

我使用此代码的示例 (=IF(A1=A2, "Duplicate", "Unique")):

Example of what I have using this code (=IF(A1=A2, "Duplicate", "Unique")):

COL A     RESULT COLUMN
Apple     Unique
Banana    Unique
Banana    DUPLICATE
Banana    DUPLICATE
Cherry    Unique
Date      Unique
Date      DUPLICATE

我想我真正需要的是在该公式中重复"文本所在的位置插入(?)查询，该查询将对相同的单元格值进行倒计时，直到看到不同的值，并回显该数字.

I guess what I really need is a query inserted(?) into where the 'Duplicate' text is in that formula that will count down the same cell values until it sees a different value, and echo that number.

如果可以在同一列内完成就好了.如果我需要另一列并且必须执行两组查询，我可以妥协.

If it can be done within the same column excellent. If I need another column and have to perform two sets of queries I can compromise.

根据 DATATOO 答案更新

我运行了那个查询，将A"列修改为E"，这就是返回的内容.

I ran that query, modified column 'A' to 'E' and this is what returned.

City Country Merge  Duplicate City Country
Aberdeen, UK    Unique
Aberdeen, UK    2
Bangor, UK          2
Bath, UK            Unique
Belfast, UK         Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique

如您所见，这不算数.想法?

As you can see this doesn't count right. Thoughts?

推荐答案

我知道这是一个老问题，但我看到它从未得到令人满意的回答，所以我希望这仍然有用.

I know this is an old question, but I see that it was never satisfactorily answered, so I hope this is still useful.

你想要这个:

=if(COUNTIF($A$1:$A1,A1)=1, "UNIQUE", COUNTIF($A$1:$A$7,A1))

这将填充并在后续行中看起来像这样:

This will fill down and look like this in subsequent rows:

=if(COUNTIF($A$1:$A2,A2)=1, "UNIQUE", COUNTIF($A$1:$A$7,A2))
=if(COUNTIF($A$1:$A3,A3)=1, "UNIQUE", COUNTIF($A$1:$A$7,A3))
...

这些是结果(假设公式被插入到 B1 并填写):

And these are the results (assuming the formula was inserted into B1 and filled down):

    A         B
1 Apple     UNIQUE
2 Banana    UNIQUE
3 Banana    3
4 Banana    3
5 Cherry    UNIQUE
6 Date      UNIQUE
7 Date      2

问题的关键在于，您要查找第一次出现的字符串作为唯一项，然后您计算重复项.因此，对于公式的第一部分，到目前为止，您实际上只是在搜索列表，而不是整个列表.

The key to your problem is that you're looking for the first occurrence of the string to count as unique, then you count the duplicates. So, for the first part of the formula, you're really only searching the list so far, not the entire list.

这种方法还有一个额外的优势，即处理未排序列表和已排序列表.

This method also has the added advantage of working with an unsorted list as well as a sorted one.

这篇关于如何检测唯一值并计算具有重复数据的单元格数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！