excel如何从矩阵数组中返回满足条件的所有组合数-英雄云拓展知识分享

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

1. 这4个值的总和等于F2中的值

2. 这4个值中彼此位于不同的行和列

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

先不看答案，自已动手试一试。

公式

在单元格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)⑴,””),{0,1,2,3},,)),0),{1;1;1;1})=F2))

公式解析

本案例的条件是：在所给定的单元格区域当选择4个单元格，其和等于目标值，并且这4个单元格彼此都不同行同列。这就意味着，结果在以下组合中产生：

=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}，明显意味着4个元素组合中的每一个都将分别来自4个不同列，然后变换传递给参数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函数：如果我们能够以某种方式生成一个数组，该数组由上述4个元素组成的所有数组组成。该数组以下：

{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多的元素的数组)，而且意味着，取决于我们所需的输出，终究可能想要比预设更多的元素。

虽然我们可以将诸如SMALL之类的函数与其他一些函数例如LARGE、FREQUENCY或MODE.MULT一起使用，返回一个大小与传递给函数的大小不同的数组，但是通常根本没有必要将数组缩减到这样的程度：可以简单地将大数组传递给IF语句，排除无关的元素。这就是本案例使用的技术。

首先，获得传递给OFFSET函数作为参数rows的排列数组，即公式中的：

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)⑴,””)

虽然会在一开始就生成一个比必须的值大很多的数组，但是由于存在最小和最大的返回值，因此可以将数组大小进行限制。本例中，我们感兴趣的将是1234和4321(实际上我们终究需要的是0123和3210;但是，如果将0123传递给ROW函数，将被解释为123，因此我们的计算将是比至今为止更大的数组。以这类方式取得1234、1243、1324等，然后在公式的后面将它们处理为0123、0132、0213等。)

处理1234:4321最少比将整列的值(即1:1048576)传递给ROW要占用更少的资源。因此：

ROW(INDIRECT(“1234:4321”))

将生成由1234至4321范围内的整数构成的数组：

{1234;1235;1236;1237;1238;1239;1240;1241;1242;1243;1244;1245;1246;1247;1248;1249;1250;1251;1252;1253;1254;1255;1256;1257;1258;1259;1260;1261;1262;1263;1264;1265;1266;1267;1268;1269;1270;1271;1272;1273;1274;1275;1276;1277;1278;1279;1280;1281;1282;1283;1284;1285;1286;1287;1288;1289;1290;1291;1292;1293;1294;1295;1296;1297;1298;1299;1300;1301;1302;1303;1304;1305;1306;1307;1308;1309;1310;1311;1312;1313;1314;1315;1316;1317;1318;1319;1320;1321;1322;1323;1324;…}

然后测试数组中每一个元素是不是都包括数字1、2、3、4：

FIND({1,2,3,4},ROW(INDIRECT(“1234:4321”)))

将产生一个3088行4列的数组，其12352个元素将是对上述数组的所有3088个元素分别查找1，2、3和4的结果：

{1,2,3,4;1,2,3,#VALUE!;1,2,3,#VALUE!;1,2,3,#VALUE!;1,2,3,#VALUE!;1,2,3,#VALUE!;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,4,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,4,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,4;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,4,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,4;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,3,2,#VALUE!;1,3,2,#VALUE!;1,3,2,#VALUE!;1,3,2,#VALUE!;1,3,2,4;…}

由于仅对具有由4个非毛病值组成的行感兴趣，因此首先将此数组传递给ISNUMBER并强迫将结果布尔值TRUE/FALSE转换成为数字，从而：

0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT(“1234:4321”)))))

的结果为：

{1,1,1,1;1,1,1,0;1,1,1,0;1,1,1,0;1,1,1,0;1,1,1,0;1,1,0,1;1,1,0,1;1,1,0,1;1,1,1,1;1,1,0,1;1,1,0,1;1,1,0,1;1,1,0,1;1,1,0,1;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,0,1,0;1,0,1,0;1,1,1,0;1,0,1,0;1,0,1,1;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,1,1,0;1,0,1,0;1,0,1,1;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,1,1,0;1,1,1,0;1,1,1,0;1,1,1,0;1,1,1,1;…}

