如何用SUMPRODUCT或其他函数按机器代码计算员工分配工时总和？

问题场景 #

我的Excel工作簿包含三张工作表，结构如下：

• Sheet1：A列为员工姓名（文本），B-J列为各机器（Machine1、Machine2…），每行是对应员工分配到各机器的工时占比（如10%、14%）
• Sheet2：A列为员工姓名（文本），B列为总工时，每日更新员工当日实际工时
• Sheet3：需要汇总所有Sheet2员工分配到各机器的总工时，表头为各机器名称

示例数据 #

Sheet1

Sheet2

Sheet3（目标结果）

原公式错误分析 #

我之前尝试的SUMPRODUCT公式出现#N/A错误，问题出在这几点：

• MATCH函数里用了文本"B1"，而非引用Sheet3的表头单元格，无法动态匹配当前机器列
• INDEX函数的区域包含了Sheet1的表头行（$B$1:$O$11），应该从数据行（$B$2:$O$11）开始
• 未处理姓名匹配时的不兼容情况，若两个表的姓名行数或顺序不一致，会导致数组运算出错

正确公式写法 #

在Sheet3的Machine1单元格（如B2）输入以下公式，向右拖动即可批量计算所有机器的总工时：

=SUMPRODUCT(VLOOKUP(Sheet2!$A$2:$A$11, Sheet1!$A$2:$C$11, MATCH(Sheet3!B$1, Sheet1!$B$1:$C$1, 0), FALSE)/100 * Sheet2!$B$2:$B$11)

公式解释 #

1. MATCH(Sheet3!B$1, Sheet1!$B$1:$C$1, 0)：找到当前机器在Sheet1中的列索引
2. VLOOKUP(...)：根据Sheet2的员工姓名，在Sheet1中匹配对应行的工时占比
3. /100：将百分比转换为小数
4. * Sheet2!$B$2:$B$11：计算单个员工分配到该机器的工时
5. SUMPRODUCT：汇总所有员工的对应工时

另一种SUMPRODUCT数组写法 #

如果更倾向于纯数组匹配逻辑，可使用：

=SUMPRODUCT((Sheet1!$A$2:$A$11=Sheet2!$A$2:$A$11)*(INDEX(Sheet1!$B$2:$C$11,,MATCH(Sheet3!B$1,Sheet1!$B$1:$C$1,0))/100)*Sheet2!$B$2:$B$11)

内容的提问来源于stack exchange，提问作者valkehry