![Power Pivot - 每项平均时间](https://linux22.com/image/1420404/Power%20Pivot%20-%20%E6%AF%8F%E9%A1%B9%E5%B9%B3%E5%9D%87%E6%97%B6%E9%97%B4.png)
我正在尝试计算平均制作每件物品需要多长时间。
以下是数据表:
Date Item Quantity Operator
01/01/2014 Item1 3 John
01/01/2014 Item2 5 John
02/01/2014 Item1 7 Bob
02/01/2014 Item2 4 John
03/01/2014 Item1 2 Bob
07/01/2014 Item2 3 John
2014 年 1 月 1 日,约翰制作了 3 个Item 1
和 5 个Item 2
。如果我们只有前两行,我们可以猜测制作 需要 0.375 天Item 1
,制作 需要 0.625 天Item 2
。
我希望能够使用所有数据来计算平均值,并考虑到显然处理不同项目的操作员。
谢谢
答案1
使用 Excel解算器我能够找到与数据最匹配(就平方误差而言)的每件产品的生产时间。为此,请按如下方式准备工作表(假设您提供的数据在 AD 列中):
- 在 J1 中输入标签“每天工作时间:”
- 在 K1 中输入每天的工作小时数(例如 8)。
- 在 J3 中输入标签“每件产品的生产时间:”
- 在 J4 中输入标签“Item1”,在 J5 中输入标签“Item2”
- 在 K4 和 K5 中设置一些初始值(例如 1 和 2)。
- 在 E 列中使用 E2 中的公式添加“生产时间”:
=VLOOKUP(B2;$J$4:$K$5;2;FALSE)*C2
并将其复制粘贴下来,计算每件物品的生产时间乘以物品数量。 - 在 F 列中,使用 F2: 中的表格添加“工人-天数 ID”,
A2&D2
然后将其复制粘贴下来,这将为工人工作的每一天创建一个唯一的 ID(我们需要它在 G 列中添加每天的工作时间)。 - 在 G 列中使用 G2 中的公式添加“每天工作时间”:
SUMIF($F$2:$F$7;F2;$E$2:$E$7)
- 在 H 列中使用 H2 中的公式添加“工作时间偏差平方”:
(G2-$K$1)^2
- 最后,通过输入 H8 来添加 H 列的总计:
SUM(H2:H7)
现在启动求解器H8
并通过改变来最小化K4:K5
。这本质上是试图找到每个项目的最佳生产时间,以解释每天的产量。
从数学上来说,存在一些警告和陷阱,但它(或多或少)回答了你的问题。
答案2
我开始根据您的示例数据寻找 Power Pivot 解决方案,但很快意识到您没有提供足够的信息来计算结果。我认为前两行表明每件商品需要 0.125 天(即任何商品的数量为 1)。
一旦考虑了前 4 行,加上间隙和其他运算符,它应该如何工作?那么几天中的间隙(例如 4 日、5 日、6 日)呢?请添加您对所有场景的预期结果并解释您的计算方法。
无论如何,我已经构建了一个工作原型来开始讨论。您可以使用我的演示文件“Power Pivot demo - average time per item.xlsx”在我的 One Drive 中试用它:
https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398
我的 DAX 计算是:
每件商品的平均天数 := DISTINCTCOUNT( [日期] ) / SUM( [数量] )