分享

Excel函数6种高级技巧!学会老板刮目相看!超干货!

 一兵个人图书馆 2022-05-24 发布于河南
Aien Excel职场 2022-05-24 08:30 发表于广东
OFFSET函数是Excel中最常用、强大的函数之一,从复杂的数据汇总,到数据透视表,再到高级动态图表,都离不开ta。

可见它的重要性!涉及到老板对你的考核,真的要学啊

事不宜迟,小可就教大家掌握OFFSET函数的超实用技巧,高效工作不是事!
1


Offset函数基础讲解




如下GIF:

公式:=OFFSET(C3,4,2,4,3)

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


函数讲解:

1、offset函数为偏移函数,它可以通过位置的偏移获取一段单元格范围区域;

2、这个函数有5个参数,而函数偏移方向为,下—右—上—左。
①第一参数为基点,即是:起始位置;
②第二参数为向下偏移多少(正数为向下,负数为向上);
③第三参数为向右偏移多少(正数为向右,负数为向左);
④第四参数为引用多少行,第五参数为引用多少列。
如果不使用第四个和第五个参数,新引用的区域就是和基点一样的大小。

学会函数基础知识后,小可由简到繁的思路,去介绍这个函数强大的功能应用


PS:文末都是精髓哦!

2


计算某员工上半年的销量和




如下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。

学会后,比如我们要统计后半年销售额,同样也可以用到这个万能公式哒!

3


查找指定产品指定月份的销量



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种神技能,大神小白都爱用!快收藏!》,里面有提到它的基础知识和运用哦!

4


比较某些员工的销量最大/小值



如下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。

哈哈,是不是很简单?灵活运用技巧很重要呢!

接下来再放其他大招

5


动态提取最新销量数据



在销售工作中,经常会流水记录产品的销量数据,如何提取最新的销量数据呢?

如下GIF,步骤:

①先输入公式:=OFFSET(B1,COUNT(B:B),)  

②再更新数据,即可看到效果!



讲解:

①先用COUNT函数计算出B列数字个数。

②用OFFSET函数公式以B1为基准位置,向下偏移COUNT函数公式返回的行数,向右偏移0列,公式里面可以省略0不写,只保留逗号。


6


offset制作动态下拉菜单栏




如下GIF,当我们重新添加部门进去的时候,下拉菜单选项会自动进行更新,如何实现的呢?

步骤:

①选中D2:D7单元格区域 - 点击【数据】 - 数据验证 - 序列 

②输入公式:=OFFSET(G$1,0,0,COUNTA(G:G))  - 确定,即可。

讲解:

①offset函数这里从G1单元格进行开始偏移;用$固定行,这样往下拖动的时候就不会变化;

②第四参选择的范围为counta函数计数文本单元格的个数得到选择多少行。

7


offset制作高大上的动态图表




最常用的制作动图的方法是利用名称和控件。

如下GIF,我们用OFFSET函数来演示一下如何制作!


步骤:

①同时选中单元格区域B10:D10。

②输入公式:=OFFSET($B$1:$D$1,MATCH(A10,$A$2:$A$7,0),0)

③并按CTRL+SHIFT+ENTER回车,即可。

思路:

①MATCH函数确定偏移量。

②OFFSET函数取值。

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


就这样几步,瞬间高大上很多!汇报时给老板看,准不亏!

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多