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

当我要求计数时，excel 的实际作用是在第 1 周和第 2 周都给出 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屋！