Excel OFFSET 函数：动态区域与滚动合计

=OFFSET(A1,3,2) 返回从 A1 向下 3 行、向右 2 列的单元格，也就是 C4。再给它高度和宽度，它就会返回整个区域，这也是 OFFSET 最主要的用途：对一个会移动或增长的区域求合计和平均值。

公式重置

G2fx

=OFFSET(A1,E2,F2)

	A	B	C	D	E	F	G
1	Product	Category	Price		Rows	Cols	Result
2	Apple	Fruit	$1.20		3	2	$0.80
3	Pear	Fruit	$1.50
4	Carrot	Vegetable	$0.80
5	Bread	Bakery	$2.40
6	Milk	Dairy	$1.10

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

从 A1 向下 3 行、向右 2 列落在 C4，即 Carrot 的价格 $0.80。把 Cols 设为 0 得到名称 Carrot，把 Rows 设为 5 则到 Milk 那一行。行和列可以是负数，表示向上或往回移动；移出工作表顶部或边缘会得到 #REF!。

OFFSET 语法

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

• reference：起始单元格（或区域）。
• rows、cols：移动多远。0 表示不动。
• height、width：要返回的区域大小，从移动到的单元格算起。省略时与 reference 大小相同。

单独放在单元格中时，返回多个单元格的 OFFSET 在 Excel 365 中会溢出；旧版本通常显示 #VALUE!。放在 SUM、AVERAGE、COUNT 或 MAX 里面时，它就作为一个区域使用。

对最后 N 行求和

这是 OFFSET 的经典用法：无论新增了多少行，合计总是覆盖最近的几行。COUNT 算出有多少个值，OFFSET 向下移动到最后 N 个值中的第一个，高度取 N 行。

公式重置

F2fx

=SUM(OFFSET(B1,COUNT(B2:B13)-E2+1,0,E2,1))

	A	B	C	D	E	F
1	Month	Sales			Last N	Total
2	Jan	4,200			3	14,900
3	Feb	3,900
4	Mar	4,800
5	Apr	5,100
6	May	4,600
7	Jun	5,300
8	Jul	5,000

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

共有 7 个值，所以 OFFSET 从 B1 向下 7-3+1，即 5 行，从 B6 开始取 3 行：5 月到 7 月，合计 14,900。在 B9（8 月）输入 4900，合计就会变成 6 月、7 月和 8 月，因为 COUNT 现在找到 8 个值。区域 B2:B13 为全年剩余月份留了位置。这一列中间不能有空单元格，否则 COUNT 会少算，窗口就会落在错误的位置。

滚动平均

向下填充一列时，行偏移为负的 OFFSET 会给每一行一个覆盖上方各行的窗口：这里是当月与前两个月的平均值。

公式重置

C4fx

=AVERAGE(OFFSET(B4,-2,0,3,1))

	A	B	C
1	Month	Sales	3-month average
2	Jan	4,200
3	Feb	3,900
4	Mar	4,800	4,300
5	Apr	5,100	4,600
6	May	4,600	4,833
7	Jun	5,300	5,000
8	Jul	5,000	4,967

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

C4 对 B2:B4（1 月到 3 月）求平均，得到 4,300。下面每一行都把窗口下移一行。把 3 改成 6、把 -2 改成 -5 就是六个月平均（这时公式从第 7 行开始）。这个例子其实根本不需要 OFFSET：从 C4 向下填充 =AVERAGE(B2:B4) 效果相同，因为相对引用本来就会移动。只有当窗口大小来自某个单元格时，OFFSET 才真正派上用场。

为什么 INDEX 往往是更好的选择

OFFSET 是易失性函数：工作簿中任何地方被编辑后，Excel 都会重新计算每个 OFFSET，因为它无法预先知道 OFFSET 会指向哪些单元格。一个包含几千个 OFFSET 的表格会变慢。INDEX 也返回引用，写成 start:INDEX(...) 的区域能以同样方式增长，却不是易失性的：

=SUM(OFFSET(B2, 0, 0, E2, 1))        first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2))           same rows, not volatile

两者都读取该列的前 E2 行。OFFSET 也更难审核：“追踪引用单元格”以及编辑公式时 Excel 绘制的彩色框线，显示的是起始单元格和参数，而不是 OFFSET 最终返回的区域。快速建模或图表区域可以用 OFFSET；在大型工作簿中优先用 INDEX。关于返回区域，INDEX 有更多内容；INDIRECT 是另一个易失性引用函数。

练习：前 N 个月的合计

公式重置

F2fx

	A	B	C	D	E	F
1	Month	Sales			First N	Total
2	Jan	4,200			4
3	Feb	3,900
4	Mar	4,800
5	Apr	5,100
6	May	4,600
7	Jun	5,300
8	Jul	5,000

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 在 F2 中，在 SUM 里使用 OFFSET 计算前 N 个月的合计，N 在 E2 中。

检查显示答案