Excel公式中的降维技术

看过前面一系列文章的朋友，一定会熟悉“重新定义数组维度”的概念。这是一项非常有用且非常重要的技术，使我们可以接受二维数组并将其转换为一维数组，同时将元素保留在该数组中。

如果希望进一步操纵某二维数组的元素，则需要使用这种技术。例如，由于某种原因，在某种情形下，需要将二维数组中的每个元素传递给一个或多个参数进行进一步处理。但是，由于需要使用的Excel函数不能处理多维数组，因此必须首先将原数组转换为一维数组。

以示例来说明，如下所示的工作表。

 

可以构造各种公式，如：

=MID(A1,1,1)

结果显然是“A”。

下面的公式：

=MID(A1,{1,2},1)

得到一维数组{“A”,”m”}，是一个单行向量。

当然，可以使用公式：

=MID(A1,{1;2},1)

得到一维数组{“A”;”m”}，是一个单列向量。

同样，对于单元格A2、A3、A4，使用公式可以得到：

{“E”,”s”}

{“P”,”e”}

{“C”,”e”}

等等。

进一步，使用公式：

=MID(A1,{1,2},{1;2;3})

可以得到一个3行2列数组：

{“A”,”m”;”Am”,”ma”;”Ama”,”map”}

公式中两个参数值的数组彼此正交，MID函数的参数start_num（{1,2}）是一个单行向量，参数num_chars（{1;2;3}）是一个单列向量。

当然可以交换这两个参数的向量类型，公式为：

=MID(A1,{1;2},{1,2,3})

得到一个2行3列的数组：

{“A”,”Am”,”Ama”;”m”,”ma”,”map”}

可以看到，只有在传递给MID函数的两个数组正交的情况下，才能成功地获得所需的6个结果。如果我们使用公式：

=MID(A1,{1,2},{1,2,3})

返回的不是预想的6个元素组成的数组，而是一个由3个元素组成的数组：

{“A”,”ma”,#N/A}

其原因是，当两个数组属于相同的向量类型时，即两个都是单行数组或都是单列数组，Excel将一个数组的元素与另一个数组中相应位置的元素“配对”。因此，公式：

=MID(A1,{1,2},{1,2,3})

等价于执行下面3个公式的结果：

=MID(A1,1,1)

=MID(A1,2,2)

=MID(A1,,2)

数组中根本没有第三个元素作为MID函数的start_num参数与num_chars参数中的第三个元素配对。这样，Excel返回#N/A作为结果数组中的第三个元素。

实际上，Excel为了解决传递两个大小不同的数组的问题，重新定义了两个中较小的一个，使其匹配较大的数组。这样，结果数组中任何额外的不配对的单元格都将填充为#N/A。

在某些情况下，我们接受其中的数组被“重新定义维数”，即便使用错误值填充，前提是我们随后可以根据需要对结果数组进行操作。

继续！我们知道，可以给函数传递多个单元格。因此，可以构造公式：

=MID(A1:A9,1,1)

返回一个9行1列的一维数组，该数组由A1:A9中每个字符串的第一个字符组成，即：

{“A”;”E”;”P”;”C”;”R”;”B”;”M”;”A”;”A”}

进一步扩展：

=MID(A1:A9,{1,2},1)

返回一个9行2列的二维数组：

{“A”,”m”;”E”,”s”;”P”,”e”;”C”,”e”;”R”,”i”;”B”,”a”;”M”,”a”;”A”,”m”;”A”,”c”}

因为A1:A9是列向量，所以MID函数的参数start_num的值必须是行向量。如果试图使用公式：

=MID(A1:A9,{1;2},1)

结果将是{“A”,”s”,#N/A, #N/A, #N/A, #N/A, #N/A, #N/A, #N/A}。

再继续扩展，公式：

=MID(A1:C9,{1,2},1)

我们希望其返回由54个元素组成的数组，该数组等于54个单独的MID构造的结果：

=MID(A1,1,1)

=MID(A1,2,1)

=MID(A2,1,1)

=MID(A2,2,1)

等等。

但实际上，结果是一个仅包含27个元素的数组：

{“A”,”ã”,#N/A;”E”,”a”,#N/A;”P”,”l”,#N/A;”C”,”a”,#N/A;”R”,”o”,#N/A;”B”,”i”,#N/A;”M”,”o”,#N/A;”A”,”i”,#N/A;”A”,”i”,#N/A}

可参考《Excel公式技巧06： COUNTIFS函数如何处理以数组方式提供的条件》中解的对数组的解析的内容。

同样，改变公式中参数的向量类型：

=MID(A1:C9,{1;2},1)

结果是：

{“A”,”S”,”P”;”s”,”a”,”a”;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A}

现在怎么办呢？Excel对可以操作的数组维数的固有限制，是否意味着我们必须放弃获取正在寻找的54个元素数组的尝试？

的确，我们不能改变维数数量，但并不是说不能通过其他方式实现。

在继续刚才的MID函数示例之前，我们以另一个示例来解释。假设在单元格A1:E10中的数据如下所示。

 

显然，这里的数据是二维的，是一个10行5列的数组，其Excel表示为：

{“A1″,”B1″,”C1″,”D1″,”E1″;”A2″,”B2″,”C2″,”D2″,”E2″;”A3″,”B3″,”C3″,”D3″,”E3″;”A4″,”B4″,”C4″,”D4″,”E4″;”A5″,”B5″,”C5″,”D5″,”E5″;”A6″,”B6″,”C6″,”D6″,”E6″;”A7″,”B7″,”C7″,”D7″,”E7″;”A8″,”B8″,”C8″,”D8″,”E8″;”A9″,”B9″,”C9″,”D9″,”E9″;”A10″,”B10″,”C10″,”D10″,”E10”}

但是，由于某些原因，我们需要将上述数据放置在一维数组中：

{“A1″;”B1″;”C1″;”D1″;”E1″;”A2″;”B2″;”C2″;”D2″;”E2″;”A3″;”B3″;”C3″;”D3″;”E3″;”A4″;”B4″;”C4″;”D4″;”E4″;”A5″;”B5″;”C5″;”D5″;”E5″;”A6″;”B6″;”C6″;”D6″;”E6″;”A7″;”B7″;”C7″;”D7″;”E7″;”A8″;”B8″;”C8″;”D8″;”E8″;”A9″;”B9″;”C9″;”D9″;”E9″;”A10″;”B10″;”C10″;”D10″;”E10”}

如何得到这个50行1列的数组？

（或者，一个1行50列的数组：

{“A1″,”B1″,”C1″,”D1″,”E1″,”A2″,”B2″,”C2″,”D2″,”E2″,”A3″,”B3″,”C3″,”D3″,”E3″,”A4″,”B4″,”C4″,”D4″,”E4″,”A5″,”B5″,”C5″,”D5″,”E5″,”A6″,”B6″,”C6″,”D6″,”E6″,”A7″,”B7″,”C7″,”D7″,”E7″,”A8″,”B8″,”C8″,”D8″,”E8″,”A9″,”B9″,”C9″,”D9″,”E9″,”A10″,”B10″,”C10″,”D10″,”E10″}）

通常使用下面的公式：

=INDEX(A1:E10,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))-1)/COLUMNS(A1:E10))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))-1),COLUMNS(A1:E10))))))

