excel数组--随心所欲

1、EXCEL函函数数常常用用技技巧巧浅浅析析(四四)技技巧巧四四:数数组组-随随心心所所欲欲数组的变化可以说是函数的精髓部分,对于数组的解析太多前辈高人做过很多的讲解过了,我这里只不过把前辈高人讲过的东西再重新解读一下,数组高手可以飘过。本技巧介绍的都是以CTRL+SHIFT+回车键结束的内存数组公式，所谓内存数组，简单的讲就是在内存中保存的或者说用F9键在公式栏查看时可以显示全部结果。一一:数数组组的的基基本本原原理理1:一一个个纵纵向向一一维维数数组组与与单单个个元元素素的的运运算算;单个元素:我这里为什么要把他叫做单个元素,因为无论是一个单元格也好,一个常量数值或文本也好,在数组的运算中,

2、我们即不能把他看成一个纵向数组,也不能把他看成一个横向数组,因为常常在与一维或二维的运算中,这个元素是具有扩展性的,如果与他运算的数组是一个五行一列的纵向数组,那这个元素也会自动扩展成一个五行一列的数组与之运算,其结果也必然是五行一列的结果;纵向一维数组单个元素自动扩展结果111222133+13+1444155516公公式式:=B13:B17+12:一一个个横横向向一一维维数数组组与与单单个个元元素素的的运运算算;同理,如果这个与其运算的数组是一个一行五列的横向数组,那这个元素也会自动扩展为一个一行五列的数组与之运算,其结果也必然是一个五行一列的结果;横向一维数组12345单个元素+1可可以

3、以用用F9键键查查看看结结果果的的公公式式栏栏,12345形形成成的的是是内内存存数数组组2,3,4,5,62,3,4,5,6+自动扩展11111结果23456公公式式:=C23:G23+13:一一个个二二维维数数组组与与单单个个元元素素的的运运算算;同样,一个二维数组与单个元素进行运算,那这个元素也会自动扩展成与之运算的二维数组的行列数,其结果与与其运算的二维数组一致;二行二列数组单个元素自动扩展16161127+127113838+11494911510510114:一一个个横横向向数数组组与与纵纵向向数数组组运运算算;一个横向数组与一个纵向数组进行运算,纵向数组扩展成为与其进行运算的横向

4、数组的列数,形成一个行数与原数组相同,列数与纵向数组相同的二维数组;横向数组扩展成为一个行数与纵向数组相同,列数为原数组列数相同;然后两个新的数组相对应的值进行运算,得到一个行数与纵向数组相同,列数与横向数组相同的结果横向数组678910纵向数组1+2345纵向数组扩展1111122222333334444455555+横向数组扩展678910678910678910678910678910结果789101189101112 可可以以用用F9键键查查看看结结果果的的公公式式栏栏,910111213 形形成成的的是是内内存存数数组组7,8,9,10,11;7,8,9,10,11;10111213

5、14 8,9,10,11,12;9,10,11,12,13;10,11,12,13,14;11,12,13,14,158,9,10,11,12;9,10,11,12,13;10,11,12,13,14;11,12,13,14,1511121314155:一一维维同同向向数数组组运运算算两个一维同向数组运算,其中有一个条件就是这两个数组的行数或列数必须是相等的,其结果行或列数与原数组相同,如果不相等,则不会自动扩展,而会产生错误值;数组一数组二结果145数组对称2+57369公公式式:=B76:B78+D76:D78数组一数组二结果145数组不对称2+573697#N/A8#N/A公公式式:=B

6、81:B83+D81:D856:一一维维数数组组与与二二维维数数组组运运算算一维数组与二维数组的运算与一维同向数组原理相同,那就是一维数组的行数或列数必须与二维数组的行数或列数相同,一维数组会自动扩展成二维数组,其大小与二维数组一致,否则会产生错误,一维数组二维数组结果147582+58710369912公公式式:=B92:B94+D92:E94一维数组二维数组结果14758纵向数组+2+587103699124#N/A#N/A公公式式:=B98:B101+D98:E1007:二二维维数数组组与与二二维维数数组组的的运运算算二维数组与二维数组的运算,两个数组的行列数必须一致,其结果行列数与原数

