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

本文带你了解一下外号“动态统计之王”的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)),),&quot；")

万能药配方不是我们今天要讲的话题，就不说了。重要的是让大家知道偏移功能也可以达到这个效果。

步骤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跨表提取，微软查询KO所有函数
• Excel图表学习使用旋风图比较数据（如何分离旋风图的中间）
• 实用Excel技巧分享:几种不同情况下的数据比较
• 实用Excel技巧分享:格式刷的各种玩法！（如何使用excel格式刷）

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