报告在Excel中估计不佳的用户

Question

我有一个电子表格，对应于用户的条目、他们的估计值和实际值(例如:特定项目的小时数-同样，这只是一个示例)，我们可以用CSV表示如下内容：

User,Estimate,Actual
"User 1",5,5
"User 1",7,7
"User 2",3,3
"User 2",9,8
"User 3",6,7
"User 3",8,7

我正在尝试构建一个关于这些用户的报告，以快速查看哪些用户低估或高估了，因此我创建了一个数据透视表。但是，我不知道如何简单地显示用户在某一点上是否低估了。我试图创建一个像=IF(Estimate > Actual, 1, 0)这样的计算字段，但是这是求和，然后比较Estimate和Actual列，并告诉我“User3”没有高估/低估。

在不向我的数据添加额外字段的情况下，我如何实现这一点？

一个类似的SQL伪查询将是：

SELECT DISTINCT al.User,
(SELECT COUNT(*) FROM ActivityLog AS l2 WHERE l2.User = al.User AND l2.Estimate > l2.Actual) AS Overestimates
FROM ActivityLog AS al

编辑：

我还在做这个工作，目前已经在边上的一些单元格中创建了一个静态用户列表，并为他们提供了数组公式：{=SUM(IF((A$2:A20 = F6)*(B$2:B20 > C$2:C20), 1, 0))}和{=SUM(IF((A$2:A20 = F6)*(B$2:B20 < C$2:C20), 1, 0))} (如果我在F6中有用户名的话)。

主要是，我希望在用户列表可以从主数据动态填充的情况下执行此操作。

Answer 1

透视表中的计算字段很糟糕。我会去掉数据透视表，用公式来做。在H15中启动一个唯一的用户列表，并在I15中输入

{=MAX(($A$2:$A$7=H16)*($B$2:$B$7-$C$2:$C$7<>0))}

已输入数组。如果估计过高或估计过低，则返回1；如果从未估计过，则返回0。缺点是您不能像透视表一样“刷新”它，因此您必须确保您的唯一用户列表始终是准确的。

如果这样做的缺点太大，我认为您需要在源数据中添加一列。具体来说

=ABS(B2-C2)

并将其添加到您的数据透视表。它将显示为零，表示永不超过/低于，否则为非零。

Answer 2

为actual-estimate添加一列

然后汇总最小值、最大值和平均值。(或stddev)

Answer 3

你知道你应该确保所有的估计都在相同的范围内吗？较小的数字可以估计得更好(当谈论小时时)。