7、组相同,否则会产生错误.数组一数组二结1014122037111514224812161624数组一数组1014182237111519234812162024二二：一一维维转转二二维维ABCD转换成ABCEDEFFGHIGHI思思维维一一：LOOKUP函函数数(此此题题出出一一位位前前辈辈高高人人贴贴子子,具具体体贴贴子子没没查查到到)LOOKUP函数一般利用第一参数来驱动数组的结果，我们来想像一下，我们需要构造一个什么样的数组才会得到结果第一步：首先利用ROW函数构造LOOKUP函数的第二参数与第三参数(向量法)1A2B3C4D5E6F7

8、G8H9ILOOKUP(现现在在不不考考虑虑第第一一参参数数,ROW(1:9),A9:A17)第二步:需要得到的结果为三行三列,我们的第一参数也必然是一个三行三列数组来驱动函数的结果想像一个如果要得到正确的结果,我们的第一参数就必须为以下数组:123456789第三步:在我们要得到正确结果中的第一参数中寻找规律,这个规律是很好找的,我们会发现这个数组直接用行列号就可以构造出来的,列号123 公公式式:=(ROW(1:3)-1)*3+COLUMN(A:C)行号112324563789公式解析:首先用ROW(1:3)-1产生一个一列三行纵向数组,其结果为0;1;2,再用这个数组乘以3,其结果依然为

9、一列三行的纵向数组0;3;6001 乘以33其原理为纵向数组的第一个元素乘以单个数字,其结果数组方向不会改变;26=ROW(1:3)-1=(ROW(1:3)-1)*3再用COLUMN(A:J)产生一个一行三列的横向数组,其结果为1,2,3123=COLUMN(A:C)最后用纵向的一维数组(ROW(1:3)-1)*3加上横向的一维数组COLUMN(A:C),其结果为一个三列三行的二维数组1,2,3;4,5,6;7,8,91230123 此数组运算的原理可以理解为:每一个纵向数组与每一个横向数组的元素相加,其结果摆放在两个数组行列相交的位置3456 或者也可以这样理解:每一个横向数组与每一个纵向数

10、组的元素相加,其结果摆放在两个数组行列相交的位置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函数的第一参数就应该如下表:

11、B9B10B11略去B91011B12B13B14121314B15B16B17151617第二步:如何实现以上效果,其实规律同思维一是一样的,只是数字不一样而已列号91011行号191011 公公式式:=(ROW(1:3)-1)*3+COLUMN(I:K)21213143151617公式解析:同思维一,只是增大了列号第三步:根椐INDIRECT函数的特性,加上列号,就形成了第一步的第一表B9B10B11B12B13B14B15B16B17第四步:用INDIRECT函数引用上面得出的单元格地址,但得到的结果为一个三维结果,用F9与单元格无法显示,在本示例中为文本,可以用T函数转换,如果是数字,

12、就需要用N函数来转换,注注意意:T与与N函函数数转转换换三三维维后后得得到到的的结结果果只只是是每每一一个个三三维维平平面面中中第第一一个个单单元元格格的的结结果果,平平面面中中不不是是第第一一个个单单元元格格中中的的内内容容无无法法取取出出.单个元素:我这里为什么要把他叫做单个元素,因为无论是一个单元格也好,一个常量数值或文本也好,在数组的运算中,我们即不能把他看成一个纵向数组,也不能把他看成一个横向数组,因为常常在与一维或二维的运算中,这个元素是具有扩展性的,如果与他运算的数组是一个五行一列的纵向数组,那这个元素也会自动扩展成一个五行一列的数组与之运算,其结果也必然是五行一列的结果;#VA

13、LUE!#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,)的发现,用V

14、LOOKUP函数实现上面的内存数组不再是难事情,虽然实现的公式看起来有点繁复,但毕竟我们又多了一把利器。第一步：用N(IF1,)可以实现第一参数的数组化,构造方法如思维一的第三步;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

