Excel 中 OFFSET 函数的学习与使用指南

一.、OFFSET 函数简介

`OFFSET` 函数是 Excel 中一个强大的引用函数，用于从指定的起始点返回一个单元格或单元格区域的引用。它可以根据给定的行数和列数偏移量，动态地引用不同的单元格或区域。

二、 函数语法

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

-reference: 起始点（参考单元格或区域）。

-rows: 从起始点向上或向下偏移的行数。正数表示向下偏移，负数表示向上偏移。

-cols: 从起始点向左或向右偏移的列数。正数表示向右偏移，负数表示向左偏移。

- [height]: （可选）返回区域的行数。如果省略，默认与起始区域的行数相同。

- [width]: （可选）返回区域的列数。如果省略，默认与起始区域的列数相同。

三、基本用法示例

1．返回单个单元格

假设 `A1` 单元格的值为 `10`，我们想从 `A1` 向下偏移 2 行，向右偏移 1 列，返回 `B3` 单元格的值。

=OFFSET(A1, 2, 1)

结果：返回 `B3` 单元格的值。

2．返回一个区域

假设 `A1` 单元格的值为 `10`，我们想从 `A1` 开始，返回一个 2 行 3 列的区域。

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

结果：返回 `A1:C2` 区域的值。

四、动态引用与数据分析

1. 动态求和

假设有一列数据从 `A1` 到 `A10`，我们想动态求和最后 3 个单元格的值。

=SUM(OFFSET(A1, COUNTA(A:A)-3, 0, 3))

- `COUNTA(A:A)` 统计 `A` 列非空单元格的数量。

- `COUNTA(A:A)-3` 计算从 `A1` 开始向下偏移的行数。

- `OFFSET` 返回最后 3 个单元格的区域，`SUM` 对其进行求和。

2. 动态图表数据源

假设有一组随时间变化的数据，我们希望图表的数据源能够自动更新为最近 5 个数据点。

=OFFSET(A1, COUNTA(A:A)-5, 0, 5)

- `COUNTA(A:A)-5` 计算从 `A1` 开始向下偏移的行数。

- `OFFSET` 返回最后 5 个单元格的区域，作为图表的数据源。

五、运用 OFFSET 函数制作动态图表

1. 例题背景

假设有一组销售数据，记录每天的销售额。数据从 `A1:B11`，我们希望创建一个动态折线图，当新增数据时，图表会自动更新。

数据示例：

2. 实现步骤

2.1步骤 1（定义动态数据范围）

（1）点击公式>定义名称

（2）输入名称（如 `折线图数据 `），在引用位置中输入以下公式：

   =OFFSET(Sheet3!$A$1, 0, 0, COUNTA(Sheet3!$A:$A), 2)

   - `Sheet3!$A$1`：起始点为 `A1`。

   - `COUNTA(Sheet3!$A:$A)`：统计 `A` 列的非空单元格数量，确定数据范围的行数。

   - `2`：表示数据范围有 2 列（`A` 列和 `B` 列）。

2.2步骤 2（创建图表）

（1）选择任意空白单元格，点击插入>选中折线图。

（2）右键点击图表，选择“选择数据”。

（3）在“图表数据区域”中输入折线图数据

（4）点击“确定”，图表将自动绑定到动态数据范围。

2.3. 测试动态效果

- 在 `A` 列和 `B` 列中添加新数据（如 `2023-10-11` 和 `600`），图表会自动更新。

六、运用 OFFSET 函数制作动态数据透视表

1. 例题背景

假设有一组销售数据，记录每天的销售额和销售区域。数据从 `A1:C11`，我们希望创建一个动态数据透视表，当新增数据时，数据透视表会自动更新。

数据示例：

2. 实现步骤

2.1步骤 1（定义动态数据范围）

（1）点击公式>定义名称

（2）输入名称（如 `透视表数据 `），在引用位置中输入以下公式：

   =OFFSET(Sheet3!$A$1, 0, 0, COUNTA(Sheet3!$A:$A), COUNTA(Sheet1!$1:$1))

   - `Sheet1!$A$1`：起始点为 `A1`。

   - `COUNTA(Sheet1!$A:$A)`：统计 `A` 列的非空单元格数量，确定数据范围的行数。

   - `COUNTA(Sheet1!$1:$1)`：统计第 1 行的非空单元格数量，确定数据范围的列数。

2.2步骤 2（创建数据透视表）

（1）点击“插入” -> “数据透视表”。

（2）在“表/区域”中输入：

   =透视表数据

（3）选择放置数据透视表的位置，点击“确定”。

（4）在数据透视表字段中，将“日期”拖到“行”，“销售额”拖到“值”，“区域”拖到“列”。

2.3步骤 3（刷新数据透视表）

- 当数据增加或减少时，右键点击数据透视表，选择“刷新”，数据透视表将自动更新。

`OFFSET` 函数在 Excel 中非常灵活，适用于动态引用、数据分析、图表数据源等场景。通过合理使用 `OFFSET`，可以大大提高数据处理的效率和灵活性。

希望这份指南能帮助你更好地理解和使用 Excel 中的 `OFFSET` 函数！