Google Sheet中用ARRAYFORMULA合并多表关联的推荐计划

需求说明 #

现有4个工作表：

• issues and recommendations：问题与对应推荐列表，单个单元格包含多个推荐项（如B2包含alpha和charlie），同一推荐可对应多个问题
• issues and recommendations split：拆分issues and recommendations的数据，使每个推荐单独成行
• recommendations：推荐的唯一列表，为每个推荐分配唯一ID
• recommendation plans：每个推荐对应多个计划，每个计划单独成行

需要在issues and recommendations的C列（plans列）中，实现：根据B列的推荐项，提取recommendation plans中对应的所有计划，合并为带项目符号的列表放在单个单元格内。

例如问题1的推荐项对应计划合并后为：

• do this
• do that
• do 001
• do 002
• do bingo

实现公式 #

在issues and recommendations的C2单元格输入以下数组公式，自动填充整列：

=ARRAYFORMULA(IF(B2:B="",,MAP(B2:B,LAMBDA(recs, TEXTJOIN(CHAR(10)&"- ", TRUE, "- "&QUERY({recommendations!A:B,recommendation plans!C:C}, "select Col3 where Col2 matches '"&SUBSTITUTE(recs, ", ", "|")&"'", 0))))))

公式解释 #

1. ARRAYFORMULA：实现批量处理B列所有行，无需手动下拉填充
2. IF(B2:B="",, ...)：跳过B列为空的行，避免无效计算
3. MAP(B2:B, LAMBDA(recs, ...))：遍历B列每个单元格的推荐内容（recs代表当前单元格的推荐项集合）
4. SUBSTITUTE(recs, ", ", "|")：将推荐项之间的分隔符从", "替换为|，适配QUERY函数的多值匹配规则
5. QUERY(...)：从recommendations和recommendation plans的合并数据中，筛选出与当前推荐项匹配的所有计划
6. TEXTJOIN(CHAR(10)&"- ", TRUE, "- "&...)：给每个计划添加- 前缀，用换行符分隔并合并为单个单元格内容（TRUE参数自动忽略空值）

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