如何用QUERY函数将唯一字符串合并至单独列中？

数据背景 #

工作表CalendarEvents的内容如下：

eventId  name            startDate        endDate            guest
A        Bob             10/7/2024 18:00  10/7/2024 19:00    bob@world.com       
B        Mike            10/9/2024 18:00  10/9/2024 19:00    mike@world.com      
C        Bob             10/9/2024 19:00  10/9/2024 20:00    bob@world.com       
D        Bob             10/9/2024 20:00  10/9/2024 21:00    bob@world.com       
E        Cancelled Mike  10/11/2024 16:00 10/11/2024 17:00   mike@world.com      
F        Other event     10/11/2024 16:00 10/11/2024 17:00

已实现部分 #

通过以下公式完成按guest分组、统计事件数（生成guest和eventCount两列）：

=QUERY(CalendarEvents!A5:Z, "SELECT E, count(E) GROUP BY E",1)

遇到的问题 #

尝试添加第三列（聚合每个guest对应的唯一事件标题）时，使用公式：

=QUERY(CalendarEvents!A5:Z, "SELECT E, count(E), STRING_AGG(DISTINCT B, ', ') GROUP BY E",1)

触发解析错误：

Error: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 31. Was expecting one of:

解决方案 #

Google Sheets的QUERY函数基于Google Visualization API Query Language，并不支持STRING_AGG这类文本聚合语法。要实现需求，可结合Sheets原生函数组合完成：

方法1：组合QUERY与VLOOKUP、TEXTJOIN

先通过QUERY生成分组后的guest和事件数：

=ARRAYFORMULA(QUERY({CalendarEvents!E5:E, CalendarEvents!B5:B}, "SELECT Col1, count(Col1) GROUP BY Col1 LABEL count(Col1) 'eventCount'", 1))

再添加第三列（唯一事件标题）：

=ARRAYFORMULA(IFNA(VLOOKUP(QUERY(CalendarEvents!E5:E, "SELECT E GROUP BY E", 1), {CalendarEvents!E5:E, BYROW(CalendarEvents!E5:E, LAMBDA(g, TEXTJOIN(", ", TRUE, UNIQUE(FILTER(CalendarEvents!B5:B, CalendarEvents!E5:E=g)))))}, 2, FALSE)))

方法2：更简洁的LET+BYROW组合公式

使用LET函数封装变量，逻辑更清晰：

=ARRAYFORMULA(
  LET(
    unique_guests, UNIQUE(CalendarEvents!E5:E),
    event_counts, COUNTIF(CalendarEvents!E5:E, unique_guests),
    unique_titles, BYROW(unique_guests, LAMBDA(guest, TEXTJOIN(", ", TRUE, UNIQUE(FILTER(CalendarEvents!B5:B, CalendarEvents!E5:E=guest))))),
    HSTACK(unique_guests, event_counts, unique_titles)
  )
)

这个公式会一次性生成三列：guest、eventCount、uniqueEventTitles，无需拆分步骤。

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