计算唯一值的简单数据透视表

这似乎是一个可以学习的简单的数据透视表。我想要为我正在分组的特定值做一个唯一值的计数。

例如，我有这个:

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

我想要的是一个枢轴表，它向我显示:

ABC   1
DEF   2

我创建的这个简单的 pivot 表给出了下面的结果(行数计数) :

ABC   3
DEF   4

但我想要的是唯一值的个数。

我真正想做的是找出第一列中的哪些值在第二列中的所有行中没有相同的值。换句话说，“ ABC”是“好”，“ DEF”是“坏”

我相信有一个更简单的方法，但认为我会给枢轴表一个尝试..。

887255 次浏览
小开
最佳答案

插入第3列并在单元格 C2中粘贴此公式

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

然后复制下来。现在根据第一和第三列创建你的轴心。参见快照

enter image description here

小开

您可以创建一个额外的列来存储唯一性，然后在数据透视表中对 那个求和。

我的意思是，细胞 C1应该总是 1。细胞 C2应该包含公式 =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1)。将这个公式复制下来，使单元格 C3包含 =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1)等等。

如果您有一个标题单元格，那么您需要将这些标题单元格全部向下移动一行，您的 C3公式应该是 =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1)。

小开

参见 Debra Dalgleish 的《计算独特项目》

enter image description here

小开

我找到了一个更简单的方法。参考 Siddarth Rout 的例子，如果我想计算列 A 中的唯一值:

  • 添加一个新的列 C，并用公式“ = 1/COUNTIF ($A: $A，A2)”填充 C2
  • 将公式拖动到列的其余部分
  • Pivot，列 A 作为行标签，和值相加{列 C) ，以获得列 A 中唯一值的数目
小开

Siddharth 的回答很棒。

但是 ，这种技术在处理大量数据时会遇到麻烦(我的计算机冻结在50,000行上)。一些处理器密集程度较低的方法:

单一唯一性检查

  1. 按两列排序(本例中为 A、 B)

  2. 使用查看较少数据的公式

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

Multiple uniqueness checks

If you need to check uniqueness in different columns, you can't rely on two sorts.

Instead,

  1. Sort single column (A)

  2. Add formula covering the maximum number of records for each grouping. If ABC might have 50 rows, the formula will be

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

如果你有数据排序. . 我建议使用以下公式

=IF(OR(A2<>A3,B2<>B3),1,0)

这是更快的，因为它使用更少的单元格来计算。

小开

我想在这个组合中添加一个额外的选项，它不需要公式，但是如果您需要跨两个不同的列计算集合中的唯一值，那么这个选项可能会有所帮助。用最初的例子来说，我没有:

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

我发现将我的数据转换成这种格式，然后能够进一步操作它的最佳方法是使用以下内容:

enter image description here

一旦选择了‘ Running total in’，然后选择辅助数据集的头部(在本例中，它是包含123、456和567的数据集的头部或列标题)。这将为您提供一个最大值，其中包含该集合中主数据集内的项目总数。

然后，我复制这些数据，将它们粘贴为值，然后将它们放到另一个 pivot 表中，以便更容易地操作它们。

仅供参考，我有大约25万行数据，所以这比一些公式方法要好得多，特别是那些试图跨两列/数据集进行比较的方法，因为它不断地使应用程序崩溃。

小开

对于以下公式，不需要对表进行排序，以便为每个唯一值返回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。

只需对数据透视表中的列求和即可

小开

更新: 现在可以使用 Excel2013自动完成此操作。我创建了这个新的答案，因为我之前的答案实际上解决了一个稍微不同的问题。

如果您有这个版本，那么选择您的数据来创建一个数据透视表，当您创建您的表时，确保选项‘ Add this Data to the Data Model’勾选框被选中(见下文)。

Tick the box next to 'Add this data to the Data Model'

然后，当您的数据透视表打开时，正常地创建您的行、列和值。然后单击要计算不同计数的字段，并编辑字段值设置: Edit field value settings

最后，向下滚动到最后一个选项，选择“独特的计数” Choose the option 'Distinct Count'

这将更新数据透视表的值，以显示您正在查找的数据。

小开

我通常根据需要执行不同计数的字段对数据进行排序，然后使用 IF (A2 = A1,0,1) ; 然后在每组 ID 的顶行中得到1。简单，不需要任何时间来计算大型数据集。

小开

Excel2013可以在枢轴中执行“计数”。如果无法访问2013年，而且数据量较小，我将原始数据复制两份，在复制 b 中，选择两列并删除重复数据。然后以此为轴心，数一数你的纵队 b。

小开

我处理这个问题的方法与我在这里看到的有些不同，所以我将与大家分享。

  1. (先复印一份资料)
  2. 把柱子连起来
  3. 删除连接列上的重复项
  4. 结果集上的 Last-pivot

注意: 我想包括图片，使这更容易理解，但不能，因为这是我的第一篇文章;)

小开

您可以对多个条件使用 COUNTIFS,

= 1/COUNTIFS (A: A，A2，B: B，B2) ，然后向下拖动。您可以在其中放入任意多条标准，但处理这些标准往往需要花费大量时间。

小开

步骤1. 添加列

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

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

步骤4. 在带公式的列中过滤“1”

小开

我发现最简单的方法是使用 Value Field Settings下的 Distinct Count选项(左边单击 Values面板中的字段)。Distinct Count选项在列表的最底部。

Location of where to click

这里是之前(顶部; 正常 Count)和之后(底部; Distinct Count)

COUNT

DISTINCT COUNT

小开

你可以使用辅助列也 VLOOKUP。我测试，看起来比 COUNTIF快一点。

如果您使用的是头部，数据从单元格 A2开始，那么在行中的任何单元格中使用此公式并在同一列中的所有其他单元格中复制:

=IFERROR(IF(VLOOKUP(A2;$A$1:A1;1;0)=A2;0;1);1)

提交答案