如何合并含重复Key的两个Google Sheets表格并生成笛卡尔积结果

要实现两个带重复Key的表格按Key做笛卡尔积合并（每个Key对应的所有数据两两组合），可以用以下两种方法解决，替代只能返回首个匹配项的INDEX/MATCH组合：

方法一：QUERY+ARRAYFORMULA组合（直观易理解） #

假设Sheet1的数据范围是A2:B（Key列A，Data列B），Sheet2的数据范围是A2:B，在合并表的A2单元格输入以下公式：

=QUERY(
  ARRAYFORMULA(
    SPLIT(
      FLATTEN(
        Sheet1!A2:A&"|"&Sheet1!B2:B&"|"&Sheet2!A2:A&"|"&Sheet2!B2:B
      ),
      "|"
    )
  ),
  "select Col1, Col2, Col4 where Col1=Col3",
  0
)

公式说明： #

1. 生成全量组合：FLATTEN(Sheet1!A2:A&"|"&Sheet1!B2:B&"|"&Sheet2!A2:A&"|"&Sheet2!B2:B) 将Sheet1的每一行与Sheet2的每一行拼接成Key1|Data1|Key2|Data2格式的字符串，再通过FLATTEN展开为单列。
2. 拆分列：SPLIT将每个字符串拆分为4列：Key1、Data1、Key2、Data2。
3. 筛选并提取目标列：QUERY语句筛选出Key1与Key2相同的行，仅保留Key1、Data1、Data2三列，得到最终的笛卡尔积结果。

方法二：LAMBDA+BYROW组合（高效简洁，适合新版Google Sheets） #

如果你的Google Sheets支持LAMBDA函数（2022年后的版本默认支持），可以用更高效的分组处理方式：

=LET(
  uniqueKeys, UNIQUE({Sheet1!A2:A; Sheet2!A2:A}),
  results, BYROW(uniqueKeys, LAMBDA(key,
    LET(
      s1Data, FILTER(Sheet1!B2:B, Sheet1!A2:A=key),
      s2Data, FILTER(Sheet2!B2:B, Sheet2!A2:A=key),
      IFERROR(ARRAYFORMULA(
        {
          REPT(key, COUNTA(s1Data)*COUNTA(s2Data)),
          FLATTEN(REPT(s1Data&"|", COUNTA(s2Data))),
          FLATTEN(TRANSPOSE(REPT(s2Data&"|", COUNTA(s1Data))))
        }
      ), "")
    )
  )),
  SPLIT(FLATTEN(results), "|")
)

公式说明： #

1. 提取唯一Key：UNIQUE({Sheet1!A2:A; Sheet2!A2:A}) 获取两个表格中所有不重复的Key值。
2. 分组处理每个Key：BYROW遍历每个Key，用FILTER分别取出该Key在Sheet1和Sheet2中的所有Data。
3. 生成笛卡尔积：通过REPT重复Data值对应次数，结合FLATTEN和TRANSPOSE生成该Key下的所有两两组合。
4. 拆分并合并结果：SPLIT拆分拼接的字符串，FLATTEN合并所有Key的结果为最终表格。

注意事项 #

• 调整公式中的数据范围（如Sheet1!A2:A）以匹配你的实际表格结构，若要支持自动新增行，不要指定固定行号（如A2:A6），改用A2:A。
• 如果Data列包含特殊字符|，需要替换公式中的分隔符（比如换成~），避免拆分出错。

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