无需辅助列：Google Sheets实现数组跨另一数组重复的公式方案

问题背景 #

需要将两组数据做笛卡尔积匹配——把一组数据的每一行重复匹配到另一组的所有唯一行中。此前依赖「Current Working Inputs」标签页的「Sequence」辅助列实现，但希望完全用公式自动化，无需辅助列，且适配动态变化的输入数据。

原方案痛点 #

原方案分三步操作，手动工作量大且无法适配动态数据：

• 在「Current Output」F2生成重复列表：

  =SORT(TRANSPOSE(SPLIT(REPT(JOIN(",",'Current Working Inputs'!A2,'Current Working Inputs'!A3,'Current Working Inputs'!A4,'Current Working Inputs'!A5&","),COUNTA(UNIQUE('Current Working Inputs'!C2:C))),",",1)))
  

• 在「Current Output」A2重复「Sequence」列表：

  =ArrayFormula(TRANSPOSE(SPLIT(REPT(CONCATENATE('Current Working Inputs'!C2:C&"~"),COUNTA(UNIQUE(F2:F500))),"~")))
  

• 在「Current Output」B2及下拉用Index(Match)匹配剩余列：

  =index('Current Working Inputs'!D:G,MATCH($A2,'Current Working Inputs'!C:C,0))
  

纯公式自动化解决方案 #

方案1（推荐，需Google Sheets支持CROSSJOIN函数） #

将以下公式直接粘贴到「Current Output」的A2单元格，自动生成所有匹配结果，且适配动态数据：

=ARRAYFORMULA(
  LET(
    // 提取A列非空唯一值（组1）
    group1, UNIQUE(FILTER('Current Working Inputs'!A:A, 'Current Working Inputs'!A:A<>"")),
    // 提取C:G列的唯一行（组2，基于C列唯一标识）
    group2, UNIQUE(FILTER('Current Working Inputs'!C:G, 'Current Working Inputs'!C:C<>"")),
    // 生成两组数据的笛卡尔积
    cross_data, CROSSJOIN(group2, group1),
    // 调整列顺序，匹配原输出结构（C列→D:G→A列）
    result, CHOOSECOLS(cross_data, 1, 2, 3, 4, 5, 6),
    // 按原逻辑排序（先按Sequence升序，再按A列值升序）
    SORT(result, 1, TRUE, 6, TRUE)
  )
)

方案2（兼容旧版Google Sheets，无需CROSSJOIN） #

如果无法使用CROSSJOIN，用索引生成笛卡尔积：

=ARRAYFORMULA(
  LET(
    group1, UNIQUE(FILTER('Current Working Inputs'!A:A, 'Current Working Inputs'!A:A<>"")),
    group2, UNIQUE(FILTER('Current Working Inputs'!C:G, 'Current Working Inputs'!C:C<>"")),
    count_group1, COUNTA(group1),
    count_group2, COUNTA(CHOOSECOLS(group2, 1)),
    // 生成重复的组2索引和组1索引
    idx_group2, FLATTEN(SEQUENCE(count_group2, 1, 1, 1)),
    idx_group1, FLATTEN(TRANSPOSE(SEQUENCE(count_group1, 1, 1, 1))),
    // 按索引提取并组合数据
    result, HSTACK(
      INDEX(group2, idx_group2, 1), INDEX(group2, idx_group2, 2), INDEX(group2, idx_group2, 3), INDEX(group2, idx_group2, 4), INDEX(group2, idx_group2, 5),
      INDEX(group1, idx_group1, 1)
    ),
    SORT(result, 1, TRUE, 6, TRUE)
  )
)

方案优势 #

• 无需任何辅助列，一键生成所有匹配结果
• 输入数据新增、删除或修改时，输出自动更新
• 完全适配动态数据，无需手动调整公式范围

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