VPS参考测评推荐
专注分享VPS主机优惠信息
衡天云优惠活动
华纳云优惠活动

浅谈Excel函数学习:动态统计偏移之王()

主机参考:VPS测评参考推荐/专注分享VPS服务器优惠信息!若您是商家可以在本站进行投稿,查看详情!此外我们还提供软文收录、PayPal代付、广告赞助等服务,查看详情!
我们发布的部分优惠活动文章可能存在时效性,购买时建议在本站搜索商家名称可查看相关文章充分了解该商家!若非中文页面可使用Edge浏览器同步翻译!PayPal代付/收录合作

本文带你了解一下外号“动态统计之王”的OFFSET函数!OFFSET函数是一个非常实用的函数,在下拉菜单、动态图表、动态引用等操作中有着不可替代的作用。毫不夸张地说,Excel表格的功能有相当一部分来自于抵销。

浅谈Excel函数学习:动态统计偏移之王()

[前言]

抵销函数是判断Excel函数用户是否先进的重要函数之一。在实际工作中,如果你需要对工作中的数据文件进行系统化、自动化的建模,那么这个功能必然要用到。

[功能和语法]

OFFSET函数的作用是根据指定的引用返回一个具有给定偏移量的新引用。

语法:OFFSET(引用,行,列,

相信大家都花了不少时间看这张图。我们可以先按照上图的指引把数据填入OFFSET函数,实际操作一下,看是否和新区的地址一致。

我们先测试第一个例子,看看以正数为参数的运行结果:

浅谈Excel函数学习:动态统计偏移之王()

通过检查计算,黄色“新区域”中的值之和等于256,与单元格C15中的值一致,结果正确。如果学生想模拟这个数据,也可以选择单元格C15,然后通过工具栏中的“公式-公式审核-公式求值”功能,可以更直观的看到OFFSET的返回值。(也可以在函数中使用F9。在公式中选择OFFSET的函数部分按F9就可以了,这里就不说了。)

浅谈Excel函数学习:动态统计偏移之王()

让我们再次测试第二个例子,看看以负数作为参数的运行结果:

浅谈Excel函数学习:动态统计偏移之王()

可以用“公式求值”自己测试一下,看看偏移函数区的返回值。

那么了解OFFSET的基本运算原理,可以帮助我们在实际工作中进行大量的运算和计算,并且有了这个函数的参与,可以实现excel中的很多自动化效果。让我们来看看OFFSET函数在实际操作中的强大作用!

第一,主要的日常用法

作为其他函数的区域参考,应该是偏移函数最基本的用法。OFFSET函数不移动单元格区域,但返回一个偏移量扩展的区域地址。因此,所有将引用区域作为参数的函数都可以使用OFFSET函数的返回值,例如我们上面的示例Sum(OFFSET())和下面的示例:

浅谈Excel函数学习:动态统计偏移之王()

函数的原理和上面的用法一样,就不赘述了。我们还是用OFFSET函数返回的面积作为MAX函数的参数。

第二,高级常规用法

特技①:模拟转置功能

1.jpg

在使用转置函数之前,我们需要选择相应大小的转置区域,还需要使用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函数的反向查询功能。

浅谈Excel函数学习:动态统计偏移之王()

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细胞吗?

绝技三:数据重置升级——重新排列数据结构

浅谈Excel函数学习:动态统计偏移之王()

在F2:H2区域输入公式后,下拉并填入数据,您会得到右侧的一维数据表。这种重新整理数据的问题在实际工作中应该不少见!那么同学们会选择什么方法来解决呢?相反,作者认为偏置函数的思想更简洁明了。

功能分析:

第一步:获取连续出现的名字。

F2细胞功能:

=OFFSET($A$1,INT((ROW(F1)-1)/3)+1,)

因为有三个主题,可以确定同一个名字需要出现三次,所以我们在下拉F2单元格填充函数的时候,一定要保证每三个单元格的OFFSET函数的行偏移量都是相同的。需要一种“分舍取整”的数学思维。让我们制作一个图表来帮助解释:

浅谈Excel函数学习:动态统计偏移之王()

从图中我们可以看到一组序列号。经过INT((序号-1)/3)+1的转换,我们可以得到右边的序列(如果有四个主语,把3换成4,以此类推)。把这个序号放入OFFSET函数的第二个参数中作为行偏移量的标准,就可以得到我们的name列的效果了。

第二步:给同一个人分配不同的科目。

G2细胞功能:

=OFFSET($A$1,,MOD(ROW(G1)-1,3)+1)

因为我们F栏的每个名字都出现了三次,这就决定了语文、数学、英语三科需要按顺序、循环排列。就像第一步的思路一样,用“除数求余数”的数学思维就可以达到效果。

浅谈Excel函数学习:动态统计偏移之王()

如上图所示,通过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快速分析数据,提取我们需要的东西。现在假设以下两种情况:

场景一:

领导布置工作,统计了一个季度的销售数据。我们立即行动,用函数快速做了报告。

场景二:

领导布置了工作,因为我们每个季度都需要统计销售数据,所以提前做了模板。至于什么时候做报告,由我们决定。记住,不要让“中层领导”知道你的工作效率很高。

你会选择哪种方式来处理这两个场景?笔者希望是第二种。

浅谈Excel函数学习:动态统计偏移之王()

思维方式决定了我们制表的模式。这是一个简单的例子。当数据源被修改时,相应的季度数据会自动调整。并不是复杂模板中的所有位置都会使用OFFSET函数,但是使用OFFSET函数来处理动态引用数据区域的需求是绝对正确的。

四。典型用法示例

特技4:制作动态下拉菜单

在数据建模过程中,我们经常使用下拉菜单(或组合框控件)。为了保证下拉内容的唯一性,我们将使用INDEX+SMALL+IF+ROW的“万金油”函数对系列中的数据进行重新提取。还记得我们在上一篇文章中谈到的偏移函数代替索引函数的例子吗?所以,如果可以用偏置函数代替指数函数,那么偏置函数也可以实现“万能油”的过程。让我们来看看复杂的“下拉菜单”的制作过程。

第一步:使用偏移功能重新提取独特的“万金油”公式。

浅谈Excel函数学习:动态统计偏移之王()

这个公式很长,如下所示:

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”,如下图所示:

浅谈Excel函数学习:动态统计偏移之王()

然后选择单元格G1,按Alt+D+L打开数据校验设置框。在“允许”中选择“序列”,在“来源”中输入“=区域”,如下图所示:

浅谈Excel函数学习:动态统计偏移之王()

单击确定,G1单元的下拉菜单将被建立。但是问题也来了。我们会发现有很多空选项,都不是我们需要的。

浅谈Excel函数学习:动态统计偏移之王()

有些同学会说,在名字管理器里选D2:D5就够了。可以,但是如果A列的区域出现新的数据,下拉菜单中的数据就会少一些,所以此时我们还是用OFFSET函数来处理这个问题。

在名称管理器中更改“区域”的引用位置:

浅谈Excel函数学习:动态统计偏移之王()

=OFFSET(动态下拉菜单!$ d $D$1,1,,COUNTA(动态下拉菜单!$D$2:$D$15)-COUNTBLANK(动态下拉菜单!$D$2:$D$15),1)

因为我们的D列的唯一值是通过一个公式得到的,它里面的“空单元格”不是名字空的,而是通过一个公式空的,所以我们不能直接通过COUNTIF(D2:D15,& quot")来获取有值单元格的个数。所以我们先用COUNTBLANK函数统计空单元格的个数,再用COUNTA函数统计非空单元格的个数,最后两者相减得到有价值的单元格个数。将得到的结果作为OFFSET函数的第四个参数(新区域扩展行数),实现动态引用有效数据的效果。如下图所示:

2.jpg

如果一个新的区域名称被添加到A列,那么新的选项将被添加到G1的下拉菜单中。我们一起来看看效果吧。我相信这就是你需要的。

浅谈Excel函数学习:动态统计偏移之王()

特技5:图表中5:OFFSET函数的使用

浅谈Excel函数学习:动态统计偏移之王()

相信大家对上图都很熟悉。参加工作的同学会有制作图表的经历。选择上图中的区域A1:B10,在工具栏中插入柱形图,完成我们图例的内容。

如果我们删除一行数据,柱形图中将少一个系列图例,但如果我们添加一行数据,我们需要更改图表数据源的范围以显示正确的图表。但是不能每次都改,这样会失去我们高效快捷使用Excel的初衷。

此时,我们仍然可以借鉴偏置函数来求解:

步骤1:使用OFFSET函数分别为日期列和数量列创建自定义名称。

名经理,上面我们已经介绍过了,就不多说了。选择“日期栏”并按如下方式设置:

浅谈Excel函数学习:动态统计偏移之王()

参考位置功能:

=OFFSET(图表系列!$A$1,1,0,COUNTA(图表系列!$A$2:$A$1000),1)

因为原始数据中没有公式得到的空单元格,所以这里不需要使用Countblank函数,非空单元格的个数可以直接用CountA函数作为OFFSET函数的第四个参数(新区域的行数)来统计。A2:A1000这里代表绝对大的区域,保证新输入的内容在这个范围内。

选择“数量列”,以同样的方式为数量创建一个用户定义的名称,如下所示:

浅谈Excel函数学习:动态统计偏移之王()

第二步:使用图表区中的名称。

这是偏移动态图的关键,加名字的位置很重要。

在绘图区选择任意一列,在编辑栏可以看到图标的函数写法(是不是第一次知道图表也有函数)。我们在这里修改了引用的范围。

浅谈Excel函数学习:动态统计偏移之王()

我们只需要改变部分地区。

图表系列!2澳元:10澳元

图表系列!$乙$ 2:$乙$10

把这两个红色部分换成自定义名称就行了,一定不能改“图表系列!$A$2:$A$10”整体替换!

浅谈Excel函数学习:动态统计偏移之王()

替换后按回车键,功能显示如上图。OFFSET.xlsx是我们工作簿的名称。效果如下:

浅谈Excel函数学习:动态统计偏移之王()

其他图表类型也是可能的。你可以试着操作它们来加深你的印象。

[编者按]

如果你理解了OFFSET函数的五个参数的含义,就不难记住了。它的返回值可以作为其他函数的参考。同样,其他返回值为数值格式的函数也可以作为OFFSET函数的参数,这样我们的数据就可以自己移动了。

该函数在Excel函数中起着不可或缺的作用,尤其是当我们需要使用Excel进行建模的时候。该功能常用于动态区域参考和自动数据处理。强烈建议同学们多花点时间学习一下,对你以后的制表过程大有裨益。

相关学习推荐:excel教程

以上是Excel函数学习中“动态统计之王”OFFSET()的详细内容。更多请关注主机参考其他相关文章!

这几篇文章你可能也喜欢:

本文由主机参考刊发,转载请注明:浅谈Excel函数学习:动态统计偏移之王() https://zhujicankao.com/94156.html

【腾讯云】领8888元采购礼包,抢爆款云服务器 每月 9元起,个人开发者加享折上折!
打赏
转载请注明原文链接:主机参考 » 浅谈Excel函数学习:动态统计偏移之王()
主机参考仅做资料收集,不对商家任何信息及交易做信用担保,购买前请注意风险,有交易纠纷请自行解决!请查阅:特别声明

评论 抢沙发

评论前必须登录!