简单的数据透视表，用于计算唯一值数量

Question

这似乎是一个简单的透视表。我想对我正在分组的特定值进行唯一值的计数。

例如，我有这个：

ABC   123
ABC   123
ABC   123
DEF   456
DEF   567
DEF   456
DEF   456

我想要的是一个数据透视表，它能够展示给我这样的结果：

ABC   1
DEF   2

我创建的简单数据透视表只是给了我这个（行数的计数）：

ABC   3
DEF   4  

但我希望得到唯一值的数量。

我真正想做的是找出第一列中所有行的第二列值不相同的值。换句话说，“ABC”是“good”，“DEF”是“bad”。

我相信有更简单的方法，但想试试透视表...

- user1586422

---

请注意，对于最近版本的Excel，这个答案最为方便。 - Dennis Jaheruddin

Answer 1

更新：您现在可以使用 Excel 2013 自动完成此操作。我创建了一个新答案，因为我的以前的答案实际上解决了一个略微不同的问题。

如果您有该版本，则选择数据以创建数据透视表，并在创建表格时确保选中“将此数据添加到数据模型”复选框（如下所示）。

然后，当您的数据透视表打开时，按照常规方式创建行、列和值。然后单击要计算唯一计数的字段并编辑字段值设置：

最后，向下滚动到最后一个选项，选择“唯一计数”。

这应该会更新您的数据透视表值，显示您要查找的数据。

- Meaghan Fitzgerald

---

@MichaelK，如果你有Excel 2013会更好。 - jrharshath

这个操作也可以应用到现有的数据透视表上吗？这样我们就不需要重新创建200多个表来获取“去重计数”功能了吗？ - Louisa

提醒一下：如果您还没有将文件保存为 Excel (.xlsx) 格式（例如：您打开了一个 .csv 文件），那么“将此数据添加到数据模型”选项将被禁用/灰显。简单的解决方法是将文件保存为 Excel 文件。 - PonyEars

这个在Mac上不支持吗？我这里没有出现这个选项。我的版本是15.27。 - jkupczak

这个选项在 Mac 上确实不存在，因为数据模型通常只是 Windows 的一个功能。 - Tomty

Answer 2

插入第三列，在单元格C2中粘贴此公式。

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

复制它并向下滑动。现在根据第1列和第3列创建您的数据透视表。请参见快照。

- Siddharth Rout

---

不错的技巧。我之前不知道这个方法。你可以使用数组函数=IF(SUM((A$2:A2=A2)*(B$2:B2=B2)) > 1, 0, 1)来实现同样的功能（在输入公式时按Ctrl-Shift-Enter，使其获得{}）。 - ErikE

通用答案，不需要任何特定功能。只需好的公式即可。 - Alberto De Caro

@MattO'Brien：在这种情况下，当前的header3将移动到第四列，并且那里的公式变为=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1)。 - Siddharth Rout

请注意，如果使用数据透视表选项过滤掉一些行，则此答案将无法提供正确的解决方案。假设第一行被过滤掉了，ABC的总和将显示为0！ - jarlemag

太好了！您能简单地解释一下这个解决方案吗？ - Fighter Jet

Answer 3

我想在这里提供一个额外的选项，它不需要公式，但如果您需要跨两个不同列计数唯一值，则可能会有所帮助。以原始示例为例，我没有：

ABC   123  
ABC   123  
ABC   123   
DEF   456  
DEF   567  
DEF   456  
DEF   456

而且希望它呈现为：

ABC   1  
DEF   2

但是更像是：

ABC   123  
ABC   123  
ABC   123  
ABC   456  
DEF   123  
DEF   456  
DEF   567  
DEF   456  
DEF   456

并且希望它呈现为：

ABC  
   123    3  
   456    1  
DEF  
   123    1  
   456    3  
   567    1

我发现将我的数据转换为以下格式，然后进一步操作是最好的方法：

一旦您选择了“Running total in”，则选择次要数据集的标题（在本例中，它将是包括123、456和567的数据集的标题或列标题）。这将为您提供该集合中的最大值和项目总数，在您的主要数据集内。

