当前位置:首页 > 办公 > Excel教程 > 正文内容

Excel公式中的降维技术

酷网3周前 (10-07)Excel教程25

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

如果希望进一步操纵某二维数组的元素,则需要使用这种技术。例如,由于某种原因,在某种情形下,需要将二维数组中的每个元素传递给一个或多个参数进行进一步处理。但是,由于需要使用的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)))

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

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39449.html

分享给朋友:

相关文章

Excel中怎么圈选无效数据?Excel圈选无效数据的方法

Excel中怎么圈选无效数据?Excel圈选无效数据的方法

  Excel是我们 经常使用的办公软件之一,它不仅仅功能强大而且操作简洁,你知道Excel中怎么圈选无效数据的吗?接下来我们呢一起往下看看Excel圈选无效数据的方法吧。  ...

巧用excel数据透视表批量生成合并单元格

巧用excel数据透视表批量生成合并单元格

今天再给大家一种方法,也很好用,就是利用数据透视表批量生成合并单元格。 示例数据如下: 关键操作第一步:添加辅助列 在“区域一列”后添加辅助列,输入从1开始的序号,如下: 添加辅助列,这一步,可有可无。 什么时候必需...

Excel有哪些最常用又简单的函数公式?

谈起学Excel,就绕不开Excel函数。不过Excel函数有几百个,什么时候能学完?曾经我有一位朋友,立下一段誓言,每天学一个函数,花一年时间学贯东西,最后——他疯了。 坦白的说,没有人能把每个函数都学完,毕竟有些函数...

excel制作日期竖排的考勤表表头

excel制作日期竖排的考勤表表头

实现了随日期而变化的考勤表表头,如下: 今天有位朋友说,根据工作数据需要,要做成日期竖排的样子,如下: 如何实现? 公式实现I 第一步:设置单元格格式 将B6单元格设置自定义格式为只显示“年月日”中的“日”,如下:...

Excel中如何在批注中插入图片

Excel中如何在批注中插入图片

  我们在使用Excel办公的时候,为了方便修改后别人更好地阅读查看,经常会在修改的数据中插入批注。但批注中除了可以插入文字以外,其实还可以插入图片显示。下面就给大家分享一下,Exce...

excel图表快速确定工作表中已使用的区域——UsedRange属性

excel图表快速确定工作表中已使用的区域——UsedRange属性

如果你想快速知道使用了工作表中的多少区域,就可以使用UsedRange属性,不过,该属性属于Worksheet对象,然而它返回的结果是一个Range对象,代表工作表中已使用的单元格区域。 下面是UsedRange属性的语...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。