offset办公小技巧(精讲函数中的极品-OFFSET函数)

【温馨提示】小伙伴们，阅读之前请您点击【关注】，您的支持将是我最大的动力！

小伙伴们大家好，之前断更了一个月实在抱歉，因为带学生参加省赛确实没时间更新，比赛结束我就回来更新了。

今天我们分享的是函数中的极品OFFSET，我们来看一下基本语法：这个函数有5个参数：
第一个参数是基点
第二个参数是要偏移几行，正数向下，负数向上。
第三个参数是要偏移几列，正数向右，负数向左。
第四个参数是新引用几行。
第五个参数是新引用几列。
如果不使用第四个和第五个参数，新引用的区域就是和基点一样的大小。

1、函数的用法：=OFFSET(基点,偏移的行数,偏移的列数,[新引用的行数],[新引用的列数])

例如：将表格行进行列转换的用法

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

F2单元格公式为：

=OFFSET($A$2,(ROW(A1)-1)/3,MOD(ROW(A1)-1,5))&""

OFFSET函数的基点为A2。

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

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

2、计算指定区间的合计值

例如求1月到任意月的总合计值。

F4单元格公式为：

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

MATCH函数部分(F2,A2:A13,0）是先计算机出F2单元格的起始到结束月份，也就是6个月。OFFSET是计算确定B2的基点，确认下偏移和右偏移，然后读取MATCH函数的结果进行引用行数，最后得到B列单元格内容引用通过SUM函数进行计算，就得到了指定月份区间的总和。

3、求最近3天的平均值

如图，日期和销量的数据，需要计算最近3天的平均销量。

F4单元公式：=AVERAGE(OFFSET(B1,COUNT(B:B),0,-3))

说明：COUNT函数统计B列销量的个数，也就是有多数条记录。OFFSET函数确定以B1为基点，用COUNT统计的结果作为偏移的行数，起始偏移数设置的是0，判断的行数设置-3，得到6月30日、7月1日、7月2日的数据区域，然后用AVERAGE平均函数计算引用的区域就得到了3天的平均值。

4、求筛选后的总合计

采购记录，按部门筛选出后计算总合计。

G1单元公式：

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

上图是已经筛选后的结果。

说明：OFFSET(A1,ROW(1:9),0)这一部分，确认A1为基点，向下偏移的行数是ROW(1:9)的返回的结果，表示依次向下偏移1~9行，最终得到9个引用区域，每个单元格区域由一个单元格构成。

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

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

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

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

今天就分享到这里里，小伙伴们，在使用Excel中还碰到过哪些问题，评论区留言一起讨论学习，坚持原创不易，您的点赞、转发、就是对文老师最大的支持，谢谢啦！

每天学一点、每天进步一点、我们就会更幸福、更快乐。加油 加油 加油

关注我吧

美好的事情即将发生……

Excel办公软件7个常用函数公式案例，复习一下以免用时求人