求Google Sheets中按拆分字符串分组统计t1行人员金额总和的公式

我有一张表格，需要统计所有标记为t1的行中每位人员的总金额。t1行的“Comments”列格式固定：

• 人员之间的分隔符为", "
• 人员与对应比例的分隔符为" - "

预期生成一张汇总表，列出每位人员及其对应的总金额。

已成功筛选t1行并拆分人员数据，得到如下结果：

Person1 - 50% | John - 50% |               | $100.00
Smith - 10%   | John - 10% | Person1 - 80% | $1,000.00
Smith - 100%  |            |               | $2,000.00

使用的公式：

={FILTER(ARRAYFORMULA(SPLIT(C2:C6, ", ", false)), A2:A6 = "t1"), FILTER(ARRAYFORMULA(B2:B6), A2:A6 = "t1")}

但后续计算人员总金额的步骤遇到瓶颈。

方法1：手动列人员后批量计算 #

若已提前列出所有人员姓名（比如D列），在对应金额单元格（如E2）输入以下公式，下拉填充即可：

=SUM(ARRAYFORMULA(IFERROR(
  (REGEXEXTRACT(FLATTEN(FILTER(SPLIT(C$2:C$6, ", "), A$2:A$6="t1")), "(\d+)%")/100) *
  VLOOKUP(ROW(FILTER(C$2:C$6, A$2:A$6="t1")), FILTER({ROW(B$2:B$6), B$2:B$6}, A$2:A$6="t1"), 2, 0)
  * (REGEXEXTRACT(FLATTEN(FILTER(SPLIT(C$2:C$6, ", "), A$2:A$6="t1")), "^(.*?) - ") = D2)
)))

方法2：QUERY函数自动生成汇总表 #

无需手动输入人员姓名，直接用以下公式生成完整汇总表：

=QUERY(
  FLATTEN(ARRAYFORMULA(
    IF(FILTER(A2:A6, A2:A6="t1")="t1", 
      SPLIT(FILTER(C2:C6, A2:A6="t1"), ", ", false)&"|"&FILTER(B2:B6, A2:A6="t1"), 
      "")
  )),
  "SELECT REGEXEXTRACT(Col1, '^(.*?) - '), SUM(
    (REGEXEXTRACT(Col1, '(\d+)%')/100) * REGEXEXTRACT(Col1, '\|(.*)$')
  ) WHERE Col1 <> '' GROUP BY REGEXEXTRACT(Col1, '^(.*?) - ') LABEL REGEXEXTRACT(Col1, '^(.*?) - ') '人员', SUM(...) '总金额'",
  1
)

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