其思路是将这个二维数组中的每个元素精确地索引一次，上面的公式转换为：

=INDEX(A1:E10,N(IF(1,{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4;5;5;5;5;5;6;6;6;6;6;7;7;7;7;7;8;8;8;8;8;9;9;9;9;9;10;10;10;10;10})),N(IF(1,{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})))

这里的关键是，传递给INDEX函数的数组（用于参数row_num和参数column_num的数组）都是相同的向量类型（均为列向量），从而确保了由INDEX产生的数组也是这种向量类型。根据其定义，列向量当然是一维的。​可以看出，INDEX结构具有不可否认的优势，不仅可以将其用于重新定义工作表区域的维度，还可以重新定义公式中某些其他子函数产生的数组的维度。

然而，还可以使用更短的公式：

=T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),”R0C00000″),0))

注意，上述公式结构使用了函数T，因此要求单元格区域A1:E10内的值是非数字的。对于由数值组成的单元格区域，可以使用N函数。对于包含混合数据类型的区域，建议使用INDEX方法。

关键是要利用MODE.MULT函数的特性来返回返回一维数组，无论传递给该函数的数组本身是一维数组还是二维数组，这都同样适用。然而，MODE.MULT函数自身也存在缺点：传递的数组中的任何元素都要至少出现一次，否则将出错，这意味着我们要强制解决该问题。因此，这里故意使用了扩展的单元格区域A1:E20：

1+MOD(ROW(A1:E20)-1,10)

转换为：

1+MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1,10)

转换为：

1+MOD({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},10)

转换为：

1+{0;1;2;3;4;5;6;7;8;9;0;1;2;3;4;5;6;7;8;9}

得到：

{1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10}

此时，公式中的：

T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),”R0C00000″),0))

转换为：

T(INDIRECT(TEXT(MODE.MULT(10^5*{1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10}+COLUMN(A1:E10)),”R0C00000″),0))

转换为：

T(INDIRECT(TEXT(MODE.MULT({100000;200000;300000;400000;500000;600000;700000;800000;900000;1000000;100000;200000;300000;400000;500000;600000;700000;800000;900000;1000000}+{1,2,3,4,5}),”R0C00000″),0))