15、),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函数第二参数与第三参数相对应产生的偏移来形成一个一

16、维数组.第一步:首先我们应该考虑应该怎样来构造第二参数与第三参数,想像一下,我们是否应该构造成以下对应数组,这里我们以B257单元格为偏移点.第二参数(行偏移) 第三参数(列偏移)测试00A01B02C03 现现在在我我们们来来证证明明一一下下如如此此D10 构构造造第第二二与与第第三三参参数数是是否否E11 正正确确F12G13H20I21J22K23L公公式式:=T(OFFSET(B257,B271:B282,C271:C282)结果证明这种思路是正确的,那剩下的事情就简单了,我们只要找出构造这种数组的办法就行了第二步:构造第二参数与第三参数,从上面的猜想我们可以看到第二参数与第三参数是有

17、明显的规律的,第二参数的规律是重复我们原数据的列数的数据,可以用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)思思维维二二:IN

18、DIRECT函函数数INDIRECT函数的原理与OFFSET函数差不多,利用INDIRECT函数的R1C1模式也可以构造出同样的数组,所谓的R1C1模式就是引用第几行(R1),第几列(C1)的单元格第一步:根据INDIRCT特性分别设想R1部分与C1部分,现数据区域在R257C2:R259C5中R1R1C1C1测测试试2572A2573B2574再来测试一下我们的设想C结果证明我2575是否正确D们的设想是正确的2582E2583F2584G2585H2592I2593J2594K2595L第二步:构造R1与C1其实我们只要仔细观察一下,上面的数组与OFFSET函数的第二与第三参数基本上雷同,

