excel中OFFSET函数的使用方法 - 经验吧

excel中OFFSET函数的使用方法

更新: 2016-11-29 02:18 编辑:生活经验

说起OFFSET函数,在excel中应用十分广泛,从复杂的数据汇总、到数据透视表再到高级动态图表,都离不开OFFSET函数的默默付出。通过OFFSET函数,可以生成数据区域的动态引用,这个引用再作为半成品,通过后续的处理加工,就可以为图表和透视表提供动态的数据源、为其他函数生成特定的引用了。

今天就来简单介绍一下这个函数的使用方法。

offset函数的作用:以指定的引用为参照系,通过给定偏移量返回新的引用。

具体解释一下:这个函数有5个参数:第一个参数是基点第二个参数是要偏移几行,正数向下,负数向上。第三个参数是要偏移几列,正数向右,负数向左。第四个参数是新引用几行。第五个参数是新引用几列。如果不使用第四个和第五个参数,新引用的区域就是和基点一样的大小。

如果使用下面这个公式:=OFFSET(C3,4,2,4,3)就是以C3为基点,向下偏移4行,向右偏移2列,新引用的行数是4行,新引用的列数是3列,最终得到对E7:G10单元格区域的引用。我这里用数字1-9标注出来了。

excel中OFFSET函数的使用方法

下面再来详细说一下=OFFSET(C3,4,2,4,3):

OFFSET函数先从C3单元格向下偏移4行,走到C7单元格,再从C7单元格向右偏移2列,走到E7单元格。然后以E7为基准点,扩张4行,3列,就是E7:G10单元格区域。

excel中OFFSET函数的使用方法

excel中OFFSET函数的使用方法

excel中OFFSET函数的使用方法

简单认识了OFFSET函数,咱们再用一个动态图表的制作,来说说OFFSET函数的实际应用。所谓动态图表,就是能根据指定的条件,自动变化图表数据源,使图表能够按照我们指定的规则,动态显示数据中的重点关注部分。

例如

在这个销售流水记录中,每天都要不断的添加数据。现在要制作一个图表,仅展示最近7天的销售状况。

分别定义两个名称:日期=OFFSET($A$1,COUNT($A:$A),0,-7)销售额=OFFSET($B$1,COUNT($A:$A),0,-7)

具体如下:单击【公式】选项卡下,选中【定义名称】,在弹出的对话框中进行下列操作,名称选项中填写【日期】,引用位置选项中,填写【=OFFSET($A$1,COUNT($A:$A),0,-7)】。用同样的方法定义销售额。

excel中OFFSET函数的使用方法

excel中OFFSET函数的使用方法

具体说说定义名称日期这个公式的意思:COUNT函数对A列数值计数,结果作为OFFSET函数的行偏移参数。OFFSET函数以A1为基点,向下偏移的行数是COUNT的结果,也就是A列有多少个数值,就向下偏移多少行。这时候就相当于到了A列数值的最后一行,给定的偏移列数是0,新引用的行数是-7,得到从A列数值的最后一行开始,向上7行这样一个动态的区域。如果A列的数值增加了,COUNT函数的计数结果就增加了,OFFSET函数的行偏移参数也就随之变化

接下来我们插入一个柱状图。选择数据区域,单击【插入】-【柱状图】,选择二位柱状图。

excel中OFFSET函数的使用方法

excel中OFFSET函数的使用方法