转换为：

T(INDIRECT(TEXT(MODE.MULT({100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005;100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005}),”R0C00000″),0))

转换为：

T(INDIRECT(TEXT({100001;100002;100003;100004;100005;200001;200002;200003;200004;200005;300001;300002;300003;300004;300005;400001;400002;400003;400004;400005;500001;500002;500003;500004;500005;600001;600002;600003;600004;600005;700001;700002;700003;700004;700005;800001;800002;800003;800004;800005;900001;900002;900003;900004;900005;1000001;1000002;1000003;1000004;1000005},”R0C00000”),0))

转换为：

T(INDIRECT({“R1C00001″;”R1C00002″;”R1C00003″;”R1C00004″;”R1C00005″;”R2C00001″;”R2C00002″;”R2C00003″;”R2C00004″;”R2C00005″;”R3C00001″;”R3C00002″;”R3C00003″;”R3C00004″;”R3C00005″;”R4C00001″;”R4C00002″;”R4C00003″;”R4C00004″;”R4C00005″;”R5C00001″;”R5C00002″;”R5C00003″;”R5C00004″;”R5C00005″;”R6C00001″;”R6C00002″;”R6C00003″;”R6C00004″;”R6C00005″;”R7C00001″;”R7C00002″;”R7C00003″;”R7C00004″;”R7C00005″;”R8C00001″;”R8C00002″;”R8C00003″;”R8C00004″;”R8C00005″;”R9C00001″;”R9C00002″;”R9C00003″;”R9C00004″;”R9C00005″;”R10C00001″;”R10C00002″;”R10C00003″;”R10C00004″;”R10C00005”},0))

结果为：

{“A1″;”B1″;”C1″;”D1″;”E1″;”A2″;”B2″;”C2″;”D2″;”E2″;”A3″;”B3″;”C3″;”D3″;”E3″;”A4″;”B4″;”C4″;”D4″;”E4″;”A5″;”B5″;”C5″;”D5″;”E5″;”A6″;”B6″;”C6″;”D6″;”E6″;”A7″;”B7″;”C7″;”D7″;”E7″;”A8″;”B8″;”C8″;”D8″;”E8″;”A9″;”B9″;”C9″;”D9″;”E9″;”A10″;”B10″;”C10″;”D10″;”E10″}

正是我们需要的一维数组。

回到上文中的MID函数示例，我们试图通过公式：

=MID(A1:C9,{1,2},1)

生成由给定这些参数的所有54个排列组成的数组。使用我们的重新定义维数的技术，只需使用A1:C9对上述公式相应位置进行替换：

MID(T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:C19)-1,10))+COLUMN(A1:C9)),”R0C00000”),0)),{1,2},1)

转换为：

MID({“Amapá”;”SãoPaulo”;”Paraná”;”Espírito Santo”;”SantaCatarina”;”Maranhão”;”Pernambuco”;”Alagoas”;”MatoGrosso”;”Ceará”;”Paraíba”;”Piauí”;”RioGrande do Sul”;”Rondônia”;”Tocantins”;”Bahia”;”MinasGerais”;”Roraima”;”Mato Grosso doSul”;”Goiás”;”Pará”;”Amazonas”;”RioGrande do Norte”;”Rio de Janeiro”;”Acre”;”DistritoFederal”;”Sergipe”},{1,2},1)

转换为：

{“A”,”m”;”S”,”ã”;”P”,”a”;”E”,”s”;”S”,”a”;”M”,”a”;”P”,”e”;”A”,”l”;”M”,”a”;”C”,”e”;”P”,”a”;”P”,”i”;”R”,”i”;”R”,”o”;”T”,”o”;”B”,”a”;”M”,”i”;”R”,”o”;”M”,”a”;”G”,”o”;”P”,”a”;”A”,”m”;”R”,”i”;”R”,”i”;”A”,”c”;”D”,”i”;”S”,”e”}

生成了想要的54个元素。

同样，我们可以将这项技术运用到“四维数组”：

=MID(A1:C9,{1,2},{1;2;3})

对于第二次重新定义数组维数，必须使用前面的INDEX构造：

=MID(INDEX(ReDim1,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(ReDim1)*COLUMNS(ReDim1)))-1)/COLUMNS(ReDim1))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(ReDim1)*COLUMNS(ReDim1)))-1),COLUMNS(ReDim1)))))),1,{1,2,3})

其中的ReDim1是我们第一次重新定义数组维数的公式：

=MID(T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW($A$1:$C$19)-1,10))+COLUMN($A$1:$C$9)),”R0C00000″),0)),{1,2},MAX(LEN($A$1:$C$9)))

太复杂了！脑筋都不够用了！