offset函数的使用方法及实例;offset函数的语法 收藏 0

Excel办公技巧：偏移函数offset用法解析

大家好，今天跟大家分享offset函数的用法。offset函数的作用是以指定的引用为参照系，通过给定的偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域。

OFFSET函数的语法结构为：OFFSET(reference,rows,cols,height,width)

第1个参数reference是作为参照系的引用区域，reference 必须为对单元格或相连单元格区域的引用，其左上角单元格是偏移量的起始位置；

第2个参数rows是相对于引用参照系的左上角单元格要上(下）偏移的行数，该参数为正数代表向下偏移，为负数代表向上偏移；

第3个参数cols是相对于引用参照系的左上角单元格要左(右）偏移的列数，该参数为正数代表向右偏移，为负数代表向左偏移；

第4个参数Height为要返回的新引用区域的行数，该参数为正数代表当前行向下多少行，为负数代表当前行向上多少行；

第5个参数Width为要返回的新引用区域的列数，该参数为正数代表当前列向右多少列，为负数代表当前列向左多少列。

如果省略 height 或width，则假定新引用区域的行数或列数与reference相同。

以下图工作表中的A2单元格为起始单元格，向下偏移4行，向右偏移4列，返回1行高1列宽的新单元格引用，将返回结果显示在G2单元格中。

操作步骤：

选中G2单元格，在英文状态下输入公式：=OFFSET(A2,4,4,1,1)，回车确认公式，可以得到新单元格引用E6中的内容“45”。

公式说明：

A2单元格向下偏移4行为A6单元格，A6单元格再向右偏移4列为E6单元格，再以E6单元格为基准返回一个1行1列的区域，该区域就是E6单元格本身，所以公式返回结果为E6单元格中的内容“45”。

以下图工作表中的A2:B3单元格区域为参照系，向下偏移3行，再向右偏移3列，再向下向右返回2行高2列宽的新单元格区域引用，将新单元格区域引用内容显示在G2：H3单元格中。

操作步骤：

选中G2：H3单元格区域，输入公式：=OFFSET(A2:B3,3,3,2,2)，同时按shift+ctrl+enter组合键，即可在G2：H3单元格区域返回对应的引用结果D5：E6单元格区域中的内容。

公式说明：

公式中的参照系为相连单元格区域A2:B3，偏移量的起始位置是A2:B3左上角的单元格A2，向下偏移3行为A5单元格，再向右偏移3列为D5单元格。以D5单元格为基准，返回一个2行高2列宽的单元格区域，得到的新引用区域就是D5：E6单元格。

注意事项：

因为公式返回的是单元格区域，是数组公式，所以公式输入完毕要同时按shift+ctrl+enter组合键确认公式。如果直接按enter键确认公式，会返回错误提示“#VALUE！”。

本文由原创，欢迎关注，一起学习更多office办公技能！

OFFSET函数常用套路

小伙伴们好啊，今天老祝和大家一起来学习OFFSET函数的常用套路。

1、函数作用：

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

2、函数用法：

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

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

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

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

1）行列转置

如下图，要将A2:D7单元格中多行多列的姓名，转换到一列中。

F2单元格公式为：

=OFFSET($A$2,(ROW(A1)-1)/4,MOD(ROW(A1)-1,4))&\”\”

OFFSET函数的基点为A2。

向下偏移的行数为(ROW(A1)-1)/4，这部分公式下拉时，可以得到从0开始，按0.25递增的序号，即0，0.25，0.5，0.75，1，1.25，1.5……OFFSET函数对带有小数的参数自动向下取整，向下偏移的行数依次为0 0 0 0 1 1 1 1 2 2 2 2……也就是公式每下拉四行，就从数据源中向下偏移一行。

向右偏移的列数为MOD(ROW(A1)-1,4)，这部分公式下拉时，可以得到0 1 2 3 0 1 2 3……的循环序列序列，也就是公式每下拉一个行，就从数据源向右偏移一列，下拉到第五行时，偏移的列数又会从0开始。

偏移行数和偏移列数二者结合，最终形成1 2 3 4 2 2 3 4 3 2 3 4 ……这样的偏移方式。

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

如下图所示，要计算从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月份到指定月份的销售总额。

3）计算最近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函数计算出这个引用区域中的平均值。

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

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

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中，OFFSET函数的使用方法

OFFSET是Excel中的函数，在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 列的区域的总值。

函数示例

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

具体应用：

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。这样设置后无论如何选择月份都会计算出正确结果。

OFFSET与count函数组合求最近几个累计数

如下图所示，我想要计算最近3个月的累计销量，当增加月份时，销量也会变动，函数如何写呢？

此时输入的函数是=SUM(OFFSET(B1,COUNT(B:B),0,-3))。在下面继续添加月份和销售时，累计销量始终是最近3个月的销售累计。

这个函数offset函数第二个参数COUNT(B:B)表示对B列数据进行计算，因为count函数对文本、空白单元格都不会进行计数，所以B列有多少有数据的单元格，count函数就返回多少。而第四个参数-3，表示从B1单元格偏移到最后一个单元格后，往回折了3个单元格。所以可以表示计算最近三笔销量之和。

综合运用：OFFSET与match、countif、vlookup函数，定义名称结合制作二级下拉菜单并动态查找数据

下图左侧是我国34个省级行政区，300多个市级行政区及对应销量，我们根据右侧的下拉箭头选择省级行政区后，就可以在后面的下拉箭头选择当前省级行政区下的市及对应销量。因为步骤比较多，此处不再对具体操作进行演示，简单说一下操作步骤。以后在介绍动态图表制作的时候会进行详细介绍。

首先把A列的数据复制到E列中(此处为了能看清楚动图，E列已隐藏)。然后选择【数据】选项卡——【删除重复值】，把E列中的每个省份名称只留下一个值。

然后打开【公式】选项卡——【定义名称】，输入函数=5\’!$E$2:$E$35，前面这个5\’!是引用的工作表名称。名称输入“省”。继续定义名称，输入函数=OFFSET(\’5\’!$B$1,MATCH(\’5\’!$G$2,\’5\’!$A$2:$A$342,0),0,COUNTIF(\’5\’!$A$2:$A$342,\’5\’!$G$2),1)，名称输入“市”。

接着在G2单元格中，添加【数据验证】——【序列】——【来源】=省。在H2单元格中，【数据验证】——【序列】——【来源】=市。

最后在I2单元格中输入函数=VLOOKUP(H2,$B:$C,2,0)，就可以实现动态查找了。

这就是本文介绍的offset函数的应用，试着操作一下吧。

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com