上面的数组传递给MMULT作为第一个参数，其第2个参数为{1;1;1;1}，这样：

MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT(“1234:4321”))))),{1;1;1;1})

的结果为：

{4;3;3;3;3;3;3;3;3;4;3;3;3;3;3;3;2;2;2;3;3;2;2;2;2;2;2;2;2;3;3;2;2;2;2;2;2;2;2;3;3;2;2;2;2;2;2;2;2;3;3;2;2;2;2;2;2;2;2;3;3;2;2;2;2;2;2;2;3;2;3;2;2;2;2;2;2;2;3;2;3;2;2;2;2;2;3;3;3;3;4;…}

数组中的4表示原来的ROW函数生成的值中分别包括1、2、3、4各一个，将该数组与4进行比较：

MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT(“1234:4321”))))),{1;1;1;1})=4

得到：

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;…}

数组中标红的TRUE值与ROW生成的原数组中的1234、1243、1324相对应。

现在，对将在公式的IF语句中生成TRUE的24个值(1234、1243、1324等)中的每个，提取一个由这4个数字组成的数组(其每一个数组为{1,2,3,4}、{1,2,4,3}、{1,3,2,4}等)传递给OFFSET函数。使用MID函数来实现，其参数start_num指定为{1,2,3,4}：

MID(ROW(INDIRECT(“1234:4321”)),{1,2,3,4},1)⑴

转换为：

MID({1234;1235;1236;1237;1238;1239;1240;1241;1242;1243;1244;1245;1246;1247;1248;1249;1250;1251;1252;1253;1254;1255;1256;1257;1258;1259;1260;1261;1262;1263;1264;1265;1266;1267;1268;1269;1270;1271;1272;1273;1274;1275;1276;1277;1278;1279;1280;1281;1282;1283;1284;1285;1286;1287;1288;1289;1290;1291;1292;1293;1294;1295;1296;1297;1298;1299;1300;1301;1302;1303;1304;1305;1306;1307;1308;1309;1310;1311;1312;1313;1314;1315;1316;1317;1318;1319;1320;1321;1322;1323;1324;…},{1,2,3,4},1)⑴

转换为：

