浅谈Excel函数学习:动态统计之王OFFSET（）（动态统计方法）

本文带你了解“动态统计之王”这个昵称的偏移功能！Offset函数是一个非常实用的函数，在下拉菜单、动态图表、动态引用等操作中发挥着不可替代的作用。毫不夸张地说，excel表格的功能有相当一部分来自抵销。

[前言]

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

【功能和语法】

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

语法:偏移量（引用，行，列，

我相信每个人都花了很多时间看这张照片。我们可以先按照上图的指导将数据填充到OFFSET函数中，实际操作一下，看是否与新区的地址一致。

让我们先测试第一个示例，看看以正数作为参数的运行结果:

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

让我们再次测试第二个示例，并查看参数为负数的运行结果:

您可以使用“公式评估”进行自我测试，并查看偏移函数区域的返回值。

那么了解OFFSET的基本操作原理就可以帮助我们在实际工作中进行大量的操作和计算，并且在该功能的参与下，可以实现excel中的许多自动化效果。让我们来看看偏移功能在实际操作中的强大作用！

第一，主要的常规用法

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

函数的原理与上述用法相同，因此我们不再赘述。我们仍然使用OFFSET函数返回的面积作为MAX函数的参数。

第二，高级常规用法

特技①:模拟移调功能

在使用转置函数之前，我们需要选择相应大小的转置区域，还需要使用Ctrl+Shift+Enter来结束公式，这相当复杂。

这里我们可以使用OFFSET函数来模拟这种移调的效果，如上图所示。

A11细胞功能:

= OFFSET（$ A $ 1，COLUMN（）-1，ROW（）-11）

功能分析:

转置数据实际上是一个“行到列”和“列到列”的过程，具体点就是行号和列号的交换。原始数据中的第一列“Name”已被转置为新区域中的第一行。同样，“名称”列中每一行的行号成为转置后的列号。使用偏移的原理是在进行偏移时改变行号和列号的引用范围。

★例如在单元格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（“d 2568“，$ B $ 2:$ B $ 7.0））

功能分析:

我们使用单元格A1作为原始基点，并且要返回的值与原始基点在同一列中，因此我们只需要考虑OFFSET函数的行偏移量，而不需要考虑列偏移量。因为员工编号通常是唯一的值，所以我们使用MATCH函数获取B2:B7地区中编号“D2568”的序列号，返回值4作为OFFSET函数的行偏移量并带入OFFSET函数= OFFSET（$ A $ 1，4，）。列偏移省略号默认为0，延伸宽度和延伸高度省略号默认为1（即单元格）。是A5细胞吗？

特技3:数据重置升级-重新安排数据结构

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

功能分析:

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

F2细胞功能:

= OFFSET（$ A $ 1，INT（（ROW（F1）-1）/3）+1，）

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

从图中，我们可以看到一组序列号。在转换INT（（序列号-1）/3）+1之后，我们可以得到右边的序列（如果有四个主题，请将3更改为4，以此类推）。将此序列号放入OFFSET函数的第二个参数中作为行偏移量的标准，我们可以得到我们的名称列的效果。

第二步:将不同的主题分配给同一个人。

G2细胞功能:

= OFFSET（$ A $ 1，，MOD（世界其他地区（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函数获取相关区域中数据对应的序列号，并将它们作为偏移量的偏移量放入第二个和第三个参数中。从参考点A1单元格偏移的单元格就是我们需要的成就值。

从上面我们不难发现，OFFSET函数经常与MATCH函数一起使用。由于Match函数可以找到一个序列中关键字的序号，因此我们经常使用该函数来确定OFFSET函数的偏移量。

三、高阶应用的思想

（动态报告模板原型）

我们使用Excel快速分析数据并提取我们需要的内容。现在假设以下两种情况:

场景一:

领导安排了工作，统计了一个季度的销售数据。我们立即采取行动，用该功能快速完成了报告。

场景二:

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

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

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

四。典型用法示例

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

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

步骤1:使用偏移功能重新提取独特的“万金油”配方。

该公式很长，如下所示:

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）-count blank（动态下拉菜单！2美元:15美元），1）

因为我们的D列的唯一值是通过公式获得的，所以其中的“空单元格”在名称上不是空的，而是通过公式空的，所以我们不能通过计数if（D2:D15，““）直接获得有值的单元格的数量。因此，我们首先使用COUNTBLANK函数来统计空单元格的数量，然后使用COUNTA函数来统计非空单元格的数量，最后将两者相减即可得到有价值的单元格的数量。使用获得的结果作为OFFSET函数的第四个参数（新区域中扩展的行数），实现了动态引用有效数据的效果。如下图所示:

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

特技5:图表中5:偏移功能的使用

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

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

在这一点上，我们仍然可以从OFFSET函数中学习求解:

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

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

参考位置功能:

=OFFSET（图表系列！$A$1，1，0，COUNTA（图表系列！2澳元:1000澳元），1）

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

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

步骤2:使用图表区中的名称。

这是偏移动态图的关键，添加名称的位置非常重要。

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

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

图表系列！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函数学习:动态统计之王OFFSET（）（动态统计方法） https://zhujicankao.com/101002.html