然后，我复制了这些数据，将其粘贴为值，然后放入另一个数据透视表中以更轻松地操纵它。

顺便说一句，我有大约25万行的数据，所以这比一些公式方法要好得多，特别是那些试图跨两个列/数据集进行比较的方法，因为它们会使应用程序崩溃。

- Meaghan Fitzgerald

---

我遇到了完全不同的问题，但这个答案指引了我正确的方向。谢谢。 - jtolle

这个回答符合我的需求，因为我有500,000行需要应用公式，如果我尝试的话我的电脑会耗尽内存。谢谢！ - cauldyclark

Answer 4

我发现最简单的方法是在“值字段设置”下使用“不同计数”选项（在“值”窗格中单击字段）。“不同计数”选项在列表的底部。

点击位置在这里： 这是使用普通“计数”前（上）和使用“不同计数”后（下）的效果：

- Peter

---

截至Office 2016版本：为了能够使用此功能，需要在创建数据透视表时勾选“将此数据添加到数据模型”。 - Leo

Answer 5

请查看 Debra Dalgleish 的计算唯一项指南。

- brettdj

Answer 6

以下公式无需对表格进行排序，即可返回每个唯一值的 1。

假设问题中所呈现数据的表格范围为 A1:B7，请在 C1 单元格输入以下公式：

=IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1))

将这个公式复制到所有行，最后一行将包含以下内容：

=IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7))

第一次找到记录时将返回“1”，之后每次都返回“0”。

在透视表中简单地求和该列。

- Jeff Baumet

---

如果你有一个大数据集，可以使用=IF(COUNTIF($B$1:$B1,B1),1,0)这样，countif函数只会运行一次！ - Peter Albert

Answer 7

针对这个问题，我的解决方法与我在这里看到的略有不同，所以我想分享一下。

1. （首先复制您的数据）
2. 连接这些列
3. 删除连接后的重复数据
4. 最后，在结果集上进行透视操作

注意：我希望包含图片以使理解更加容易，但是由于这是我的第一篇文章，所以无法添加图片；)

- Jacob Noone Wade

Answer 8

Siddharth的回答非常棒。

然而，当处理大量数据时（我的电脑在50000行上卡住了），这种技术可能会遇到麻烦。一些 less processor-intensive 的方法：

单一唯一性检查

1. 按这两列（A、B）排序

2. 使用一个查看更少数据的公式

   =IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1) 
   

多重唯一性检查

如果您需要在不同的列中检查唯一性，您不能依赖于两种排序。

相反，

1. 对单列（A）进行排序

2. 添加公式以覆盖每个分组的最大记录数。如果ABC可能有50行，则公式将为

   =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)
   

- workglide

---

另一种可能更少处理器负荷的方法是添加一个列C，在C2中输入=A2&B2。然后添加一个列D，在D2中输入=IF(MATCH(C2, C$2:C2, 0) = ROW(C1), 1, 0)。向下填充两个列。虽然这仍然从整个范围的开头搜索，但它在找到第一个值时停止，并且不是将50,000行的值相乘，而只需定位该值--因此它应该执行得更好。 - ErikE

@ErikE Sharp - 我也认为你的技巧会在第一次查找时停止。但是，如果C中有很多唯一值（例如：只有50个ABC），您将继续检查大量数据。很酷的功能：您的公式在数据未排序时效果最佳。 - workglide

Answer 9

步骤1： 添加一列

步骤2： 在第一条记录中使用公式=IF(COUNTIF(C2:$C$2410,C2)>1,0,1)

步骤3： 将其拖动到所有记录中

步骤4： 在具有公式的列中过滤“1”

- Abhishek Verma

Answer 10

Excel 2013可以在数据透视表中进行去重计数。如果没有使用2013版本，且数据量较小，可以复制原始数据两次，在复制的b中选择两列并删除重复项。然后创建透视表并计算列b的数量。

- Zachary