【原】Excel中的OFFSET函数不能用于数组，试试OFFSETARRAY：自定义函数

ExcelEasy 2023-05-15 发布于北京

展开全文

Excel中的OFFSET函数非常有用。尤其是在设计计算效率高的公式时，经常可以看到这个函数的身影，比如，在这几篇文章中，它都起了非常关键的作用：

Excel奇招！妙用算法，改进SUMIFS公式计算效率

Excel函数式编程：一个简单的方法，让预订问题的计算速度提升100倍

Excel的这个求和公式，速度是不可思议的快

但是，这个函数只能用于单元格区域。因此，如果在LET函数或者LAMBDA函数内部，我们需要对过程中的中间结果使用类似于OFFSET函数的功能，这个函数就无能为力了。

所以，我们需要OFFSET函数的数组版本。

回顾OFFSET函数的作用

简单地说，OFFSET函数的作用就是从一个给定的区域左上角单元格开始，向指定方向偏移给定行数和列数，然后从偏移到的位置开始，取给定高度（行数）和宽度（列数）的区域：

上图中的左上角为B5单元格，黄色区域为下面公式的返回结果：

=OFFSET(B5, 5, 2, 5, 1)

OFFSETARRAY函数

要设计OFFSETARRAY函数，参照OFFSET函数的用法，就是在一个数组中，从数组的第一个元素开始，取出给定行数和列数之后的一个给定大小子数组。（为了简单起见，我们假定针对的是二维数组。对于一维数组的情况，只要将其看作行或列为1的二维数组即可）

所以OFFSETARRAY函数和OFFSET函数应该具有相同的参数，

=OFFSETARRAY(arr, rows, columns, height, width)

除了第一个参数外，后面四个参数与OFFSET函数相同。

由于这个自定义函数时针对数组的，因此，我们不能使用数组中的一个元素来标定起始位置，因此，我们总是使用整个数组作为第一个参数，而将给数组的第一个元素作为起始位置）。

接下来只要简单的几个步骤：

1. 去掉数组arr中从第一行开始，总共rows行，这可以通过DROP函数完成：

DROP(array, rows)

2. 在上一步去掉rows行的数组中，选取总共height行的数组：

array_offset_rows_height, TAKE(DROP(array, rows), height)

3. 在上一步的基础上，去掉前面总共columns列，

DROP(array_offset_rows_height, , columns)

4. 在上一步的基础上，取给定的width的列，

TAKE(DROP(array_offset_rows_height, , columns), , width)

5. 大功告成。

完整的自定义函数代码如下：

//OFFSETARRAY: offset for arraOFFSETARRAY = 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        ));