EP05. “OFFSET Function OFFSET 函数”

• OFFSET 函数用来从一个基准单元格出发，往指定方向偏移指定的行数和列数
• 返回偏移后的单元格或范围
• 前置知识不需要
• 学完能理解为什么有些动态图表、动态命名范围底层会用到 OFFSET

重点内容

函数速查

返回单个单元格

1. =OFFSET(A2,3,2)：以 A2 为基准，向下偏移 3 行、向右偏移 2 列，height 和 width 都省略（等同 1），返回偏移后的那个单元格
2. =OFFSET(A2,12,0)：以 A2 为基准，向下偏移 12 行、不偏移列。省略 height 和 width 时，新引用会跟起始引用一样是单个单元格

返回一个范围

1. =SUM(OFFSET(A2,8,1,1,2))：以 A2 为基准，向下偏移 8 行、向右偏移 1 列，返回一个 1 行 2 列的范围，再用 SUM 求和
2. =SUM(OFFSET(B2:C2,4,0))：以 B2:C2 这个范围为基准整体偏移，向下偏移 4 行，同样用 SUM 求和
3. 不搭配 SUM 直接取范围：选中目标范围（如 E8:F8）输入公式后要按 Ctrl + Shift + Enter 才能生成数组公式
4. Excel 365 / 2021：不用按组合键，直接 Enter 就能靠 spilling（溢出）特性自动铺开到相邻单元格

负数偏移

偏移参数可以用负数，代表往上或往左偏移：

=OFFSET(C14,-12,-1)

以 C14 为基准，向上偏移 12 行、向左偏移 1 列。

学完你会

• ✅ 能用 OFFSET 从基准单元格偏移出单个单元格或一个范围
• ✅ 能用负数偏移参数往上或往左移动
• ✅ 知道 OFFSET 是易失性函数，大量使用会拖慢大型工作表的运算速度

常见错误

• 旧版本 Excel 里用 OFFSET 返回多单元格范围，忘记按 Ctrl + Shift + Enter 确认成数组公式，导致只显示第一个值
• rows/cols 参数正负号搞反：正数是向下/向右，负数是向上/向左
• OFFSET 是易失性函数（volatile function），工作表任何变动都会触发它重新计算，大量使用会拖慢大型工作表的运算速度

Sources

Blog / Website：

1. Offset