offset函数什么意思(offset函数的应用) 收藏 0

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函数还经常用于动态图表的制作，这些内容咱们以后继续分享。

图文制作：祝洪忠

三件套之:OFFSET函数一次性搞明白

作者表格学院：赵建军

今天在我们的星球会员群里面吵疯了，为什么我用OFFSET写出来的函数结果不是我想要的呢？

各路神仙齐上阵，各种解释，可是没有哪个有说服力，废话不说直接上图：

offset函数的使用结果，我们的学员理解应该等于234，没有按照他所预想的偏移一列输出结果。

说实在的也难为我们的学员了，下面简单介绍一下我对OFFSET函数认识。

它是一个指路函数，也可以叫做导航仪，

一共5个参数：offset(自身原点（0，0），偏移行，偏移列，区域高度，区域宽度)

第一个参数：就是立足点，也就是你想从哪儿开始；

第二个参数：就是开始偏移行，本身立足点是0行0列，

第三个参数：就是开始偏移列，本身立足点是0行0列

第四个参数：是你要找区域的高度，1就表示一行，2就表示2行

第五个参数：是你要找的区域的宽度，1就表示1列，2就表示2列

比如说，你的最后两个参数分别是2和3，那么你要找的是2×3的单元格区域，表示的不是一个值，是一片区域！

这时候你的这个公式有两种用法，

一种是直接数组用法，你直接选中要输出的区域，按ctrl+shift+enter，直接输出数组公式，出现结果就好了。

一种是普通公式用法，这种用法比较容易出错，或者说出现自己难于理解的结果，今天争论的焦点就在这儿。

而如果是1和1的话就是一个单元格，一个单元格时最小区域单位。这个基本不会出错

理解这个函数之后，再找自己公式里面返回数据的问题的时候就方便不少。

返回头来看上面图片反馈这个问题：

上图中我们的学员想返回的是第一个数据‘234’，结果出现的是第二个数据‘213’，觉得我的公式没错啊，为什么出现的结果有问题呢？

这里解惑：offset（$B$3,$K$2,1,1,7）这个公式中$K$2=1，表示的是，以B3单元格为基准点，向下偏移一行，偏移一列的区域为1行7列的数据；这里大家都理解，没有问题，但是问题出现在，为什么写在D列单元格数据返回有问题?

excel在我们不使用数组公式的时候，它的偏移列，按照原点来的，所以，当你是C列写公式的时候默认相对B列偏移1列，在你的数据中输出第一个数，当你是D列的时候，默认相对B列偏移2列，所以输出第二个数据。

或许大家最主要的疑问在明明写的是偏移1列，这里告诉你，你的偏移只限定的是你的数据区域，并没有在普通公式中输出限定偏移列数，OFFSET函数在普通公式中偏移列数，是根据默认原点位置来的！这是根儿哈！

归结起来，提醒大家，虽然offset函数能够用做普通函数的用法，但是建议还是用他的数组用法，以免自己难于控制结果。

over！

领取课程请直接联系我

OFFSET函数真有那么难吗？我花3小时写了一篇适合所有人学的教程

Excel中的OFFSET函数，在我们工作中使用的频率非常之高，当然，这个函数也是比较难于理解的，官方给定的意思是“以指定的引用为参照系，通过给定偏移量返回新的引用”。仅仅看这句话很难清楚什么意思。今天我来跟大家详细讲讲这个函数的用法！

一、OFFSET函数语法。

=OFFSET(reference,rows,cols,[height],[width])。

=OFFSET(基准点，向下或向上偏移的行数，向右或左偏移的列数，引用区域的高度，引用区域的宽度）。

二、OFFSET函数说明。

reference：引用的基准点。可以为单元格或者相连的单元格区域。

rows：偏移的行数。正数表示向下偏移，负数表示向上偏移。

cols：偏移的列数。正数表示向右偏移，负数表示向左偏移。

[height]： 所要返回的引用区域的行数。该参数可以为空，也可以为负数。-x表示当前行向上的x行。

[width]： 所要返回的引用区域的列数。该参数可以为空，也可以为负数。-x表示当前列向左的x列。

三、函数用法举例。

下图中C10单元格的公式为“=OFFSET(B2,3,0)”，结果等于4。公式的意思是：以B2单元格为基准位置，向下移动3行。列数不发生改变。

下图中C10单元格的公式为“=OFFSET(B2,3,3)”，结果等于4000。公式的意思是：以B2单元格为基准位置，向下移动3行。向右移动3列。

下图中C10单元格的公式为“=OFFSET(B2,3,1,2,2)”，结果等于{40,400;50,500}。公式的意思是：以B2单元格为基准位置，向下移动3行。向右移动1列。得到C5单元格，在向下引用2行，向右引用2列，所以得到一个单元格区域C5:D6。结果也就是{40;400;50;500}。

下图中C10单元格的公式为“=OFFSET(D5,-2,-2)”，结果等于2。公式的意思是：以D5单元格为基准位置，向上移动2行。向左移动2列，得到B3单元格，所在的值等于2。

下图中C10单元格的公式为“=OFFSET(D5,-2,-1,-2,-2)”，结果等于{1,10;2,20}。公式的意思是：以D5单元格为基准位置，向上移动2行。向左移动1列。得到C3单元格，在向上引用2行，向左引用2列，所以得到一个单元格区域B2:C3。结果也就是{1,10;2,20}。

四、统计最近一周的销售额。

下图中，F5单元格的公式等于“=SUM(OFFSET(C2,COUNT(C:C),0,-7,1))”。结果为16147。

该公式的意思是：首先用COUNT函数对C列的数字单元格进行计数。COUNT(C:C)中数字单元格的个数为14，以C2单元格为基准位置，向下移动14行到C16单元格，列数不发生改变，向上引用7行，即C10:C16单元格区域，也就是最近一周。最后用SUM函数将得到的单元格区域进行求和，就是我们要统计的结果。

使用OFFSET函数配合SUM函数求最近一周的销售额有什么优点？当我们每天都更新销售额的时候，会动态的统计最近一周的销售额，不需要我们手动去计算。不信你看看下图！

完整的动态演示如下。

五、提取截至当天的最大销售额。

下图中，F5单元格的公式等于“=MAX(OFFSET(C2,1,0,COUNT(C:C),1))”。结果为3340

。

该公式的意思是：首先用COUNT函数对C列数字单元格进行计数。COUNT(C:C)中数字单元格的个数为14，以C2单元格为基准位置，向下移动1行到C3单元格，列数不发生改变，向下引用14行，即C3:C16单元格区域。最后用MAX函数将C3:C16单元格区域的最大值提取出来，结果就是3340。

完整的动图演示如下。

如果认真看完这篇文章，相信你已经对OFFSET函数有了进一步的了解，想了解该函数的高级用法，欢迎关注我哦！

如果您觉得文章不错，请转发分享给更多的人看到，这是对小编的鼓励与支持，谢谢！

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