摘要：用于生成数据区域的引用，再将这个引用作为半成品，作为动态图表的数据源、或是作为其他函数的参数，进行二次加工。

小伙伴们好啊，今天和大家一起来学习OFFSET函数的几个典型应用。

1、函数作用：

用于生成数据区域的引用，再将这个引用作为半成品，作为动态图表的数据源、或是作为其他函数的参数，进行二次加工。

2、函数用法：

=OFFSET(基点,偏移的行数,偏移的列数,[新引用的行数],[新引用的列数])

第二参数使用正数时，表示从基地向下偏移，负数表示向上偏移。

第三参数使用正数时，表示向右偏移，使用负数时表示向左偏移。

第四和第五参数是可选的，如果省略这两个参数，新引用的区域就是和基点一样的大小。

例如，使用下面这个公式：

=OFFSET(C3,4,2,4,3)

就是以C3为基点，向下偏移4行，向右偏移2列，新引用的行数是4行，新引用的列数是3列，最终得到对E7:G10单元格区域的引用。

OFFSET函数就像是一个敌人的小分队，从据点（C3）出动，顺着大路向南走4里（C7）

拐弯儿再向东2里，这时候就到马家河子（E7）了

队长说了，我要以马家河子（E7）这个地方开始，再占领一片地盘。

有多大呢？向南4里，向东3里。结果就是E7:G10单元格区域。

1）计算指定区间的销售额

如下图所示，要计算从1月份到指定月份的累计销售额。

F4单元格公式为：

=SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0)))

MATCH(F2,A2:A13,0)部分，先使用MATCH函数计算出F2单元格中的月份在A2:A13中的位置，结果为9.

OFFSET函数以B2单元格为基点，向下偏移0行，向右偏移0列，以MATCH函数的计算结果作为新引用的行数，最终得到B2:B10单元格区域的引用，再使用SUM函数计算这个区域中的总和，得到从1月份到指定月份的销售总额。

2）计算最近7天的平均销量

如下图所示，A列和B列是销售流水记录，要计算出最近7天的平均销量。

F4单元格公式为：

=AVERAGE(OFFSET(B1,COUNT(B:B),0,-7))

先使用COUNT函数，统计出B列的数值个数。

OFFSET函数以B1为基点，以COUNT的结果作为向下偏移的行数，也就是B列有多少个数值，就向下偏移多少行。

这时候就相当于到了B列数值的最后一行，给定的偏移列数是0，新引用的行数是-7，得到从B列数值的最后一行开始，再向上7行这样一个动态的区域。

如果B列的数值增加了，COUNT函数的计数结果就增加了，OFFSET函数的行偏移参数也就随之变化。

就相当于一竿子捅到底，然后来个烧鸡大窝脖儿，向上引用7行，所以得到的始终是最后7行的引用。

最后使用AVERAGE函数计算出这个引用区域中的平均值。

3）计算筛选后的商品总价

如下图，是各食堂的采购记录，需要计算筛选后的商品总价。

G1单元格公式为：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(1:9),0))*C2:C10*D2:D10)

要计算筛选后的内容，首先需要判断单元格是不是处于显示状态。

先来看OFFSET(A1,ROW(1:9),0)部分，OFFSET函数以A1单元格为基点，向下偏移的行数是ROW(1:9)的计算结果，表示依次向下偏移1~9行，最终得到9个引用区域，每个单元格区域由一个单元格构成。

这里涉及到多维引用的知识点了，小伙伴们如果犯迷糊，可以先收藏一下。

接下来使用SUBTOTAL函数对OFFSET函数得到的多个引用区域进行处理，第一参数使用3，表示使用COUNTA函数的计算规则，即依次统计A2~A9这九个单元格区域中的不为空的单元格个数。

如果单元格处于显示状态，则对这个单元格的统计结果为1，否则统计结果为0。

这部分公式得到类似下面的效果：

{0;0;0;0;0;1;1;1;1}

再用SUBTOTAL函数的结果乘以C列的单价和D列的数量，如果单元格处于显示状态，则相当于1*数量*单价，否则相当于0*数量*单价。

最后使用SUMPRODUCT函数对乘积进行求和，这样就得到筛选后的商品总价了。

除了以上常规的用法，OFFSET函数还经常用于动态图表的制作，这些内容咱们以后继续分享。

好了，今天的内容就是这些吧，祝各位小伙伴一天好心情！

图文制作：祝洪忠

来源：Excel之家