首页 > Excel专区 > Excel教程 >

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

Excel教程 2021-12-27 21:34:35

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

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

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

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)


标签: excel函数excel函数公式excel表格制作excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.