详解OFFSET函数

OFFSET函数可以给我们提供了一个对单元格区域的引用，从给定的起始单元格开始，移动到给定的单元格并扩展给定的高度和宽度。

OFFSET函数的语法如下：

=OFFSET(起始单元格,移动的行数,移动的列数,高度,宽度)

其中：

• 起始单元格：想要从哪个单元格或单元格区域开始偏移。

• 移动的行数/列数：想要从起始单元格移动多少行/列，可以指定正值、负值或零。

• 高度,宽度：想要返回的单元格区域大小。例如5,3将指定返回的单元格区域为5行3列。

此外，OFFSET函数的所有参数都可以引用其它单元格，也就是说，可以编写：

=OFFSET(B1,B2,B3,B4,B5)

引用的单元格是：以B1为起点，偏移B2中数字指定的行数和B3中数字指定的列数，返回B4中数字指定的行数和B5中数字指定的列数大小的单元格区域。

如下图1所示的示例，帮你理解OFFSET函数。

图1

为什么不直接输入对单元格区域的引用，而要使用OFFSET呢？有以下两个原因：

• 如果直接输入对单元格区域的引用，例如A1:C5，那么这个引用是静态的。然而有时候，我们想要单元格区域是动态的，因为数据会不断发生变化，例如在新的行列中添加数据。

• 有时候，我们不知道单元格区域的实际地址，只知道从某个单元格开始。

下图2所示是一个交互式工作簿，以帮助你了解OFFSET函数是如何工作的。当你输入所有5个参数时，工作簿会突出显示你的OFFSET函数给出的单元格区域。多试试，你就会更好地理解这个函数。

图2

示例

如下图3所示，列A中的数据是不断更新的，现在要求最后5个数字的平均值，可以使用公式：

=AVERAGE(OFFSET(A1,COUNTA(A1:A1000)-5,0,5,1))

图3

OFFSET的局限

虽然使用OFFSET函数的公式可以返回一个动态单元格区域，但它也有一些限制：

• OFFSET函数是易失的：这意味着，只要你的工作簿中有任何更改，就会重新计算 OFFSET公式。如果工作簿不大，使用OFFSET公式不是问题。但是，当在大型工作簿中使用大量的OFFSET公式时，Excel会因为它而需要太多时间来重新计算。

• 使用OFFSET函数的公式很难调试：因为引用是动态的，所以调试包含大量OFFSET函数的公式的工作簿会变得棘手。

注：本文学习整理自chandoo.org，供有兴趣的朋友参考。