使用 LAMBDA 在 Excel 中生成所有排列

这是一个经常被问到和回答的问题：如何在 Excel 中生成所有排列:

2011 https://stackoverflow.com/questions/5724373/permutations-in-excel 2016 https://stackoverflow.com/questions/41053936/generate-all-permutation-of-multiple-lists-in-excel 2017 https://stackoverflow.com/questions/46101847/excel-permutation-table-without-vba 2017年超级用户 https://superuser.com/questions/1194312/excel-create-table-with-all-combinations/1194380#1194380 2018 https://stackoverflow.com/questions/48651400/how-to-list-all-possible-combinations-of-the-values-in-three-columns-in-excel 2021 https://stackoverflow.com/questions/67905393/how-to-generate-all-combinations-using-5-columns-using-a-formula-in-excel/67926177#67926177

现在在2022 https://stackoverflow.com/questions/71175705/excel-loop-from-list-with-formulas?noredirect=1#comment125834346_71175705在作为重复项关闭之前没有得到答案，这是不幸的，因为LAMBDA确实改变了回答这个问题的方式。

我很少有同样的需求，并且因不得不重新发明一个复杂的轮子而感到沮丧。因此，我将重新提出问题并在下面给出我自己的答案。我不会将任何提交标记为答案，但会邀请好的想法。我确信我自己的方法可以改进。

重申 2022 年问题

我正在尝试仅使用公式在 Excel 中创建循环。我想要实现的目标如下所述。假设我有 3 列作为输入： (i) 国家/地区； (ii) 变量； (iii) 年份。我想从这些输入进行扩展，然后为这些参数分配值。

Inputs:

Outputs:

如何使用 Excel 有效地做到这一点？

扩展 2018 年问题

我有三列，每一列都有不同类型的主数据，如下所示：

现在，我想要这三个单元格的所有可能组合 - 就像

aa kk jj
aa kk ff
aa ll jj
aa ll ff
aa mm jj
...

这可以用公式来完成吗？我发现一个公式有 2 列，但我无法正确地将其扩展到 3 列

包含 2 列的公式：

=IF(ROW()-ROW($G$1)+1>COUNTA($A$2:$A$15)*COUNTA($B$2:$B$4),"",
INDEX($A$2:$A$15,INT((ROW()-ROW($G$1))/COUNTA($B$2:$B$4)+1))&
INDEX($B$2:$B$4,MOD(ROW()-ROW($G$1),COUNTA($B$2:$B$4))+1))

其中 G1 是放置结果值的单元格

共同要求

它们的共同点是它们都试图从一组有序的符号中创建一组有序的排列。它们都恰好需要 3 级符号，但 2018 年的问题是请求帮助从 2 级到 3 级，2021 年的问题是要求从 3 级到 5 级。2022 年的问题只是要求 3 级，但是输出需要是一个表。

如果我们像这样上升到 6 个级别会怎样？

这将生成 1'440 种排列。

制定一个包含任意数量级别（列）的通用公式很困难。只需浏览所提供的答案即可 - 它们每个都需要一些火箭科学，到目前为止，所有解决方案都对符号列的数量进行了硬编码限制。所以LAMBDA能给我们一个通用的解决方案吗？

很酷的问题和脑筋急转弯；我只是对正在使用的东西感到困惑MAKEARRAY():

选项1：

您所说的“超级低效”是在计算行^列时创建排列列表。我认为下面的方法并没有那么低效。让我们想象一下以下情况：

公式为E1:

=LET(A,A1:C3,B,ROWS(A),C,COLUMNS(A),D,B^C,E,UNIQUE(MAKEARRAY(D,C,LAMBDA(rw,cl,INDEX(IF(A="","",A),MOD(CEILING(rw/(D/(B^cl)),1)-1,B)+1,cl)))),FILTER(E,MMULT(--(E<>""),SEQUENCE(C,,,0))=C))

简而言之，它的作用是：

• 变量 A-D 都是辅助变量。
• 然后的想法是只使用简单的INDEX()s 返回所有值。为此，我们需要行和列的正确索引。
• MAKEARRAY()由于 lambda 带来的递归功能，将使计算相对容易。在这些函数内部，其基本数学运算返回这些行和列的正确索引。事实上，列不需要计算，因为我们只是引用“cl”，并且所有行索引的所有计算都是通过MOD(CEILING(rw/(D/(B^cl)),1)-1,B)+1.
• 将上面的结果代入UNIQUE()使用很少的资源来过滤掉任何潜在的重复项and将潜在的空行限制为一个空行。
• FILTER() and MMULT()很好地协同工作，过滤掉任何不需要的结果（读；空）。

