Excel数据透视表中的平均值和

Question

我正在测量数据转储的房间利用率(时间使用/可用时间)。每行包含一天的可用时间和用于特定情况的时间。图像是数据的简化版本。

如果你读到黄色和绿色的高光(第1室)：

• 在1号房间，2016年1月1日有200分钟的可用时间。
• 病例1需时60分钟，例2需50分钟。
• 2016年2月1日有500分钟，当天只发生了一次，用了350分钟。

Room 1 utilization = (60 + 50 + 350)/(200 + 500)

将可用时间相加的问题是，它会对2016年1月1日的200分钟进行双倍计数，给出：Utilization = (60+50+350)/(200+200+500)

这些数据中有数百行数据(不同的#'s行将有多个数据转储)，每天都会发生多个情况。我试图使用枢轴表，但无法获得特定房间的“平均值之和”(见图)。我正在使用宏从总计列中提取数字。

这个是可能的吗？你认为另一种获得利用的方法吗？(注:数据中还有许多其他列，如案例开始、案例结束、一周中的日期等，这些列不在计算中使用，而是可用的)。

​

​

Answer 1

对于可用时间列的平均值，您获得300的原因是，总计是基于总体平均值而不是平均数之和的总计。

• 第1室: 200 + 200 + 500 /3= 300
• 第2室: 300 + 300 + 300 /3= 300

我不能对原来的问题发表评论，所以我的解决办法是基于一些假设。

假设1:数据总是分组的。在某一天，第一房间的所有病例都将按顺序排列。

假设2:可用时间列是一整天的单个值，在同一天不会有不同的可用时间。

解决方案：使用列E作为实际可用时间。该列将使用一个公式来确定当前行是否具有与前一行唯一的组合(Date + Room + Available Time)，如果是的话，单元格将包含该行的可用时间。

在E2中使用的公式：

=IF(AND($A1 = $A2, $B1 = $B2, $C1 = $C2), 0, $C2)

根据需要扩展公式，然后将新列包含在PivotTable数据范围内。

最终结果

Answer 2

我通过合并列创建了一个唯一的引用，然后使用sumif/countif/countif。

所以E列中的公式是：=sumif(colB，cellB，ColC)/Countif(colB，cellE)/Countif(colB，cellE)

数据是否有序并不重要。

根据需要扩展公式，然后将新列包含在PivotTable数据范围内。

Answer 3

我推荐的最简单的方法就是这个。

=SUM(H:H)-GETPIVOTDATA("Average of Available Time",$G$3)

第一项与H列之和，第二项减去总价值。这是一个动态的解决方案，并将改变以适应枢轴表的大小。

我的假设是，数据透视表最初放置在单元格G3中。