[Excel] 简单数据透视表来计算唯一值


Answers

插入第三列并在Cell C2粘贴此公式

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

并将其复制下来。 现在基于第1列和第3列创建您的数据透视表。 见快照

Question

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

例如,我有这样的:

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

我想要的是一个数据透视表,它显示了我:

ABC   1
DEF   2

我创建的简单数据透视表只是给了我这个数(有多少行):

ABC   3
DEF   4  

但我想要的唯一值的数量。

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

我相信有一个更简单的方法来做到这一点,但认为我会给数据透视表一个尝试...




步骤1.添加一列

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

第3步。将其拖动到所有记录

第4步。使用公式在列中过滤“1”




对于下列公式,无需对表格进行排序,以便为存在的每个唯一值返回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。

简单地总结数据透视表中的列




更新:您现在可以使用Excel 2013自动执行此操作。我已将此创建为新答案,因为我之前的答案实际上解决了一个稍有不同的问题。

如果你有这个版本,那么选择你的数据来创建一个数据透视表,并且当你创建你的数据表时,确保选项'添加这个数据到数据模型'复选框是check(见下文)。

然后,当数据透视表打开时,通常会创建行,列和值。 然后点击您想要计算不同计数的字段并编辑字段值设置:

最后,向下滚动到最后一个选项并选择“区分计数”。

这应该更新您的数据透视表值,以显示您正在查找的数据。




您可以创建一个额外的列来存储唯一性,然后它与您的数据透视表进行总结。

我的意思是,单元格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)




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

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

这是因为它使用更少的单元格进行计算。




Excel 2013可以在数据库中执行不同的计数。 如果没有2013访问权限,并且数据量较少,我会创建两份原始数据,并在副本b中选择两列并删除重复项。 然后制作数据透视表并计算您的列数b。




您可以将COUNTIFS用于多个条件,

= 1 / COUNTIFS(A:A,A2,B:B,B2),然后向下拖动。 您可以在其中放置尽可能多的标准,但这往往需要大量时间来处理。




我通常按​​字段对数据进行排序,我需要做不同的计数然后使用IF(A2 = A1,0,1); 你会得到每个ID组的第一行中的1。 简单并且不需要任何时间在大型数据集上进行计算。







Siddharth的回答非常棒。

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

单一唯一性检查

  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)
    



我对这个问题的处理方式与我在这里看到的有点不同,所以我会分享。

  1. (首先制作数据的副本)
  2. 连接列
  3. 删除连接列上的重复项
  4. 最后 - 在结果集上旋转

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







我发现最简单的方法是使用Value Field Settings下的Distinct Count选项( 左键单击Values窗格中的字段)。 Distinct Count选项位于列表最底部。

这里是之前(TOP;普通Count )和之后(BOTTOM; Distinct Count




我想在混合中添加一个不需要公式的附加选项,但如果您需要对两组不同列中的唯一值进行计数,可能会有所帮助。 使用最初的例子,我没有:

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

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

一旦你选择'运行总数在'然后选择二级数据集的头(在这种情况下,它将是数据集的标题或列标题,包括123,456和567)。 这将为您提供主数据集内该项目中项目总数的最大值。

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

仅供参考,我有大约25万行数据,所以这比一些公式方法好得多,尤其是那些试图在两列/数据集之间进行比较的数据,因为它会使应用程序崩溃。




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

  • 添加一个新的列C并填入公式为“= 1 / COUNTIF($ A:$ A,A2)”的C2
  • 将公式拖至列的其余部分
  • 以A列作为行标签,以及Sum(C列)值来获取A列中唯一值的数量