excel - 简单的数据透视表来计算唯一值(Simple Pivot Table to Count Unique Values)

Question

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"

(换句话说，“ABC”是“好”，“DEF”是“坏”)

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

(我确信有一种更简单的方法可以做到这一点，但我想我会试试枢轴表......)

  ask by user1586422 translate from so

Answer 1

UPDATE: You can do this now automatically with Excel 2013. I've created this as a new answer because my previous answer actually solves a slightly different problem.

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

If you have that version, then select your data to create a pivot table, and when you create your table, make sure the option 'Add this data to the Data Model' tickbox is check (see below).

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

Then, when your pivot table opens, create your rows, columns and values normally.

(然后，当您的数据透视表打开时，通常会创建行，列和值。)

Then click the field you want to calculate the distinct count of and edit the Field Value Settings:

(然后单击要计算其不同计数的字段并编辑“字段值设置”：)

Finally, scroll down to the very last option and choose 'Distinct Count.'

(最后，向下滚动到最后一个选项，然后选择“Distinct Count”。)

This should update your pivot table values to show the data you're looking for.

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