这是我认为我能得到的那样紧凑和快速。该公式现在适用于任何连续的单元格范围。单个单元格、单行、单列或任何二维范围。

选项2：

OP 正确地提到，选项 1 可能会在开始时创建太多元组，然后才丢弃它们。这可能效率低下。为了解决这个问题（如果这不是您想要的），我们可以使用更大的公式。让我们想象一下以下数据：

我们看到有空单元格和重复值。这些就是选项 1 创建过多元组的原因。为了反驳这一点，我想出了一个更长的公式：

=LET(A,A1:C5,B,ROWS(A),C,COLUMNS(A),D,IF(A="",NA(),A),E,MAKEARRAY(B,C,LAMBDA(rw,cl,INDEX(SORT(INDEX(D,0,cl)),rw))),F,BYCOL(E,LAMBDA(cl,COUNTA(FILTER(cl,NOT(ISERROR(cl)))))),G,MAKEARRAY(PRODUCT(F),C,LAMBDA(rw,cl,INDEX(E,MOD(CEILING(rw/IFERROR(PRODUCT(INDEX(F,SEQUENCE(C-cl,,cl+1))),1),1)-1,INDEX(F,cl))+1,cl))),UNIQUE(G))

分解一下：

• LET()- 使用变量；
• A- 我们最初的全范围细胞（连续）；
• B- A的总行数；
• C- A的列总数；
• D- 公式IF(A="",NA(),A)旨在检查矩阵中的每个值是否为空（字符串）。如果是这样，请将其设为错误（这将在下一步中有意义）。
• E- 在这一步中，公式MAKEARRAY(B,C,LAMBDA(rw,cl,INDEX(SORT(INDEX(D,0,cl)),rw)))正在对每一列进行排序，因此值位于顶部，所有错误都被推到下面：

• F- 该变量的公式BYCOL(E,LAMBDA(cl,COUNTA(FILTER(cl,NOT(ISERROR(cl))))))现在将计算每列的项目数量。这是稍后使用和计算所有排列所必需的。在这个特定案例中的结果将是{3;3;4}.
• G- 最后一个变量（如果选择这样使用它）使用MAKEARRAY(PRODUCT(F),C,LAMBDA(rw,cl,INDEX(E,MOD(CEILING(rw/IFERROR(PRODUCT(INDEX(F,SEQUENCE(C-cl,,cl+1))),1),1)-1,INDEX(F,cl))+1,cl)))。虽然很长，但每一步都很有意义；获取乘积（所有可能的排列）来计算行总数，列保持不变。在里面LAMBDA()我们引用当前列索引之后的所有列F多变的。这是一个相当大的块需要消化，不幸的是我解释得不够好=)。
• UNIQUE(G)- 最后一步是过滤掉所有双重排列（如果有人选择的话）。

结果：

现在，尽管选项 1 在可读性方面胜过选项 2，但第二个选项（经过非常有限的测试）计算时间仅为第一个选项的三分之一。因此，就速度而言，第二种选择是首选。

作为第二个选项的替代方案，我首先有：

=LET(A,A1:C5,B,ROWS(A),C,COLUMNS(A),D,MAKEARRAY(B,C,LAMBDA(rw,cl,IF(MATCH(INDEX(A,rw,cl),INDEX(A,0,cl),0)=rw,INDEX(A,rw,cl),NA()))),E,MAKEARRAY(B,C,LAMBDA(rw,cl,INDEX(SORT(INDEX(D,0,cl)),rw))),F,BYCOL(E,LAMBDA(cl,COUNTA(UNIQUE(FILTER(cl,NOT(ISERROR(cl))))))),G,MAKEARRAY(PRODUCT(F),C,LAMBDA(rw,cl,INDEX(E,MOD(CEILING(rw/IFERROR(PRODUCT(INDEX(F,SEQUENCE(C-cl,,cl+1))),1),1)-1,INDEX(F,cl))+1,cl))),G)

现在这将改变D变量为更长的公式以预先删除每列中的重复项。两种变体都可以很好地工作。