数据透视表 - 计数唯一值 - 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屋!