Excel数据透视表:如何根据日期-时间值计算员工的工作日数?

In my theoretical data set, I have a list which shows the date-time of a sale, and the employee who completed the transaction.

在我的理论数据集中，我有一个列表，其中显示了销售的日期时间，以及完成交易的员工。

I know how to do grouping in order to show how many sales each employee has per day, but I'm wondering if there's a way to count how many grouped days have more than 0 sales.

我知道如何进行分组，以显示每个员工每天有多少销售额，但我想知道是否有一种方法来计算有多少分组的天数大于0。

For example, here's the original data set:

例如，这是原始数据集:

Employee | Order Time
    A    | 8/12 8:00
    B    | 8/12 9:00
    A    | 8/12 10:00
    A    | 8/12 14:00
    B    | 8/13 10:00
    B    | 8/13 11:00
    A    | 8/13 15:00
    A    | 8/14 12:00

Here's the pivot table that I have created:

这是我创建的数据透视表:

Employee | 8/12 | 8/13 | 8/14
    A    |   3  |   1  |   1
    B    |   1  |   2  |   0

And here's what I want to know:

我想知道的是:

Employee | Working Days
    A    |      3
    B    |      2

1 个解决方案

#1

Split your Order Time column (assumed to be B) into two, say with Text to Columns and Space as the delimiter (might need a little adjustment). Then pivot (using the Data Model) as shown:

将您的订单时间列(假定为B)分割为两个，比如文本到列和空间作为分隔符(可能需要稍微调整一下)。然后pivot(使用数据模型)如下所示:

and sum the results (outside the PT) such as with:

并将结果(PT之外)相加，如:

=SUM(F3:H3)

copied down to suit.

复制下来。

Columns F:G may then be hidden.

然后可以隐藏列F:G。

I fully support @Andrea's Comment (a correction) on the above:

我完全支持@Andrea对上述评论(更正):

I think this could have been made simpler. If you remove the "Time" in values of the pivot table and then move "Order" from columns to values and use distinct count as in the example. It should count Employee per date making the sum not needed. If you scale this to make it larger. Say 50 dates then the =Sum() needs to be moved each time.

我想这应该更简单一些。如果在pivot表的值中删除“Time”，然后将“Order”从列移动到值，并使用不同的计数，如本例所示。它应该计算每个日期的员工数量，以确定不需要的总数。如果你把它放大。假设是50个日期，那么每次都需要移动=Sum()。

#1