Google Sheets带位置匹配的SUMPRODUCT函数需求及问题

问题描述 #

现有如下表格：

需要实现类似=SUMPRODUCT(A2:A="db 05";B2:B;C2:C)的求和功能，但需按位置匹配逻辑计算：

• 第一行："db 05"在第1位，取比例列第1个值0,6，计算34140*0,6=20484
• 第二行：仅含"db 05"，直接用净额×比例（或净额×1）
• 第三行："db 05"在第3位，取比例列第3个值0,2，计算10000*0,2=2000

此前尝试的公式无法逐行正确匹配对应位置的比例值，需解决该问题。

解决方案（Google Sheets） #

核心问题是原公式中SPLIT仅针对单个单元格，无法适配SUMPRODUCT的整列数组运算，改用BYROW逐行处理即可解决：

=SUM(BYROW(A2:B4&C2:C, LAMBDA(row, 
  LET(
    parts, SPLIT(INDEX(row,1), ";"),
    amount, INDEX(row,2),
    ratios, SPLIT(INDEX(row,3), ";"),
    pos, MATCH("db 05", parts, 0),
    ratio, IFERROR(INDEX(ratios, pos), 1),
    amount * ratio
  )
)))

公式说明：

• BYROW(..., LAMBDA(row,...))：逐行遍历目标数据区域
• LET函数定义变量简化逻辑：
  • parts：拆分当前行A列的"部分"内容为数组
  • amount：当前行B列的净额
  • ratios：拆分当前行C列的"比例"内容为数组
  • pos：定位"db 05"在parts数组中的位置
  • ratio：根据位置取对应比例，找不到则默认取1（适配仅含"db 05"的行）
• 最后用SUM汇总所有行的计算结果

解决方案（Excel 365/2021） #

Excel中用TEXTSPLIT替代SPLIT，同时需处理逗号格式的比例为小数点：

=SUM(BYROW(A2:B4&C2:C, LAMBDA(row, 
  LET(
    parts, TEXTSPLIT(INDEX(row,1), ";"),
    amount, INDEX(row,2),
    ratios, TEXTSPLIT(INDEX(row,3), ";"),
    pos, MATCH("db 05", parts, 0),
    ratio, IFERROR(INDEX(ratios, pos), 1),
    amount * VALUE(SUBSTITUTE(ratio, ",", "."))
  )
)))

原公式失效原因 #

你之前的公式中，SPLIT(A2; ";")仅针对单个单元格A2，无法自动扩展到A2:A的所有行，SUMPRODUCT的数组运算会出现维度不匹配，导致位置匹配错误。BYROW逐行处理可彻底解决这个维度问题。

内容的提问来源于stack exchange，提问作者Fernando Arns Derg