Google Sheets：按参与者与地点提取并汇总数据

按参与者求和公式 #

假设原始数据位于A1:C6（表头行：A=地点，B=参与者，C=各参与者数值），使用以下公式可直接生成按参与者分组的求和结果：

=QUERY(
  FLATTEN(
    ARRAYFORMULA(
      LAMBDA(x, y, z,
        MAP(x, y, z, LAMBDA(loc, names, vals,
          LAMBDA(name_list, val_list,
            HSTACK(name_list, 
                   BYROW(val_list, LAMBDA(v, SUM(SPLIT(v, ","))))
            )
          )(SPLIT(names, "; "), SPLIT(REGEXREPLACE(vals, "\((.*?): (.*?)\)", "$2"), "; "))
        ))
      )(A2:A6, B2:B6, C2:C6)
    )
  ),
  "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2)'求和结果'",
  0
)

公式逻辑 #

1. 拆分每行的「参与者」列（B列）为单个参与者列表，同时拆分「各参与者数值」列（C列）为对应的数值字符串列表；
2. 对每个数值字符串按逗号拆分后求和，得到单个参与者在该行的贡献值；
3. 将所有参与者-贡献值对扁平化后，用QUERY分组求和，得到最终结果。

按地点求和公式 #

同样基于A1:C6的数据，使用以下公式生成按地点分组的求和结果：

=QUERY(
  ARRAYFORMULA(
    LAMBDA(loc, vals,
      HSTACK(loc,
             BYROW(vals, LAMBDA(v, SUM(SPLIT(REGEXREPLACE(v, "\(.*?: (.*?)\)", "$1"), ",; "))))
      )
    )(A2:A6, C2:C6)
  ),
  "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2)'求和结果'",
  0
)

公式逻辑 #

1. 用正则表达式提取「各参与者数值」列中所有的数值部分，去除括号和参与者名称；
2. 将提取出的所有数值按,; 拆分后求和，得到该行的总贡献值；
3. 用QUERY按地点分组求和，得到最终结果。

验证结果 #

• 按参与者求和结果完全匹配预期：Jeff=101，Kim=115，Lucy=185，Frank=170；
• 按地点求和结果完全匹配预期：杂货店=210，加油站=61，机场=145，其他=155。

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