Google Sheets如何将含FILTER的JOIN公式改写为自动填充数组公式

问题描述 #

我在Google Sheets中搭建了人员信息数据库，存储内容包含人员姓名、所属分组、获得的颜色标签等多列字段，数据样式参考：

我需要新建一个工作表，将每个分组对应的同字段值以逗号分隔合并到单个单元格中，预期效果参考：

目前在输出工作表的B2单元格使用以下公式，可以得到单行的正确结果：

=IF(A2:A<>"",JOIN(", ",FILTER(Sheet1!C2:C,Sheet1!B2:B =A2)),"")

我希望将上述公式改写为ARRAYFORMULA数组公式，实现输入数据库的分组增删时整列自动计算填充。我尝试在B1单元格写入以下公式，但FILTER函数的匹配条件未按行单独生效，直接返回了全量数据的合并结果，无法得到预期效果：

={"Colors"; ARRAYFORMULA(IF(A2:A<>"",JOIN(", ",FILTER(Sheet1!C2:C,Sheet1!B2:B =A2:A)),""))} 

补充说明 #

实际数据库中每个人员对应多列数据（除颜色外还有年龄、身高字段等），需要对每一列字段都实现上述按分组合并的数组计算效果，输出表B列为各分组的颜色合并结果、C列为年龄合并结果、D列为身高合并结果，示例文件可参考：示例表格。

解决方案 #

原公式失效的核心原因是FILTER和JOIN本身不支持在普通ARRAYFORMULA上下文中逐行隔离计算，会直接匹配全量数据返回结果。要实现自动按分组合并、源数据更新时自动重算，有两种可直接落地的方案：

方案1：单公式全表自动生成（无需提前维护分组列） #

直接在输出表的A1单元格粘贴以下公式，即可自动生成表头、去重分组列表、所有字段的分组合并结果，源数据增删人员/调整分组时会自动更新：

=LET(
  raw_data, FILTER(Sheet1!A2:D, Sheet1!A2:A<>""),
  group_col, INDEX(raw_data,,2),
  unique_groups, UNIQUE(group_col),
  merged_res, BYROW(unique_groups, LAMBDA(cur_g,
    HSTACK(
      cur_g,
      JOIN(", ",FILTER(INDEX(raw_data,,3), group_col=cur_g)),
      JOIN(", ",FILTER(INDEX(raw_data,,4), group_col=cur_g)),
      JOIN(", ",FILTER(INDEX(raw_data,,5), group_col=cur_g))
    )
  )),
  VSTACK({"分组","Colors","年龄","身高"}, merged_res)
)

如果后续需要新增合并字段，只需要在HSTACK内按相同格式新增JOIN行，调整INDEX取数列号即可。

方案2：适配已有手动维护分组列的场景 #

如果输出表A列已经提前维护好了分组列表，只需要在B2单元格粘贴以下数组公式，向右拖动填充到需要的列即可自动适配列对应关系，无需逐列修改公式，源数据更新时会自动重算所有结果：

=ARRAYFORMULA(
  IF(A2:A="",,
    BYROW(A2:A, LAMBDA(g,
      IF(g="",,
        JOIN(", ", FILTER(OFFSET(Sheet1!C$1,1,COLUMN(A:A)-1,COUNTA(Sheet1!C:C)-1), Sheet1!B2:B=g))
      )
    ))
  )
)

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