首发于 Excel应用之家
最全的OFFSET函数应用集合,收藏这一篇就够了

最全的OFFSET函数应用集合,收藏这一篇就够了

OFFSET函数是EXCEL中最常用的函数之一。我之前有一篇帖子 offset函数的斗转星移神功简单介绍了这个函数的语法结构和基本用法,大家可以参考。

在实际工作中,OFFSET函数的应用主要在以下几个方面:

我们将按以上由简到繁的思路来想大家介绍一下这个函数强大的功能。


01 查找某一数值

OFFSET函数最基本的用法就是以指定的引用为参照系,通过给定的偏移量得到新的引用。

在单元格A9中输入“=OFFSET(A1,3,3)”即可。

思路:

TIPs: 如果上例中以D5为参照系,第二项和第三项参数值为-3,则意味这向上和向左偏移


02 偏移后区域求和

下表是某公司商品的区域销售统计。我们可以一次性统计商品“彩电”的销售总量。

在单元格A10中输入“=SUM(OFFSET(A1,3,1,1,6))”即可。

思路:

TIPs: 这里可以和OFFSET函数配合使用的还有AVERAGE函数,COUNT函数,MAX/MIN函数等等,可以灵活运用。


03 区域偏移

上例中,求洗衣机的最大销售量。

在单元格A10中输入“=MAX(OFFSET($B$1:$G$1,4,0))”即可。

思路:


04反向查找

OFFSET函数配合MATCH函数也可以达到反向查找的目的。

在单元格E2中输入“=OFFSET($A$1,MATCH(D2,$B$2:$B$10,0),0)”并按CTRL+SHIFT+ENTER回车即可。

思路:

TIPs:都有哪些函数(组合)可以实现反向查找?

VLOOKUP+IF

INDEX+MATCH

LOOKUP

OFFSET+MATCH


05 提取不重复清单

同样地,OFFSET函数和MATCH函数配合也可以完成“提取不重复清单“的功能。

TIPS:INDEX函数也可以完成“提取不重复清单”这样一个功能

在单元格C2中输入“=OFFSET($A$1,SMALL(IF(MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($A$2:$A$11)-1,ROW($A$2:$A$11)-1),ROW()-1),0)”并按CTRL+SHIFT+ENTER回车即可。

思路:



06 OFFSET函数和MATCH函数组合动态查找

在实际工作中,很多时候我们是希望在一张数据表上依据关键字来动态查询或统计相关的数据。这种情况下,使用MATCH函数和OFFSET函数的组合就可以轻松地解决问题。

在单元格B12中输入“=SUM(OFFSET($B$1,MATCH($A$12,$A$2:$A$7,0),0,1,6))”并按CTRL+SHIFT+ENTER回车即可。

思路:

以上例子中提到的的MATCH函数具体的用法,请参看帖子 总结篇-MATCH函数使用终极帖,那里有更详细的介绍。


07 OFFSET函数和SUBTOTAL函数组合动态查找

OFFSET函数的偏移量除常量外,还可以是公式生成的数组,从而形成三维引用,最终实现动态统计需求。

我们仍以上篇中的例子为例来演示具体过程。

在单元格B12中输入“=MAX(SUBTOTAL(1,OFFSET($B$1,ROW($1:$6),0,1,6)))”并按CTRL+SHIFT+ENTER回车即可。

思路:


08 OFFSET函数和COUNTA函数配合形成动态数据区域

和COUNTA配合制作动态数据区域,多使用于创建动态图表。在下面的图标中,随着A列数据的增加,图表数据源也随着增加。

具体的公式为:“=OFFSET($A$1,COUNTA(A:A)-1,0)。建立一个名称,将这个公式写在名称中即可。

TIPs:EXCEL自带的表格也具有动态区域的功能。在创建动态图时,也可以利用表格来创建动态数据区域。

由此例又拓展出了一系列实际应用:

....

由于查询最后一个/最后几个数据比较简单,下面仅以求最后三行销售数据的总和为例。

在单元格A11中输入“=SUM(OFFSET($A$1,COUNTA($A$2:$A$7)-2,1,3,6))”并按CTRL+SHIFT+ENTER回车即可。

思路:


09 OFFSET函数重复录入数据

这个功能需要配合INT函数和ROW函数。

在单元格A10中输入“=OFFSET(A$2,INT((ROW(A1)-1)/3),0)”并向下拖曳即可。

思路:


