Excel利用Offset函数制作动态图表

Excel利用Offset函数制作动态图表

动态图表有着非常广泛的应用，在Excel中可以利用Offset函数改变图表源数据，实现数据动图表也动的目的。

语法：

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

说明：

以指定的引用为参照系，通过给定偏移量返回新的引用。

参数：

第一个参数reference是基点。

第二个参数rows是要偏移几行，正数向下，负数向上。

第三个参数cols是要偏移几列，正数向右，负数向左。

第四个参数height是新引用几行。

第五个参数width是新引用几列。

如果不使用第四个和第五个参数，新引用的区域就是和基点一样的大小。

如：OFFSET(B1,4,3,4,3)

就是以B1为基点，向下4行，向右3列，得到E5,再以E5为起点向下4行，向右3列，得到新引用区域E5:G8。

比如要做一个图展示最近三个月的销售额：

因为月份和销售额的源数据是可以增加的，以当前是6月来讲，最近三月是只4-6月，当生成7月数据时，最近三月指的是5-7月。

所以要用Offset函数来抓取动态的源数据，那么就要先定义这两个字段的函数名称。

菜单栏：公式--定义公式名称，定义一个公式名称为“月份”，

引用位置为：=OFFSET($A$1,COUNTA($A$2:$A$13),0,IF(COUNTA($A$2:$A$13)<3,COUNTA($A$2:$A$13)*-1,-3))

这个公式的意思是：以A1为基点，用COUNTA函数对A2:A13计数，有多少个月就向下偏移几行，不偏移列，然后再往上引用行数，这个行数用IF来断是否小于3，如果小于3，那么往上引用的行数就是COUNTA的计数，如果大于等3，则引用3行。

同理，定义公式“销售额”

引用位置为：=OFFSET($B$1,COUNTA($B$2:$B$13),0,IF(COUNTA($B$2:$B$13)<3,COUNTA($B$2:$B$13)*-1,-3))

以二维柱形图为例（其他类型也可以）：

选择数据区域A1:B7插入一个二维柱形图。

右键单击图表区域，选择数据，用来更改数据源。

编辑图例项销售额，系列名称引用B1，系列值改为“OFFSET动态图表.xlsx!销售额”。

编辑水平轴标签月份，将轴标签区域值改为“OFFSET动态图表.xlsx!月份”

注：“OFFSET动态图表.xlsx!”就是当前EXCEL文件名。

美化一下，完成，如下图：

如果月份数据增加，图表也会随着数据变动。

如增加7、8月数据，图表变动如下：

如果数据少于3个月，那么有多少个月，图表就只有多少个柱子：

其他的图表类型请大家自已尝试去做，谢谢。