19、只是把数字的大小放大了而已R1R1C1C1257225732574257525822583258425852592259325942595公公式式:=INT(ROW(4:15)/4+256)公公式式:=MOD(ROW(4:15),4)+2第三步:组装原理与OFFSET函数第三步一样ABCDEFGHIJKL公公式式:=T(INDIRECT(R&INT(ROW(4:15)/4+256)&C&MOD(ROW(4:15),4)+2,0)思思维维三三:INDEX函函数数INDEX函数的原理与OFFSET函数一样,也是通过构造第二参数与第三参数来实现二维转一维,不过如果要形成内存数

20、组,就必须加上N(IF1,)结构,否则形成的就是多单元格数组公式,而不是内存数组第一步:根据INDEX函数设想第二参数与第三参数索引值区域索引测试11A12B13C14 现现在在我我们们来来证证明明一一下下如如此此D21 构构造造第第二二与与第第三三参参数数是是否否E22 正正确确F23G24H31I32J33K34L=INDEX(B257:E259,N(IF(1,B374:B385),N(IF(1,C374:C385)第二步:构造索引值与区域索引通过观察,发现上面构造出来的数组与OFFSET雷同索引值区域索引111213142122232431323334=INT(ROW(4:15)/4)=

21、MOD(ROW(4:15),4)+1第三步:组装这一步要给两个参数加上N(IF(1,)结构,否则不会形成内存数组,AAB请请通通过过F9键键查查看看前前后后两两个个公公式式的的区区别别,BCCDDEEFFGGHHIIJJKKLL=INDEX(B257:E259,N(IF(1,INT(ROW(4:15)/4),N(IF(1,MOD(ROW(4:15),4)+1)=INDEX(B257:E259,INT(ROW(4:15)/4),MOD(ROW(4:15),4)+1)思思维维四四：数数字字文文本本混混合合二二维维数数组组转转一一维维上面演示的是纯文本的转换,用T函数就可以转化成结果,那我们也可以举

22、一反三,如果源数据是数字那可以用N函数转换,那如果是文本与数字混合的应该怎么办,这里我介绍三种方法,不做解析,思路还是使用上面三个函数,但都不完美,这里只是起一个抛砖引玉的做用,第一种方法不会形成内存数组,第二三种方法嵌套太多,欢迎各位前辈高人提供更佳思路.A12DEF3HIJKL方方法法一一:结果结果A结结果果是是出出来来了了,但但用用F9查查看看只只显显示示一一个个单单元元格格的的结结果果,A套套T也也无无效效,只只是是更更为为糟糟糕糕,不不但但没没有有形形成成内内存存数数组组,1 不不会会形形成成内内存存数数组组,1反反而而把把数数字字也也转转成成文文本本型型了了22DDEEFF33HH

23、IIJJKKLL=IF(1,OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)=T(IF(1,OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)&)方方法法二二:A1 这这里里是是完完美美转转换换了了,而而且且形形成成内内存存数数组组,2 但但问问题题是是公公式式过过长长, ,嵌嵌套套也也多多了了DEF3HIJKL=IF(T(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)=,N(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(RO

24、W(4:15),4),T(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)方方法法三三:A12DEF3HIJKL=INDEX(B427:E429,N(IF(1,INT(ROW(4:15)/4),N(IF(1,MOD(ROW(4:15),4)+1)此此种种方方法法较较佳佳,但但是是嵌嵌套套还还是是太太多多,我我们们后后续续处处理理就就不不能能套套太太多多函函数数了了此贴为chenhh803原创,转贴请注明可可以以用用F9键键查查看看结结果果的的公公式式栏栏,形形成成的的是是内内存存数数组组2;3;4;5;62;3;4;5;6结果27 可可以以用用F9

25、键键查查看看结结果果的的公公式式栏栏,38 形形成成的的是是内内存存数数组组2,7;3,8;4,9;5,10;6,112,7;3,8;4,9;5,10;6,1149510611EXCEL函函数数常常用用技技巧巧浅浅析析(四四)数组的变化可以说是函数的精髓部分,对于数组的解析太多前辈高人做过很多的讲解过了,我这里只不过把前辈高人讲过的东西再重新解读一下,数组高手可以飘过。本技巧介绍的都是以CTRL+SHIFT+回车键结束的内存数组公式，所谓内存数组，简单的讲就是在内存中保存的或者说用F9键在公式栏查看时可以显示全部结果。单个元素:我这里为什么要把他叫做单个元素,因为无论是一个单元格也好,一个常量

26、数值或文本也好,在数组的运算中,我们即不能把他看成一个纵向数组,也不能把他看成一个横向数组,因为常常在与一维或二维的运算中,这个元素是具有扩展性的,如果与他运算的数组是一个五行一列的纵向数组,那这个元素也会自动扩展成一个五行同理,如果这个与其运算的数组是一个一行五列的横向数组,那这个元素也会自动扩展为一个一行五列的数组与之运算,其结果也必然是一个五行一列的结果;可可以以用用F9键键查查看看结结果果的的公公式式栏栏,形形成成的的是是内内存存数数组组2,3,4,5,62,3,4,5,6同样,一个二维数组与单个元素进行运算,那这个元素也会自动扩展成与之运算的二维数组的行列数,其结果与与其运算的二维数

27、组一致;一个横向数组与一个纵向数组进行运算,纵向数组扩展成为与其进行运算的横向数组的列数,形成一个行数与原数组相同,列数与纵向数组相同的二维数组;横向数组扩展成为一个行数与纵向数组相同,列数为原数组列数相同;然后两个新的数组相对应的值进行运算,得到一个行数与纵向数组相同,列数与横向数组相同的结果行数相同行数不同可可以以用用F9键键查查看看结结果果的的公公式式栏栏,形形成成的的是是内内存存数数组组7,8,9,10,11;7,8,9,10,11;8,9,10,11,12;9,10,11,12,13;10,11,12,13,14;11,12,13,14,158,9,10,11,12;9,10,11,

28、12,13;10,11,12,13,14;11,12,13,14,15两个一维同向数组运算,其中有一个条件就是这两个数组的行数或列数必须是相等的,其结果行或列数与原数组相同,如果不相等,则不会自动扩展,而会产生错误值;一维数组与二维数组的运算与一维同向数组原理相同,那就是一维数组的行数或列数必须与二维数组的行数或列数相同,一维数组会自动扩展成二维数组,其大小与二维数组一致,行列数一样结果1018#N/A#N/A行数相同1220#N/A#N/A列数不同1422#N/A#N/A1624#N/A#N/ALOOKUP函数一般利用第一参数来驱动数组的结果，我们来想像一下，我们需要构造一个什么样的数组才会

29、得到结果第二步:需要得到的结果为三行三列,我们的第一参数也必然是一个三行三列数组来驱动函数的结果第三步:在我们要得到正确结果中的第一参数中寻找规律,这个规律是很好找的,我们会发现这个数组直接用行列号就可以构造出来的,公公式式:=(ROW(1:3)-1)*3+COLUMN(A:C)首先用ROW(1:3)-1产生一个一列三行纵向数组,其结果为0;1;2,再用这个数组乘以3,其结果依然为一列三行的纵向数组0;3;6其原理为纵向数组的第一个元素乘以单个数字,其结果数组方向不会改变;最后用纵向的一维数组(ROW(1:3)-1)*3加上横向的一维数组COLUMN(A:C),其结果为一个三列三行的二维数组1

30、,2,3;4,5,6;7,8,9此数组运算的原理可以理解为:每一个纵向数组与每一个横向数组的元素相加,其结果摆放在两个数组行列相交的位置或者也可以这样理解:每一个横向数组与每一个纵向数组的元素相加,其结果摆放在两个数组行列相交的位置第四步:利用LOOKUP的向量法进行每一个第一参数的查找,得到结果的数组方向与第一参数一致利用INDIRECT函数对单元格引用的特性也可以实现以上结果,利用INDIRECT函数的第一参数来驱到数组的方向,公公式式:=(ROW(1:3)-1)*3+COLUMN(I:K)#VALUE!#VALUE!#VALUE!第四步:用INDIRECT函数引用上面得出的单元格地址,但

31、得到的结果为一个三维结果,用F9与单元格无法显示,在本示例中为文本,可以用T函数转换,如果是数字,就需要用N函数来转换,注注意意:T与与N函函数数转转换换三三维维后后得得到到的的结结果果只只是是每每一一个个三三维维平平面面中中第第一一个个单单元元格格的的结结果果,平平面面中中不不是是第第一一个个单单元元格格中中的的内内容容无无法法取取出出.利用OFFSET函数的第二参数对行的偏移,也可以实现以上效果,第二参数的数组构造与思维一一致,因为OFFSET函数产生的结果也是三维的,所以也需要用T函数来转换因为PINY版主的N(IF(1,)的发现,用VLOOKUP函数实现上面的内存数组不再是难事情,虽然

32、实现的公式看起来有点繁复,但毕竟我们又多了一把利器。同理,用N(IF(1,)也可以实现INDEX函数第二参数的数组化,而且INDEX函数结果的方向也是由第二参数来驱动.第一步:首先我们应该考虑应该怎样来构造第二参数与第三参数,想像一下,我们是否应该构造成以下对应数组,这里我们以B257单元格为偏移点.公公式式:=T(OFFSET(B257,B271:B282,C271:C282)第二参数的规律是重复我们原数据的列数的数据,可以用INT加除法实现,第二参数重复从0到原数据的列数减1,直接用MOD函数就可以得出了再把构造出的参数代入OFFSET函数,因为OFFSET函数取出的数据是三维平面,而这里

33、又是文本,所有要用T函数取出INDIRECT函数的原理与OFFSET函数差不多,利用INDIRECT函数的R1C1模式也可以构造出同样的数组,所谓的R1C1模式就是引用第几行(R1),第几列(C1)的单元格其实我们只要仔细观察一下,上面的数组与OFFSET函数的第二与第三参数基本上雷同,只是把数字的大小放大了而已INDEX函数的原理与OFFSET函数一样,也是通过构造第二参数与第三参数来实现二维转一维,不过如果要形成内存数组,就必须加上N(IF1,)结构,否则形成的就是=INDEX(B257:E259,N(IF(1,B374:B385),N(IF(1,C374:C385)=INDEX(B257

34、:E259,INT(ROW(4:15)/4),MOD(ROW(4:15),4)+1)上面演示的是纯文本的转换,用T函数就可以转化成结果,那我们也可以举一反三,如果源数据是数字那可以用N函数转换,那如果是文本与数字混合的应该怎么办,这里我介绍三种方法,不做解析,思路还是使用上面三个函数,但都不完美,这里只是起一个抛砖引玉的做用,第一种方法不会形成内存数组,第二三种方法嵌套太多,套套T也也无无效效,只只是是更更为为糟糟糕糕,不不但但没没有有形形成成内内存存数数组组,反反而而把把数数字字也也转转成成文文本本型型了了=T(IF(1,OFFSET(B373,INT(ROW(4:15)/4-1),MOD(

35、ROW(4:15),4)&)=IF(T(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)=,N(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4),T(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)技技巧巧解解析析一一：下表为奇偶个数相同的数据,现要对数据进行奇偶相间排列(不包含负数)源数据结果235235343722464737224635354734公公式式:=ABS(SMALL(-1A4:A9*A4:A9,(6-ROW(1:6)*MOD(ROW

36、(1:6),2)+INT(ROW(2:7)/2)公公式式解解析析:此公式主要是对SMALL函数的数组化构造,SMALL与LARGE函数的两个参数都支持数组,在构造数组的时候是用得比较多的.第一步:我们先来看一下SMALL函数的第一参数的构造过程,先用技巧一里面的-1N的方法来区分奇偶数,再与源数据相乘,这样结果就把奇数转化为负数,偶数不变区分奇偶源数据生成数组-1235-235134341*2462461372372-135-35-147-47=-1A4:A9第二步:根据第一步生成的结果,我们就要考虑第二参数的设置,可以设想一下,我们如何排列才能奇偶相间,从上一步的结果我们可以看出,如果用SM

37、ALL函数来构造把SMALL函数的第二参数转化为以下数组就可以了.设想的第二参数测试63721-23552462-474343-35=SMALL(-1A4:A9*A4:A9,B27:B32)从测试结果来看,这种办法是行得通的,即使源数据里面有相同数字,得出的结果也会是正确的,现在我们就要来考虑如何才能构造成这种数组转转化化过过程程:(6-ROW(1:6)*MOD(ROW(1:6),2)+INT(ROW(2:7)/2)5154003*13+200111000=6-ROW(1:6)=MOD(ROW(1:6),2)=(6-ROW(1:6)*MOD(ROW(1:6),2)第三步:组装SMALL函数的第

38、一与第二参数都构造出来了,问题也就简单了,我们只要把两个构造好的参数装入SMALL函数就好了,再用ABS函数把负数转为正数.结果372235246473435=ABS(SMALL(-1A4:A9*A4:A9,(6-ROW(1:6)*MOD(ROW(1:6),2)+INT(ROW(2:7)/2)当然,实现这种奇偶相间的法办还有很多,请用其它办法试解此题,比如换一种思维:=ABS(SMALL(-1A4:A9*A4:A9,MOD(ROW(1:6),2)*3+INT(ROW(2:7)/2)技技巧巧解解析析二二：源数据表为某公司三种产品的销售情况情况,单号中包含了日期信息,例如A62单元格ULK-201

39、10423-CC中的数字代表,2011年4月23日,源数据表每种产品是按日期降序排列的,现在要求按查询表的数量查询从最晚时间到每种产品累计到查询表的数量的销售明细。（此例最后的结果是多单元格数组公式，非内存数组）源数据表查询表单号物料名称销售数量KK-LL-A1GG-LL-B1FF-LL-C1ULK-20110423-CCKK-LL-A112462110341176ULK-20110419-CCKK-LL-A1497ULK-20110415-CCKK-LL-A1637ULK-20110410-CCKK-LL-A1847ULK-20110411-CCGG-LL-B1215ULK-20110407-CCGG-LL-B1819ULK-20110402-CCGG-LL-B1407ULK-20110430-CCFF-LL-C1988ULK-20110425-CCFF-LL-C11