Aien Excel职场 2022-05-24 08:30 发表于广东 OFFSET函数是Excel中最常用、强大的函数之一,从复杂的数据汇总,到数据透视表,再到高级动态图表,都离不开ta。事不宜迟,小可就教大家掌握OFFSET函数的超实用技巧,高效工作不是事!就是以C3为基点,向下偏移4行,向右偏移2列,新引用的行数是4行,新引用的列数是3列,最终得到对E7:G10单元格区域的引用。1、offset函数为偏移函数,它可以通过位置的偏移获取一段单元格范围区域;2、这个函数有5个参数,而函数偏移方向为,下—右—上—左。②第二参数为向下偏移多少(正数为向下,负数为向上);③第三参数为向右偏移多少(正数为向右,负数为向左);如果不使用第四个和第五个参数,新引用的区域就是和基点一样的大小。学会函数基础知识后,小可由简到繁的思路,去介绍这个函数强大的功能应用
PS:文末都是精髓哦!如下GIF,要计算第六行的员工“王晓珊”在1-6月份的总销量,其实只需,求B6:G6单元格区域的和,就ok啦。 公式:=SUM(OFFSET(A1,5,1,1,6))。 ①OFFSET(A1,5,1,1,6):以A1单元格为基准,向下移动5行,向右移动1列,即B6单元格的位置。②第4个参数和第5个参数表示偏移后新的区域是1行6列的,也就是B6:G6单元格区域。③最后,利用SUM函数将B6:G6单元格的值相加,得到结果:1299。学会后,比如我们要统计后半年销售额,同样也可以用到这个万能公式哒!OFFSET函数也可以用来进行多条件查找,举个栗子。 如下GIF,我们要查找产品B的5月份销量: 在I2单元格,输入公式:=OFFSET(A1,MATCH(H2,A2:A11,0),MATCH(G2,B1:E1,)) ①先用MATCH函数分别定位出指定月份和产品在A2:A11和B1:E1区域中的位置。②作为OFFSET函数的第2和第3个参数,然后以A1为基准位置偏移对应的行数和列数即可。对MATCH函数不熟悉的朋友,可点击看这篇文章👉《Excel数据去重和排序的5种神技能,大神小白都爱用!快收藏!》,里面有提到它的基础知识和运用哦! 如下GIF,要进行2个员工(第六行的王晓珊、第七行的杨筱慧)在3-5月份之间,的最大值或最小值。 换言之,只需计算D6:F7单元格区域的最大/小值,就好! ①最大值公式:=MAX(OFFSET(A1,6,3,-2,3))。 ②最小值公式:=MIN(OFFSET(A1,6,3,-2,3)),得到结果:168。 以最大值公式为例,进行讲解: ①OFFSET(A1,6,3,-2,3):以A1单元格为基准,向下移动6行,向右移动3列,即D7单元格的位置。②第4个参数和第5个参数表示偏移后新的区域是2行3列的,-2表示向上扩展2行,也就是D6:F7单元格区域。③最后,利用MAX函数求出D6:F7单元格区域的最大值,得到结果:236。在销售工作中,经常会流水记录产品的销量数据,如何提取最新的销量数据呢? 如下GIF,步骤: ①先输入公式:=OFFSET(B1,COUNT(B:B),) ②再更新数据,即可看到效果! ②用OFFSET函数公式以B1为基准位置,向下偏移COUNT函数公式返回的行数,向右偏移0列,公式里面可以省略0不写,只保留逗号。如下GIF,当我们重新添加部门进去的时候,下拉菜单选项会自动进行更新,如何实现的呢? ①选中D2:D7单元格区域 - 点击【数据】 - 数据验证 - 序列 ②输入公式:=OFFSET(G$1,0,0,COUNTA(G:G)) - 确定,即可。讲解: ①offset函数这里从G1单元格进行开始偏移;用$固定行,这样往下拖动的时候就不会变化;②第四参选择的范围为counta函数计数文本单元格的个数得到选择多少行。最常用的制作动图的方法是利用名称和控件。 如下GIF,我们用OFFSET函数来演示一下如何制作! ②输入公式:=OFFSET($B$1:$D$1,MATCH(A10,$A$2:$A$7,0),0)③并按CTRL+SHIFT+ENTER回车,即可。就这样几步,瞬间高大上很多!汇报时给老板看,准不亏!
|