Excel中，OFFSET函数的使用方法 _Excel

OFFSET是Excel中的函数，在Excel中，OFFSET函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用。
它的语法结构为：
【Excel中，OFFSET函数的使用方法】=OFFSET（基准位置，向下或上偏移几行，向右或左偏移几列，引用区域的高度，引用区域的宽度）

文章插图

公式里面的第1个参数可以是单元格，也可以是单元格区域，第2和第3个参数可为正数，也可以是负数，如果是正数，表示向下和向右偏移，如果是负数，则表示向上和向左偏移，第4和第5个参数如果省略不写，则默认为和第1个参数大小一致。

返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值#VALUE! 。

函数说明
如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF! 。
如果省略 height 或width，则假设其高度或宽度与 reference 相同。
函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数 OFFSET可用于任何需要将引用作为参数的函数。
例如，公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。

函数示例
将示例复制到空白工作表中，你可能会更易于理解该示例。

A
B
1
公式
说明（结果）
2
=OFFSET(C3,2,3,1,1)
显示单元格 F5 中的值 (0)
3
=SUM(OFFSET(C3:E5,-1,0,3,3))
对数据区域 C2:E4 求和 (0)
4
=OFFSET(C3:E5,0,-3,3,3)
返回错误值 #REF!，因为引用区域不在工作表中
具体应用：
OFFSET函数经过偏移后返回的是一个区域，所以我们可以对这个区域求和、平均值、计数、最大最小值等。比如对下面的返回的区域求和，那么在offset函数前加上sum函数，然后就可以计算出结果是36 。

文章插图

OFFSET与一个match函数结合求和。
在下图中，要计算1月到某月的销量和，如果切换月份时，累计销量也会变化。该如何操作呢？
首先，在F2单元格添加数据验证。选择【数据】——【数据验证】——允许选择【序列】——来源选择A2到A13单元格的数据——【确定】。这时我们就在F2单元格建立好月份的下拉菜单选项。
然后在G2单元格输入函数=SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0)))，此时在F2单元格选择相应的月份，G2单元格的累计销量就会随着选择的月变化而变化。
这个函数有三个公式，最里面的MATCH(F2,A2:A13,0)，表示查找F2位于A2到A13单元格的第几行，比如F2单元格是十月，十月在A2到A13单元格的第10行，所以match函数返回的结果是10 。OFFSET(B2,0,0,10）中省略了第五个参数，返回的结果是B2到B11单元格，最后利用sum函数对B2到B11单元格进行求和。

文章插图

OFFSET函数与多个match函数进行求和。
仍然是上面一组数据，那么可不可以求任意两个月份之间的累计销量呢？我们考虑到既然match函数返回的值是所选单元格在区域中的位置，那么就可以利用match函数嵌套来编制公式。
我们在D2和F2单元格分别设置月份的下拉菜单选项，然后在G2单元格中输入函数=SUM(OFFSET(B2,MATCH(D2,A2:A13,0)-1,0,MATCH(F2,A2:A13,0)-MATCH(D2,A2:A13,0)+1),0) 。此时我们在D2和F2中选择相应的月份，就可以求出两个月份之间的累计销量了。
这个函数看上去比较长，实际在编写函数的时候比较容易想到，也容易理解，因为match函数可以返回行数，所以在以B2单元格为起始单元格的前提下，向下偏移的行数要根据D2单元格的变化而变化，第二个参数用了MATCH(D2,A2:A13,0)-1，表示如果D2单元格选择九月，那么这个match函数返回的是8，即9月对应的销量在B2开始数的第9-1=8行数据。第4个参数用到了两个match函数相减，因为F2单元格所在的行数减D2单元格所在的行数，需要对计算结果加1进行调整。最后利用sum函数求和即可。
但是这种情况如果选择时D2单元格大于F2单元格时，计算的就不是正确结果怎么办？此时只要把offset函数第四个参数嵌套一个if函数，即如果F2的月份大于D2，那么match函数相减后加1，如果F2的月份小于D2，那么等于match函数相减后减1 。这样设置后无论如何选择月份都会计算出正确结果。