Google Sheets：如何将同书名的卷号合并至单元格而非仅统计数量

需求 #

已实现合并重复书名、去除后缀，但仅能统计卷数，需修改公式将对应卷号以逗号分隔的列表形式展示。

现有数据 #

现有公式 #

=ARRAYFORMULA(((QUERY({QUERY(Sheet1!A:M, "select A, C, J where A is not null"), REGEXEXTRACT(QUERY(Sheet1!A:M, "select A where A is not null"), "(.*),")}, "select Col4, Count(Col4), Col2 where Col4 is not null and Col3 = 'Y' group by Col4, Col2 label Col4 'Book Name', Count(Col4) 'Volumes'"))))

期望效果 #

修改后的公式（新版Google Sheets可用） #

=ARRAYFORMULA(
  LET(
    raw_data, FILTER(Sheet1!A:C, Sheet1!A:A<>"", Sheet1!C:C="Y"),
    clean_title, IFERROR(REGEXEXTRACT(INDEX(raw_data,,1), "^(.*), Vol\.? "), INDEX(raw_data,,1)),
    volume_num, IFERROR(REGEXEXTRACT(INDEX(raw_data,,1), "Vol\.? (\d+)"), ""),
    GROUPBY(clean_title, volume_num, LAMBDA(vals, TEXTJOIN(", ", TRUE, vals)), 0, 1)
  )
)

公式逻辑说明 #

1. 筛选有效数据：通过FILTER获取非空书名且Sheet1!C:C="Y"的行（对应原公式的Col3='Y'条件）
2. 提取干净书名：用正则表达式去掉书名中", Vol. X"或", Vol X"后缀，无卷号的书名直接保留原内容
3. 提取卷号：匹配并提取卷号数字，无卷号则返回空值
4. 分组合并卷号：用GROUPBY按干净书名分组，通过TEXTJOIN将同书名的卷号以逗号分隔合并成列表

兼容旧版Sheets的替代公式 #

如果无法使用GROUPBY函数，可使用以下基于QUERY和BYROW的方案：

=ARRAYFORMULA(
  LET(
    raw_data, FILTER(Sheet1!A:C, Sheet1!A:A<>"", Sheet1!C:C="Y"),
    clean_title, IFERROR(REGEXEXTRACT(INDEX(raw_data,,1), "^(.*), Vol\.? "), INDEX(raw_data,,1)),
    volume_num, IFERROR(REGEXEXTRACT(INDEX(raw_data,,1), "Vol\.? (\d+)"), ""),
    unique_titles, UNIQUE(clean_title),
    BYROW(unique_titles, LAMBDA(title, {
      title,
      TEXTJOIN(", ", TRUE, FILTER(volume_num, clean_title=title))
    }))
  )
)

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