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多的元素的数组),而且意味着,取决于我们所需的输出,最终可能想要比预期更多的元素。
虽然我们可以将诸如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)-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;…}
由于仅对具有由四个非错误值组成的行感兴趣,因此首先将此数组传递给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作为第一个参数,其第二个参数为{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等)中的每一个,提取一个由这四个数字组成的数组(其每个数组为{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)-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
转换为:
{“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
减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,-1;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,-1;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,-1;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,-1;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,-1;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,-1;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,-1,-1;0,2,-1,0;0,2,-1,1;0,2,-1,2;0,2,-1,3;0,2,-1,4;0,2,-1,5;0,2,-1,6;0,2,-1,7;0,2,-1,8;0,2,0,-1;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,-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)-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,-1;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,-1;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,-1;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,-1;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,-1;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,-1;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,-1,-1;0,2,-1,0;0,2,-1,1;0,2,-1,2;0,2,-1,3;0,2,-1,4;0,2,-1,5;0,2,-1,6;0,2,-1,7;0,2,-1,8;0,2,0,-1;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,-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)-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)-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)-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)-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)-1)/4^{3,2,1,0}),4),10^{3;2;1;0}),”0000″)
这种构造背后的原理是Excel与数学的综合。如果我们减少元素的数量,只考虑3×3的类似构造,将更容易解释,也更容易理解。此时,上面的公式构造等价于:
TEXT(MMULT(MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3),10^{2;1;0}),”000”)
暂时不考虑TEXT和MMULT函数,此公式构造的关键是生成一个由三个整数组成的数组,包含数字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)-1)/3^{2,1,0}),3)
将转换成的数组是什么呢?
实际上,我们在这里所做的就是将一系列以10为底的值转换为以3为底的值。因此,以10为底的0到26之间的值,我们可以用3的底数表示的等效表为:
图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)-1)/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和合适的第二个数组将该数组合并为10为底的值:
MMULT(MOD(INT((ROW(1:27)-1)/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)-1)/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函数原理,而且要打好数学基础,这是灵活应用公式的一切。