如何在Google Sheets生成无重复排列？解决数组过大问题

为游戏《真女神转生3》生成怪物融合链的无重复排列，需求如下：

• 初始有7个独特怪物，游戏提供8个怪物栏位
• 融合规则：先生成唯一配对（A+B），排除B+A这类反向配对（因融合结果相同），再依次添加C、D等怪物，直到最终只剩单个融合产物
• 当前问题：使用现有Google Sheets公式生成8个怪物的排列时，触发**“生成的数组过大”**错误
• 现有公式：

=iferror(if(counta($A$2:$A$13)>=2,arrayformula(query(query(split(flatten(flatten(flatten(flatten(flatten(flatten(
filter($F$2:$F,$F$2:$F<>"")&if(counta($A$2:$A$13)>=3,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=4,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=5,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=6,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=7,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=8,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))),","),
"where Col1 <> Col2"&
if(counta($A$2:$A$13)>=3," and Col1 <> Col3 and Col2 <> Col3"&
if(counta($A$2:$A$13)>=4," and Col1 <> Col4 and Col2 <> Col4 and Col3 <> Col4"&
if(counta($A$2:$A$13)>=5," and Col1 <> Col5 and Col2 <> Col5 and Col3 <> Col5 and Col4 <> Col5"&
if(counta($A$2:$A$13)>=6," and Col1 <> Col6 and Col2 <> Col6 and Col3 <> Col6 and Col4 <> Col6 and Col5 <> Col6"&
if(counta($A$2:$A$13)>=7," and Col1 <> Col7 and Col2 <> Col7 and Col3 <> Col7 and Col4 <> Col7 and Col5 <> Col7 and Col6 <> Col7"&
if(counta($A$2:$A$13)>=8," and Col1 <> Col8 and Col2 <> Col8 and Col3 <> Col8 and Col4 <> Col8 and Col5 <> Col8 and Col6 <> Col8 and Col7 <> Col8",),),),),),),0),"where Col1 <>''",0)),"not enough data"),)

• 核心诉求：需要更高效的Google Sheets公式，解决数组过大问题，支持至少8个、甚至12个元素的无重复排列生成。

以下两种方案针对内存占用和排列生成效率做了优化，可解决数组过大问题：

方案1：递归式全排列生成（支持≤10个元素） #

使用LAMBDA递归逻辑逐步构建排列，同时针对融合规则过滤反向配对，内存占用远低于原公式：

=LET(
  items, FILTER(A2:A13, A2:A13<>""),
  n, COUNTA(items),
  // 递归生成无重复排列
  generatePerms, LAMBDA(arr, k, IF(k=1, arr, LET(
    prevPerms, generatePerms(arr, k-1),
    // 为每个已有排列匹配未使用的元素
    newPerms, FLATTEN(prevPerms & "," & TRANSPOSE(FILTER(arr, NOT(REGEXMATCH(FLATTEN(prevPerms), arr))))),
    IFERROR(newPerms, prevPerms)
  ))),
  rawPerms, generatePerms(items, n),
  // 过滤A+B的反向配对B+A（仅保留A文本排序小于B的组合）
  filteredPairs, IF(n=2, FILTER(rawPerms, INDEX(SPLIT(rawPerms, ","),,1) < INDEX(SPLIT(rawPerms, ","),,2)), rawPerms),
  filteredPairs
)

优化点 #

• 递归生成避免多层FLATTEN导致的数组爆炸
• 动态过滤反向配对，减少无效排列数量
• 自动适配A列怪物数量，无需手动修改公式逻辑

方案2：分批次生成排列（支持≤12个元素） #

当元素数量超过10个时，全排列总数会超出Google Sheets单元格行数限制，使用此方案分批次导出：

=LET(
  items, FILTER(A2:A13, A2:A13<>""),
  batchSize, 10000, // 每批次生成的排列数量
  startRow, 1, // 起始排列序号
  totalPerms, FACT(COUNTA(items)),
  endRow, MIN(startRow + batchSize - 1, totalPerms),
  // 根据序号生成对应排列
  permFromIndex, LAMBDA(idx, LET(
    arr, items,
    res, "",
    FOR(i, COUNTA(arr), 1, -1, LET(
      factVal, FACT(i-1),
      pos, INT((idx-1)/factVal) + 1,
      res, res & IF(res="", "", ",") & INDEX(arr, pos),
      arr, FILTER(arr, arr<>INDEX(arr, pos)),
      idx, MOD(idx-1, factVal) + 1
    )),
    res
  )),
  // 生成当前批次的排列
  BYROW(SEQUENCE(endRow - startRow + 1, 1, startRow), permFromIndex)
)

使用方法 #

修改startRow参数切换批次，例如第一批次用startRow=1，第二批次用startRow=10001，以此类推。

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