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

实例 基本原理与维数转换 KK LL A B C D E F G H I =ROW(1:3)-1 =COLUMN(A:C) =(ROW(1:3)-1)*3 =C60:C62+D59:F59 =LOOKUP((ROW(1:3)-1)*3+COLUMN(A:C),ROW(1:9),B9:B17) B9 B10 B11 B12 B13 B14 B15 B16 B17 =IF({1},(ROW(1:3)-1)*3+COLUMN(A:C)) =IF({1,0},ROW(1:9),B9:B17) =INDEX(B9:B17,N(IF({1},(ROW(1:3)-1)*3+COLUMN(A:C)))) EXCEL函数常用技巧浅析(四) 数组的变化可以说是函数的精髓部分,对于数组的解析太多前辈高人做过很多的讲解过了,我这里只不过把前辈高人讲过的东西再重新解读一下,数组高手可以飘过。 转换成 LOOKUP函数一般利用第一参数来驱动数组的结果，我们来想像一下，我们需要构造一个什么样的数组才会得到结果 第一步：首先利用ROW函数构造LOOKUP函数的第二参数与第三参数(向量法) LOOKUP(现在不考虑第一参数,ROW(1:9),A9:A17) 第二步:需要得到的结果为三行三列,我们的第一参数也必然是一个三行三列数组来驱动函数的结果 想像一个如果要得到正确的结果,我们的第一参数就必须为以下数组: 第三步:在我们要得到正确结果中的第一参数中寻找规律,这个规律是很好找的,我们会发现这个数组直接用行列号就可以构造出来的, 列号 公式:=(ROW(1:3)-1)*3+COLUMN(A:C) 行号 公式解析: 首先用ROW(1:3)-1产生一个一列三行纵向数组,其结果为{0;1;2},再用这个数组乘以3,其结果依然为一列三行的纵向数组{0;3;6} 乘以3 其原理为纵向数组的第一个元素乘以单个数字,其结果数组方向不会改变; 再用COLUMN(A:J)产生一个一行三列的横向数组,其结果为{1,2,3} 最后用纵向的一维数组(ROW(1:3)-1)*3加上横向的一维数组COLUMN(A:C),其结果为一个三列三行的二维数组{1,2,3;4,5,6;7,8,9} 此数组运算的原理可以理解为:每一个纵向数组与每一个横向数组的元素相加,其结果摆放在两个数组行列相交的位置 或者也可以这样理解:每一个横向数组与每一个纵向数组的元素相加,其结果摆放在两个数组行列相交的位置 第四步:利用LOOKUP的向量法进行每一个第一参数的查找,得到结果的数组方向与第一参数一致 利用INDIRECT函数对单元格引用的特性也可以实现以上结果,利用INDIRECT函数的第一参数来驱到数组的方向, 第一步:我们设想一下我们我们应该如果构造INDIRECT函数的第一参数,现我们的原数据在B9:B17区域, 那INDIRECT函数的第一参数就应该如下表: 略去B 第二步:如何实现以上效果,其实规律同思维一是一样的,只是数字不一样而已 列号 行号 公式:=(ROW(1:3)-1)*3+COLUMN(I:K) 公式解析:同思维一,只是增大了列号 第三步:根椐INDIRECT函数的特性,加上列号,就形成了第一步的第一表 第四步:用INDIRECT函数引用上面得出的单元格地址,但得到的结果为一个三维结果,用F9与单元格无法显示,在本示例中为文本,可以用T函数转换,如果是数字, 就需要用N函数来转换,注意:T与N函数转换三维后得到的结果只是每一个三维平面中第一个单元格的结果,平面中不是第一个单元格中的内容无法取出. 如果不加T的结果为 最后公式:=T(INDIRECT(b(ROW(1:3)-1)*3+COLUMN(I:K))) 思维三:OFFSET函数 利用OFFSET函数的第二参数对行的偏移,也可以实现以上效果,第二参数的数组构造与思维一一致,因为OFFSET函数产生的结果也是三维的,所以也需要用T函数来转换 公式:=T(OFFSET(B8,(ROW(1:3)-1)*3+COLUMN(A:C),)) 思维四:VLOOKUP函数 因为PINY版主的N(IF({1},)的发现,用VLOOKUP函数实现上面的内存数组不再是难事情,虽然实现的公式看起来有点繁复,但毕竟我们又多了一把利器。 第一步：用N(IF{1},)可以实现第一参数的数组化,构造方法如思维一的第三步; 第二步:构造VLOOKUP函数第二参数,这里可以用IF函数构造一个9行二列的数组 第三步:进行组装,VLOOKUP函数结果的方向由第一参数驱动, 公式:=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({