{“1″,”2″,”3″,”4″;”1″,”2″,”3″,”5″;”1″,”2″,”3″,”6″;”1″,”2″,”3″,”7″;”1″,”2″,”3″,”8″;”1″,”2″,”3″,”9″;”1″,”2″,”4″,”0″;”1″,”2″,”4″,”1″;”1″,”2″,”4″,”2″;”1″,”2″,”4″,”3″;”1″,”2″,”4″,”4″;”1″,”2″,”4″,”5″;”1″,”2″,”4″,”6″;”1″,”2″,”4″,”7″;”1″,”2″,”4″,”8″;”1″,”2″,”4″,”9″;”1″,”2″,”5″,”0″;”1″,”2″,”5″,”1″;”1″,”2″,”5″,”2″;”1″,”2″,”5″,”3″;”1″,”2″,”5″,”4″;”1″,”2″,”5″,”5″;”1″,”2″,”5″,”6″;”1″,”2″,”5″,”7″;”1″,”2″,”5″,”8″;”1″,”2″,”5″,”9″;”1″,”2″,”6″,”0″;”1″,”2″,”6″,”1″;”1″,”2″,”6″,”2″;”1″,”2″,”6″,”3″;”1″,”2″,”6″,”4″;”1″,”2″,”6″,”5″;”1″,”2″,”6″,”6″;”1″,”2″,”6″,”7″;”1″,”2″,”6″,”8″;”1″,”2″,”6″,”9″;”1″,”2″,”7″,”0″;”1″,”2″,”7″,”1″;”1″,”2″,”7″,”2″;”1″,”2″,”7″,”3″;”1″,”2″,”7″,”4″;”1″,”2″,”7″,”5″;”1″,”2″,”7″,”6″;”1″,”2″,”7″,”7″;”1″,”2″,”7″,”8″;”1″,”2″,”7″,”9″;”1″,”2″,”8″,”0″;”1″,”2″,”8″,”1″;”1″,”2″,”8″,”2″;”1″,”2″,”8″,”3″;”1″,”2″,”8″,”4″;”1″,”2″,”8″,”5″;”1″,”2″,”8″,”6″;”1″,”2″,”8″,”7″;”1″,”2″,”8″,”8″;”1″,”2″,”8″,”9″;”1″,”2″,”9″,”0″;”1″,”2″,”9″,”1″;”1″,”2″,”9″,”2″;”1″,”2″,”9″,”3″;”1″,”2″,”9″,”4″;”1″,”2″,”9″,”5″;”1″,”2″,”9″,”6″;”1″,”2″,”9″,”7″;”1″,”2″,”9″,”8″;”1″,”2″,”9″,”9″;”1″,”3″,”0″,”0″;”1″,”3″,”0″,”1″;”1″,”3″,”0″,”2″;”1″,”3″,”0″,”3″;”1″,”3″,”0″,”4″;”1″,”3″,”0″,”5″;”1″,”3″,”0″,”6″;”1″,”3″,”0″,”7″;”1″,”3″,”0″,”8″;”1″,”3″,”0″,”9″;”1″,”3″,”1″,”0″;”1″,”3″,”1″,”1″;”1″,”3″,”1″,”2″;”1″,”3″,”1″,”3″;”1″,”3″,”1″,”4″;”1″,”3″,”1″,”5″;”1″,”3″,”1″,”6″;”1″,”3″,”1″,”7″;”1″,”3″,”1″,”8″;”1″,”3″,”1″,”9″;”1″,”3″,”2″,”0″;”1″,”3″,”2″,”1″;”1″,”3″,”2″,”2″;”1″,”3″,”2″,”3″;”1″,”3″,”2″,”4”;…}⑴

减1后得到：

{0,1,2,3;0,1,2,4;0,1,2,5;0,1,2,6;0,1,2,7;0,1,2,8;0,1,3,⑴;0,1,3,0;0,1,3,1;0,1,3,2;0,1,3,3;0,1,3,4;0,1,3,5;0,1,3,6;0,1,3,7;0,1,3,8;0,1,4,⑴;0,1,4,0;0,1,4,1;0,1,4,2;0,1,4,3;0,1,4,4;0,1,4,5;0,1,4,6;0,1,4,7;0,1,4,8;0,1,5,⑴;0,1,5,0;0,1,5,1;0,1,5,2;0,1,5,3;0,1,5,4;0,1,5,5;0,1,5,6;0,1,5,7;0,1,5,8;0,1,6,⑴;0,1,6,0;0,1,6,1;0,1,6,2;0,1,6,3;0,1,6,4;0,1,6,5;0,1,6,6;0,1,6,7;0,1,6,8;0,1,7,⑴;0,1,7,0;0,1,7,1;0,1,7,2;0,1,7,3;0,1,7,4;0,1,7,5;0,1,7,6;0,1,7,7;0,1,7,8;0,1,8,⑴;0,1,8,0;0,1,8,1;0,1,8,2;0,1,8,3;0,1,8,4;0,1,8,5;0,1,8,6;0,1,8,7;0,1,8,8;0,2,⑴,⑴;0,2,⑴,0;0,2,⑴,1;0,2,⑴,2;0,2,⑴,3;0,2,⑴,4;0,2,⑴,5;0,2,⑴,6;0,2,⑴,7;0,2,⑴,8;0,2,0,⑴;0,2,0,0;0,2,0,1;0,2,0,2;0,2,0,3;0,2,0,4;0,2,0,5;0,2,0,6;0,2,0,7;0,2,0,8;0,2,1,⑴;0,2,1,0;0,2,1,1;0,2,1,2;0,2,1,3;…}

由上文得出的结果，可知：

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)⑴,””)

