excel - 在Excel中生成多个列表的所有排列

Question

我遇到这个问题，我有多个值列表，并且我需要获得所有可能的排列，所以基本上我有

列表 A = 1,2,3,4,5
列表 B = 10,20,30,40,50
列表 C = 5,4,5,2,1
列表 D = a,b,c,d,e

它们每个都位于彼此相邻的列中，因此第一行应该有 1,10,5,1，后面是 2,10,5,1，依此类推。有没有办法在不使用宏的情况下获取此列表？

如有任何帮助，我们将不胜感激!

Answer 1

您可以通过实现一个循环遍历列表直至到达末尾的计数器来创建排列。

例如，假设您的列表位于 A 列和 B 列中，并且您希望 C 列中存在一组可能的排列。

从 if 语句开始来绑定(bind)数据。如果当前行的索引减去第一行加 1 大于可能的排列数，那么我们可以将该单元格留空，否则，我们要返回其中一个排列。

=IF(ROW()-ROW($C$1)+1>
COUNTA(A:A)*COUNTA(B:B),
"",

现在我们需要创建我们的计数器。为此，我们将结合使用 INDEX 公式和 INT 公式。这里我们本质上是说，查看列表 A，根据列表 B 循环遍历它，如果到达列表 B 的末尾，则继续查找列表 A 中的下一个值，然后重新启动。这将为我们提供列表 A 中每个值乘以列表 B 中值的数量的输出。

INDEX(A:A,INT((ROW()-ROW($C$1))/COUNTA(B:B)+1))

如果您需要文本分隔符来进行列表之间的排列，则可以使用 & 符号。

&" | "

现在我们有了列表 A 的可能性数量，我们可以加入列表 B。使用 INDEX 函数和 MOD 函数，我们可以循环列表 B 并打印其值，直到没有可供打印的排列为止。

&INDEX(B:B,MOD(ROW()-ROW($C$1),COUNTA(B:B))+1))

您的函数将如下所示:

 =IF(ROW()-ROW($C$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($C$1))/COUNTA(B:B)+1))&" | "&INDEX(B:B,MOD(ROW()-ROW($C$1),COUNTA(B:B))+1))

相同的概念可以应用于其他列；只需修改计数器即可考虑其他排列。

对于两个以上的列表，您需要对计数器采取不同的方法。在 Excel 中尝试一下这个公式。对于 A、B 和 C 列中的列表，将此公式放入 D 列中并向下拖动到可能的排列数:

 =IF(ROW()-ROW($D$1)+1>COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C),"",INDEX(A:A,MOD(INT(INT((ROWS($1:1)-1)/COUNTA(A:A))/COUNTA(B:B)),COUNTA(C:C))+1)&" | "&INDEX(B:B,MOD(INT((ROWS($1:1)-1)/COUNTA(B:B)),COUNTA(C:C))+1)&" | "&INDEX(C:C,MOD(ROWS($1:1)-1,COUNTA(C:C))+1))