excel怎么从矩阵数组中返回满足条件的所有组合数 _知识经验

如下图1所示，在一个4行4列的单元格区域A1:D4中，每个单元格内都是一个一位整数，并且目标值单元格(此处为F2)也为整数，要求在单元格G2中编写一个公式返回单元格A1:D4中四个不同值的组合的数量，条件如下：
1. 这四个值的总和等于F2中的值
2. 这四个值中彼此位于不同的行和列

文章插图
图1
下图2是图1示例中满足条件的6种组合。

文章插图
图2
先不看答案，自已动手试一试。
公式
在单元格G2中的数组公式为：
=SUM(0+(MMULT(IFERROR(N(OFFSET(A1,IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT(“1234:4321”))))),{1;1;1;1})=4,MID(ROW(INDIRECT(“1234:4321″)),{1,2,3,4},1)-1,””),{0,1,2,3},,)),0),{1;1;1;1})=F2))
公式解析
本案例的条件是：在所给定的单元格区域中选择四个单元格，其和等于目标值，并且这四个单元格彼此都不同行同列。这就意味着，结果在下列组合中产生：
=A1+B2+C3+D4 =A2+B1+C3+D4 =A3+B1+C2+D4 =A4+B1+C2+D3
=A1+B2+C4+D3 =A2+B1+C4+D3 =A3+B1+C4+D2 =A4+B1+C3+D2
=A1+B3+C2+D4 =A2+B3+C1+D4 =A3+B2+C1+D4 =A4+B2+C1+D3
=A1+B3+C4+D2 =A2+B3+C4+D1 =A3+B2+C4+D1 =A4+B2+C3+D1
=A1+B4+C2+D3 =A2+B4+C1+D3 =A3+B4+C1+D2 =A4+B3+C1+D2
=A1+B4+C3+D2 =A2+B4+C3+D1 =A3+B4+C2+D1 =A4+B3+C2+D1
共有24种组合。组合数为n!，对于4行4列为4!=24 。
现在，看看上面给出的那24个和，可以推断出它们可以使用OFFSET获得，即：
=A1+B2+C3+D4
等价于：
=SUM(N(OFFSET(A1,{0,1,2,3},{0,1,2,3})))
同样：
=A2+B1+C3+D4
等价于：
=SUM(N(OFFSET(A1,{1,0,2,3},{0,1,2,3})))
同样：
=A3+B1+C2+D4
等价于：
=SUM(N(OFFSET(A1,{2,0,1,3},{0,1,2,3})))
……
等等。
关键是，参数cols固定为数组{0,1,2,3}，显然意味着四个元素组合中的每个都将分别来自四个不同列，然后变换传递给参数rows的数组，即满足确保没有两个元素在同一行的条件的所有可能排列。因此，这24个rows参数传递给OFFSET时将产生与先前给出的24个和相等的结果，即集合{0,1,2,3}的24个可能的排列，即：
{0,1,2,3} {1,0,2,3} {2,0,1,3} {3,0,1,2}
{0,1,3,2} {1,0,3,2} {2,0,3,1} {3,0,2,1}
{0,2,1,3} {1,2,0,3} {2,1,0,3} {3,1,0,2}
{0,2,3,1} {1,2,3,0} {2,1,3,0} {3,1,2,0}
{0,3,1,2} {1,3,0,2} {2,3,0,1} {3,2,0,1}
{0,3,2,1} {1,3,2,0} {2,3,1,0} {3,2,1,0}
现在，我们有24个单独的和要计算。然而，我们不仅限于将一维数组传递给OFFSET函数：如果我们能够以某种方式生成一个数组，该数组由上述四个元素组成的所有数组组成。该数组如下：
{0,1,2,3;1,0,2,3;2,0,1,3;3,0,1,2;0,1,3,2;1,0,3,2;2,0,3,1;3,0,2,1;0,2,1,3;1,2,0,3;2,1,0,3;3,1,0,2;0,2,3,1;1,2,3,0;2,1,3,0;3,1,2,0;0,3,1,2;1,3,0,2;2,3,0,1;3,2,0,1;0,3,2,1;1,3,2,0;2,3,1,0;3,2,1,0}
一个由24行4列组成的数组，其中的每一行等于上面给出的24种排列之一，然后将其传递给OFFSET函数，实现对所有24个数组的同时处理。
实际上，这也是为什么有些解决方案将定义排列为：
={1234;1243;1324;1342;1423;1432;2134;2143;2314;2341;2413;2431;3124;3142;3214;3241;3412;3421;4123;4132;4213;4231;4312;4321}
的原因。然后，进一步操作该数组以获取传递给OFFSET函数的矩阵。
可是，尽管这样确实可以提供我们所需要的结果，但我们还是希望能够动态生成这样的数组。因为如果案例扩展到5行5列或6行6列，那么矩阵元素会大幅增长，手工构造排列就不可取了。
不幸的是，在Excel中生成这种排列的数组绝非易事。在Excel中生成大型数组唯一现实的方法是通过使用ROW函数的公式构造。但是，这不仅限制了结果数组的大小(我们至少不能生成比工作表中的行数即1,048,576多的元素的数组)，而且意味着，取决于我们所需的输出，最终可能想要比预期更多的元素。