没有VBA的Excel排列表 [英] Excel Permutation Table without VBA

问题描述

是否可以在不使用VBA且不使用任何辅助"工作表或行/列的情况下在Excel中生成排列表?

Is it possible to generate a table of permutations in Excel without using VBA and without using any "helper" sheets or rows/columns?

对于N列，将有N！行.

For N columns, there would be N! rows.

例如，N = 3的表如下所示:

For example, the table for N=3 would look like this:

1  2  3
1  3  2
2  1  3
2  3  1
3  1  2
3  2  1

推荐答案

打开一个空白工作表，并将以下公式粘贴到单元格A1中. (为了便于阅读，我添加了换行符.)

Open a blank worksheet and paste the following formula into cell A1. (I included line breaks for readability.)

= IF(ROW()<=FACT(COLUMN()-1),COLUMN(),
INDIRECT(ADDRESS(ROW()-FACT(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+1),
IF(COLUMN()=(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+2),1,COLUMN()+1))))

只需将此公式拖动到N列上，然后向下拖动N！行以生成顺序为N的完整排列表.

Simply drag this formula over N columns and then down N! rows in order to generate a full permutation table of order N.

一些注意事项:

1. 因为阶乘增长非常快，所以上面的公式仅适用于N <= 10.但是，这并不是真正的限制，因为10！ = 3,628,800，该数字超过Excel 2010中的最大行数(1,048,576).因此，Excel仍然无法为N = 10生成排列表.

1. Because factorials grow very quickly, the formula above only works for N<=10. However, this isn't really a limitation because 10! = 3,628,800 which is a number that exceeds the maximum number of rows in Excel 2010 (which is 1,048,576). Therefore, Excel can't generate a permutation table for N=10 anyway.

如果您希望表格的左上角是A1以外的单元格，则可以手动修改公式以解决这一问题.例如，如果您希望表从A2而不是A1开始，则用(ROW()-1)替换上面公式中的每个ROW()实例.

If you want the top left of the table to be a cell other than A1, the formula can manually be modified to account for this shift. For example, if you want the table to start at A2 instead of A1, replace each instance of ROW() in the formula above with (ROW()-1).

不包括第一行(列出数字升序)和最后一行(列出数字降序)，此表中排列顺序的排列顺序与列表中列出的顺序不同最初的问题，但是模式仍然是确定性的.请参见下面的使用顺序4的公式排列表的屏幕截图，使用条件格式设置可以轻松发现排列顺序如何随着行数增加而变化的模式.

Excluding the first row (which lists the numbers ascending) and the last row (which lists the numbers descending), the order in which the permutations are listed in this table are not in the same order as listed in the original question, but the pattern is still deterministic. See below for a screenshot of a permutation table using this formula of order 4, using conditional formatting to make it easy to spot the pattern of how the order of the permutations change as row numbers increase.

---

具有与上述相同的所有属性的另一个解决方案是，排列以不同的顺序生成，如下所示:

Another solution with all of the same properties as above except that the permutations are generated in a different order is the following:

= IF(ROW()<=FACT(COLUMN()-1),COLUMN(),
INDIRECT(ADDRESS(ROW()-FACT(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+1),COLUMN()))
+IF(ROW()<=FACT(COLUMN()),-1,
IF(INDIRECT(ADDRESS(ROW()-FACT(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+1),COLUMN()))
=INDIRECT(ADDRESS(ROW(),(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+2))),1,0)))

此公式显然比第一个公式长，但通常仍可以完成相同的操作:打开一个空白工作表并将此公式粘贴到单元格A1中.然后将公式拖到N列上，然后向下拖N！行以生成顺序为N的完整排列表.

This formula is obviously longer than the first formula, but it still generally accomplishes the same thing: Open a blank worksheet and paste this formula into cell A1. Then drag the formula over N columns and then down N! rows in order to generate a full permutation table of order N.

但是，如上所述，与先前的解决方案相比，排列的顺序发生了变化.该解决方案中的排列顺序可以说比第一个解决方案的排列顺序更好，因为每一列始终包含相同大小的数字块".请参见下面的使用顺序4的公式排列表的屏幕截图，使用条件格式设置可以轻松发现排列顺序如何随着行数增加而变化的模式.

As already stated, however, the order of the permutations changed as compared to the previous solution. The order of permutations in this solution is arguably better than that of the first solution because each column always contains "blocks" of numbers of the same size. See below for a screenshot of a permutation table using this formula of order 4, using conditional formatting to make it easy to spot the pattern of how the order of the permutations change as row numbers increase.

这篇关于没有VBA的Excel排列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！