|
|
OFFSET函数是Excel中最常用、强大的函数之一，从复杂的数据汇总，到数据透视表，再到高级动态图表，都离不开ta。
可见它的重要性！涉及到老板对你的考核，真的要学啊
事不宜迟，现在就教大家掌握OFFSET函数的超实用技巧，高效工作不是事！
1.Offset函数基础讲解
如下GIF：
公式：=OFFSET(C3,4,2,4,3)
就是以C3为基点，向下偏移4行，向右偏移2列，新引用的行数是4行，新引用的列数是3列，最终得到对E7:G10单元格区域的引用。
函数讲解：
1、offset函数为偏移函数，它可以通过位置的偏移获取一段单元格范围区域；
2、这个函数有5个参数，而函数偏移方向为，下—右—上—左。
①第一参数为基点，即是：起始位置；
②第二参数为向下偏移多少（正数为向下，负数为向上）；
③第三参数为向右偏移多少（正数为向右，负数为向左）；
④第四参数为引用多少行，第五参数为引用多少列。
如果不使用第四个和第五个参数，新引用的区域就是和基点一样的大小。
学会函数基础知识后，由简到繁的思路，去介绍这个函数强大的功能应用。
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为基准位置偏移对应的行数和列数即可。
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函数取值。
最终，做成动态饼图如下：
就这样几步，瞬间高大上很多！汇报时给老板看，准不亏！
今天就分享到这~相信大家在实际工作中会想出更多更有用的运用！ |
|
发表于 2023-2-10 09:47:15