一篇文章让你彻底搞懂Excel中的offset公式

前面我们用Excel中的数据验证+sumifs公式实现了一个简单的动态图表，感兴趣的可以看这里Excel动态图表初体验：数据验证+sumifs。

然而，要实现更加复杂的动态图表，需要offset公式结合各种控件才能实现。

本文就先来说说Excel动态图表中的核心公式offset吧。

offset公式能够返回对单元格区域中指定行数和列数的区域的引用，初学者看完这句话估计蒙了，还是不明白啊。

下面用一个图来说明，例如，我们想引用下方黄色区域中的蓝色区域。

如何引用呢？思路如下，分成两个问题。

第一个问题，如何找到引用区域？

首先将数字所在的单元格作为起点，然后先向下偏移2行，再向右偏移3列，此时到了数字16所在的单元格，

第二个问题，引用区域有多大？

蓝色区域是一个2行1列的区域，所以行数为2，列数为1。

搞明白了引用的思路，回到offset这个公式。

offset公式的用法为：

OFFSET(reference, rows, cols, [height], [width])

其中有5个参数，说明如下。

第一个参数表示引用的起点，或基点。

第二个参数表示要偏移几行，正数向下，负数向上。

第三个参数表示要偏移几列，正数向右，负数向左。

第四个参数表示引用区域有几行。

第五个参数表示引用区域有几列。

结合上面的思路，就很容易理解这5个参数的意思。

下面来3个例子。

示例1：有如下数据区域，需要用offset公式引用蓝色单元格。

输入公式：=OFFSET(A1,1,3,1,1)，回车即可得到结果，如下图所示。

解释一下，上面这个公式表示以A1单元格为起点，向下偏移1行，向右偏移3列，引用区域的大小为1行1列。

当然，引用一个单元格时，offset公式中的最后两个参数可以省略，即=OFFSET(A1,1,3)也是可以的。

示例2：有如下数据区域，用offset公式引用蓝色单元格区域。

输入公式：=OFFSET(A7,2,3,2,1)，回车，然后选中一个2*1的区域，在上面的编辑栏中Ctrl+Shift+Enter一起按，即可得到结果，如下图所示。

说明：由于要返回多个结果，所以将offset公式作为数组公式执行。

Ctrl+Shift+Enter为数组公式的执行方式。

示例3：有如下数据区域，用offset公式引用蓝色单元格区域。

输入公式：=OFFSET(A13,0,2,3,4)，回车后会看到“#VALUE!”错误。

不过不用担心，以公式所在单元格为起点，选中一个3*4的区域，在上面的编辑栏中，Ctrl+Shift+Enter一起按，即可得到结果，如下图所示。

到此为止，相信你已经明白了offset公式的用法，接下来思考一下，如果起点不选择数字所在的单元格会怎样？

例如，示例2中的数据区域，如果起点选择为数字6所在的单元格，offset公式应该如何写呢？

先分析一下，以6为起点，需要向下偏移2行，向左偏移2列，被引用区域的大小为2行1列。

所以offset公式应该为：=OFFSET(F7,2,-2,2,1)

注意：第三个参数是-2，因为是向左偏移。

按照数组公式的执行方式执行，即可得到如下图所示的结果。

以上就是Excel动态图表中的核心公式offset的用法，希望对你也有帮助。

想了解更多精彩内容，快来关注笨鸟学数据分析