做聪明自动化表格，减少重复轻松自己，非它不可

哈喽，大家好！在上篇文章中，我们初步学习了OFFSET的基础和进阶用法，掌握了用OFFSET转置数据、逆向查找数据、二维表转一维表等操作，今天我们就来学习一些关于OFFSET的高阶用法：制作动态下拉菜单和动态图表。相信一定会对大家的工作和学习大有帮助。

【前言】

在上篇文章中，我们了解了OFFSET函数的运算原理和各个参数的作用，并且我们也通过一些OFFSET的案例，了解了它的用途。那么本篇我们继续来看看，OFFSET函数在实际工作中所能起到的强大效果吧。

我们使用Excel是为了快速地统计分析数据，快速地提取出我们需要的内容。现在假设以下两个场景：

场景一：

领导安排了工作，统计某季度的销售数据，我们马上行动，用函数快速的制作报表；

场景二：

领导安排了工作，因为每季度都需要统计销售数据，所以我们早就提前制作了模板，至于什么时候给出报表，就随我们的便了。切记，不要让“中层领导”知道你的工作效率很高。

两个场景，你会选择哪种处理方式呢？作者希望是第二个。

思路决定了我们制表的格局，这是一个简单的案例，当数据源被修改后，相对应的季度数据也会自动做出调整。在复杂的模版中并不是所有的位置都会使用OFFSET函数，但对于动态引用数据区域的需求，用OFFSET函数来处理是绝对不会错的。

绝技4：制作动态下拉菜单

在数据建模的过程中，我们经常会使用到下拉菜单（或者是组合框控件）。为了确保下拉内容的唯一性，我们会使用INDEX+SMALL+IF+ROW的“万金油”函数来去重提取数列中的数据。还记得我们在上篇讲到的OFFSET函数替代INDEX函数的例子吗？所以说，如果OFFSET函数可以代替INDEX函数使用的话，那么OFFSET函数同样也可以实现“万金油”的过程。下面我们就一起来看看复杂的“下拉菜单”的制作过程。

步骤一：使用OFFSET函数去重提取唯一值的 “万金油”公式

这个公式比较长，列出如下：

D2单元格函数：

=IFERROR(OFFSET($A$1,SMALL(IF(ROW($A$2:$A$27)-1=MATCH($A$2:$A$27,$A$2:$A$27,0),ROW($1:$20),9^9),ROW(D1)),),"")

万金油公式不是我们今天要讲的主题，就不展开讲了。重要就是为了让大家知道OFFSET函数也是可以达到这样去重的效果。

步骤二：在名称管理器中使用OFFSET函数，建立数据源

我们可以用Ctrl+F3组合键，打开名称管理器窗口，然后新建名称，名称设置为“区域”，引用位置为“D2:D15”，如下图所示：

然后选择G1单元格，按Alt+D+L组合键可以打开数据验证设置框，在允许中选择“序列”，在来源中输入“=区域”，如下图所示：

点击确定按钮，那么我们G1单元格的下拉菜单就建立好了。但是问题也来了，我们会发现有好多的空选项，这不是我们需要的。

有的同学会说，名称管理器中选择D2:D5就可以了。是的，但是如果我们A列的区域中出现了新的数据，那下拉菜单中的数据可就少了，所以此时我们依然使用OFFSET函数来处理这个问题。

更改名称管理器中，“区域”的引用位置：

=OFFSET(动态下拉菜单!$D$1,1,,COUNTA(动态下拉菜单!$D$2:$D$15)-COUNTBLANK(动态下拉菜单!$D$2:$D$15),1)

因为我们D列的唯一值，是用公式得到的，里面的“空单元格”不是名义上的“空”，而是由公式得到的空，所以不能直接通过COUNTIF(D2:D15,"<>")的方式得到有值的单元格个数。因此我们先使用了COUNTBLANK函数（空值单元格计数），统计空值单元格的数量，再用COUNTA函数统计非空单元格的数量，最后二者相减就得到了有值单元格的个数。将得到的结果作为OFFSET函数的第四参数（新区域的扩展行数）使用，就实现了动态引用有效数据的效果。如下图所示：

如果A列中增加了新的区域名称，那么G1的下拉菜单也会增加新的选项，让我们来一起看一下效果，我相信这是你需要的。

绝技5：OFFSET函数在图表中的使用

上面的这张图表，相信大家都不陌生吧。参加工作的同学们都会有制作图表的经历，在上图中选择A1:B10区域，在工具栏——“插入”——柱形图，就完成了我们图例的内容。

如果我们删掉一行数据，那么柱形图中的系列图例就会少一个，可是如果增加一行数据的话，就需要更改图表数据源的范围，才能显示正确的图表。但总不能每次都更改呀，那就失去了我们使用Excel 高效快速的初衷。

此时，我们依然可以借鉴OFFSET函数来解决：

步骤一：使用OFFSET函数分别对“日期列”和“数量列”，制作自定义名称

名称管理器，我们上面有介绍，就不多说了。选中“日期列”，设置如下：

引用位置函数：

=OFFSET(图表系列!$A$1,1,0,COUNTA(图表系列!$A$2:$A$1000),1)

因为原数据中并不存在公式得到的空单元格，所以这里不需要使用COUNTBLANK函数，直接用COUNTA函数统计出非空单元格的个数，作为OFFSET函数的第四参数（新区域的行数）即可。这里的A2:A1000，表示一个绝对大的区域，保证新输入的内容在这个范围内。

选中“数量列”，同理制作出数量的自定义名称，如下：

步骤二：在图表区域使用名称

这是OFFSET动态图表的关键所在，添加名称的位置是很重要的。

在绘图区，选择任意一个柱形，在编辑栏中你可以看到图标的函数写法（是不是第一次知道图表也有函数）。我们就在这里修改引用的范围。

我们只需改动区域的部分。

图表系列!$A$2:$A$10

图表系列!$B$2:$B$10

用自定义名称替换这两个红色的部分即可，切不可以将“图表系列!$A$2:$A$10”整体替换！

替换后按回车，函数就会像上图这样显示，OFFSET.xlsx是我们的工作薄名称。效果如下：

其他的图表类型也是可以的，大家可以试着操作一下，加深印象。

【编后语】

OFFSET函数的五个参数，如果理解了意义，就不难记住。它的返回值可以作为其他函数的引用，同理其他“返回值是数值格式”的函数也可以作为OFFSET函数的参数，让我们的数据可以自己动起来。

这个函数在Excel函数中起着不可或缺的作用，尤其是我们需要使用Excel建模的时候，动态区域的引用、自动化处理数据，往往都会使用到这个函数，强烈建议同学们，能多花一些时间来学习一下，对你今后制表的过程，将大有裨益。