Excel函数学习之聊聊动态统计之王OFFSET()-

本篇文章带大家了解一下人送外号“动态统计之王”的OFFSET函数!OFFSET函数是一个非常实用的函数,它在下拉菜单、动态图表、动态引用等操作中都具有不可替代的作用。毫不夸张的说Excel表格的高效,有相当一部分的功能来源于OFFSET。

【前言】

OFFSET函数是判断Excel函数使用者是否进阶的一个重要函数之一。在实际工作中,如果你需要对工作中的数据文件进行系统化、自动化的建模,那么势必会使用这个函数。

【功能及语法】

OFFSET函数的功能是,以指定的引用为参照系,通过给定的偏移量返回新的引用。

语法:OFFSET(reference,rows,cols,[height],[width])

reference 是原基础点

rows 是要偏移的行数,正数向下,负数向上,零不变。

cols 是要偏移的列数,正数向右,负数向左,零不变。

[height] 是基础点偏移后,纵向扩展几行,正数向下扩展,负数向上扩展。

[width] 是基础点偏移后,横向扩展几列,正数向右扩展,负数向左扩展。

如果不使用第四个和第五个参数(但不可以为零),则新引用的区域和原基础点大小一致。

原基础点可以是一个单元格,也可以是一个区域。

刚刚接触OFFSET函数的同学,想要理解上面这些参数,可能存在一定的难度,那么我们用一个图解的方式来给大家说明一下吧。

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

先来测试下第一个例子,看看正数为参量的运行结果:

通过验算,对黄色 “新区域”中的值进行求和,等于256,与单元格C15中的值一致,结果正确。如果同学们想模拟这个数据,也可以选中C15单元格,再通过工具栏中“公式——公式审核——公式求值”的功能,就能更加直观的看到OFFSET的返回值。(在函数中使用F9也是可以的,选中公式中OFFSET的函数部分,再按F9即可,这里就不多讲了。)

再来测试下第二个例子,看看负数为参量的运行结果:

大家可以用“公式求值”的方式,自己测试一下,看看OFFSET函数区域的返回值。

那么知道了OFFSET的基本运行原理之后,它在实际的工作中就可以帮助我们进行很多的操作和运算,而且有了这个函数的参与,可以实现excel中很多自动化的效果。下面让我们一起来看看OFFSET函数在实际操作中起到的强大作用!

一、初级常规用法

作为其他函数的区域引用,应该是OFFSET函数最基础的用途了。OFFSET函数并不是移动了单元格区域,而是返回了一个偏移扩展后的区域地址。因此所有将引用区域作为参数的函数,都可以利用OFFSET函数的返回值,例如我们上面的例子Sum(OFFSET()),再比如下面这个例子:

函数原理和上面的用法相同,我们就不再赘述了,依然是利用OFFSET函数返回的区域作为MAX函数的参数。

二、进阶常规用法

绝技①:模拟转置TRANSPOSE函数

我们在使用TRANSPOSE函数前,需要先选择相应大小的转置区域,而且还需用Ctrl+Shift+Enter三键结束公式,比较繁琐。

这里我们可以使用OFFSET函数来模拟这个转置的效果,如上图所示。

A11单元格函数:

=OFFSET($A$1,COLUMN()-1,ROW()-11)

函数解析:

转置数据其实就是一个“行转列”、“列转行”的过程,再说具体点就是行号与列号互换的问题。在原数据中的第一列“姓名”列,转置后变成了新区域中的第一行。同理“姓名”列中每行的行号,就成为了转置后的列号。使用OFFSET的原理,就是偏移取值的时候,调换行列号的引值范围。

★ 比如A11单元格,COLUMN()=1,1-1=0,那么OFFSET的第二参数为0,说明原基础点的行数不偏移(OFFSET的第二参数表示行偏移量,不熟悉的话看看前面的内容哟!)。ROW()=11,11-11=0,OFFSET的第三参数为0,说明列数也不偏移,所以引用的是原基础点A1单元格的值。

★★ 把函数向右拉动填充,B11单元格,COLUMN()=2,2-1=1,那么OFFSET的第二参数为1,说明原基础点的行数向下偏移一个位置。ROW()=11,11-11=0,OFFSET的第三参数为0,说明列数不偏移,所以B11单元格引用的是基础点A1向下偏移后的A2单元格的值。

★★★ 把A11单元格的函数向下拉动填充,A12单元格,COLUMN()=1,1-1=0,行数不偏移。ROW()=12,12-11=1,OFFSET的第三参数为1,说明列数从基础点A1向右偏移一个位置,引用的是B1单元格的值(我们公式中的A1之所以使用绝对引用,是因为我们所有的单元格都是以A1为基础点)。

以此类推,当我们使用鼠标下拉右拉填充公式之后,借助COLUMN和ROW函数帮我们定位出各个单元格的偏移量,由此达到了转置的效果。

绝技②:模拟Vlookup函数的反向查询功能

VLOOKUP函数的反向查询大多是借助数组完成的,但因为数组的原因,在数据量较多的情况下,函数可能会卡顿,所以很多同学也会使用INDEX函数来代替。那么今天就再丰富一下大家的知识量,我们用OFFSET函数来处理这类问题。

C12单元格函数:

=OFFSET($A$1,MATCH("D2568",$B$2:$B$7,0),)

函数解析:

我们以单元格A1作为原基础点,需要返回的值与原基础点在同一列,所以我们只需要考虑OFFSET函数的行偏移量,不用考虑列偏移量。因为员工编号一般都是具有唯一性的值,所以我们采用MATCH函数得到编号“D2568”在区域B2:B7中的序号,返回值4作为OFFSET函数的行偏移量,带入到OFFSET函数中,=OFFSET($A$1,4,)。列偏移省略默认为0,扩展宽度和扩展高度省略默认为1 (即一个单元格),是不是就是A5单元格啦!

绝技③:数据重置升级版——重排数据结构

在F2:H2区域输入公式后,下拉填充数据,就得到了右面的一维数据表。这种重排数据的问题,在实际工作中应该不少见吧!那么同学们会选择什么方法解决呢?作者反而觉得OFFSET函数的思路更加的简洁清晰。

函数解析:

第一步:得到连续出现的姓名

F2单元格函数:

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

因为科目一共有三个,所以可以确定同一个姓名需要出现三次,那么当我们下拉F2单元格填充函数的时候,就要保证OFFSET函数的行偏移量每3个单元格的参数值都是一样的。这里就需要有一个“除数取整”的数学思维了,我们列个图来辅助说明:

从图中我们可以看出一组序号,通过INT((序号-1)/3)+1的转换后,就可以得到右侧的序列(如果有4个科目,那就把3改成4,依此类推)。将这个序列号放入OFFSET函数的第二参数,作为行偏移的标准,就可以得到我们姓名列的效果了。

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

G2单元格函数:

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

因为我们F列中的每个姓名都出现了三次,这就决定了语文、数学、英语这三个科目需要顺序、循环地罗列出来,同第一步的思路一样,用“除数求余”的数学思维来达到效果。

如上图所示,序号通过MOD函数的转换,得到一个顺序、循环罗列的序号。将该序号作为OFFSET函数的第三参数列偏移量,就可以顺序、循环的引出原数据的科目内容。

第三步:通过姓名和科目,模拟INDEX函数,在原数据中引出成绩

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的“万金油”函数来去重提取数列中的数据。还记得我们在上篇讲到的OFFSET函数替代INDEX函数的例子吗?所以说,如果OFFSET函数可以代替Index函数使用的话,那么OFFSET函数同样也可以实现“万金油”的过程。下面我们就一起来看看复杂的“下拉菜单”的制作过程。

步骤一:使用OFFSET函数去重提取唯一值的 “万金油”公式

这个公式比较长,列出如下:

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)),),"")

万金油公式不是我们今天要讲的主题,就不展开讲了。重要就是为了让大家知道OFFSET函数也是可以达到这样去重的效果。

步骤二:在名称管理器中使用OFFSET函数,建立数据源

我们可以用Ctrl+F3组合键,打开名称管理器窗口,然后新建名称,名称设置为“区域”,引用位置为“D2:D15”,如下图所示:

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

点击确定按钮,那么我们G1单元格的下拉菜单就建立好了。但是问题也来了,我们会发现有好多的空选项,这不是我们需要的。

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

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

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

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

如果A列中增加了新的区域名称,那么G1的下拉菜单也会增加新的选项,让我们来一起看一下效果,我相信这是你需要的。

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

上面的这张图表,相信大家都不陌生吧。参加工作的同学们都会有制作图表的经历,在上图中选择A1:B10区域,在工具栏——“插入”——柱形图,就完成了我们图例的内容。

如果我们删掉一行数据,那么柱形图中的系列图例就会少一个,可是如果增加一行数据的话,就需要更改图表数据源的范围,才能显示正确的图表。但总不能每次都更改呀,那就失去了我们使用Excel 高效快速的初衷。

此时,我们依然可以借鉴OFFSET函数来解决:

步骤一:使用OFFSET函数分别对“日期列”和“数量列”,制作自定义名称

名称管理器,我们上面有介绍,就不多说了。选中“日期列”,设置如下:

引用位置函数:

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

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

选中“数量列”,同理制作出数量的自定义名称,如下:

步骤二:在图表区域使用名称

这是OFFSET动态图表的关键所在,添加名称的位置是很重要的。

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

我们只需改动区域的部分。

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

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

用自定义名称替换这两个红色的部分即可,切不可以将“图表系列!$A$2:$A$10”整体替换!

替换后按回车,函数就会像上图这样显示,OFFSET.xlsx是我们的工作薄名称。效果如下:

其他的图表类型也是可以的,大家可以试着操作一下,加深印象。

【编后语】

OFFSET函数的五个参数,如果理解了意义,就不难记住。它的返回值可以作为其他函数的引用,同理其他“返回值是数值格式”的函数也可以作为OFFSET函数的参数,让我们的数据可以自己动起来。

这个函数在Excel函数中起着不可或缺的作用,尤其是我们需要使用Excel建模的时候,动态区域的引用、自动化处理数据,往往都会使用到这个函数,强烈建议同学们,能多花一些时间来学习一下,对你今后制表的过程,将大有裨益。

相关学习推荐:excel教程

Excel函数学习之聊聊动态统计之王OFFSET()-