Excel公式技能中的降维技术-英雄云拓展知识分享

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

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

以示例来讲明，以下图1所示的工作表。

图1

可以构造各种公式，如：

=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)

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

事实上，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列的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中的数据以下图2所示。

图2

明显，这里的数据是2维的，是一个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)))⑴)/COLUMNS(A1:E10))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))⑴),COLUMNS(A1:E10))))))

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

=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产生的数组也是这类向量类型。针对其定义内涵，列向量固然是一维的。这里使用的技术请参阅《Excel公式技能03：INDEX函数，给公式提供数组》、《Excel公式练习44：从多列中返回唯一且按字母顺序排列的列表》。

可以看出，INDEX结构具有不可否认的优点，不但可以将其用于重新定义内涵工作表区域的维度，还可以重新定义内涵公式中某些其他子函数产生的数组的维度。

但是，还可使用更短的公式：

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

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

关键是要利用MODE.MULT函数的特质来返回返回一维数组，不管传递给该函数的数组本身是一维数组还是2维数组，这都一样适用。但是，MODE.MULT函数本身也存在缺点：传递的数组中的任何元素都要最少出现一次，否则将出错，这意味着我们要强迫解决该问题。因此，这种情况下，这里故意使用了扩大的单元格区域A1:E20：

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

转换为：

1+MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}⑴,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)⑴,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)⑴,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个元素。

一样，我们可以将这项技术应用到“4维数组”：

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

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

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

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

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

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