报告在 Excel 中估算不佳的用户
我有一个电子表格，对应于用户的条目、他们的估计和实际值（例如：特定项目的小时数 - 再说一遍，这只是一个示例），我们可以用 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 code> 列并告诉我“用户 3”不会高估/低估。
如果不向我的数据添加额外的字段，我该如何实现这一目标？
类似的 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 中有用户名）。
主要是，我想做到这一点，用户列表可以从主数据动态填充。
I have a spreadsheet corresponding to entries of a user, their estimation, and the actual value (for example: hours for a particular project - again, this is only an example), which we can represent in CSV like:
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
I'm trying to build a report on these users, to quickly see which users underestimate or overestimate, and so I created a pivot table. But, I can't figure out how to simply show if a user has underestimated at some point. I tried to create a calculated field like =IF(Estimate > Actual, 1, 0), but this sums, then compares the Estimate and Actual columns and tells me that "User 3" doesn't over/underestimate.
Without adding an additional field to my data, how can I accomplish this?
A similar SQL pseudo-query would be:
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
Edit:
I'm still working on this, and currently have created a static list of users in some cells on the side, and have given them the Array Formulas: {=SUM(IF((A$2:A20 = F6)*(B$2:B20 > C$2:C20), 1, 0))} and {=SUM(IF((A$2:A20 = F6)*(B$2:B20 < C$2:C20), 1, 0))} (if I have the user's name in F6).
Mainly, I want to do this where the list of users can populate dynamically from the main data.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
数据透视表中的计算字段很糟糕。我会摆脱数据透视表并用公式来完成它。在 H15 中启动唯一的用户列表，并将其输入到
输入的 I15 数组中。如果他们超过或低于估计值，则返回 1；如果从未超过或低于估计值，则返回 0。缺点是您无法像数据透视表一样“刷新”它，因此您必须确保您的唯一用户列表始终准确。
如果这是一个太大的缺点，我认为您需要在源数据中添加一列。具体
并将其添加到您的数据透视表中。它将显示零，表示永不超过/低于，否则显示非零。
Calculated fields in pivot tables stink. I would get rid of the pivot table and do it with formulas. Start a unique list of users in H15 and enter this in I15
array entered. This will return 1 if they ever over or under estimated and zero if they never did. The downside is that you can't "refresh" it like a pivot table so you have to make sure your unique user list is accurate all the time.
If that's too big of a downside, I think you'll need to add a column to your source data. Specifically
And add that to your pivot table. It will show zero for never over/under and non-zero otherwise.
您知道应该确保估计值都在同一范围内吗？较小的数字可以更好地估计（当谈论小时时）。
You are aware that you should make sure the estimates are all in the same range? Smaller numbers can be estimated better (when talking about hours).
添加实际估计列，
然后汇总这些值的最小值、最大值和平均值。 （或标准设备）
Add a column for actual-estimate
then summarize those values for min max and average. (or stddev)