EXCEL中必须要掌握的引用函数OFFSET用法介绍

EXCEL中的引用函数OFFSET是必须要掌握的函数之一，它在EXCEL中的应用非常广泛，从复杂的数据汇总、到数据透视表再到高级动态图表，都离不开功能强大的OFFSET函数。

例1：先举一个例子来感受一个OFFSET函数

如下图所示，在D2中输入：

= OFFSET(A1,1,2,1,1)，表示引用以A1为基点，向下偏移1行、向右偏移2列的值。

各参数解释如下：

A1：引用的单元格。

1： 表示要移动的行数。 正数意味着向下移动，负数意味着向上移动。

2： 表示要移动的列数。 正数意味着向右移动，负数意味着向左移动。

1（倒数第二个值）：（可选。） 表示要返回的数据行数。 这个数必须是正数。

1（最后值）：（可选。） 表示要返回的数据列数。 这个数必须是正数。

下面再举一些例子对这个函数进行说明。

例2：提取最后的报价

D2单元格内是写好的公式返回的最后报价。

D2 = OFFSET(B1,COUNTA(B:B)-1,)

COUNTA(B:B)-1返回B列有报价的单元格数量。公式是从B1开始向下引用最后一行，偏移的列为为0，参数省略。

例3：按条件计算区域数据之和

如下图所示，当条件有所变化时，公式自动计算条件所对应的部门数据之和。

= SUM(OFFSET(A2:A8,,MATCH(G2,A1:E1,)-1))

本例中向下偏移量为0，故省略OFFSET函数中第2个参数；MATCH函数返回向右偏移的列数。

例4：将二维数组转化为一维数组

如下图所示，左部分为二维数据，在F2单元格中输入公式：

OFFSET(A$1,INT((ROW(A1)-1)/4),MOD(ROW(A4),4))

下拉填充公式，即可转化为一维数组。

以单元格A1为引用点，用INT((ROW(A8)-1)/4)返回向下偏移量，用MOD(ROW(A11),4))返回向右的偏移量。

例5：按条件动态引用区域数据

如下图所示，按条件引用各地区的整行数据，该整行数据作为动态图表的数据源。

选中B9:F9输入数组公式：

= OFFSET(B1:F1,MATCH(A9,A2:A5,),)

输入完毕后按CTRL + SHIFT +ENTER形成数组公式。

OFFSET公式中MATCH函数返回向下偏移的行数，向右偏移为0，该参数省略。