如何在 Excel 中使用 OFFSET 函数

使用负值 B 会产生向上的偏移，并产生负值 C 将创建一个左偏移。

OFFSET 的基本示例

为了举例说明 OFFSET 的工作原理，我将使用一个非常基本的、非实际的示例。打字

=OFFSET(A1,2,3,2,3)

进入单元格 A6 会使用单元格 A1 作为起点 (A)，向下移动 2 行 (B)，跨过 3 列 (C) ），并返回一系列 2 个单元格高 (D) 和 3 个单元格宽 (E) 的值：

如果我没有在公式中输入值 D 和 E ，结果将默认单独引用单元格 D3（鹿）。这是因为如果您不指定高度和宽度，Excel 会假定结果与参考的大小相同。

基于这个原则，您还可以在值A内定义结果的大小。例如，输入

=OFFSET(A1:A3,2,3)

将返回三个单元格高的结果，因为这是起点 A 的大小。

在现实世界中使用 OFFSET

现在，我将向您展示如何在实际示例中使用 OFFSET。

现实世界示例 1

在这里，我有十名员工，以及他们在三周内售出的单位数量。我将使用 OFFSET 创建三个数据报告。

在第一个数据报告（紫色）中，当我输入员工 ID 和周数时，我希望 Excel 告诉我该员工已售出的商品数量。为此，我将在单元格 H4 中输入

=OFFSET(B1,H2,H3)

其中 B1 是起点，H2 告诉 Excel 需要从起点向下偏移多少行（换句话说，员工 ID）， H3 告诉 Excel 从起始点偏移多少列（换句话说，周数）。请注意我在这里如何使用单元格引用，因为这意味着 OFFSET 函数从表中提取的数据可以是动态的。

我从单元格 B1 作为第一个输入开始，因为我希望第一周成为该起点右侧的第一个单元格。因此，每当您使用 OFFSET 函数时，请仔细考虑您的起点应该在哪里。

在单元格 H4 中输入上述公式后，我将在 ID 框（单元格 H2）中输入 6，在周框（单元格 H3）中输入 2 。这将告诉我 Ollie 在第 2 周售出了多少件商品，因为 OFFSET 函数从起始点单元格 B1 向下六行、对面两列的单元格中提取数据。

现在，我可以随时更改单元格 H2 和 H3 中的值，以从表中提取任何单独的数字。

接下来，我想在蓝色表中创建员工报告。当我输入员工 ID 时，Excel 会自动告诉我该员工的姓名（使用 VLOOKUP 函数）、我们拥有的数据的总周数（使用 COUNTIF 函数）以及该员工销售的总单位数和平均单位数（使用总和和平均值（带偏移量）。

首先，我将在单元格 H7 中输入员工 ID 以开始。我们来查找员工 ID 3，即 Jason。然后，我将使用 VLOOKUP 函数返回单元格 H8 中 Jason 的姓名：

=VLOOKUP(H7,A2:B11,2)

现在，我将使用带有通配符的 COUNTIF 函数来告诉 Excel 计算第 1 行中包含单词“Week”后跟数字的单元格数量：

=COUNTIF(1:1,"Week*")

我现在可以使用此信息计算出 Jason 的总销售量和平均销售量。首先，在单元格 H10 中，我将输入

=SUM(OFFSET(B1,H7,1,1,H9))

其中 B1 是我的起点，H7 是向下偏移的行数（员工 ID），第一个 1 是列数向右偏移，第二个1是结果的高度，H9是结果的宽度（总周数）。但是，由于 OFFSET 嵌入在 SUM 函数中，因此结果将仅为单个单元格大小。

我可以将相同的公式复制并粘贴到单元格 H11 中，但将 SUM 更改为 AVERAGE，因为我使用相同的引用。

=AVERAGE(OFFSET(B1,H7,1,1,H9))

现在，我可以更改单元格 H7 中的值，为数据表中的任何其他员工生成报告。此外，如果我要在数据中再添加一周，单元格 H9 将自动更改为 4，并且单元格 H10 和 H11 中的后续计算也会更新。

最后，我想在橙色表中创建每周报告。这将要求我输入周数，然后 Excel 将生成该周销售的总单位数和平均单位数。首先，我将在单元格 H14 中键入 1，这样 Excel 将生成第 1 周的数据。然后，在单元格 H15 中，我将键入

=SUM(OFFSET(B1,1,H14,10,1))

其中B1是起点，第一个1是向下偏移的行数，H14是向左偏移的列数右（周数），10 是结果的高度，第二个 1 是结果的宽度。但是，由于 OFFSET 嵌入在 SUM 函数中，因此结果将仅为单个单元格大小。

最后，我可以将该公式复制并粘贴到单元格 H15 中，并将 SUM 更改为 AVERAGE。

=AVERAGE(OFFSET(B1,1,H14,10,1))

真实示例 2

在此示例中，我将向您展示如何将 OFFSET 与格式化表格一起使用，以及用于在多个单元格中返回结果时的外观。

在这里，我将今年迄今为止的当前销售额和利润保存在一个格式化表格中，我希望右侧的报告表单独显示过去三个月的利润总额，以及同期的平均利润和总利润。

首先，我需要Excel计算出到目前为止已经过去了多少个月，这样当我使用OFFSET时，我可以使用这个数字告诉Excel我需要偏移距离起点多远。为此，我将在单元格 F1 中输入

=MONTH(TODAY())

这将显示当前月份数字。在本例中，时间是 11 月，即 11 月。

我们现在可以使用这个月的数字来显示过去三个月的利润。在单元格 F3 中，我将输入

=OFFSET(

然后单击单元格 A1，我的起点。这将自动将格式化表的月份列标题添加到公式中：

=OFFSET(Table1[[#Headers],[Month]]

现在，我将添加一个逗号，并继续我的 OFFSET 公式。下一阶段是告诉 Excel 向下偏移多少行。这将是当前月份（单元格 F1）减去三：

=OFFSET(Table1[[#Headers],[Month]],SUM(F1-3),

最后，我将告诉 Excel 我们需要向右偏移两列，结果将是三行高、一列宽。

=OFFSET(Table1[[#Headers],[Month]],SUM(F1-3),2,3,1)

请注意单元格 F4 和 F5 如何包含数据，即使公式仅在 F3 中。这称为溢出数组，因为数据已溢出活动单元格。

现在，当月份更改为 12（12 月）时，Excel 会自动将此结果下移一个单元格，因为单元格 F1 中的值将增加 1。

然后我可以通过输入来完成我的表格

=AVERAGE(F3#)

进入单元格 F7，并且

=SUM(F3#)

进入单元格 F8。

八角 (#) 符号是 Excel 计算溢出数组的方式。

要记住的事情

使用 OFFSET 时需要记住两件重要的事情：

OFFSET 是一个易失性函数，这意味着它总是寻求更新。如果在特别大的电子表格或低内存计算机上使用，这可能会导致性能问题。
上面的所有示例都用于说明使用 OFFSET 函数的不同方法。您可能还有其他更有效的方法来创建相同的结果，例如使用 INDIRECT 函数。但是，一旦您了解了这些功能的工作原理，您就可以决定最适合您的情况的使用！