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

问题描述

这似乎是一个简单的数据透视表，可以用来学习.我想为我分组的特定值计算唯一值.

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.

例如，我有这个:

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.

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

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...

推荐答案

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

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

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

然后复制下来.现在根据第 1 列和第 3 列创建您的数据透视表.查看快照

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

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