10 动态图表

最常用的制作动图的方法是利用名称和控件。下面我们就用OFFSET函数来演示一下如何制作。

同时选中单元格区域B10:D10,输入“=OFFSET($B$1:$D$1,MATCH(A10,$A$2:$A$7,0),0)”并

按CTRL+SHIFT+ENTER回车即可。

思路:

最终,做成动态饼图如下:

OFFSET函数还有非常多的实际应用,限于篇幅,就不再展示了。本篇后半部分可能对于一些朋友有些难度,不要紧,先收藏起来,今后在使用过程中慢慢消化理解!

对于本文介绍的OFFSET函数的功能,你觉得那些对你的帮助最大呢?欢迎来投下你的一票!


文章推荐理由:

本篇文章比较全面地介绍了OFFSET函数的在使用过程中的方法和例子,可以为日常操作提供参考和借鉴。



-END-


长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助


推荐阅读:

妙不可言的自定义坐标轴--设置任意间距垂直轴

妙不可言的自定义坐标轴--自定义垂直轴标签

你面对EXCEL操作时流的泪,恰是当初学习时脑袋进的水

会这5个EXCEL技巧,告别【五加二,白加黑】加班模式(职场白领必看)

【SUBTOTAL函数】和【OFFSET函数】动态求最值

天下网TXWEB滁州市品牌网站设计哪家好衡水市网络推广周口seo排名公司天水企业网站设计哪家专业商丘营销网站建设哪家专业阳泉企业网站建设报价晋城网站制作镇江市网站优化合肥市优化推荐三亚做网站哪家好蚌埠网站制作盐城seo报价东莞市建站哪家好亳州seo按天扣费报价忻州模板网站建设海口企业网站设计公司重庆市网站优化哪家好东营市网页制作多少钱普洱网页制作哪家专业天津网站优化哪家好许昌企业网站改版价格衢州模板网站建设多少钱曲靖seo按天计费多少钱焦作定制网站哪家好六安市阿里店铺运营推荐汕尾市企业网站设计多少钱贵阳关键词排名报价徐州市阿里店铺运营哪家好桂林网页制作哪家专业揭阳市营销网站建设哪家好香港通过《维护国家安全条例》两大学生合买彩票中奖一人不认账让美丽中国“从细节出发”19岁小伙救下5人后溺亡 多方发声卫健委通报少年有偿捐血浆16次猝死汪小菲曝离婚始末何赛飞追着代拍打雅江山火三名扑火人员牺牲系谣言男子被猫抓伤后确诊“猫抓病”周杰伦一审败诉网易中国拥有亿元资产的家庭达13.3万户315晚会后胖东来又人满为患了高校汽车撞人致3死16伤 司机系学生张家界的山上“长”满了韩国人?张立群任西安交通大学校长手机成瘾是影响睡眠质量重要因素网友洛杉矶偶遇贾玲“重生之我在北大当嫡校长”单亲妈妈陷入热恋 14岁儿子报警倪萍分享减重40斤方法杨倩无缘巴黎奥运考生莫言也上北大硕士复试名单了许家印被限制高消费奥巴马现身唐宁街 黑色着装引猜测专访95后高颜值猪保姆男孩8年未见母亲被告知被遗忘七年后宇文玥被薅头发捞上岸郑州一火锅店爆改成麻辣烫店西双版纳热带植物园回应蜉蝣大爆发沉迷短剧的人就像掉进了杀猪盘当地回应沈阳致3死车祸车主疑毒驾开除党籍5年后 原水城县长再被查凯特王妃现身!外出购物视频曝光初中生遭15人围殴自卫刺伤3人判无罪事业单位女子向同事水杯投不明物质男子被流浪猫绊倒 投喂者赔24万外国人感慨凌晨的中国很安全路边卖淀粉肠阿姨主动出示声明书胖东来员工每周单休无小长假王树国卸任西安交大校长 师生送别小米汽车超级工厂正式揭幕黑马情侣提车了妈妈回应孩子在校撞护栏坠楼校方回应护栏损坏小学生课间坠楼房客欠租失踪 房东直发愁专家建议不必谈骨泥色变老人退休金被冒领16年 金额超20万西藏招商引资投资者子女可当地高考特朗普无法缴纳4.54亿美元罚金浙江一高校内汽车冲撞行人 多人受伤

天下网TXWEB XML地图 TXT地图 虚拟主机 SEO 网站制作 网站优化