当前位置: 首页 > 最新文章 > 正文

OFFSET函数常用套路

小伙伴们好啊,今天老祝和大家一起来学习OFFSET函数的常用套路。第三参数使用正数时,表示向右偏移,使用负数时表示向左偏移。F2单元格公式为:=OFFSET&""OFFSET函数的基点为A2。2)计算指定区间的销售额如下图所示,要计算从1月份到指定月份的累计销售额。OFFSET函数以B1为基点,以COUNT的结果作为向下偏移的行数,也就是B列有多少个数值,就向下偏移多少行。接下来使用SUBTOTA

admin

小伙伴们好啊,今天老祝和大家一起来学习OFFSET函数的常用套路。第三参数使用正数时,表示向右偏移,使用负数时表示向左偏移。F2单元格公式为:=OFFSET&""OFFSET函数的基点为A2。2)计算指定区间的销售额如下图所示,要计算从1月份到指定月份的累计销售额。OFFSET函数以B1为基点,以COUNT的结果作为向下偏移的行数,也就是B列有多少个数值,就向下偏移多少行。接下来使用SUBTOTAL函数对OFFSET函数得到的多个引用区域进行处理,第一参数使用3,表示使用COUNTA函数的计算规则,即依次统计A2~A9这九个单元格区域中的不为空的单元格个数。如果单元格处于显示状态,则对这个单元格的统计结果为1,否则统计结果为0。最后使用SUMPRODUCT函数对乘积进行求和,这样就得到筛选后的商品总价了。

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

1、函数作用:

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

2、函数用法:

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

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

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

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

1)行列转置

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

F2单元格公式为:

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

OFFSET函数常用套路

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)))

OFFSET函数常用套路

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))

OFFSET函数常用套路

先使用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函数常用套路

要计算筛选后的内容,首先需要判断单元格是不是处于显示状态。

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


图文制作:祝洪忠


上一篇: 急性缺血性脑卒中死亡率(缺血性脑卒中的死亡率) 下一篇:编辑刊物巴金主编的文学丛刊(作家出版社最近出版了巴金先生)
返回顶部