Excel多对多关系下基于父子规则的数值汇总技术求助

Excel 365/2021（推荐，支持多层级递归） #

在父子关联表的hours列（如D2单元格）输入以下公式，下拉填充即可，Excel会自动处理递归计算：

=LET(
  target_item, B2,
  target_qty, C2,
  // 计算当前item自身的小时总和
  base_hours, SUM(XLOOKUP(target_item, data!$A$2:$A$8, data!$C$2:$F$8, 0)*target_qty),
  // 筛选当前item作为父项的所有子项（名称+数量）
  child_rows, FILTER($B$2:$C$11, $A$2:$A$11=target_item),
  // 递归计算所有子项的汇总小时数
  child_total, IFERROR(SUM(BYROW(child_rows, LAMBDA(row, INDEX(D:D, MATCH(INDEX(row,1), $B$2:$B$11, 0))))), 0),
  // 返回自身+所有子项的总小时数
  base_hours + child_total
)

首次输入时，Excel会提示启用递归，确认后即可正常使用。该公式支持无限层级的父子嵌套（如cupcake→cake→flour这类多层关联）。

旧版Excel（仅支持单层级子项汇总） #

如果无法升级Excel，可使用数组公式实现单层级子项汇总（多层级需借助VBA）。在D2输入公式后按Ctrl+Shift+Enter：

=SUM(
  // 自身数据计算
  (data!$C$2:$F$99999)*(--(data!$A$2:$A$99999=B2))*C2,
  // 子项数据汇总
  SUMPRODUCT(
    data!$C$2:$F$99999,
    --(data!$A$2:$A$99999=TRANSPOSE(IF($A$2:$A$11=B2, $B$2:$B$11, ""))),
    TRANSPOSE(IF($A$2:$A$11=B2, $C$2:$C$11, 0))
  )
)

公式说明

• 第一部分：和你原公式逻辑一致，计算当前item的小时数
• 第二部分：通过TRANSPOSE获取当前父项的所有子项，匹配data表中的对应数据并乘以子项数量，求和后得到子项的总小时数

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