转换为：

IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;…},{0,1,2,3;0,1,2,4;0,1,2,5;0,1,2,6;0,1,2,7;0,1,2,8;0,1,3,⑴;0,1,3,0;0,1,3,1;0,1,3,2;0,1,3,3;0,1,3,4;0,1,3,5;0,1,3,6;0,1,3,7;0,1,3,8;0,1,4,⑴;0,1,4,0;0,1,4,1;0,1,4,2;0,1,4,3;0,1,4,4;0,1,4,5;0,1,4,6;0,1,4,7;0,1,4,8;0,1,5,⑴;0,1,5,0;0,1,5,1;0,1,5,2;0,1,5,3;0,1,5,4;0,1,5,5;0,1,5,6;0,1,5,7;0,1,5,8;0,1,6,⑴;0,1,6,0;0,1,6,1;0,1,6,2;0,1,6,3;0,1,6,4;0,1,6,5;0,1,6,6;0,1,6,7;0,1,6,8;0,1,7,⑴;0,1,7,0;0,1,7,1;0,1,7,2;0,1,7,3;0,1,7,4;0,1,7,5;0,1,7,6;0,1,7,7;0,1,7,8;0,1,8,⑴;0,1,8,0;0,1,8,1;0,1,8,2;0,1,8,3;0,1,8,4;0,1,8,5;0,1,8,6;0,1,8,7;0,1,8,8;0,2,⑴,⑴;0,2,⑴,0;0,2,⑴,1;0,2,⑴,2;0,2,⑴,3;0,2,⑴,4;0,2,⑴,5;0,2,⑴,6;0,2,⑴,7;0,2,⑴,8;0,2,0,⑴;0,2,0,0;0,2,0,1;0,2,0,2;0,2,0,3;0,2,0,4;0,2,0,5;0,2,0,6;0,2,0,7;0,2,0,8;0,2,1,⑴;0,2,1,0;0,2,1,1;0,2,1,2;0,2,1,3;…},””)

得到：

{0,1,2,3;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;0,1,3,2;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;0,2,1,3;…}

这个数组为OFFSET函数的rows参数值，而先前已讨论过，其cols参数值为{0,1,2,3}，因此：

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)⑴,””),{0,1,2,3},,))

转换为：

N(OFFSET(A1,{0,1,2,3;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;0,1,3,2;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;0,2,1,3;…},{0,1,2,3},,))

结果为：

{1,6,3,2;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;1,6,4,2;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;1,7,2,2;…}

传递给IFERROR函数，这样：

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)⑴,””),{0,1,2,3},,)),0)

转换为：

{1,6,3,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,6,4,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,7,2,2;…}

数组中红色数字分别对应着履行下面的公式操作：

N(OFFSET(A1,{0,1,2,3},{0,1,2,3},,))

N(OFFSET(A1,{0,1,3,2},{0,1,2,3},,))

N(OFFSET(A1,{0,2,1,3},{0,1,2,3},,))

接着使用MMULT对已生成的数组矩阵中的每行求和，因此：

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)⑴,””),{0,1,2,3},,)),0),{1;1;1;1})

转换为：

MMULT({1,6,3,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,6,4,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,7,2,2;…},{1;1;1;1})

得到：

{12;0;0;0;0;0;0;0;0;13;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;12;…}

然后，与单元格F2中的目标值比较：

=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)⑴,””),{0,1,2,3},,)),0),{1;1;1;1})=F2))

转换为：

=SUM(0+({12;0;0;0;0;0;0;0;0;13;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;12;…}=12))

可以返回结果：

6

至此，公式解析终了。

现在，我们来看一下减少数组元素的方法。上文中，由

ROW(INDIRECT(“1234:4321″))

生成了含有3088个元素的数组，而我们只对其中的24个元素感兴趣，也就是由1234进行排列的元素，因此与我们无关的有3064个元素，占总元素的99%以上。

这里首先将这个数组减少到只有256个元素，这样无关元素比提高至了91%。采取下面的公式构造：

