excel如何從矩陣數組中返回滿足條件的所有組合數

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函數原理，而且要打好數學基礎，這是靈活應用公式的一切。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。