在Excel中计算一列中的唯一值数量

Question

我有一个包含数据列的.xls文件。我该如何计算这一列中包含多少个唯一值？

我已经搜索了很多选项，但是我找到的公式总是给我报错。例如，

=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0))

返回

- Ivanka Todorova

---

你不能只是使用数据透视表来得到答案吗？ - Skip Intro

实际上我并不想保存答案。我只是想检查该列包含多少个唯一值，因为这个文件稍后会被导入到 MySQL 中，我想看看它是否被正确导入。 - Ivanka Todorova

你接受得了最受欢迎的答案吗？我认为它更好。 - greg121

Answer 1

要计算A2:A100中不同值的数量（不包括空白单元格）：

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

---

摘自@Ulli Schmid在What is this COUNTIF() formula doing?的回答：

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

计算A1:A100中唯一单元格的数量，排除空单元格和空字符串("")。

如何实现？例如：

A1:A100 = [1, 1, 2, "apple", "peach", "apple", "", "", -, -, -, ...]
then:
A1:A100&"" = ["1", "1", "2", "apple", "peach", "apple", "", "", "", "", "", ...]

所以这个&""是必需的，可以将空单元格（-）转换为空字符串（""）。如果你直接计算空单元格，COUNTIF（）函数将返回0。使用这个技巧，""和-被视为相同：

COUNTIF(A1:A100,A1:A100) = [2, 2, 1, 2, 1, 2, 94, 94, 0, 0, 0, ...]
but:
COUNTIF(A1:A100,A1:A100&"") = [2, 2, 1, 2, 1, 2, 94, 94, 94, 94, 94, ...]

如果现在我们想要获取所有唯一单元格的数量，不包括空白和""，我们可以执行以下操作

(A1:A100<>""), which is [1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, ...]

通过我们的中间结果，使用COUNTIF(A1:A100,A1:A100&"")进行计算，并对这些值求和。

SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))  
= (1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0/94 + 0/94 + 0/94 + 0/94 + ...)
= 4

如果我们使用的是COUNTIF（A1：A100，A1：A100）而不是COUNTIF（A1：A100，A1：A100&""），那么其中一些0/94将变为0/0。由于除以零是不允许的，因此我们将会出现错误。

- barry houdini

---

好的，我会尽力进行翻译。以下是需要翻译的内容：注意：我再次修改了 - 分隔符由区域设置确定，因此逗号对于我在英国有效（在美国也有效），但是在其他地方可能会使用分号，例如欧洲大陆。 - barry houdini

这应该是“更好”的答案。如果能标记一下就更好了。 - DynamicDan

工作正常，但它减慢了表格速度，该死的，天哪。 - Tascalator

结果发现我试图检查整个列，这会使过程变得非常缓慢，以至于一个需要1秒的宏花费了10分钟。在与客户要求核对后，最大行数为156，因此我将查询设置为在1和250之间进行检查。完美地解决了问题。谢谢houdini ;) - Tascalator

Answer 2

尝试 - =SUM(IF(FREQUENCY(MATCH(COLUMNRANGE,COLUMNRANGE,0),MATCH(COLUMNRANGE,COLUMNRANGE,0))>0,1))

其中COLUMNRANGE = 您拥有这些值的范围。

例如 - =SUM(IF(FREQUENCY(MATCH(C12:C26,C12:C26,0),MATCH(C12:C26,C12:C26,0))>0,1))

按Ctrl+Shift+Enter使公式成为数组（否则不会正确计算）

- Nilesh Deshmukh

---

由于某些原因，这对我不起作用。我的区域设置要求我使用“;”作为参数分隔符和“.”作为小数分隔符，但它仍然无法工作。我正在使用Excel 2013...也许API已经改变了？ - ravemir

对我来说是有效的。我正在使用Excel 2013。 我也不需要将公式转换为数组才能运行。 - Mike

我刚刚尝试了各种不同的答案，大约有六个，而这是唯一一个可以计算唯一文本值的答案。 - claudekennilol

Answer 3

以下是一种快速获取唯一值数量和唯一值的方法。将您关心的列复制到另一个工作表中，然后选择整个列。单击“数据”->“删除重复项”->“确定”。这将删除所有重复值。

- Arconath

---

这个方法赢得了“最有可能被普通人使用”的奖项。 :) - estephan500

这对我来说比barry houdini的答案快得多，大约有100万行。 - StockB

Answer 4

这里有一个优雅的数组公式（我在 http://www.excel-easy.com/examples/count-unique-values.html 上找到的），可以很好地完成任务：

输入以下公式：

=SUM(1/COUNTIF(List,List))

然后使用CTRL-SHIFT-ENTER确认。

- Your Excel Guy

---

这是我见过的最优雅的，但像其他所有的一样，它也不能进行精确字符串匹配。 - Sam Brightman

我喜欢这个公式潜在的扭曲思维。 - MattClarke

在短列表上运行得很好。但是在长列表上会出现问题。我有一个包含42000行的列表，已经计算了五分钟还没有完成。200行时，几乎感觉不到延迟。但是42000行时，需要等待，等待，等待...可能是时候将其转移到数据库或某些统计应用程序中了。 - adamek

Answer 5

使用条件计算唯一值。列A是ID，使用条件ID=32，列B是名称，我们试图计算特定ID的唯一名称数量。

=SUMPRODUCT((B2:B12<>"")*(A2:A12=32)/COUNTIF(B2:B12,B2:B12))

- Derek

Answer 6

使用动态数组公式(截至本篇文章，仅限于Office 365 Insiders)：

=COUNTA(UNIQUE(A:A))

- Scott Craner

Answer 7

我想到了另一种棘手的方法（经过测试，它是有效的！）。

• 选择列中的数据
• 在菜单中，选择条件格式、突出显示单元格、重复值
• 选择您想要突出显示的唯一值或重复值。
• 保存突出显示
• 选择数据
• 转到数据，然后筛选器

基于颜色进行筛选：

不可否认，这更适用于对数据进行一次性检查而不是经常使用的电子表格，因为它需要进行一些格式更改。

- SeanKilleen

---

快速计数非常高效！请注意，您要筛选没有填充的单元格。 - cpilko

Answer 8

如果使用 Mac：

1. 高亮所需列 2. 复制 3. 打开 terminal.app 4. 输入 pbpaste|sort -u|wc -l Linux 用户将 pbpaste 替换为 xclip、xsel 或类似工具。

Windows 用户也可以实现此功能，但需要编写一些脚本。首先请参考 http://brianreiter.org/2010/09/03/copy-and-paste-with-clipboard-from-powershell/。

- furicle

Answer 9

你可以按照以下步骤操作：

1. 首先隔离列（如果有相邻的列，则在要计算唯一值的列之前和/或之后插入一个空列；

2. 然后选择整个列，进入“数据”>“高级筛选”，并勾选“仅唯一记录”复选框。这将隐藏所有非唯一记录，因此您可以通过选择整个列来计算唯一记录。

- Jerry

Answer 10

我使用带有标题的电子表格，其中第1行包含数据，而数据在第2行及以下。

ID位于A列。为了计算有多少个不同的值，我将此公式从第2行放到可用的第一列[F]的电子表格末尾：=IF（A2=A1，F1+1，1） 。

然后我在一个空白单元格中使用以下公式：=COUNTIF（F：F，1） 。通过这种方式，我确定每个ID都被计数。

请注意，ID必须排序，否则它们将被计算多次...但与数组公式不同，即使是具有150,000行电子表格，速度也非常快。

- grab74