OFFSET函数不会用?四个典型用法帮到你

说起OFFSET函数,真的是像雾像雨又像风,从复杂的数据汇总、到数据透视表再到高级动态图表,都离不开OFFSET函数的默默付出。

通过OFFSET函数,可以生成数据区域的动态引用,这个引用再作为半成品,通过后续的处理加工,就可以为图表和透视表提供动态的数据源、为其他函数生成特定的引用了。

首先,咱们说说这个函数的作用,微软的帮助文件是这样描述的:

以指定的引用为参照系,通过给定偏移量返回新的引用。

瞧瞧,这么简单的几句话,让人家怎么能猜透你的心思嘛。

这个函数有5个参数:

第一个参数是基点

第二个参数是要偏移几行,正数向下,负数向上。

第三个参数是要偏移几列,正数向右,负数向左。

第四个参数是新引用几行。

第五个参数是新引用几列。

如果不使用第四个和第五个参数,新引用的区域就是和基点一样的大小。

如果使用下面这个公式:

=OFFSET(C3,4,2,4,3)

就是以C3为基点,向下偏移4行,向右偏移2列,新引用的行数是4行,新引用的列数是3列,最终得到对E7:G10单元格区域的引用。

当然,仅仅得到引用是没啥用处的,咱们的目的是把OFFSET函数得到的引用作为一个半成品,再通过其他方法进行再加工。

这么说还是有点抽象啊,再来一个形象一点的:

OFFSET函数就像是一个敌人的小分队,从据点(C3)出动,顺着大路向南走4里(C7)

拐弯儿再向东2里,这时候就到马家河子(E7)了

敌人队长说了,我要以马家河子(E7)这个地方开始,再占领一片地盘。

有多大呢?向南4里,向东3里。结果就是E7:G10单元格区域了。

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

好了,今天的内容就是这些吧,后半部分对于很多小伙伴来说可能有点难了,不过不用担心,咱们可以先收藏,然后慢慢消化,积少成多循序渐进。

祝各位小伙伴一天好心情!

图文制作:祝洪忠

(0)

相关推荐

  • OFFSET函数,有点烧脑!

    行云里讲堂(ID:xingyunli2022) 践行终身学习,专注个人提升. 整理编辑:枏北 OFFSET函数以指定的引用为参照,通过给定偏移量得到新的引用. 返回的引用可以为一个单元格或单元格区域, ...

  • Excel应用大全 |如何计算插值?

    SIMPLE HEADLINE 插值法又称"内插法",主要包括线性插 值.抛物线插值和拉格朗日插值等.其中的线 性插值法是指使用连接两个已知量的直线,来 确定在这两个已知量之间的一 ...

  • 谈恋爱不香吗?非要来学excel,佩服!

    你好,我是刘卓.欢迎来到我的公号,excel函数解析.明天就是520了,你是准备撒狗粮呢还是吃狗粮呢? 最让我感到意外的是,有个小伙伴说:"与找对象相比,更愿意成为excel大佬." ...

  • 又一个强大的引用函数offset,高手必学。

    之前我们学过的引用函数有index,indirect,今天我们学另一个强大的引用函数offset.它的意思是以指定的引用为参照系,通过给定偏移量返回新的引用. -01- 函数说明 它的结构写法如下: ...

  • OFFSET函数,四个典型用法请收好

    说起OFFSET函数,真的是像雾像雨又像风,从复杂的数据汇总.到数据透视表再到高级动态图表,都离不开OFFSET函数的默默付出. 通过OFFSET函数,可以生成数据区域的动态引用,这个引用再作为半成品 ...

  • 【函数003】-SUMIFS的10种用法帮你整理好了

    想必大家也是眼睛疲劳了,所以我们今天讲讲函数 主题就是SUMIFS函数! 你可能觉得SUMIFS很简单 但是,真的学会了吗? SUMIFS 我们一起来看看吧! 数据源(注意:图片大小限制,实际明细数据 ...

  • 约定交房条件“取得商品住宅交付使用批准文件”在政策废止后无效?(附四个典型案例)|房产裁判指南

    两高法律资讯 6天前 以下文章来源于房产裁判指南 ,作者龚炯 裁判要旨 双方约定的交房条件为"该商品房取得商品住宅交付使用批准文件"的,合法有效.当地政府主管部门就开发商需办理该批 ...

  • 初中函数考点(五):反比例函数的应用以及动点典型题分析

    考纲要求 命题趋势 1.利用待定系数法确定反比例函数解析式 2.反比例函数与图形的面积问题. 3.能用反比例函数解决简单实际问题. 反比例函数的应用是中考命题热点之一,,经常与一次函数.二次函数及几何 ...

  • OFFSET函数

    今天我们来学习一个有趣实用的函数,她是一个很"漂移"的函数,既简单又复杂!你可能已经猜到了- 他就是OFFSET,官方的话,我们就不带大家一句句过了,想必你看完也就没兴趣学习了,直 ...

  • 巧妙利用offset函数制作Excel动态图表

    或许你每月要也写总结,比如根据最近半年的收入或流量做图表,如果每月手动修改数据源,很麻烦.而如果每天要制作最近一周的销量图,每天都要修改图表的数据源,那就更费时间了,怎样做到每天打开表格,自动展现最近 ...

  • 四种典型的富贵命手相

    四种典型的富贵命手相 漂亮的双手总是像有魔力的吸铁石一样吸引着人们的眼光,手不仅仅是漂亮的还能通过它们看出一个人是不是富贵的命,下面让我们一起来看看四种典型富贵命的手相是哪些呢?钱财纹在无名指和小指有 ...

  • OFFSET函数——Excel最难的函数,太抽象了

    今天跟大家讲解下Excel大神必备的Excel函数offset,offset函数的用法十分广泛,动态图表,动态表格,以及动态区域的计算等.很多人都觉得offset函数十分难理解,但是今天我要带你们吃透 ...

  • 函数及图像知识点总结含典型例题,复习必备!

    中考数学 助力轻松升学! |标签:中考数学  备战中考