如何用Excel纯公式对二维数组每行排序，将排列转组合？

问题背景 #

现有公式可生成cols（本文为1至6）的无重复排列二维数组，但需求是将排列转为组合（如[1,2,4]与[4,2,1]视为同一组合）。思路是先对每行排序再过滤重复行，但最终BYROW(perms,LAMBDA(row,SORT(row,,,TRUE)))部分出现#CALC!错误。

期望效果 #

处理前的部分数据：

| 1 |   |  |  |  |  |
| 2 |   |  |  |  |  |
| 3 |   |  |  |  |  |
| 4 |   |  |  |  |  |
| 5 |   |  |  |  |  |
| 6 |   |  |  |  |  |
| 1 | 2 |  |  |  |  |
| 1 | 3 |  |  |  |  |
| 1 | 4 |  |  |  |  |
| 1 | 5 |  |  |  |  |
| 1 | 6 |  |  |  |  |
| 2 | 1 |  |  |  |  |

处理后需将重复排列转为统一排序的组合：

| 1 |   |  |  |  |  |
| 2 |   |  |  |  |  |
| 3 |   |  |  |  |  |
| 4 |   |  |  |  |  |
| 5 |   |  |  |  |  |
| 6 |   |  |  |  |  |
| 1 | 2 |  |  |  |  |
| 1 | 3 |  |  |  |  |
| 1 | 4 |  |  |  |  |
| 1 | 5 |  |  |  |  |
| 1 | 6 |  |  |  |  |
| 1 | 2 |  |  |  |  |

当前使用公式 #

=LET(
  cols, SEQUENCE(1, COLUMNS(TableStu)),
  firstperm, VALUE(CONCAT(cols)),
  lastperm, VALUE(CONCAT(SORT(cols,,-1,TRUE))),
  diff, (lastperm-firstperm)+1,
  list, SEQUENCE(diff, 1, firstperm),
  wanted, FILTER(list, LEN(REDUCE(list, cols, LAMBDA(m,j, SUBSTITUTE(m,j,"")))) = 0),
  all, FILTER(wanted, wanted <> "", ""),
  repeaters, UNIQUE(TOCOL(IF(LEN(all)-LEN(SUBSTITUTE(all,cols,""))=0, all, ""))),
  repsnoblanks, FILTER(repeaters, LEN(repeaters) > 0),
  allnoreps, FILTER(all, NOT(ISNUMBER(XMATCH(all, repsnoblanks)))),
  allAndLess, VALUE(TOCOL(VSTACK(allnoreps, LEFT(allnoreps, SEQUENCE(1, 4))))),
  nums, UNIQUE(SORT(FILTER(allAndLess, NOT(ISNA(allAndLess))))),
  starts, IF(REDUCE(cols, MAX(cols), LAMBDA(x,y, cols <= MAX(cols))), cols, ""),
  perms, IFERROR(VALUE(MID(nums, starts, 1)), ""),
  BYROW(perms,LAMBDA(row,SORT(row,,,TRUE)))
)

已尝试的调整 #

将perms中的IFERROR(VALUE(MID(nums, starts, 1)), "")改为IFERROR(VALUE(MID(nums, starts, 1)), 0)或IFERROR(VALUE(MID(nums, starts, 1)), 9999)，仍出现#CALC!错误。补充测试：单独使用SORT(perms,,,TRUE)无效果，怀疑BYROW仅能处理单列，询问合适的替代函数。

需求 #

需要纯Excel公式解决方案（无法使用Power Query或VBA），也欢迎更简洁高效的公式方案。

解决方案 #

1. 修正原公式的错误 #

原错误原因：perms数组中混合了数字和空文本""，SORT无法处理混合数据类型导致报错。解决思路是用一个大于cols最大值的数字占位空值，排序后再换回空文本：

=LET(
  cols, SEQUENCE(1, COLUMNS(TableStu)),
  max_col, MAX(cols),
  firstperm, VALUE(CONCAT(cols)),
  lastperm, VALUE(CONCAT(SORT(cols,,-1,TRUE))),
  diff, (lastperm-firstperm)+1,
  list, SEQUENCE(diff, 1, firstperm),
  wanted, FILTER(list, LEN(REDUCE(list, cols, LAMBDA(m,j, SUBSTITUTE(m,j,"")))) = 0),
  all, FILTER(wanted, wanted <> "", ""),
  repeaters, UNIQUE(TOCOL(IF(LEN(all)-LEN(SUBSTITUTE(all,cols,""))=0, all, ""))),
  repsnoblanks, FILTER(repeaters, LEN(repeaters) > 0),
  allnoreps, FILTER(all, NOT(ISNUMBER(XMATCH(all, repsnoblanks)))),
  allAndLess, VALUE(TOCOL(VSTACK(allnoreps, LEFT(allnoreps, SEQUENCE(1, 4))))),
  nums, UNIQUE(SORT(FILTER(allAndLess, NOT(ISNA(allAndLess))))),
  starts, IF(REDUCE(cols, max_col, LAMBDA(x,y, cols <= max_col)), cols, ""),
  // 用max_col+1占位空值，避免数据类型混合
  perms, IFERROR(VALUE(MID(nums, starts, 1)), max_col+1),
  // 每行排序后将占位值换回空文本
  sorted_perms, BYROW(perms,LAMBDA(row,LET(sorted,SORT(row),IF(sorted=max_col+1,"",sorted)))),
  // 过滤重复行得到最终组合
  UNIQUE(sorted_perms)
)

2. 更简洁的直接生成组合公式 #

无需先生成排列再转换，直接用COMBINATIONS生成所有长度的组合，效率更高：

=LET(
    n, COLUMNS(TableStu),
    cols, SEQUENCE(n),
    // 生成1到n的所有组合长度
    lengths, SEQUENCE(n),
    // 堆叠所有长度的组合
    all_combs, TOCOL(REDUCE("", lengths, LAMBDA(a,l, VSTACK(a, COMBINATIONS(cols,l))))),
    // 生成每行对应的组合长度
    row_lens, TOCOL(REDUCE("", lengths, LAMBDA(a,l, VSTACK(a, REPT(l, COMBIN(n,l)))))),
    // 将组合转为固定n列的数组，空值补位
    result, BYROW(all_combs, LAMBDA(r, 
        LET(
            len, XMATCH(r, all_combs, 0, -1),
            IF(SEQUENCE(n)<=len, INDEX(r, SEQUENCE(len)), "")
        )
    )),
    result
)

说明：COMBINATIONS生成的组合本身就是升序排列的，天然符合组合的要求，无需额外排序去重，直接得到所有1元到n元的组合。

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