请求编写Google Sheets公式/代码生成输入与模板全组合结果

问题概述 #

需要基于多组输入列（自动忽略空白行），结合指定模板生成所有可能的组合结果，此前尝试数组公式未得到完整输出。

解决方案1：数组公式（适合组数较少的场景） #

假设输入列分别为：

• 组1：A2:A（颜色）
• 组2：B2:B（尺寸）
• 组3：C2:C（材质）
  模板放在E1，格式为"{0}-{1}-{2}"（{0}对应组1，{1}对应组2，{2}对应组3）

使用以下公式生成所有组合：

=ARRAYFORMULA(
  LET(
    // 提取每组非空白数据
    group1, FILTER(A2:A, A2:A<>""),
    group2, FILTER(B2:B, B2:B<>""),
    group3, FILTER(C2:C, C2:C<>""),
    // 生成笛卡尔积并拼接为分隔字符串
    combos, FLATTEN(group1 & "|" & TRANSPOSE(group2) & "|" & TRANSPOSE(TRANSPOSE(group3))),
    // 拆分组合并替换模板占位符
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          E1,
          "{0}", INDEX(SPLIT(combos, "|", 0, 0)),
          "{1}", INDEX(SPLIT(combos, "|", 0, 1)),
          "{2}", INDEX(SPLIT(combos, "|", 0, 2))
        )
      )
    )
  )
)

公式说明 #

1. LET函数简化变量定义，提取每组非空白数据
2. FLATTEN+TRANSPOSE生成多组数据的笛卡尔积
3. SPLIT拆分组合字符串，SUBSTITUTE替换模板中的占位符

解决方案2：Google Apps Script（适合组数较多/复杂模板场景） #

如果输入组数较多，公式会变得繁琐，推荐使用自定义脚本函数。

步骤1：添加脚本 #

1. 打开Google Sheets，点击扩展程序 > Apps 脚本
2. 清空默认代码，粘贴以下脚本：

function GENERATE_COMBINATIONS(inputRanges, template) {
  // 提取每组非空白数据（排除空行）
  const groups = inputRanges.map(range => {
    return range.filter(row => row[0] !== "").map(row => row[0]);
  });
  
  // 生成所有可能的笛卡尔积组合
  const combinations = cartesianProduct(...groups);
  
  // 替换模板中的占位符（{0}对应第一组，{1}对应第二组，以此类推）
  return combinations.map(combination => {
    let result = template;
    combination.forEach((value, index) => {
      result = result.replace(new RegExp(`\\{${index}\\}`, 'g'), value);
    });
    return [result];
  });
}

// 辅助函数：计算多数组的笛卡尔积
function cartesianProduct(...arrays) {
  return arrays.reduce((accumulator, currentArray) => {
    return accumulator.flatMap(accItem => currentArray.map(currItem => [...accItem, currItem]));
  }, [[]]);
}

3. 点击保存，命名项目（比如CombinationGenerator）

步骤2：使用自定义函数 #

在任意单元格输入：

=GENERATE_COMBINATIONS({A2:A, B2:B, C2:C}, "{0} - {1} ({2})")

• {A2:A, B2:B, C2:C}：输入列的范围数组，可根据实际需求添加更多列
• "{0} - {1} ({2})"：模板字符串，{n}对应第n+1组输入的内容

脚本优势 #

• 支持任意数量的输入组
• 模板占位符灵活，支持重复替换（比如模板"{0} {0} {1}"会将第一组内容重复两次）
• 自动忽略所有空白行，无需额外处理

示例验证 #

假设输入数据：

模板："{0} {1} {2}"

生成的结果：

红 S 棉
红 S 麻
红 M 棉
红 M 麻
蓝 S 棉
蓝 S 麻
蓝 M 棉
蓝 M 麻
绿 S 棉
绿 S 麻
绿 M 棉
绿 M 麻

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