数据透视表 - 计数唯一值 - Excel 2010 [英] Pivot Table - Count unique values - Excel 2010

问题描述

根据网络输出，我在excel 2010中有一个数据透视表。我想每周发布在网络上的用户数量。

I have a pivot table in excel 2010 based on a network output. I would like to have a count of unique values per week of users who posted on the network.

我发现这个主题：简单数据透视表来计算唯一值，这将为我的数据添加一个额外的列。我的问题是，我每周都需要独特的价值观，而不是所有的表格。

I found this topic: Simple Pivot Table to Count Unique Values which would add an extra column to my data. Problem for me is, I need unique values per week, not over all the table.

示例输入：

week 1 USER_A message1
week 1 USER_B message2
week 1 USER_A message3
week 2 USER_A message4
week 2 USER_B message5
week 2 USER_C message6

实际上，什么是当我要求一个计数，它是否3我们需要第1周的计数为2（因为有2个用户），第2周的计数为3（因为有3个用户）。

What excel actually does is when I ask for a count, is it gives 3 as a count both for week 1 as for week 2. I need the count for week 1 to be 2 (as there are 2 users) and the count for week 2 to be 3 (as there are 3 users).

任何人都知道如何做到这一点？

Anyone know how this can be done?

推荐答案

您可以创建一个新列添加公式：

You can create a new column and add the formula:

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

并在此列上进行支点：

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