TEXT(MMULT(MOD(INT((ROW(1:256)⑴)/4^{3,2,1,0}),4),10^{3;2;1;0}),”0000″)

这类构造背后的原理是Excel与数学的综合。如果我们减少元素的数量，只斟酌3×3的类似构造，将更容易解释，也更容易理解。此时，上面的公式构造等价于：

TEXT(MMULT(MOD(INT((ROW(1:27)⑴)/3^{2,1,0}),3),10^{2;1;0}),”000”)

暂时不斟酌TEXT和MMULT函数，此公式构造的关键是生成一个由3个整数组成的数组，包括数字0、1和2的所有可能排列。即：

{0,0,0;0,0,1;0,0,2;0,1,0;0,1,1;0,1,2;0,2,0;0,2,1;0,2,2;1,0,0;1,0,1;1,0,2;1,1,0;1,1,1;1,1,2;1,2,0;1,2,1;1,2,2;2,0,0;2,0,1;2,0,2;2,1,0;2,1,1;2,1,2;2,2,0;2,2,1;2,2,2}

这是上述构造中取27的缘由。对4个元素取256，由于n的元素的可能排列数为n^n，所以3^3=27，4^4=256。

这样，公式构造中的：

MOD(INT((ROW(1:27)⑴)/3^{2,1,0}),3)

将转换成的数组是甚么呢?

事实上，我们在这里所做的就是将一系列以10为底的值转换为以3为底的值。因此，这种情况下，以10为底的0到26之间的值，我们可以用3的底数表示的等效表为：

这正是我们要生成的27个排列。

对以10为底的给定值，为了肯定该值的以3为底的表示情势中存在的3^2、3^1和3^0的数量，首先肯定3^2、3^1和3^0的以基数为10的值，然后对所得值取模(模为3)。举例来看，以10为底的值7，以3为底的值的表示情势为021，由于3^2=9在7中出现0次且MOD(0,3)=0，3^1=3在7中出现2次且MOD(2,3)=2，3^0=1在7中出现1次且MOD(1,3)=1。这意味着：

MOD(INT((ROW(1:27)⑴)/3^{2,1,0}),3)

转换为：

{0,0,0;0,0,1;0,0,2;0,1,0;0,1,1;0,1,2;0,2,0;0,2,1;0,2,2;1,0,0;1,0,1;1,0,2;1,1,0;1,1,1;1,1,2;1,2,0;1,2,1;1,2,2;2,0,0;2,0,1;2,0,2;2,1,0;2,1,1;2,1,2;2,2,0;2,2,1;2,2,2}

然后，使用MMULT和适合的第2个数组将该数组合并为10为底的值：

MMULT(MOD(INT((ROW(1:27)⑴)/3^{2,1,0}),3),10^{2;1;0})

转换为：

MMULT({0,0,0;0,0,1;0,0,2;0,1,0;0,1,1;0,1,2;0,2,0;0,2,1;0,2,2;1,0,0;1,0,1;1,0,2;1,1,0;1,1,1;1,1,2;1,2,0;1,2,1;1,2,2;2,0,0;2,0,1;2,0,2;2,1,0;2,1,1;2,1,2;2,2,0;2,2,1;2,2,2},{100;10;1})

得到：

{0;1;2;10;11;12;20;21;22;100;101;102;110;111;112;120;121;122;200;201;202;210;211;212;220;221;222}

最后，使用TEXT函数格式化以生成所需要的排列：

TEXT(MMULT(MOD(INT((ROW(1:27)⑴)/3^{2,1,0}),3),10^{2;1;0}),”000″)

得到结果：

{“000″;”001″;”002″;”010″;”011″;”012″;”020″;”021″;”022″;”100″;”101″;”102″;”110″;”111″;”112″;”120″;”121″;”122″;”200″;”201″;”202″;”210″;”211″;”212″;”220″;”221″;”222”}

小结

1.找到规律，然后寻求解决之道。

2.不但要理解Excel函数原理，而且要打好数学基础，这是灵活利用公式的一切。