巧用OFFSET函数，始终统计分析近三个月的销量

Excel教程 2021-12-24 21:01:15

某单位，要时时统计分析各产品最近三个月的销量情况。

但：数据表中，会不断插入行或者列，来记录新的一个月销售数量。

如何保证统计结果永远是最近三个月的呢？

OFFSET函数，可以帮忙。

公式实现

月份列分布：

如下工作表：

在E2中输入公式：

=SUM(OFFSET(E2,0,-3,1,3))，可实现即使有新列插入，总能计算结果列之前三列的加和。

如下动图：

其中：

OFFSET(E2,0,-3,1,3)的含义是：

由E2单元格偏移0行，向左偏移3列，到B2单元格，由B2单元格开始的1行3列的区域，即B2:D2区域。

如下图：

在E列前插入列，基准点E2都会自动变为当前统计结果所在列。

因此，这个公式永远统计前三列，即最近三个月的和。

统计最近三个月销量的其他情况，可把SUM函数改为相应的函数。

如：

近三个月平均值：

=AVERAGE(OFFSET(E2,0,-3,1,3))

近三个月最大值：

=MAX(OFFSET(E2,0,-3,1,3))

月份行分布：

如下工作表：

在B5中输入公式：

=SUM(OFFSET(B5,-1,0,-3,1))，可实现即使有新行插入，总能计算结果行之前三行的加和。

如下动图：

其中：

OFFSET(B5,-1,0,-3,1)的含义是：

由B5单元格向上偏移1行，偏移0列，到B4单元格，由B4单元格开始的向上2行1列的区域，即B2:B4区域。

OFFSET函数说明

语法：OFFSET(reference,rows,cols,height,width)

引用必需。要以其为偏移量的底数的引用。引用必须是对单元格或相邻的单元格区域的引用；否则OFFSET 返回错误值 #VALUE!。

Rows 必需。需要左上角单元格引用的向上或向下行数。使用 5 作为 rows 参数，可指定引用中的左上角单元格为引用下方的 5 行。 Rows 可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）。

Cols 必需。需要结果的左上角单元格引用的从左到右的列数。使用 5 作为 cols 参数，可指定引用中的左上角单元格为引用右方的 5 列。 Cols 可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）。

高度可选。需要返回的引用的行高。 Height 必须为正数。

宽度可选。需要返回的引用的列宽。 Width 必须为正数

图示说明：

蓝色区域由A1偏移形成，那么函数就是：OFFSET(A1,5,2,12,3)