Excel Offset功能|公式&免费视频

Excel Offset函数(示例+视频)

Excel偏移功能

何时使用Excel Offset函数

当您想要获取引用时,可以使用Excel Offset函数,该引用抵消了从起始点的指定行数和列。

它回来了什么

它返回偏移功能指向的引用。

句法

=偏移量(参考,行,COL,[高度],[宽度])

输入参数

  • 参考 -您要偏移的参考。这可以是小区参考或一系列相邻单元。
  • 行 -偏移的行数。如果使用正数,则将其偏移到下面的行,如果使用负数,则它将偏移到上面的行。
  • COL -偏移量的列数。如果您使用正数,它将偏移到右侧的列,如果使用负数,则它将偏移到左侧的列。
  • [高度] -这是一个表示返回参考中的行数的数字。
  • [宽度] -这是一个表示返回参考中列数的数字。

了解Excel Offset函数的基础知识

Excel Offset函数可能是Excel中最令人困惑的函数之一。

让我们举一场简单的棋牌游戏的例子。国际象棋中有一块叫做车(也称为塔,侯爵或校长)。

120px-chess_piece _-_ white_rook[图片礼貌:美妙的维基百科]

现在,作为所有其他棋子,一个车有一个固定的路径,它可以在电路板上移动。它可以直接(向右/向左或向上/向下/向上/向下),但它不能对角线。

例如,假设我们在Excel(如下所示)中有一个棋盘(如下所示),在给定框中(D5在这种情况下),车辆只能进入四个突出显示的方向。

Excel Offset功能 - 国际象棋

现在,如果我要求你从它的当前位置带到黄色突出的那个位置,你会告诉车怎么样?

Excel Offset功能 - 国际象棋移动

你会要求它向下采取两个步骤和右边的两个步骤..不是吗?

并且这是偏移功能如何工作的近距离。

现在让我们看看在Excel中的意思。我想从小区D5开始(这是车辆的位置),然后向右转两次,向右向右移动两列并从那里获取来自单元格的值。

公式是:
= offset(从哪里开始,向下右转,右侧有多少列)

Excel Offset功能 - 返回值国际象棋

如您所见,Cell J1中的上面示例中的公式是=偏移(D5,2,2)。

它在D5开始,然后向下两排,向右两列左右到达单元格F7。然后它返回了单元F7中的值。

在上面的示例中,我们研究了具有3个参数的偏移函数。但是有两种可以使用的选项可以使用。

让我们来看看一个简单的例子:

Excel偏移功能 - 区域

假设您要使用对单元格A1(以黄色)使用的引用,并且您希望在公式中引用以蓝色(C2:E4)突出显示的整个范围。

你怎么用键盘做的?您将首先转到单元格C2,然后选择C2中的所有单元格:E4。

现在让我们看看如何使用偏移公式执行此操作:

=偏移(A1,1,2,3,3)

如果在单元格中使用此公式,它将返回#Value!错误,但如果进入编辑模式并选择公式并按F9,则会看到它返回以蓝色突出显示的所有值。

Excel Offset功能 -  F9

现在让我们看看这个公式如何工作:

=偏移(A1,1,2,3,3)
  • 第一个参数是它应该开始的单元格。
  • 第二个参数为1,它告诉Excel返回已偏移1行的引用。Excel Offset函数 -  arg 1
  • 第三个参数是2,它告诉Excel返回由2列偏移的引用。Excel Offset函数 -  arg 3
  • 第四个参数为3.这指定参考应涵盖3行。这称为高度参数。Excel Offset函数 -  arg 4
  • 第五个参数为3.这指定参考应涵盖3列。这称为宽度参数。Excel Offset函数 -  arg 5

既然您有一系列单元格(C2:E4),您可以在其他功能中使用它(例如总和,计数,最大,平均)。

希望您对使用Excel Offset功能有很好的基本理解。现在让我们看看使用偏移函数的一些实际示例。

Excel Offset函数 - 示例

以下是使用Excel Offset函数的两个示例。

示例1 - 找到列中的最后一个填充的单元格

假设您在列中有一些数据,如下所示:

要查找列中的最后一个单元格,请使用以下公式:

Excel Offset功能 - 示例1

=偏移(A1,COUNT(A:A)-1,0)

该公式假定与所示的值分开,这些单元格在其中没有空白单元。它通过计数填充的小区总数,并相应地偏离单元A1。

例如,它是这种情况,存在8个值,因此计数(a:a)返回8.我们将小区a1偏移7×7以获得最后值。

示例2 - 创建动态下拉

您可以扩展示例1中的概念显示以创建动态名为范围。如果您使用公式中的命名范围或创建,则这些可能很有用118bet网址多少 并且您可能会从制作命名范围的引用中添加/删除数据。

这是一个例子:

Excel Offset函数 - 动态命名范围示例2

请注意,当年份添加或删除时,下降会自动调整。

这发生在于使用用于创建下拉的公式是动态的,并识别任何添加或删除并相应调整范围。

以下是如何做到这一点:

  • 选择要插入下拉的单元格。
  • 转到数据 - >数据工具 - >数据验证。
  • 在“数据验证”对话框中,“设置”选项卡在“设置”选项卡中,从下拉下来选择列表。
  • 在源中,输入以下公式:=偏移(A1,0,0,Count(A:A),1)
  • 单击确定。

让我们看看这个公式如何工作:

  • 偏移函数的前三个参数是A1,0和0。这基本上意味着它将返回相同的参考单元(这是A1)。
  • 第四个参数用于高度,此处计数函数返回列表中的项目总数。它假设列表中没有空白。
  • 第五个参数为1,表示列宽应该是一个。

补充笔记:

  • 偏移是A.挥发性功能(谨慎使用)。
    • 只要Excel工作簿打开或在工作表中触发计算时,它会重新计算。这可以添加到处理时间并减慢工作簿。
  • 如果省略高度或宽度值,则将其视为参考。
  • 如果科尔斯负数是负数,然后偏移方向颠倒。

Excel偏移功能替代方案

由于Excel偏移功能的一些局限性,您希望考虑替代方案:

  • 索引功能:索引函数也可用于返回单元格引用。点击这里要查看如何使用索引函数创建动态命名范围的示例。
  • Excel表:如果在Excel表中使用结构化引用,则无需担心添加的新数据以及调整公式的需要。

Excel偏移功能 - 视频教程

相关Excel功188亚洲金博宝app能:

您也可能喜欢以下Excel教程:

Excel电子书订阅

免费Excel书籍

获得51个Excel提示电子书来飙升您的生产力,并更快地完成工作

关于“Excel Offset函数的思考公式&免费视频“

  1. 如何使用Excel在一行中检测两个不同的数字,出现在另一行中?

评论被关闭。