合并不同spill数组，计算人员月度工时贡献

核心需求与现有问题 #

• 两个基础表格：
  • 子任务月度工时汇总表：存储各子任务每月平均工时（维度：子任务×月份）
  • 人员子任务贡献表：存储每人在各子任务的工时占比/数值（维度：人员×子任务）
• 目标：生成人员×月份的工时贡献矩阵，计算逻辑为：对每个人员+月份组合，求和「该人员子任务占比 × 对应子任务当月工时」
• 之前尝试的问题：
  • MAKEARRAY公式返回#N/A!：未关联子任务作为中间匹配维度，导致占比与工时无法正确对应
  • SUMPRODUCT报错：直接使用的数组维度不匹配，未对齐子任务维度就进行运算

修正方案（结构化引用+精准匹配） #

第一步：定义清晰的命名区域 #

先给两个表格的关键区域命名（确保子任务列表在两个表格中完全一致）：

• 子任务月度工时表：
  • Task_Names：子任务名称列（如A2:A10，包含所有子任务）
  • Month_Names：月份标题行（如B1:Z1，包含所有统计月份）
  • Task_Month_Data：工时数据区域（如B2:Z10，对应子任务×月份的平均工时）
• 人员子任务贡献表：
  • Person_Names：人员名称列（如P2:P20，包含所有人员）
  • Person_Task_Names：子任务标题行（如Q1:X1，需与Task_Names完全一致）
  • Person_Task_Data：占比数据区域（如Q2:X20，对应人员×子任务的占比）

第二步：使用修正后的MAKEARRAY公式 #

=MAKEARRAY(ROWS(Person_Names), COLUMNS(Month_Names), LAMBDA(r,c,
  SUM(
    INDEX(Person_Task_Data, r, 0) *
    XLOOKUP(Task_Names, Person_Task_Names, INDEX(Task_Month_Data, 0, c))
  )
))

• 逻辑说明：
  1. INDEX(Person_Task_Data, r, 0)：提取第r行（当前人员）的所有子任务占比，形成一维数组
  2. XLOOKUP(...)：将当前月份的子任务工时数据，按照人员贡献表的子任务顺序重新对齐，确保与占比数组维度完全匹配
  3. 数组相乘后求和，得到当前人员当月的总工时贡献

替代方案：BYROW+BYCOL组合（可读性更强） #

=BYROW(Person_Names, LAMBDA(person,
  BYCOL(Month_Names, LAMBDA(month,
    SUMPRODUCT(
      (Person_Task_Names=Task_Names)*1,
      INDEX(Person_Task_Data, MATCH(person, Person_Names, 0), 0),
      INDEX(Task_Month_Data, 0, MATCH(month, Month_Names, 0))
    )
  ))
))

• 逻辑说明：
  1. MATCH函数定位当前人员、月份在对应表格中的位置
  2. (Person_Task_Names=Task_Names)*1生成子任务匹配的布尔数组，确保占比与工时一一对应
  3. SUMPRODUCT自动完成数组相乘与求和

错误原因复盘 #

• 原MAKEARRAY公式缺失子任务维度的匹配逻辑，导致人员占比与月度工时无法建立关联，触发#N/A!
• 原SUMPRODUCT报错是因为直接使用的数组（人员×子任务 vs 子任务×月份）维度不兼容，必须先对齐子任务维度再运算

内容的提问来源于stack exchange，提问作者Mark S.