excelperfect INDEX函数虽然可以生成数组,但如果不用数组公式,似乎只能返回其生成的数组中的第一个元素。然而,可以使用INDEX函数来给公式提供数组。例如: =SUM(INDEX(A1:A10,N(IF(1,{1,4,8})))) 将计算单元格A1、A4和A8的和。 下面的公式也可以得到相同的结果: =SUM(INDEX(A1:A10,N(INDEX({1,4,8},,)))) 可以将其扩展到二维单元格区域: =SUM(INDEX(A1:C10,N(IF(1,{1,4,8})),N(IF(1,{1,2,3})))) 计算单元格A1、B4和C8的和,即: =SUM(A1, B4, C8) 此外,可以采取转置其中一个数组常量的标准技术来生成更多不同的结果: =SUM(INDEX(A1:C10,N(IF(1,{1,4,8})),N(IF(1,{1;2})))) 得到的结果与下面的公式相同: =SUM(A1, A4, A8, B1, B4, B8) 甚至生成我们通常可能会采用OFFSET函数来生成的数组: =SUM(INDEX(A1:C10,N(IF(1,{1,2,3})),N(IF(1,{1;2;2;3})))) 得到的结果与下面的公式相同: =SUM(SUBTOTAL(9,OFFSET(A1,,{0,1},3,2))) 实际上等价于: =SUM(A1:B3) + SUM(B1:C3) 当然,上述内容只是举例,实际上可以通过很多方法来实现上述结果。最关键的是,通过这些例子发现了有用的技术,即可以使用INDEX函数生成多个返回值。不像OFFSET函数,其第一个参数必须是对工作表中实际单元格区域的引用,INDEX函数可以接受和处理其引用的数组,该数组由公式中其他函数生成的值组成。 并且,这种强制返回多个值的技术的使用不限于INDEX函数。例如,试图找到A1:A10中第一次出现“A”、“B”和“C”所对应的相同行中B1:B10的最大值,如果使用公式: =MAX(VLOOKUP({'A','B','C'},A1:B10,2,0)) 不一定会得出正确的结果,原因是Excel会将该公式转换为: =MAX(VLOOKUP('A',A1:B10,2,0)) 也就是说,传递给VLOOKUP函数的数组中的3个元素仅第1个元素被处理。 然而,使用上文所讲解的强制生成多个值的技术,其公式为: =MAX(VLOOKUP(T(IF(1,{'A','B','C'})),A1:B10,2,0)) 可以得到正确的结果,如下图1所示。 图1 注:本技巧整理自excelxor.com,有兴趣的朋友可以研阅原文。
|
|
来自: hercules028 > 《excel》