如何检测唯一性并计算重复数据的像元数 [英] 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屋！