Excel动态汇总多项目数据：先运算再求和的公式实现

场景适配说明 #

假设你的每个项目表格结构一致：每个项目包含Units（数量行）和对应的材料单位成本/处理成本/单位利润行，且同一项目内这两行的相对位置固定（比如Units在某行，对应的单位值在其下第2行，如你示例中的B2对应B4、B10对应B12）。

方案1：兼容Excel 2019及更早版本（非动态数组） #

使用SUMPRODUCT结合MATCH和OFFSET实现批量匹配计算，无需硬编码位置：

材料总成本公式

=SUMPRODUCT((A$1:A$100="Units")*B$1:B$100*OFFSET(B$1:B$100,2,0))

• 逻辑拆解：
  1. (A$1:A$100="Units")：遍历A列，标记所有Units所在行的位置（返回由1和0组成的数组）
  2. B$1:B$100：提取所有Units对应的数量值
  3. OFFSET(B$1:B$100,2,0)：将数量行向下偏移2行，得到对应项目的材料单位成本值（可根据实际相对位置调整偏移量，比如单位成本在Units下第3行就改成3）
  4. SUMPRODUCT自动将对应位置的数量×单位成本后求和，忽略非Units行的无效值

处理成本/总利润公式

只需调整OFFSET的偏移量或匹配标签：

=SUMPRODUCT((A$1:A$100="处理单位成本")*OFFSET(B$1:B$100,-2,0)*B$1:B$100)

（示例假设"处理单位成本"在Units下第2行，用OFFSET(..., -2,0)反向偏移获取对应Units值）

方案2：Excel 365/2021（动态数组推荐） #

用FILTER函数精准提取对应值，公式更直观：

材料总成本公式

=SUM(FILTER(B:B,A:A="Units")*FILTER(B:B,A:A="材料单位成本"))

• 逻辑拆解：
  1. FILTER(B:B,A:A="Units")：提取所有Units对应的数量数组
  2. FILTER(B:B,A:A="材料单位成本")：提取所有项目的材料单位成本数组（需保证两个数组顺序一一对应，即每个项目的Units和单位成本在结果中位置一致）
  3. SUM对两个数组的对应元素相乘后求和

关键注意事项 #

• 可将公式中的范围（如A$1:A$100）改成实际数据的最大范围，或直接用整列（如A:A），Excel会自动忽略空行
• 确保每个项目的标签（如"Units"、"材料单位成本"）完全一致，避免大小写或空格导致匹配失败
• 项目间有空行不影响公式生效，SUMPRODUCT和FILTER会自动跳过空值

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