在Excel中实现Sprint Planning的团队平台潜在产能计算可行吗？

问题描述 #

我希望用Excel解决Sprint Planning中的产能计算问题，但不确定是否可行：
团队有3-7名成员，每人有固定工时容量（如Person 1：3小时）；每人可操作1个或多个平台（如Person 1可操作Platform 1、Platform 2）；各平台有需求工时（如Platform 1：2小时）。现有3张Excel表格存储这些数据：

1. 人员工时表：记录每位成员的总工时容量
2. 平台需求工时表：记录各平台的需求工时
3. 人员-平台适配表：记录成员是否可操作对应平台（示例如下）

需要计算各平台的潜在可用工时（不绑定具体人员，仅基于产能灵活分配），示例结果如Platform 4：1小时（仅Person 2可操作，其剩余1小时产能）等。纸笔推演逻辑清晰，但无法转化为Excel公式，请问该需求在Excel中是否可行？实现思路是什么？

可行性结论 #

完全可行，Excel的函数组合或Power Query工具可以实现这个需求。

实现思路 #

1. 统一数据规范 #

确保三张表的核心标识（人员名称、平台名称）完全一致，避免因命名差异导致计算错误。

2. 计算成员剩余产能（按需选择） #

如果需要先扣除已分配给平台的需求工时，先计算每位成员的剩余产能：

• 使用SUMPRODUCT函数匹配成员可操作的平台，汇总对应平台的需求工时，再用成员总容量减去该值得到剩余产能。
• 公式示例（假设人员工时表在Sheet1，平台需求表在Sheet2，适配表在Sheet3）：

  =Sheet1!B2 - SUMPRODUCT((Sheet3!B$2:C$3=TRUE)*(Sheet2!B$2:C$2)*(Sheet3!A$2:A$3=Sheet1!A2))
  

  注：需根据实际单元格范围调整参数。

3. 计算平台潜在可用工时 #

针对每个平台，汇总所有可操作该平台的成员的产能（总产能或剩余产能）：

• 使用SUMPRODUCT或SUMIFS函数筛选适配该平台的成员，再求和对应产能。
• 公式示例（计算Platform 1的潜在可用工时，基于总产能）：

  =SUMPRODUCT((Sheet3!B$2:B$3=TRUE)*(Sheet1!B$2:B$3))
  

  若基于剩余产能，将Sheet1!B$2:B$3替换为成员剩余产能的单元格范围即可。

4. 进阶方案：用Power Query简化复杂逻辑 #

如果数据量较大或规则频繁变动，推荐使用Power Query：

• 合并三张表的数据源，筛选出成员与平台的适配关系。
• 添加自定义列计算成员剩余产能，再按平台分组汇总剩余产能，直接生成结构化结果表。

内容的提问来源于stack exchange，提问作者Pedro Moreno Iturbe