如何在Google Sheets中生成两列分号分隔值的全组合并保留其他列

需求说明 #

处理Google Sheets中的术语表：

• A列：英文术语（分号分隔同义词）
• B列：葡萄牙语术语（分号分隔同义词）
• C-E列：关联列（注释、验证来源、所属领域）
  目标是生成每行A、B列术语的笛卡尔积全组合，同时保留对应行的所有关联列内容。

示例输入 #

预期输出 #

可行公式 #

将以下公式输入到空白单元格（如G2）即可实现需求：

=LET(
    data, A2:E,
    split_en, LAMBDA(x, TRIM(SPLIT(x, ";"))),
    split_pt, LAMBDA(x, TRIM(SPLIT(x, ";"))),
    expand_row, LAMBDA(row,
        LET(
            en_list, split_en(INDEX(row, 1)),
            pt_list, split_pt(INDEX(row, 2)),
            meta, INDEX(row, 3, 1):INDEX(row, 5, 1),
            en_expand, TOCOL(en_list, 1),
            pt_expand, TOCOL(IFERROR(pt_list/SEQUENCE(ROWS(en_list))), 1),
            meta_expand, TOCOL(IFERROR(meta/SEQUENCE(ROWS(en_list)*ROWS(pt_list))), 1),
            HSTACK(en_expand, pt_expand, meta_expand)
        )
    ),
    result, REDUCE("", SEQUENCE(ROWS(data)), LAMBDA(acc, i, VSTACK(acc, expand_row(INDEX(data, i, 1):INDEX(data, i, 5))))),
    FILTER(result, INDEX(result, ,1)<>"")
)

公式说明 #

1. data：定义待处理的数据源范围（包含术语列和关联列）
2. split_en/split_pt：拆分分号分隔的同义词，并自动去除内容两侧空格
3. expand_row：单行数据处理逻辑：
   • 拆分当前行的英、葡语同义词列表
   • 提取当前行的关联列内容
   • 生成术语的笛卡尔积组合，同时将关联列内容按组合数量重复，确保对应关系
   • 合并展开后的术语列与关联列
4. result：遍历所有行，将每行的展开结果纵向堆叠
5. FILTER：过滤掉可能产生的空行

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