EXCEL函数常用技巧浅析(四)：数组--随心所欲

EXCEL函数常用技巧浅析(四)技巧四:数组--随心所欲数组的变化可以说是函数的精髓部分,对于数组的解析太多前辈高人做过很多的讲解过了,我这里只不过把前辈高人讲过的东西再重新解读一下,数组高手可以飘过。本技巧介绍的都是以CTRL+SHIFT+回车键结束的内存数组公式，所谓内存数组，简单的讲就是在内存中保存的或者说用F9键在公式栏查看时可以显示全部结果。一:数组的基本原理1:一个纵向一维数组与单个元素的运算;单个元素:我这里为什么要把他叫做单个元素,因为无论是一个单元格也好,一个常量数值或文本也好,在数组的运算中,我们即不能把他看成一个纵向数组,也不能把他看成一个横向数组,因为常常在与一维或二维的运算中,这个元素是具有扩展性的,如果与他运算的数组是一个五行一列的纵向数组,那这个元素也会自动扩展成一个五行一列的数组与之运算,其结果也必然是五行一列的结果;纵向一维数组单个元素自动扩展结果111222133+13+1444155516公式:=B13:B17+12:一个横向一维数组与单个元素的运算;同理,如果这个与其运算的数组是一个一行五列的横向数组,那这个元素也会自动扩展为一个一行五列的数组与之运算,其结果也必然是一个五行一列的结果;横向一维数组12345单个元素+1可以用F9键查看结果的公式栏,12345形成的是内存数组{2,3,4,5,6}+自动扩展11111结果23456公式:=C23:G23+13:一个二维数组与单个元素的运算;同样,一个二维数组与单个元素进行运算,那这个元素也会自动扩展成与之运算的二维数组的行列数,其结果与与其运算的二维数组一致;单个元素16161127+127113838+11494911510510114:一个横向数组与纵向数组运算;一个横向数组与一个纵向数组进行运算,纵向数组扩展成为与其进行运算的横向数组的列数,形成一个行数与原数组相同,列数与纵向数组相同的二维数组;横向数组扩展成为一个行数与纵向数组相同,列数为原数组列数相同;然后两个新的数组相对应的值进行运算,得到一个行数与纵向数组相同,列数与横向数组相同的结果横向数组678910123二行二列数组+纵向数组自动扩展451111122222333334444455555+678910678910678910678910678910789101189101112可以用F9键查看结果的公式栏,910111213形成的是内存数组{7,8,9,10,11;10111213148,9,10,11,12;9,10,11,12,13;10,11,12,13,14;11,12,13,14,15}11121314155:一维同向数组运算两个一维同向数组运算,其中有一个条件就是这两个数组的行数或列数必须是相等的,其结果行或列数与原数组相同,如果不相等,则不会自动扩展,而会产生错误值;数组一数组二结果145数组对称2+57369公式:=B76:B78+D76:D78数组一数组二结果145数组不对称2+573697#N/A8#N/A公式:=B81:B83+D81:D856:一维数组与二维数组运算一维数组与二维数组的运算与一维同向数组原理相同,那就是一维数组的行数或列数必须与二维数组的行数或列数相同,一维数组会自动扩展成二维数组,其大小与二维数组一致,否则会产生错误,一维数组147582+58710369912公式:=B92:B94+D92:E94一维数组147582+58710横向数组扩展结果二维数组+纵向数组纵向数组扩展结果二维数组结果3699124#N/A#N/A公式:=B98:B101+D98:E1007:二维数组与二维数组的运算二维数组与二维数组的运算,两个数组的行列数必须一致,其结果行列数与原数组相同,否则会产生错误.15913101826+101412203711151422481216162415913172126+1014182237111519234812162024二：一维转二维ABCD转换成ABCEDEFFGHIGHI思维一：LOOKUP函数(此题出一位前辈高人贴子,具体贴子没查到)LOOKUP函数一般利用第一参数来驱动数组的结果，我们来想像一下，我们需要构造一个什么样的数组才会得到结果第一步：首先利用ROW函数构造LOOKUP函数的第二参数与第三参数(向量法)1A2B3C4D5E6F7G8H9ILOOKUP(现在不考虑第一参数,ROW(1:9),A9:A17)第二步:需要得到的结果为三行三列,我们的第一参数也必然是一个三行三列数组来驱动函数的结果想像一个如果要得到正确的结果,我们的第一参数就必须为以下数组:123456数组一数组二数组一数组二结果789第三步:在我们要得到正确结果中的第一参数中寻找规律,这个规律是很好找的,我们会发现这个数组直接用行列号就可以构造出来的,123公式:=(ROW(1:3)-1)*3+COLUMN(A:C)112324563789公式解析:首先用ROW(1:3)-1产生一个一列三行纵向数组,其结果为{0;1;2},再用这个数组乘以3,其结果依然为一列三行的纵向数组{0;3;6}001乘以33其原理为纵向数组的第一个元素乘以单个数字,其结果数组方向不会改变;26=ROW(1:3)-1=(ROW(1:3)-1)*3再用COLUMN(A:J)产生一个一行三列的横向数组,其结果为{1,2,3}123=COLUMN(A:C)最后用纵向的一维数组(ROW(1:3)-1)*3加上横向的一维数组COLUMN(A:C),其结果为一个三列三行的二维数组{1,2,3;4,5,6;7,8,9}1230123此数组运算的原理可以理解为:每一个纵向数组与每一个横向数组的元素相加,其结果摆放在两个数组行列相交的位置3456或者也可以这样理解:每一个横向数组与每一个纵向数组的元素相加,其结果摆放在两个数组行列相交的位置6789=C60:C62+D59:F59第四步:利用LOOKUP的向量法进行每一个第一参数的查找,得到结果的数组方向与第一参数一致ABCDEFGHI=LOOKUP((ROW(1:3)-1)*3+COLUMN(A:C),ROW(1:9),B9:B17)思维二:INDIRECT函数利用INDIRECT函数对单元格引用的特性也可以实现以上结果,利用INDIRECT函数的第一参数来驱到数组的方向,第一步:我们设想一下我们我们应该如果构造INDIRECT函数的第一参数,现我们的原数据在B9:B17区域,那INDIRECT函数的第一参数就应该如下表:B9B10B11略去B91011B12B13B14121314B15B16B17151617第二步:如何实现以上效果,其实规律同思维一是一样的,只是数字不一样而已91011191011公式:=(ROW(1:3)-1)*3+COLUMN(I:K)21213143151617公式解析:同思维一,只是增大了列号第三步:根椐INDIRECT函数的特性,加上列号,就形成了第一步的第一表行号列号行号列号B9B10B11B12B13B14B15B16B17第四步:用INDIRECT函数引用上面得出的单元格地址,但得到的结果为一个三维结果,用F9与单元格无法显示,在本示例中为文本,可以用T函数转换,如果是数字,就需要用N函数来转换,注意:T与N函数转换三维后得到的结果只是每一个三维平面中第一个单元格的结果,平面中不是第一个单元格中的内容无法取出.单个元素:我这里为什么要把他叫做单个元素,因为无论是一个单元格也好,一个常量数值或文本也好,在数组的运算中,我们即不能把他看成一个纵向数组,也不能把他看成一个横向数组,因为常常在与一维或二维的运算中,这个元素是具有扩展性的,如果与他运算的数组是一个五行一列的纵向数组,那这个元素也会自动扩展成一个五行一列的数组与之运算,其结果也必然是五行一列的结果;#VALUE!#VALUE!纵向一维数组如果不加T的结果为#VALUE!#VALUE!#VALUE!#VALUE!最后公式:=T(INDIRECT(b&(ROW(1:3)-1)*3+COLUMN(I:K)))思维三:OFFSET函数利用OFFSET函数的第二参数对行的偏移,也可以实现以上效果,第二参数的数组构造与思维一一致,因为OFFSET函数产生的结果也是三维的,所以也需要用T函数来转换ABCDEFGHI公式:=T(OFFSET(B8,(ROW(1:3)-1)*3+COLUMN(A:C),))思维四:VLOOKUP函数因为PINY版主的N(IF({1},)的发现,用VLOOKUP函数实现上面的内存数组不再是难事情,虽然实现的公式看起来有点繁复,但毕竟我们又多了一把利器。第一步：用N(IF{1},)可以实现第一参数的数组化,构造方法如思维一的第三步;123456789=IF({1},(ROW(1:3)-1)*3+COLUMN(A:C))第二步:构造VLOOKUP函数第二参数,这里可以用IF函数构造一个9行二列的数组1A=IF({1,0},ROW(1:9),B9:B17)2B3C4D5E6F7G8H9I第三步:进行组装,VLOOKUP函数结果的方向由第一参数驱动,ABCDEFGHI公式:=VLOOKUP(N(IF({1},(ROW(1:3)-1)*3+COLUMN(A:C))),IF({1,0},ROW(1:9),B9:B17),2,0)思维五:INDEX函数同理,用N(IF({1},)也可以实现INDEX函数第二参数的数组化,而且INDEX函数结果的方向也是由第二参数来驱动.ABCDEFGHI=INDEX(B9:B17,N(IF({1},(ROW(1:3)-1)*3+COLUMN(A:C))))三:二维转一维二维转一维的显示原数据内存数组的办法不是很多,而且如果待转区域数值与文本相混合的话会变得很繁琐ABCABCD转换成DEFGHEIJKLFGHIJKL思维一:OFFSET函数基原理利用OFFSET函数第二参数与第三参数相对应产生的偏移来形成一个一维数组.第一步:首先我们应该考虑应该怎样来构造第二参数与第三参数,想像一下,我们是否应该构造成以下对应数组,这里我们以B257单元格为偏移点.第二参数(行偏移)第三参数(列偏移)测试00A01B02C03现在我们来证明一下如此D10构造第二与第三参数是否E11正确F12G13H20I21J22K23L公式:=T(OFFSET(B257,B271:B282,C271:C282))结果证明这种思路是正确的,那剩下的事情就简单了,我们只要找出构造这种数组的办法就行了第二步:构造第二参数与第三参数,从上面的猜想我们可以看到第二参数与第三参数是有明显的规律的,第二参数的规律是重复我们原数据的列数的数据,可以用INT加除法实现,第二参数重复从0到原数据的列数减1,直接用MOD函数就可以得出了第二参数(行偏移)第三参数(列偏移)000102031011121320212223公式:=INT(ROW(4:15)/4-1)公式:=MOD(ROW(4:15),4)第三步:组装再把构造出的参数代入OFFSET函数,因为OFFSET函数取出的数据是三维平面,而这里又是文本,所有要用T函数取出ABCDEFGHIJKL公式:=T(OFFSET(B257,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)))思维二:INDIRECT函数INDIRECT函数的原理与OFFSET函数差不多,利用INDIRECT函数的R1C1模式也可以构造出同样的数组,所谓的R1C1模式就是引用第几行(R1),第几列(C1)的单元格第一步:根据INDIRCT特性分别设想R1部分与C1部分,现数据区域在R257C2:R259C5中R1C1测试2572A2573B2574再来测试一下我们的设想C结果证明我2575是否正确D们的设想是正确的2582E2583F2584G2585H2592I2593J2594K2595L第二步:构造R1与C1其实我们只要仔细观察一下,上面的数组与OFFSET函数的第二与第三参数基本上