【Excel】OFFSET函数应用

1 什么是OFFSET函数

请参考Microsoft帮助文档：

OFFSET 函数 - Microsoft 支持https://support.microsoft.com/zh-cn/office/offset-%E5%87%BD%E6%95%B0-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

2 应用OFFSET函数

2.1 应用场景

现在有如下的数据：

数据每天新增一条，例如当前为7月10日，明天将新增7月11日的数据。

需求为：生成一个柱状图，该柱状图只显示最近3天的数据（例如今天需要显示7月8日到10日的数据），且能在每天数据新增以后自动更新柱状图（明天需要显示7月9日到11日的数据，后天需要显示7月10日到12日的数据）。

2.2 解决方案

总体思路：利用OFFSET函数配合COUNTA函数定位最后三条数据，然后将定位定义为名称，整合到柱状图里。

步骤：

选中最后三条数据，插入柱状图如下：

右击图表，单击选择数据，看到如下窗口：

可以看到图表数据区域是A9到E11。选中系列1，然后单击编辑，看到如下窗口：

看到系列值为B9到B11，即为表格中项目A这一列的后三条数据。分别检查系列2、系列3、系列4，其系列值分别为C9到C11、D9到D11、E9到E11。显然，在需求中，这些区域不断变化，只要有新的数据插入，后三行就会发生变化。因此后面需要结合COUNTA函数计算行数。

在图1-选择数据源中，点击水平(分类)轴标签下的编辑按钮，可以发现7月8日、7月9日、7月10日这三项的轴标签区域都是一样的：

但是这三项仍会随着新数据插入而变化，因此也要结合COUNTA函数计算行数然后用OFFSET函数提取。

在菜单栏找到公式-定义名称，新建如下名称：

起名itemA表示原表格中的项目A这一列，在引用位置栏写入如下函数：

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-3,1,3,1)

其中，Sheet1!$A:$1表明了参考位置为A1单元格，COUNTA(Sheet1!$A:$A)表示计算A列有多少行数据，COUNTA(Sheet1!$A:$A)-3表示向下偏移到倒数第3行，1表示向右偏移1行，最后的3,1表示选择高度为3宽度为1的单元格区域。

再次右击图表，单击选择数据，单击系列1，单击编辑，在对话框中把系列值改为下图中所示内容：

=Sheet1!itemA

itemA就代表了我们选择的系列1这一列的后三行。同理，新建itemB、itemC和itemD的名称，其引用位置分别为：

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-3,2,3,1)
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-3,3,3,1)
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-3,4,3,1)

右击图表，为系列2、系列3和系列4选择的系列值分别为：

=Sheet1!itemB
=Sheet1!itemC
=Sheet1!itemD

同时，我们还需要日期随时更新，所以为日期也定义一个名称：

 右击图表，为水平（分类）轴标签选择的系列值为：

=Sheet1!DATE

现在输入一行新数据试试：

图表已经可以自动更新了。