简单数据透视表来计算唯一值 [英] Simple Pivot Table to Count Unique Values

问题描述

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

例如，我有这个：

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

我想要的是一个显示我的数据透视表：

  ABC 1 
 DEF 2 
  

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

  ABC 3 
 DEF 4 
  

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

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

我确定有一个更简单的方法来做， d给予枢轴表一个尝试...

解决方案

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

  = IF（SUMPRODUCT（（$ A $ 2：$ A2 = A2）* B $ 2：$ B2 = B2））> 1,0,1）
  

并将其复制。现在根据第1列和第3列创建您的枢轴。查看快照

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on.

For instance, I have this:

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

What I want is a pivot table that shows me this:

ABC   1
DEF   2

The simple pivot table that I create just gives me this (a count of how many rows):

ABC   3
DEF   4  

But I want the number of unique values instead.

What I'm really trying to do is find out which values in the first column don't have the same value in the second column for all rows. In other words, "ABC" is "good", "DEF" is "bad"

I'm sure there is an easier way to do it but thought I'd give pivot table a try...

解决方案

Insert a 3rd column and in Cell C2 paste this formula

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

and copy it down. Now create your pivot based on 1st and 3rd column. See snapshot

这篇关于简单数据透视表来计算唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！