Google Sheets中实现两列数据笛卡尔积组合（解决字符超限问题）

问题说明 #

A列是动态可变的姓名列表（如Bob Jones、John Doe、Sally Smithers），B列是动态可变的日期列表（如6/1/23、6/2/23、6/3/23），需要生成两列的笛卡尔积——即每个姓名与所有日期逐一配对。原使用公式：

transpose(ARRAYFORMULA(TRIM(SPLIT(CONCATENATE(REPT(A2:A&"@@",count(B:B))), "@@"))))

但列表变长时触发CONCATENATE的文本结果超过50000字符限制，需替换为适配长列表的方案。

可行方案 #

方案1：用MAKEARRAY+INDEX（Google Sheets 2022及以上版本支持） #

纯数组运算，完全规避文本拼接的字符限制，自动适配动态列表：

=MAKEARRAY(COUNTA(A2:A)*COUNTA(B2:B), 2, 
  LAMBDA(r, c, 
    IF(c=1, INDEX(A2:A, CEILING(r/COUNTA(B2:B), 1)), 
      INDEX(B2:B, MOD(r-1, COUNTA(B2:B))+1)
    )
  )
)

• 逻辑：总行数为「姓名数量×日期数量」，第一列通过CEILING(r/日期数量)循环提取姓名，第二列通过MOD(r-1,日期数量)+1循环提取日期。

方案2：用SEQUENCE+INDEX（兼容旧版Google Sheets） #

无需依赖LAMBDA函数，适配更早版本的Google Sheets，同样无字符限制：

=ARRAYFORMULA(
  {
    INDEX(A2:A, CEILING(SEQUENCE(COUNTA(A2:A)*COUNTA(B2:B))/COUNTA(B2:B), 1)),
    INDEX(B2:B, MOD(SEQUENCE(COUNTA(A2:A)*COUNTA(B2:B))-1, COUNTA(B2:B))+1)
  }
)

• 逻辑：用SEQUENCE生成1到总条数的序列，分别计算姓名和日期的索引位置，组合成两列数组。

注意事项 #

• 可根据实际数据起始行调整公式中的A2:A和B2:B（比如表头在A1、B1时，确保从第二行取数据）。
• 两个方案都会自动适配列表行数的变化，无需手动修改公式。

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