【原】这些函数需要特殊对待：那些返回引用的函数们（2）- Excel函数式编程

ExcelEasy 2024-04-04 发布于北京

展开全文

继续介绍返回引用的函数们。

IF/IFS/SWTICH

这三个函数都是条件函数，其实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

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函数

这个函数的作用就是返回一个区域，

=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        ));