主机参考:VPS测评参考推荐/专注分享VPS服务器优惠信息!若您是商家可以在本站进行投稿,查看详情!此外我们还提供软文收录、PayPal代付、广告赞助等服务,查看详情! |
我们发布的部分优惠活动文章可能存在时效性,购买时建议在本站搜索商家名称可查看相关文章充分了解该商家!若非中文页面可使用Edge浏览器同步翻译!PayPal代付/收录合作 |
本文带你了解一下外号“动态统计之王”的OFFSET函数!OFFSET函数是一个非常实用的函数,在下拉菜单、动态图表、动态引用等操作中有着不可替代的作用。毫不夸张地说,Excel表格的功能有相当一部分来自于抵销。
[前言]
抵销函数是判断Excel函数用户是否先进的重要函数之一。在实际工作中,如果你需要对工作中的数据文件进行系统化、自动化的建模,那么这个功能必然要用到。
[功能和语法]
OFFSET函数的作用是根据指定的引用返回一个具有给定偏移量的新引用。
语法:OFFSET(引用,行,列,
相信大家都花了不少时间看这张图。我们可以先按照上图的指引把数据填入OFFSET函数,实际操作一下,看是否和新区的地址一致。
我们先测试第一个例子,看看以正数为参数的运行结果:
通过检查计算,黄色“新区域”中的值之和等于256,与单元格C15中的值一致,结果正确。如果学生想模拟这个数据,也可以选择单元格C15,然后通过工具栏中的“公式-公式审核-公式求值”功能,可以更直观的看到OFFSET的返回值。(也可以在函数中使用F9。在公式中选择OFFSET的函数部分按F9就可以了,这里就不说了。)
让我们再次测试第二个例子,看看以负数作为参数的运行结果:
可以用“公式求值”自己测试一下,看看偏移函数区的返回值。
那么了解OFFSET的基本运算原理,可以帮助我们在实际工作中进行大量的运算和计算,并且有了这个函数的参与,可以实现excel中的很多自动化效果。让我们来看看OFFSET函数在实际操作中的强大作用!
第一,主要的日常用法
作为其他函数的区域参考,应该是偏移函数最基本的用法。OFFSET函数不移动单元格区域,但返回一个偏移量扩展的区域地址。因此,所有将引用区域作为参数的函数都可以使用OFFSET函数的返回值,例如我们上面的示例Sum(OFFSET())和下面的示例:
函数的原理和上面的用法一样,就不赘述了。我们还是用OFFSET函数返回的面积作为MAX函数的参数。
第二,高级常规用法
特技①:模拟转置功能
在使用转置函数之前,我们需要选择相应大小的转置区域,还需要使用Ctrl+Shift+Enter结束公式,比较复杂。
这里我们可以使用OFFSET函数来模拟这种移调的效果,如上图所示。
A11细胞功能:
=OFFSET($A$1,列()-1,行()-11)
功能分析:
转置数据实际上是一个“行到列”和“列到列”的过程,具体点就是行号和列号的交换。原始数据中的第一列“Name”已被转置为新区域中的第一行。类似地,Name列中每一行的行号成为转置后的列号。使用offset的原理是在取OFFSET时改变行号和列号的引用范围。
★比如在单元格A11中,COLUMN()=1,1=0,那么OFFSET的第二个参数为0,表示原基点的行数没有偏移(OFFSET的第二个参数表示行偏移,如果不熟悉,请看前面的内容!)。ROW()=11,111=0,OFFSET的第三个参数为0,表示列数没有偏移,所以引用原基点A1单元格的值。
★★将函数向右拉填充,单元格B11,COLUMN()=2,1=1,则OFFSET的第二个参数为1,表示原基点的行数下移一位。ROW()=11,111=0,OFFSET的第三个参数为0,表示列数没有偏移,所以单元格B11引用的是基点A1下移后单元格A2的值。
★★★★下拉单元格A11的函数填充,单元格A12,COLUMN()=1,1=0,行数不偏移。ROW()=12,111=1,OFFSET的第三个参数为1,表示列数从基点A1向右移动一个位置,引用B1单元格的值(我们公式中的A1使用绝对引用,因为我们所有的单元格都是基于A1的)。
以此类推,当我们用鼠标下拉右填公式时,可以借助行列函数帮助我们定位每个单元格的偏移量,从而达到换位的效果。
特技②:模拟Vlookup函数的反向查询功能。
VLOOKUP函数的反向查询大多是借助数组来完成的,但是因为数组的原因,函数在数据量很大的情况下可能会卡死,所以很多同学也会用INDEX函数来代替。所以今天就来丰富一下你的知识吧。我们使用偏移函数来处理这类问题。
C12细胞功能:
=OFFSET($A$1,MATCH(& quot;D2568 & quot,$ B $ 2:$ B $ 7.0),)
功能分析:
我们用单元格A1作为原始基点,要返回的值与原始基点在同一列,所以只需要考虑OFFSET函数的行偏移量,不需要考虑列偏移量。因为员工号一般都是唯一的值,所以我们用MATCH函数得到B2:B7地区的数字“D2568”的序列号,返回值4作为OFFSET函数的行偏移量带入OFFSET函数,=OFFSET($A$1,4,)。列偏移省略号默认为0,扩展宽度和扩展高度省略号默认为1(即一个单元格)。是A5细胞吗?
绝技三:数据重置升级——重新排列数据结构
在F2:H2区域输入公式后,下拉并填入数据,您会得到右侧的一维数据表。这种重新整理数据的问题在实际工作中应该不少见!那么同学们会选择什么方法来解决呢?相反,作者认为偏置函数的思想更简洁明了。
功能分析:
第一步:获取连续出现的名字。
F2细胞功能:
=OFFSET($A$1,INT((ROW(F1)-1)/3)+1,)
因为有三个主题,可以确定同一个名字需要出现三次,所以我们在下拉F2单元格填充函数的时候,一定要保证每三个单元格的OFFSET函数的行偏移量都是相同的。需要一种“分舍取整”的数学思维。让我们制作一个图表来帮助解释:
从图中我们可以看到一组序列号。经过INT((序号-1)/3)+1的转换,我们可以得到右边的序列(如果有四个主语,把3换成4,以此类推)。把这个序号放入OFFSET函数的第二个参数中作为行偏移量的标准,就可以得到我们的name列的效果了。
第二步:给同一个人分配不同的科目。
G2细胞功能:
=OFFSET($A$1,,MOD(ROW(G1)-1,3)+1)
因为我们F栏的每个名字都出现了三次,这就决定了语文、数学、英语三科需要按顺序、循环排列。就像第一步的思路一样,用“除数求余数”的数学思维就可以达到效果。
如上图所示,通过MOD函数对序列号进行转换,得到一个顺序循环列出的序列号。以此序号作为偏移函数第三个参数列的偏移,可以依次循环导出原始数据的科目内容。
第三步:按名称和主题模拟索引函数,在原始数据中抽出结果。
H2细胞功能:
=OFFSET($A$1,MATCH(F2,$ A $ 2:$ A $ 5.0),MATCH(G2,$ B $ 1:$ D $ 1.0))
使用MATCH函数获取相关区域数据对应的序号,放入第二个和第三个参数中作为OFFSET的偏移量。相对于参考点A1单元格的单元格偏移量就是我们需要的成绩值。
从上面我们不难发现,OFFSET函数经常和MATCH函数一起使用。因为Match函数可以找到一个序列中关键字的序号,所以我们经常用这个函数来确定OFFSET函数的偏移量。
第三,高阶应用的思想
(动态报表模板原型)
我们用Excel快速分析数据,提取我们需要的东西。现在假设以下两种情况:
场景一:
领导布置工作,统计了一个季度的销售数据。我们立即行动,用函数快速做了报告。
场景二:
领导布置了工作,因为我们每个季度都需要统计销售数据,所以提前做了模板。至于什么时候做报告,由我们决定。记住,不要让“中层领导”知道你的工作效率很高。
你会选择哪种方式来处理这两个场景?笔者希望是第二种。
思维方式决定了我们制表的模式。这是一个简单的例子。当数据源被修改时,相应的季度数据会自动调整。并不是复杂模板中的所有位置都会使用OFFSET函数,但是使用OFFSET函数来处理动态引用数据区域的需求是绝对正确的。
四。典型用法示例
特技4:制作动态下拉菜单
在数据建模过程中,我们经常使用下拉菜单(或组合框控件)。为了保证下拉内容的唯一性,我们将使用INDEX+SMALL+IF+ROW的“万金油”函数对系列中的数据进行重新提取。还记得我们在上一篇文章中谈到的偏移函数代替索引函数的例子吗?所以,如果可以用偏置函数代替指数函数,那么偏置函数也可以实现“万能油”的过程。让我们来看看复杂的“下拉菜单”的制作过程。
第一步:使用偏移功能重新提取独特的“万金油”公式。
这个公式很长,如下所示:
D2细胞功能:
=iferror(offset($a$1,small(if(row($a$2:$a$27)-1=match($a$2:$a$27,$a$2:$a$27,0),row($1:$20),9^9),row(d1)),),";")
万能药配方不是我们今天要讲的话题,就不说了。重要的是让大家知道偏移功能也可以达到这个效果。
步骤2:使用名称管理器中的OFFSET函数创建一个数据源。
我们可以使用Ctrl+F3打开名称管理器窗口,然后新建一个名称,名称设置为“地区”,参考位置为“D2:D15”,如下图所示:
然后选择单元格G1,按Alt+D+L打开数据校验设置框。在“允许”中选择“序列”,在“来源”中输入“=区域”,如下图所示:
单击确定,G1单元的下拉菜单将被建立。但是问题也来了。我们会发现有很多空选项,都不是我们需要的。
有些同学会说,在名字管理器里选D2:D5就够了。可以,但是如果A列的区域出现新的数据,下拉菜单中的数据就会少一些,所以此时我们还是用OFFSET函数来处理这个问题。
在名称管理器中更改“区域”的引用位置:
=OFFSET(动态下拉菜单!$ d $D$1,1,,COUNTA(动态下拉菜单!$D$2:$D$15)-COUNTBLANK(动态下拉菜单!$D$2:$D$15),1)
因为我们的D列的唯一值是通过一个公式得到的,它里面的“空单元格”不是名字空的,而是通过一个公式空的,所以我们不能直接通过COUNTIF(D2:D15,& quot")来获取有值单元格的个数。所以我们先用COUNTBLANK函数统计空单元格的个数,再用COUNTA函数统计非空单元格的个数,最后两者相减得到有价值的单元格个数。将得到的结果作为OFFSET函数的第四个参数(新区域扩展行数),实现动态引用有效数据的效果。如下图所示:
如果一个新的区域名称被添加到A列,那么新的选项将被添加到G1的下拉菜单中。我们一起来看看效果吧。我相信这就是你需要的。
特技5:图表中5:OFFSET函数的使用
相信大家对上图都很熟悉。参加工作的同学会有制作图表的经历。选择上图中的区域A1:B10,在工具栏中插入柱形图,完成我们图例的内容。
如果我们删除一行数据,柱形图中将少一个系列图例,但如果我们添加一行数据,我们需要更改图表数据源的范围以显示正确的图表。但是不能每次都改,这样会失去我们高效快捷使用Excel的初衷。
此时,我们仍然可以借鉴偏置函数来求解:
步骤1:使用OFFSET函数分别为日期列和数量列创建自定义名称。
名经理,上面我们已经介绍过了,就不多说了。选择“日期栏”并按如下方式设置:
参考位置功能:
=OFFSET(图表系列!$A$1,1,0,COUNTA(图表系列!$A$2:$A$1000),1)
因为原始数据中没有公式得到的空单元格,所以这里不需要使用Countblank函数,非空单元格的个数可以直接用CountA函数作为OFFSET函数的第四个参数(新区域的行数)来统计。A2:A1000这里代表绝对大的区域,保证新输入的内容在这个范围内。
选择“数量列”,以同样的方式为数量创建一个用户定义的名称,如下所示:
第二步:使用图表区中的名称。
这是偏移动态图的关键,加名字的位置很重要。
在绘图区选择任意一列,在编辑栏可以看到图标的函数写法(是不是第一次知道图表也有函数)。我们在这里修改了引用的范围。
我们只需要改变部分地区。
图表系列!2澳元:10澳元
图表系列!$乙$ 2:$乙$10
把这两个红色部分换成自定义名称就行了,一定不能改“图表系列!$A$2:$A$10”整体替换!
替换后按回车键,功能显示如上图。OFFSET.xlsx是我们工作簿的名称。效果如下:
其他图表类型也是可能的。你可以试着操作它们来加深你的印象。
[编者按]
如果你理解了OFFSET函数的五个参数的含义,就不难记住了。它的返回值可以作为其他函数的参考。同样,其他返回值为数值格式的函数也可以作为OFFSET函数的参数,这样我们的数据就可以自己移动了。
该函数在Excel函数中起着不可或缺的作用,尤其是当我们需要使用Excel进行建模的时候。该功能常用于动态区域参考和自动数据处理。强烈建议同学们多花点时间学习一下,对你以后的制表过程大有裨益。
相关学习推荐:excel教程
以上是Excel函数学习中“动态统计之王”OFFSET()的详细内容。更多请关注主机参考其他相关文章!
这几篇文章你可能也喜欢:
- 如何使用Excel控件(Excel控件)
- 在Excel中按名称查询照片很简单(如何在Excel中按名称搜索照片)
- 使用Excel网格实现项目进度图表(如何使用Excel创建项目进度条)
- 在Excel中轻松查看任意条件下的数据(如何在Excel中根据条件搜索数据)
- 下面讲一下Excel函数学习中的多重求和函数(Excel中如何对多个数据求和)
本文由主机参考刊发,转载请注明:浅谈Excel函数学习:动态统计偏移之王() https://zhujicankao.com/94156.html
评论前必须登录!
注册