INDEX函数是非常强大好用的查找引用函数,常用语法如下: =INDEX(array,row_num,column_num) 通过参数row_num指定行号,参数column_num指定列号就能从参数array中返回对应位置的数据。但当参数 row_num 或 column_num 使用数组时,INDEX 函数并不能生成内存数组,如图1所示。 图1 单列数据隔行求和 C2单元格公式为 =SUM(INDEX(列1,{1;3;5;7;9})) 其中“列1”为名称,指代A2:A11单元格区域。虽然从逻辑上看公式没有问题,但实际SUM函数只处理了INDEX函数返回的第一个数据,因此返回1。 C3单元格公式为 =SUM(INDEX(列1,N(IF(1,{1;3;5;7;9})))) 正确返回隔行求和的结果。原理解析如下。 使用N函数结合IF函数对INDEX的参数row_num或column_num进行处理后,INDEX函数就能返回内存数组,具体使用时可参考以下模型: =INDEX(array,N(IF(1,row_num)),N(IF(1,column_num))) 如图2所示,将参数row_num和column_num同时进行处理后,INDEX函数就能返回二维的内存数组,其中D3单元格公式如下: =SUM(INDEX(数据表,N(IF(1,{1;3;5;7;9})),N(IF(1,{1,2})))) 图2 二维区域隔行求和 原理和单列隔行求和相同,不再赘述。VLOOKUP 函数第1个参数 lookup_value 使用数组时也不能返回内存数组,考虑参数lookup_value一般为文本,此时可以使用T函数结合IF函数对lookup_value参数进行处理,最终使得VLOOKUP函数返回内存数组。具体使用时可套用一下模型。 =VLOOKUP(T(IF(1,lookup_value)),table_array,col_index_num,range_lookup) 如图3 所示,希望将索引为 A 和 C 对应的数量相加,E2 单元格公式为 “=SUM(VLOOKUP ({'A';'C'},$A$2:$B$5,2,0))”, 从逻辑上看公式没有问题,但实际SUM函数只求得A对应的数量1。E3单元格使用T函数结合IF函数处理后,返回正确的结果,E3单元格公式如下: =SUM(VLOOKUP(T(IF(1,{'A';'C'})),$A$2:$B$5,2,0)) 图3 强制VLOOKUP函数返回内存数组 |
|