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

EXCEL函数常用技巧浅析(四)技巧四:数组--随心所欲数组的变化可以说是函数的精髓部分,对于数组的解析太多前辈高人做过很多的讲解过了,我这里只不过把前辈高人讲过的东西再重新解读一下,数组高手可以飘过。本技巧介绍的都是以CTRL+SHIFT+回车键结束的内存数组公式，所谓内存数组，简单的讲就是在内存中保存的或者说用F9键在公式栏查看时可以显示全部结果。一:数组的基本原理1:一个纵向一维数组与单个元素的运算;单个元素:我这里为什么要把他叫做单个元素,因为无论是一个单元格也好,一个常量数值或文本也好,在数组的运算中,我们即不能把他看成一个纵向数组,也不能把他看成一个横向数组,因为常常在与一维或二维的运算中,这个元素是具有扩展性的,如果与他运算的数组是一个五行一列的纵向数组,那这个元素也会自动扩展成一个五行一列的数组与之运算,其结果也必然是五行一列的结果;纵向一维数组单个元素自动扩展结果1112可以用F9键查看结果的公式栏,2213形成的是内存数组{2;3;4;5;6}3+13+1444155516公式:=B13:B17+12:一个横向一维数组与单个元素的运算;同理,如果这个与其运算的数组是一个一行五列的横向数组,那这个元素也会自动扩展为一个一行五列的数组与之运算,其结果也必然是一个五行一列的结果;横向一维数组12345单个元素+1可以用F9键查看结果的公式栏,12345形成的是内存数组{2,3,4,5,6}+自动扩展11111结果23456公式:=C23:G23+13:一个二维数组与单个元素的运算;同样,一个二维数组与单个元素进行运算,那这个元素也会自动扩展成与之运算的二维数组的行列数,其结果与与其运算的二维数组一致;二行二列数组单个元素自动扩展结果16161127可以用F9键查看结果的公式栏,27+1271138形成的是内存数组{2,7;3,8;4,9;5,10;6,11}3838+1149494911510510510116114:一个横向数组与纵向数组运算;一个横向数组与一个纵向数组进行运算,纵向数组扩展成为与其进行运算的横向数组的列数,形成一个行数与原数组相同,列数与纵向数组相同的二维数组;横向数组扩展成为一个行数与纵向数组相同,列数为原数组列数相同;然后两个新的数组相对应的值进行运算,得到一个行数与纵向数组相同,列数与横向数组相同的结果横向数组678910纵向数组1+2345纵向数组扩展1111122222333334444455555+横向数组扩展678910678910678910678910678910结果789101189101112可以用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:一维数组与二维数组运算一维数组与二维数组的运算与一维同向数组原理相同,那就是一维数组的行数或列数必须与二维数组的行数或列数相同,一维数组会自动扩展成二维数组,其大小与二维数组一致,否则会产生错误,一维数组二维数组结果14758行数相同2+58710369912公式:=B92:B94+D92:E94一维数组二维数组结果14758行数不同2+587103699124#N/A#N/A公式:=B98:B101+D98:E1007:二维数组与二维数组的运算二维数组与二维数组的运算,两个数组的行列数必须一致,其结果行列数与原数组相同,否则会产生错误.数组一数组二结果159131018行列数一样26+1014122037111514224812161624数组一数组二结果1591317211018#N/A#N/A行数相同26+101418221220#N/A#N/A列数不同37111519231422#N/A#N/A48121620241624#N/A#N/A二：一维转二维ABCD转换成ABCEDEFFGHIGHI思维一：LOOKUP函数(此题出一位前辈高人贴子,具体贴子没查到)LOOKUP函数一般利用第一参数来驱动数组的结果，我们来想像一下，我们需要构造一个什么样的数组才会得到结果第一步：首先利用ROW函数构造LOOKUP函数的第二参数与第三参数(向量法)1A2B3C4D5E6F7G8H9ILOOKUP(现在不考虑第一参数,ROW(1:9),A9:A17)第二步:需要得到的结果为三行三列,我们的第一参数也必然是一个三行三列数组来驱动函数的结果想像一个如果要得到正确的结果,我们的第一参数就必须为以下数组:123456789第三步:在我们要得到正确结果中的第一参数中寻找规律,这个规律是很好找的,我们会发现这个数组直接用行列号就可以构造出来的,列号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函数的第一参数就应该如下 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf :B9B10B11略去B91011B12B13B14121314B15B16B17151617第二步:如何实现以上效果,其实规律同思维一是一样的,只是数字不一样而已列号91011行号191011公式:=(ROW(1:3)-1)*3+COLUMN(I:K)21213143151617公式解析:同思维一,只是增大了列号第三步:根椐INDIRECT函数的特性,加上列号,就形成了第一步的第一表B9B10B11B12B13B14B15B16B17第四步:用INDIRECT函数引用上面得出的单元格地址,但得到的结果为一个三维结果,用F9与单元格无法显示,在本示例中为文本,可以用T函数转换,如果是数字,就需要用N函数来转换,注意:T与N函数转换三维后得到的结果只是每一个三维平面中第一个单元格的结果,平面中不是第一个单元格中的内容无法取出.单个元素:我这里为什么要把他叫做单个元素,因为无论是一个单元格也好,一个常量数值或文本也好,在数组的运算中,我们即不能把他看成一个纵向数组,也不能把他看成一个横向数组,因为常常在与一维或二维的运算中,这个元素是具有扩展性的,如果与他运算的数组是一个五行一列的纵向数组,那这个元素也会自动扩展成一个五行一列的数组与之运算,其结果也必然是五行一列的结果;#VALUE!#VALUE!#VALUE!纵向一维数组如果不加T的结果为#VALUE!#VALUE!#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函数的第二与第三参数基本上雷同,只是把数字的大小放大了而已R1C1257225732574257525822583258425852592259325942595公式:=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函数一样,也是通过构造第二参数与第三参数来实现二维转一维,不过如果要形成内存数组,就必须加上N(IF{1},)结构,否则形成的就是多单元格数组公式,而不是内存数组第一步:根据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)=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函数就可以转化成结果,那我们也可以举一反三,如果源数据是数字那可以用N函数转换,那如果是文本与数字混合的应该怎么办,这里我介绍三种方法,不做解析,思路还是使用上面三个函数,但都不完美,这里只是起一个抛砖引玉的做用,第一种方法不会形成内存数组,第二三种方法嵌套太多,欢迎各位前辈高人提供更佳思路.A12DEF3HIJKL方法一:结果结果A结果是出来了,但用F9查看只显示一个单元格的结果,A套T也无效,只是更为糟糕,不但没有形成内存数组,1不会形成内存数组,1反而把数字也转成文本型了22DDEEFF33HHIIJJKKLL=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(ROW(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原创,转贴请注明技巧解析一：下表为奇偶个数相同的数据,现要对数据进行奇偶相间排列(不包含负数)源数据结果235235343722464737224635354734公式:=ABS(SMALL(-1^A4:A9*A4:A9,(6-ROW(1:6))*MOD(ROW(1:6),2)+INT(ROW(2:7)/2)))公式解析:此公式主要是对SMALL函数的数组化构造,SMALL与LARGE函数的两个参数都支持数组,在构造数组的时候是用得比较多的.第一步:我们先来看一下SMALL函数的第一参数的构造过程,先用技巧一里面的-1^N的方法来区分奇偶数,再与源数据相乘,这样结果就把奇数转化为负数,偶数不变区分奇偶源数据生成数组-1235-235134341*2462461372372-135-35-147-47=-1^A4:A9第二步:根据第一步生成的结果,我们就要考虑第二参数的设置,可以设想一下,我们如何排列才能奇偶相间,从上一步的结果我们可以看出,如果用SMALL函数来构造把SMALL函数的第二参数转化为以下数组就可以了.设想的第二参数测试63721-23552462-474343-35=SMALL(-1^A4:A9*A4:A9,B27:B32)从测试结果来看,这种办法是行得通的,即使源数据里面有相同数字,得出的结果也会是正确的,现在我们就要来考虑如何才能构造成这种数组转化过程:(6-ROW(1:6))*MOD(ROW(1:6),2)+INT(ROW(2:7)/2)51516400113*13+25200221113400033=6-ROW(1:6)=MOD(ROW(1:6),2)=(6-ROW(1:6))*MOD(ROW(1:6),2)=INT(ROW(2:7)/2)=(6-ROW(1:6))*MOD(ROW(1:6),2)+INT(ROW(2:7)/2)第三步:组装SMALL函数的第一与第二参数都构造出来了,问题也就简单了,我们只要把两个构造好的参数装入SMALL函数就好了,再用ABS函数把负数转为正数.结果372235246473435=ABS(SMALL(-1^A4:A9*A4:A9,(6-ROW(1:6))*MOD(ROW(1:6),2)+INT(ROW(2:7)/2)))当然,实现这种奇偶相间的法办还有很多,请用其它办法试解此题,比如换一种思维:=ABS(SMALL(-1^A4:A9*A4:A9,MOD(ROW(1:6),2)*3+INT(ROW(2:7)/2)))技巧解析二：源数据表为某公司三种产品的销售情况情况,单号中包含了日期信息,例如A62单元格ULK-20110423-CC中的数字代表,2011年4月23日,源数据表每种产品是按日期降序排列的,现在要求按查询表的数量查询从最晚时间到每种产品累计到查询表的数量的销售明细。（此例最后的结果是多单元格数组公式，非内存数组）源数据表查询表单号物料名称销售数量KK-LL-A1GG-LL-B1FF-LL-C1单号物料名称销售数量ULK-20110423-CCKK-LL-A112462110341176ULK-20110423-CCKK-LL-A1124ULK-20110419-CCKK-LL-A1497ULK-20110419-CCKK-LL-A1497ULK-20110415-CCKK-LL-A1637ULK-20110411-CCGG-LL-B1215ULK-20110410-CCKK-LL-A1847ULK-20110407-CCGG-LL-B1819ULK-20110411-CCGG-LL-B1215ULK-20110430-CCFF-LL-C1988ULK-20110407-CCGG-LL-B1819ULK-20110425-CCFF-LL-C1188ULK-20110402-CCGG-LL-B1407ULK-20110430-CCFF-LL-C1988ULK-20110425-CCFF-LL-C1188ULK-20110315-CCFF-LL-C1149公式:=INDEX(A:C,SMALL(IF((MMULT((ROW($1:$10)>=COLUMN(A:J))*(B62:B71=TRANSPOSE(B62:B71)),C62:C71)<=E62:G62)*($B62:$B71=E61:G61),ROW(62:71),4^8),ROW(1:29)),{1,2,3})&""公式解析:此题解法看起来比较复杂,但只要你对前面介绍的数组基础理解透了,再有一点点MMULT函数的基础,其实也不是很难,解法的关键在于数组的变化，现在我们就一层一层来剖析此条公式,此例解析比较简单,目的想让大家自己动手,一步一步去理解,为什么要这样构造?这样构造的目的何在?然后再转化成自己的功力。第一步：首先来看题意，要求查询累计销售，我们首先想到的肯定是SUBTOTAL函数，但此题三种数料的销售数量在一列之中，如果用SUBTOTAL函数解，那形成的累计销售就会把前面的物料种类也统计进去，因为技巧二说了，SUBTOTAL函数只能对单元格引用，所有我们这里就要用MMULT函数来完成此项任务，那我们就需要构造数组。1000000000110000000011100000001111000000111110000011111100001111111000111111110011111111101111111111‘=--(ROW($1:$10)>=COLUMN(A:J))这里我们利用行数大于列数的方法构造成一个十行十列的数组，这里我加多一个把逻辑值转为数值的方法，方便我们观察。显然这样看起来就初步形成了一个累计的初步模型；1111000000111100000011110000001111000000000011100000001110000000111000000000011100000001110000000111’=--(B62:B71=TRANSPOSE(B62:B71))把一个倒置的物料名称与没有倒置的物料名称比较，形成了一个以上十行十列的数组；再把上面两个数组相乘，产生结果如下：1000000000110000000011100000001111000000000010000000001100000000111000000000010000000001100000000111形成以上数组后，我们就可以看出只要用MMULT函数把这个数组与销售数量的矩阵相乘，就会形成每一个产品的销售明细的累计销售额；124621125821052151034144198811761325‘=MMULT((ROW($1:$10)>=COLUMN(A:J))*(B62:B71=TRANSPOSE(B62:B71)),C62:C71)第二步：通过MMULT函数的计算我们得到了各个产品的累计销售额，这个数组是十行一列，现在我们再用这个累计销售额与我们要查询的数量进行对比，形成了以下数组,此数组为十行三列;111111000000111011000011001000=--(MMULT((ROW($1:$10)>=COLUMN(A:J))*(B62:B71=TRANSPOSE(B62:B71)),C62:C71)<=E62:G62)再来排除每一列中不属于此种物料的销售额;100100000000010010000001001000=(MMULT((ROW($1:$10)>=COLUMN(A:J))*(B62:B71=TRANSPOSE(B62:B71)),C62:C71)<=E62:G62)*($B62:$B71=E61:G61)这样每一种产品符合查询条件的行就会在数组中变成1,第一列为产品一,第二列为产品二,第三列为产品三;通过IF函数把1转为行号,0转为最大行号,626553665536636553665536655366553665536655366553665536655366665536655366765536655366553665536655366553669655366553670655366553665536=IF((MMULT((ROW($1:$10)>=COLUMN(A:J))*(B62:B71=TRANSPOSE(B62:B71)),C62:C71)<=E62:G62)*($B62:$B71=E61:G61),ROW(62:71),4^8)第三步:通过SMALL函数提取符合条件的行号,用小到大排列,不符合的为6553562636667697065536655366553665536=SMALL(IF((MMULT((ROW($1:$10)>=COLUMN(A:J))*(B62:B71=TRANSPOSE(B62:B71)),C62:C71)<=E62:G62)*($B62:$B71=E61:G61),ROW(62:71),4^8),ROW(1:29))第四步:通过INDEX函数取得结果ULK-20110423-CCKK-LL-A1124ULK-20110419-CCKK-LL-A1497ULK-20110411-CCGG-LL-B1215ULK-20110407-CCGG-LL-B1819ULK-20110430-CCFF-LL-C1988ULK-20110425-CCFF-LL-C1188=INDEX(A:C,SMALL(IF((MMULT((ROW($1:$10)>=COLUMN(A:J))*(B62:B71=TRANSPOSE(B62:B71)),C62:C71)<=E62:G62)*($B62:$B71=E61:G61),ROW(62:71),4^8),ROW(1:29)),{1,2,3})&""