【前言】
offset函数是判断excel函数使用者是否进阶的一个重要函数之一。在实际工作中，如果你需要对工作中的数据文件进行系统化、自动化的建模，那么势必会使用这个函数。
【功能及语法】
offset函数的功能是，以指定的引用为参照系，通过给定的偏移量返回新的引用。
语法：offset(reference,rows,cols,[height],[width])
reference 是原基础点
rows 是要偏移的行数，正数向下，负数向上，零不变。
cols 是要偏移的列数，正数向右，负数向左，零不变。
[height] 是基础点偏移后，纵向扩展几行，正数向下扩展，负数向上扩展。
[width] 是基础点偏移后，横向扩展几列，正数向右扩展，负数向左扩展。
如果不使用第四个和第五个参数（但不可以为零），则新引用的区域和原基础点大小一致。
原基础点可以是一个单元格，也可以是一个区域。
刚刚接触offset函数的同学，想要理解上面这些参数，可能存在一定的难度，那么我们用一个图解的方式来给大家说明一下吧。
相信大家看这个图都花费了不少时间吧。我们可以先按照上图的指引，将数据填入offset函数中，实际操作一下，来看看是否和新区域的地址一致呢？
先来测试下第一个例子，看看正数为参量的运行结果：
通过验算，对黄色 “新区域”中的值进行求和，等于256，与单元格c15中的值一致，结果正确。如果同学们想模拟这个数据，也可以选中c15单元格，再通过工具栏中“公式——公式审核——公式求值”的功能，就能更加直观的看到offset的返回值。（在函数中使用f9也是可以的，选中公式中offset的函数部分，再按f9即可，这里就不多讲了。）
再来测试下第二个例子，看看负数为参量的运行结果：
大家可以用“公式求值”的方式，自己测试一下，看看offset函数区域的返回值。
那么知道了offset的基本运行原理之后，它在实际的工作中就可以帮助我们进行很多的操作和运算，而且有了这个函数的参与，可以实现excel中很多自动化的效果。下面让我们一起来看看offset函数在实际操作中起到的强大作用！
一、初级常规用法
作为其他函数的区域引用，应该是offset函数最基础的用途了。offset函数并不是移动了单元格区域，而是返回了一个偏移扩展后的区域地址。因此所有将引用区域作为参数的函数，都可以利用offset函数的返回值，例如我们上面的例子sum（offset（）），再比如下面这个例子：
函数原理和上面的用法相同，我们就不再赘述了，依然是利用offset函数返回的区域作为max函数的参数。
二、进阶常规用法
绝技①：模拟转置transpose函数
我们在使用transpose函数前，需要先选择相应大小的转置区域，而且还需用ctrl+shift+enter三键结束公式，比较繁琐。
这里我们可以使用offset函数来模拟这个转置的效果，如上图所示。
a11单元格函数：
=offset($a$1,column()-1,row()-11)
函数解析：
转置数据其实就是一个“行转列”、“列转行”的过程，再说具体点就是行号与列号互换的问题。在原数据中的第一列“姓名”列，转置后变成了新区域中的第一行。同理“姓名”列中每行的行号，就成为了转置后的列号。使用offset的原理，就是偏移取值的时候，调换行列号的引值范围。
★ 比如a11单元格，column()=1，1-1=0，那么offset的第二参数为0，说明原基础点的行数不偏移（offset的第二参数表示行偏移量，不熟悉的话看看前面的内容哟！）。row()=11，11-11=0，offset的第三参数为0，说明列数也不偏移，所以引用的是原基础点a1单元格的值。
★★ 把函数向右拉动填充，b11单元格，column()=2，2-1=1，那么offset的第二参数为1，说明原基础点的行数向下偏移一个位置。row()=11，11-11=0，offset的第三参数为0，说明列数不偏移，所以b11单元格引用的是基础点a1向下偏移后的a2单元格的值。
★★★ 把a11单元格的函数向下拉动填充，a12单元格，column()=1，1-1=0，行数不偏移。row()=12，12-11=1，offset的第三参数为1，说明列数从基础点a1向右偏移一个位置，引用的是b1单元格的值（我们公式中的a1之所以使用绝对引用，是因为我们所有的单元格都是以a1为基础点）。
以此类推，当我们使用鼠标下拉右拉填充公式之后，借助column和row函数帮我们定位出各个单元格的偏移量，由此达到了转置的效果。
绝技②：模拟vlookup函数的反向查询功能
vlookup函数的反向查询大多是借助数组完成的，但因为数组的原因，在数据量较多的情况下，函数可能会卡顿，所以很多同学也会使用index函数来代替。那么今天就再丰富一下大家的知识量，我们用offset函数来处理这类问题。
c12单元格函数：
=offset($a$1,match(d2568,$b$2:$b$7,0),)
函数解析：
我们以单元格a1作为原基础点，需要返回的值与原基础点在同一列，所以我们只需要考虑offset函数的行偏移量，不用考虑列偏移量。因为员工编号一般都是具有唯一性的值，所以我们采用match函数得到编号“d2568”在区域b2:b7中的序号，返回值4作为offset函数的行偏移量，带入到offset函数中，=offset($a$1,4,)。列偏移省略默认为0，扩展宽度和扩展高度省略默认为1 （即一个单元格），是不是就是a5单元格啦！
绝技③：数据重置升级版——重排数据结构
在f2:h2区域输入公式后，下拉填充数据，就得到了右面的一维数据表。这种重排数据的问题，在实际工作中应该不少见吧！那么同学们会选择什么方法解决呢？作者反而觉得offset函数的思路更加的简洁清晰。
函数解析：
第一步：得到连续出现的姓名
f2单元格函数：
=offset($a$1,int((row(f1)-1)/3)+1,)
因为科目一共有三个，所以可以确定同一个姓名需要出现三次，那么当我们下拉f2单元格填充函数的时候，就要保证offset函数的行偏移量每3个单元格的参数值都是一样的。这里就需要有一个“除数取整”的数学思维了，我们列个图来辅助说明：
从图中我们可以看出一组序号，通过int((序号-1)/3)+1的转换后，就可以得到右侧的序列（如果有4个科目，那就把3改成4，依此类推）。将这个序列号放入offset函数的第二参数，作为行偏移的标准，就可以得到我们姓名列的效果了。
第二步：给同一个人分配不同的科目
g2单元格函数：
=offset($a$1,,mod(row(g1)-1,3)+1)
因为我们f列中的每个姓名都出现了三次，这就决定了语文、数学、英语这三个科目需要顺序、循环地罗列出来，同第一步的思路一样，用“除数求余”的数学思维来达到效果。
如上图所示，序号通过mod函数的转换，得到一个顺序、循环罗列的序号。将该序号作为offset函数的第三参数列偏移量，就可以顺序、循环的引出原数据的科目内容。
第三步：通过姓名和科目，模拟index函数，在原数据中引出成绩
h2单元格函数：
=offset($a$1,match(f2,$a$2:$a$5,0),match(g2,$b$1:$d$1,0))
分别用match函数，得到数据在相关区域中所对应的序号，作为offset的偏移量，分别放入第二、三参数中。从基准点a1单元格偏移后的单元格，就是我们需要的成绩值。
通过上面的内容，我们不难发现offset函数，往往都是和match函数连用。因为match函数可以找到关键字在一个数列中的序号，所以我们经常利用这个函数来确定offset函数的偏移量。
三、高阶应用的思路
（动态报表模板的原型）
我们使用excel是为了快速地统计分析数据，快速地提取出我们需要的内容。现在假设以下两个场景：
场景一：
领导安排了工作，统计某季度的销售数据，我们马上行动，用函数快速的制作报表；
场景二：
领导安排了工作，因为每季度都需要统计销售数据，所以我们早就提前制作了模板，至于什么时候给出报表，就随我们的便了。切记，不要让“中层领导”知道你的工作效率很高。
两个场景，你会选择哪种处理方式呢？作者希望是第二个。
思路决定了我们制表的格局，这是一个简单的案例，当数据源被修改后，相对应的季度数据也会自动做出调整。在复杂的模版中并不是所有的位置都会使用offset函数，但对于动态引用数据区域的需求，用offset函数来处理是绝对不会错的。
四、典型用法举例
绝技4：制作动态下拉菜单
在数据建模的过程中，我们经常会使用到下拉菜单（或者是组合框控件）。为了确保下拉内容的唯一性，我们会使用index+small+if+row的“万金油”函数来去重提取数列中的数据。还记得我们在上篇讲到的offset函数替代index函数的例子吗？所以说，如果offset函数可以代替index函数使用的话，那么offset函数同样也可以实现“万金油”的过程。下面我们就一起来看看复杂的“下拉菜单”的制作过程。
步骤一：使用offset函数去重提取唯一值的 “万金油”公式
这个公式比较长，列出如下：
d2单元格函数：
=iferror(offset($a$1,small(if(row($a$2:$a$27)-1=match($a$2:$a$27,$a$2:$a$27,0),row($1:$20),9^9),
row(d1)),),)
万金油公式不是我们今天要讲的主题，就不展开讲了。重要就是为了让大家知道offset函数也是可以达到这样去重的效果。
步骤二：在名称管理器中使用offset函数，建立数据源
我们可以用ctrl+f3组合键，打开名称管理器窗口，然后新建名称，名称设置为“区域”，引用位置为“d2:d15”，如下图所示：
然后选择g1单元格，按alt+d+l组合键可以打开数据验证设置框，在允许中选择“序列”，在来源中输入“=区域”，如下图所示：
点击确定按钮，那么我们g1单元格的下拉菜单就建立好了。但是问题也来了，我们会发现有好多的空选项，这不是我们需要的。
有的同学会说，名称管理器中选择d2:d5就可以了。是的，但是如果我们a列的区域中出现了新的数据，那下拉菜单中的数据可就少了，所以此时我们依然使用offset函数来处理这个问题。
更改名称管理器中，“区域”的引用位置：
=offset(动态下拉菜单!$d$1,1,,counta(动态下拉菜单!$d$2:$d$15)-countblank(动态下拉菜单!$d$2:$d$15),1)
因为我们d列的唯一值，是用公式得到的，里面的“空单元格”不是名义上的“空”，而是由公式得到的空，所以不能直接通过countif(d2:d15,)的方式得到有值的单元格个数。因此我们先使用了countblank函数（空值单元格计数），统计空值单元格的数量，再用counta函数统计非空单元格的数量，最后二者相减就得到了有值单元格的个数。将得到的结果作为offset函数的第四参数（新区域的扩展行数）使用，就实现了动态引用有效数据的效果。如下图所示：
如果a列中增加了新的区域名称，那么g1的下拉菜单也会增加新的选项，让我们来一起看一下效果，我相信这是你需要的。
绝技5：offset函数在图表中的使用
上面的这张图表，相信大家都不陌生吧。参加工作的同学们都会有制作图表的经历，在上图中选择a1:b10区域，在工具栏——“插入”——柱形图，就完成了我们图例的内容。
如果我们删掉一行数据，那么柱形图中的系列图例就会少一个，可是如果增加一行数据的话，就需要更改图表数据源的范围，才能显示正确的图表。但总不能每次都更改呀，那就失去了我们使用excel 高效快速的初衷。
此时，我们依然可以借鉴offset函数来解决：
步骤一：使用offset函数分别对“日期列”和“数量列”，制作自定义名称
名称管理器，我们上面有介绍，就不多说了。选中“日期列”，设置如下：
引用位置函数：
=offset(图表系列!$a$1,1,0,counta(图表系列!$a$2:$a$1000),1)
因为原数据中并不存在公式得到的空单元格，所以这里不需要使用countblank函数，直接用counta函数统计出非空单元格的个数，作为offset函数的第四参数（新区域的行数）即可。这里的a2:a1000，表示一个绝对大的区域，保证新输入的内容在这个范围内。
选中“数量列”，同理制作出数量的自定义名称，如下：
步骤二：在图表区域使用名称
这是offset动态图表的关键所在，添加名称的位置是很重要的。
在绘图区，选择任意一个柱形，在编辑栏中你可以看到图标的函数写法（是不是第一次知道图表也有函数）。我们就在这里修改引用的范围。
我们只需改动区域的部分。
图表系列!$a$2:$a$10
图表系列!$b$2:$b$10
用自定义名称替换这两个红色的部分即可，切不可以将“图表系列!$a$2:$a$10”整体替换！
替换后按回车，函数就会像上图这样显示，offset.xlsx是我们的工作薄名称。效果如下：
其他的图表类型也是可以的，大家可以试着操作一下，加深印象。
【编后语】
offset函数的五个参数，如果理解了意义，就不难记住。它的返回值可以作为其他函数的引用，同理其他“返回值是数值格式”的函数也可以作为offset函数的参数，让我们的数据可以自己动起来。
这个函数在excel函数中起着不可或缺的作用，尤其是我们需要使用excel建模的时候，动态区域的引用、自动化处理数据，往往都会使用到这个函数，强烈建议同学们，能多花一些时间来学习一下，对你今后制表的过程，将大有裨益。
相关学习推荐：excel教程
以上就是excel函数学习之聊聊动态统计之王offset()的详细内容。