Excel 使用offset函数

offset()是一个Excel函数，用于获取范围的引用并返回给用户。这是一个比较棘手的函数，不太容易理解和使用。一旦你学会了它，你就可以轻松地在Excel工作表上使用它。

注意：返回的值可以是单个单元格或多个相邻单元格的范围。

本章将向您介绍offset函数的功能、语法和示例。您可以在Excel的 查找与引用 列表中的 公式 选项卡下找到这个LOOKUP函数（用户界面）。请按照我们的完整教程操作。

使用offset()

使用offset()函数，您可以直接移动到任何方向的单元格（左、右、上、下，以及对角线）。例如，

像车一样，在国际象棋上只能直线移动，不能对角线移动。同样，使用Excel的其他函数，用户可以直接进入一个单元格。offset()函数使用户可以进行对角线移动。

语法

这是offset()函数的语法。它接受五个参数。

=OFFSET(reference, rows, cols, [height], [width])

在引用参数中提供一个起始点和行列数，向左、向右、向上、向下移动。高度和宽度是可选的。

参数列表

OFFSET()函数有五个参数，前三个是必需的，其余的是可选参数 –

必需参数

reference (必需) – 它是起始点，可以作为范围或调用参考。例如，A2，B5等。它是基本单元格。

rows (必需) – 从起始参考值按上方或下方移动的行数。它可以是正数或负数。

column (必需) – 从起始参考值向左或向右移动的列数。此参数可以接受正数或负数。

可选参数

[height] (可选) – 高度参数用于获取要返回的行数。此参数只接受数字值，并且必须是正数。

通常，此参数用于获取要返回的多个值。

例如， 如果高度为1，则只返回一行数据。但是如果高度参数值为2，则将返回目标单元格及其下方单元格数据（多个值）。

[width] (可选) – 宽度参数用于获取要返回的列数的总和。与高度参数一样，它也只接受数字值，并且必须是正数。

例如， 如果值为2，则返回所选单元格（由前三个必需参数选择）的总和以及下一列中的单元格。

注意：您可以在另一个Excel函数中使用OFFSET()函数来处理其使用高度和宽度参数返回的多个值。

现在，我们将通过实际示例来说明这个理论，通过例子的帮助，您将更好地理解它。

返回值

OFFSET()函数返回对范围的引用。此引用可以是单个单元格或多个单元格。

OFFSET警告

OFFSET()函数是一个 易变函数 。如果在Excel工作簿中应用于太多单元格，它会减慢该工作簿的处理速度。因此，您可以使用其他Excel函数代替它，以保持处理速度更快。

Excel提供了一个非易变函数返回引用，如INDEX()。它不会减慢工作簿的处理速度。

OFFSET示例

在直接在Excel工作表上学习OFFSET函数之前，请尝试通过语法示例来理解它。请参阅以下一些OFFSET语法示例：

=OFFSET(A2,4,3)说明

• A2 是基准单元格，即起始点。
• 4,3 指的是基准单元格的第四行和第三列。

结果单元格的值将为D6。

=SUM(OFFSET(A2,2,4, 1,1)) 说明

• A2 是基准单元格，即起始点。
• 2,4 指的是从起始点开始的第二行和第四列。
• 1,1 只指一行和一列的数据。

结果单元格的值将只为E4。因为选择的单元格只有E4，所以不会进行求和。

=AVERAGE(OFFSET(A2,2,4, 2,1))说明

与上面的例子类似，

• A2 是起始点（基准单元格）。
• 2,4 指的是从起始点开始的第二行和第四列。
• 2,1 指的是两行和一列的数据。

首先，使用前三个参数选定单元格E4。然后，使用可选参数，在下一行选定另一个单元格（E5）。 最后，计算并返回E4和E5的平均值。

Offset() 函数的需求

offset() 函数在动态计算中非常有帮助。让我们通过一个例子了解为什么需要这个函数。我们有一个问题，可以通过 offset() 来解决。

问题

我们有一组为期7天的数据，我们想要计算最近5天的平均值。可以使用 Excel 的平均值公式 (=AVERAGE(B3:B7)) 很容易地实现这个目标。

但问题是 – 我们在最后添加了一行 (B8)，并再次计算了最近5天的平均值。在这种情况下，我们必须更改目标列的范围，并且平均值公式将变为 (=AVERAGE(B4:B8))。结果值也会发生变化。

解决方案

可以使用 offset() 函数来解决这个问题。它使用户能够获取单元格的引用，然后通过使用可选参数选择多个值来计算其 SUM、AVERAGE 或其他操作。 例如，

=AVERAGE(OFFSET(A2,5,3,2,1))

如何使用 OFFSET() 函数

通过不同的例子，您现在将看到如何使用 OFFSET() 函数。对于 OFFSET() 函数的例子，我们有以下一个月的数据集。

我们有一个包含每个月每天的周报的工作表。现在我们将在这个工作表上应用OFFSET()函数。

没有高度和宽度参数

示例1

步骤1： 选择一个单元格来保存OFFSET()函数返回的结果，并在公式栏中输入以下OFFSET()公式。

=OFFSET(A2,4,3)

步骤2： 在公式栏中编写OFFSET()公式后，点击 回车 键获取结果值。

查看执行OFFSET()函数后返回的3100。在这个例子中，我们只使用了前三个必填参数。

示例2

查看offset()函数的另一个例子，没有高度和宽度参数。我们将在这个例子中使用相同的数据表，只是参数有所改变。

步骤1： 选择一个单元格来保存OFFSET()函数返回的结果，并在公式栏中应用以下OFFSET()公式。

offset公式

=OFFSET(A1,2,3)

步骤2： 在公式栏中编写OFFSE()公式后，点击Enter键获取结果值。

注意到执行OFFSET()函数时，offset()函数返回了3000。

使用高度和宽度参数

在这个例子中，我们将使用offset()函数的高度和宽度（可选）参数。看看它的结果有多不同。

offset()函数通常返回多个值，可以通过使用另一个函数来保存这些值。这意味着您必须将offset()嵌套在另一个函数中，以保存它返回的值。

示例1

在这个例子中，我们将在SUM()函数中使用OFFSET()函数，以获取offset()函数返回的值（参考单元格）的总和。在这里，我们将使用它来通过将高度值设置为2来选择多行的单元格。

计算带offset的求和步骤

请看下面的几个简单步骤：

步骤1： 像上面的例子一样，选择一个单元格来保存OFFSET()函数返回的结果，并在公式栏中编写以下带有高度和宽度参数的OFFSET()公式。

= OFFSET(A2,4,3,2,1)       //return the reference of D6 and D7 cell

步骤2： 现在，按下Enter键。它将生成一个 #VALUE! 错误，因为返回了多个值。

步骤3： 为了处理这个错误，我们将把这个公式放在另一个函数中。我们将使用SUM()函数，它将返回由offset()函数选择的单元格的总和。

= SUM(OFFSET(A2,4,3,2,1))

步骤4： 在公式栏中写入OFFSET()公式后，点击回车键以获得结果值。

观察到它返回了offset()返回的单元格(D6, D7)的求和。

总和已返回，即8100。您现在知道了可选参数如何使用offset函数的工作方式。

示例2

请看另一个使用可选参数（高度和宽度）的offset（offset()）函数的示例。在这个示例中，我们将在AVERAGE()函数内使用offset（offset()）函数来获取offset（offset()）函数返回的值（参考单元格）的平均值。

这次，我们将使用宽度值2来选择多列的单元格。

计算带有offset（offset）的总和的步骤

请参阅以下简单步骤：

步骤1： 选择一个单元格，将由OFFSET()函数返回的SUM()函数的结果存放在其中，并在公式栏中写入以下带有高度和宽度参数的OFFSET()公式。

步骤 2: 而 offset() 将返回 D6 和 E6 单元格的引用，因为宽度为 2。因此，将返回两个单元格的引用，其和将进行计算并返回。

使用上述的offset()公式与SUM()函数，计算结果6100已返回。

示例3：求一列中所有单元格的和

在这个Excel表中，有5列包含5周的数据，每列包含6天的数据。以使用offset()函数来求一列中所有单元格的和作为示例。

步骤1： 在SUM()中使用以下offset()公式，以获得一列中所有单元格的总和。

=SUM(OFFSET(A2,0,2,6,1))      //offset will return the reference of C2 to C7 cell

它将从起始点开始，沿着同一行的第二列，然后计算该列的六个单元格的总和。

步骤2： 按下回车键，六个单元格的总和将从列C（由offset()返回的参考）返回到选定的单元格。请查看下面的输出：

在这里，通过offset()函数计算总和。

offset()函数可能是Excel中最令人困惑的函数。

示例4：对行中的单元格求和

在这个Excel表中，你可以看到有7行包含6个不同的日期数据，每一列包含一周的数据。通过一个简单的例子，我们将演示使用offset()函数对一行中所有包含数据的单元格求和。

步骤1： 将以下offset()公式放入SUM()函数中，以获取要对一行中所有要求和的单元格。

=SUM(OFFSET(A2,3,1,1,5))      //offset will return the reference of 6th row

它将从起始点开始运行，跳到第五行和第一列，然后计算相对于第五行的五个单元格的总和。

步骤2： 按下回车键，与第5行相关的五个单元格的总和（通过offset()返回的引用）将返回到所选单元格。请查看下面的输出：

在这里，通过offset()函数计算总和。