继续介绍返回引用的函数们。
这三个函数都是条件函数,其实IFS和SWITCH都是Excel 2019之后才推出的新函数,它们的作用是类似的。
就以我们都很熟悉的IF函数来说吧,
=IF(B2>100, A2, A3)
这个公式无论B2取任何值,返回值都是单元格引用(要么是A2单元格,要么是A3)单元格。
我们可以使用函数ISREF验证这一点,
=ISREF(IF(B2>100, A2, A3))
不过我们在实际工作中利用IF函数(包括IFS,SWITCH函数)这个特性的机会很少。设想一下,如果我们要根据条件去返回某个区域,一般我们都是用MATCH函数根据条件去匹配,然后使用INDEX函数,或者OFFSET函数返回相应的区域。或者使用XLOOKUP函数返回相应的区域。
关于IF函数返回单元格区域引用的实例,我见到最多的是用于VLOOKUP函数反向查找,调换两列的顺序,
=IF({1,0},B1:B6,A1:A6)
这个公式的第一个参数(即表示条件的参数)是一个数组,计算过程是先取第一个元素:1,它表示TRUE,所以返回第二个参数:B1:B6,然后取第二个元素:0,表示FALSE,所以返回第三个参数:A1:A6,然后将这两个参数合成一个数组。
这里顺便说一下上次文章中介绍的CHOOSE函数的那个例子,
=CHOOSE({2,1},A1:A6,B1:B6)
这个公式也返回交换顺序后的两列。
它们的作用是一样的。
而且,这里要提醒一下,这么返回的不是一个单元格区域!
而是一个数组。
这两个公式的计算过程中,返回的是区域,但是最后需要将返回的多个区域合并,结果就是一个数组了。
顺便再说一下:
下面的公式:
=IF(B2>0, A2, A3)
返回的是一个单元格引用。
但是下面的公式返回的就是一个数组,即使B2:B6全部都大于0,
=IF(B2:B6>0,B2:B6,B2:B6)
尽管二者从数据逻辑上是相似的。
但是,下面的公式返回的又是一个单元格区域,
=IF(1,B2:B6,B2:B6)
其实,这其中的区别就在于条件(第一个参数是否是数组。如果是,那么返回的就是数组,否则就是单元格区域)。
XLOOKUP函数的详细介绍见这篇文章:VLOOKUP,HLOOKUP,LOOKUP,XLOOKUP
这个函数可以返回引用,
=XLOOKUP("数据",A1:B1,A2:B6)
这个特性的使用场景跟INDEX函数类似,都是根据条件找到两个单元格,然后返回一个区域:
=XLOOKUP(--"2001/1/1",A2:A10,B2:B10) :XLOOKUP(--"2006/1/1",A2:A10,B2:B10)
第一个XLOOKUP返回B3,第二个返回B8,合并就是B3:B8。
所以,如果要在数组场景中使用这个特性,就是使用CHOOSEROWS,用MTACH和SEQUENCE函数辅组,跟INDEX函数部分一样操作(见上一篇文章:这些函数需要特殊对待:那些返回引用的函数们 (1)- Excel函数式编程)
OFFSET函数的详细介绍见这里:Offset函数
这个函数的作用就是返回一个区域,
=OFFSET(B5, 5, 2, 5, 1)
非常好用。相当于可以从一个矩阵中获取任意一个子矩阵。
在实际应用时,其实单元格(第一个参数)一般是INDEX + MATCH获得的,或者是XLOOKUP函数获得的。
偏移的行或列(第二、三个参数)是输入的,或者是其他函数获得的。
需要的行数和列数(第四、五个参数)往往是COUNTIFS获得(行数),或输入的(列数)。
可惜这个函数在数组场景中不能使用。
为此,我做了一个自定义函数,它的用法和作用与OFFSET相同,可以用于数组或者单元格区域:
OFFSETARRAY = LAMBDA(array, rows, columns, height, width,
LET(
array_offset_rows_height, TAKE(DROP(array, rows), height),
array_offset_cols_width, TAKE(DROP(array_offset_rows_height, , columns), , width),
array_offset_cols_width
)
);
其实质就是使用TAKE函数和DROP函数对数组中不需要的部分进行取舍。
这个自定义函数的详细介绍见这篇文章:Excel中的OFFSET函数不能用于数组,试试OFFSETARRAY
未完待续
详情咨询客服(底部菜单-知识库-客服)
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
Excel企业应用 底部菜单:企业应用
联系客服