Excel：总和列，用列和总行乘
我们正在使用Excel在团队中进行能力计划。我们有一堆填充桌子的人（P1-P3）。如果他们整天工作（1），他们每天都放置，工作半天（0.5）或休息（0）。有些人承担额外的责任，并且在我们的项目上没有100％的工作。他们的工作量是项目单元（0-1）中的项目的百分比。
我正在寻找一种计算每周可用容量的方法：对于每个星期（周一至周五），对于每个人，总计该一周的容量，将其与他们的％工作相乘，然后将其累积在所有人上。
我最初的方法是为每周创建一个隐藏的列，让我总结每个人的原始容量，然后使用sumproduct将这个单元格乘以％的工作，然后将其与所有人总结。但是，人们在更改计划时仍将大规模删除列删除，也删除了隐藏的列。
是否有一种方法可以在一个步骤中进行类似Sumproduct的操作，但是我没有选择两列，而是选择了一列和其他列的总和：％工作 *（m+t+t+w+t+t+f）
示例示例< /strong>从图片中：P1在第1周有5个完整的“原始”工作日，P2有4.5“ RAW”天和P3 4“ RAW”天。我想将5天乘以P1的1（= 5个生效天），4.5，为1（= 4.5天）和4个带0.5（= 2个生效的天）的4个，根据％工作列，然后添加然后添加总计总计11.5。
Excel：Microsoft Apps 365用于企业/V 2108
示例表
we are using excel to do our capacity planning in the team. We have a bunch of people (P1-P3) that fill in a table. For each day they put if they work the entire day (1), work half-day (0.5) or are off (0). Some people have additional responsibilities and are not working 100% on our project. The percentage how much they work for are project is set in the %Work cell (0-1).
I'm looking for a way to calculate the total available capacity per week: For each week (mon-fri), for each person sum up the capacity for that week, multiply it with their %Work, then accumulate this over all people.
My initial approach was to create a hidden column for each week in which I sum up the raw capacity for each person, then use the sumproduct to multiply this cell with the %work and sum it up over all people. However, people kept mass deleting columns when they changed their plan, deleting the hidden columns as well.
Is there a way to do something like sumproduct in one step, but instead of choosing two columns, I choose one column and the sum of other columns: %Work * (M+T+W+T+F)
Example from the picture: P1 has 5 full "raw" working days in week 1, P2 has 4.5 "raw" days and P3 4 "raw" days. I want to multiply the 5 days with the 1 of P1 (=5 effective days), the 4.5 with 1 (=4.5 effective days) and the 4 with 0.5 (=2 effective days), according to the %Work column, add then sum up to a total of 11.5.
excel: Microsoft apps 365 for enterprise / V 2108
Thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您只需要将百分比列乘以天矩阵并添加结果即可。如果锚定百分比列，则将其应用于下一周，当您通过以下方面复制公式：
You just need to multiply the percentage column by the days matrix and add the results up. If you anchor the percentage column, it will be applied to the following week as you copy the formula across: