Excel 中的 offset 函数

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

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

本章将简要介绍 Offset 函数的功能、语法和示例。你可以在 Excel 的**公式**选项卡中**查找与引用**列表下找到此查找函数（用户界面）。请遵循我们的完整教程。

Offset() 的使用

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

国际象棋中，车只能直行（左、右、上、下），不能斜行。同样，使用 Excel 的其他函数，用户可以直线移动到单元格。Offset() 函数允许用户斜向移动。

语法

以下是 Offset() 函数的语法。它接受五个参数。

在 reference 参数中提供一个起始点，以及向左、右、上、下移动的行数和列数。而 height 和 width 是可选的。

参数列表

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

必需参数

reference (必需) - 它是起始点，用作范围或调用引用。例如，A2、B5 等。它是基准单元格。

rows (必需) - 从起始引用值**上方或下方**的行数。它可以是正数或负数。

column (必需) - 从起始引用值**左侧或右侧**的列数。此参数可以接受正数或负数。

可选参数

[height] (可选) - 传递 height 参数以获取要返回的行数。此参数只接受数字值，并且必须是正数。

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

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

[width] (可选) - 传递 width 参数以获取要返回的列数之和。与 **height** 参数一样，它也只接受数字值，并且必须是正数。

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

注意：你可以将 OFFSET() 函数嵌套在另一个 Excel 函数中，以处理使用 height 和 width 参数返回的多个值。

现在，我们将通过实际操作和示例来阐明理论，你会更好地理解它。

返回值

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

OFFSET 警告

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

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

OFFSET 示例

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

Offset() 函数的需求

offset() 函数在动态计算中非常有用。让我们通过一个示例来理解为什么需要此函数。我们有一个问题，通过 offset() 来解决。

问题

我们有一组 7 天的数据，其中我们想计算过去 5 天的平均值。这可以使用 Excel 的 Average 公式 (=AVERAGE(B3:B7)) 轻松完成。

但问题是 - 我们在末尾又添加了一行 (B8)，并再次计算了过去 5 天的平均值。在这种情况下，我们必须更改目标列的范围，平均公式将变为 (=AVERAGE(B4:B8))。结果值现在也将更改。

解决方案

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

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

OFFSET() 函数如何使用？

通过不同的示例，你现在将看到如何使用 OFFSET() 函数。对于 OFFSET() 函数的示例，我们有一组一个月的数据。

此工作表包含一个月中每天的每周报告。我们现在将在此工作表上应用 OFFSET() 公式。

不带 height 和 width 参数

示例 1

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

=OFFSET(A2,4,3)

步骤 2： 在公式栏中写入 OFFSET() 公式后，单击 **Enter** 键以获取结果值。

可以看到，执行 OFFSET() 函数后返回了 3100。在此示例中，我们只使用了前三个必需参数。

示例 2

再看一个不带 height 和 width 参数的 offset() 函数示例。我们将使用相同的数据表作为此示例，只更改参数。

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

Offset 公式

=OFFSET(A1,2,3)

步骤 2： 在公式栏中写入 OFFSET() 公式后，单击 Enter 键以获取结果值。

可以看到，执行 OFFSET() 函数后，offset() 函数返回了 3000。

使用 height 和 width 参数

在此示例中，我们将在 offset() 函数中使用 height 和 width（可选）参数。看看它的结果有何不同。

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

示例 1

在此示例中，我们将把 OFFSET() 函数嵌套在 SUM() 函数中，以获取 offset() 函数返回的值（引用单元格）的总和。在这里，我们将通过将 height 值输入为 2 来将其用于从多行中选择单元格。

使用 offset 计算总和的步骤

请看下面的几个简单步骤

步骤 1： 与上面的示例一样，选择一个单元格以保存 OFFSET() 函数返回的结果，并在公式栏中写入带有 height 和 width 参数的以下 OFFSET() 公式。

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

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

步骤 4： 在公式栏中写入 OFFSET() 公式后，单击 Enter 键以获取结果值。

可以看到，它返回了 offset() 返回引用的单元格（D6、D7）的总和。

总和已返回，即 8100。你现在已经明白了 offset 函数如何与可选参数一起工作。

示例 2

再看一个带有可选参数（height 和 width）的 offset() 函数示例。在此示例中，我们将把 offset() 函数嵌套在 AVERAGE() 中，以获取 offset() 函数返回的值（引用单元格）的平均值。

这次，我们将通过将 width 值输入为 2 来将其用于从多列中选择单元格。

使用 offset 计算总和的步骤

请看下面的几个简单步骤

步骤 1： 选择一个单元格以保存 OFFSET() 函数与 SUM() 返回的结果，并在公式栏中写入带有 height 和 width 参数的以下 OFFSET() 公式。

步骤 2： offset() 将返回 D6 和 E6 单元格的引用，因为 width 为 2。因此，将返回两个单元格的引用，其总和将被计算并返回。

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

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

此 Excel 表中有 5 列，每列包含 6 天的数据，共包含 5 周的数据。举例说明如何使用 offset() 函数对某一列的所有单元格求和。

步骤 1： 将以下 offset() 公式嵌套在 SUM() 中，以对某一列的所有单元格求和。

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

步骤 2： 按 Enter 键，C 列（offset() 返回的引用）中六个单元格的总和将返回到选定的单元格。请看下面的输出

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

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

示例 3：求某一行的单元格之和

在此 Excel 表中，你可以看到有 7 行包含 6 天的不同数据，其中每列包含一周的数据。通过一个简单的示例，我们将说明如何使用 offset() 函数对某一行中的所有单元格（包含某些数据）求和。

步骤 1： 将以下 offset() 公式嵌套在 SUM() 中，以对某一行中的所有单元格求和。

它将从起始点开始，移到第五行和第一列，然后计算第五行中五个单元格的总和。

步骤 2： 按 Enter 键，第五行中五个单元格的总和（offset() 返回的引用）将返回到选定的单元格。请看下面的输出

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