【前言】在上篇文章中,我们了解了OFFSET函数的运算原理和各个参数的作用,并且我们也通过一些OFFSET的案例,了解了它的用途。那么本篇我们继续来看看,OFFSET函数在实际工作中所能起到的强大效果吧。
一、高阶应用的思路
(动态报表模板的原型)
我们使用Excel是为了快速地统计分析数据,快速地提取出我们需要的内容。现在假设以下两个场景:
场景一:领导安排了工作,统计某季度的销售数据,我们马上行动,用函数快速的制作报表;
场景二:领导安排了工作,因为每季度都需要统计销售数据,所以我们早就提前制作了模板,至于什么时候给出报表,就随我们的便了。切记,不要让“中层领导”知道你的工作效率很高。
两个场景,你会选择哪种处理方式呢?作者希望是第二个。
![](https://i-blog.csdnimg.cn/blog_migrate/097223b684d9cf856887398d168cf2cd.png)
思路决定了我们制表的格局,这是一个简单的案例,当数据源被修改后,相对应的季度数据也会自动做出调整。在复杂的模版中并不是所有的位置都会使用OFFSET函数,但对于动态引用数据区域的需求,用OFFSET函数来处理是绝对不会错的。
二、典型用法举例
绝技4:制作动态下拉菜单
在数据建模的过程中,我们经常会使用到下拉菜单(或者是组合框控件)。为了确保下拉内容的唯一性,我们会使用INDEX+SMALL+IF+ROW的“万金油”函数来去重提取数列中的数据。还记得我们在上篇讲到的OFFSET函数替代INDEX函数的例子吗?所以说,如果OFFSET函数可以代替INDEX函数使用的话,那么OFFSET函数同样也可以实现“万金油”的过程。下面我们就一起来看看复杂的“下拉菜单”的制作过程。
步骤一:使用OFFSET函数去重提取唯一值的 “万金油”公式
![](https://i-blog.csdnimg.cn/blog_migrate/af9dbd65fe4f31c48fdf23a0909e47b1.png)
这个公式比较长,列出如下:
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”,如下图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/0d1f1b07098930e738325ea05f5c1e53.png)
然后选择G1单元格,按Alt+D+L组合键可以打开数据验证设置框,在允许中选择“序列”,在来源中输入“=区域”,如下图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/afd53d9b771a1a780a26f94a30a014a3.png)
点击确定按钮,那么我们G1单元格的下拉菜单就建立好了。但是问题也来了,我们会发现有好多的空选项,这不是我们需要的。
![](https://i-blog.csdnimg.cn/blog_migrate/d405e0734a62b4d6500fa4665cff3540.png)
有的同学会说,名称管理器中选择D2:D5就可以了。是的,但是如果我们A列的区域中出现了新的数据,那下拉菜单中的数据可就少了,所以此时我们依然使用OFFSET函数来处理这个问题。
更改名称管理器中,“区域”的引用位置:
![](https://i-blog.csdnimg.cn/blog_migrate/a1522e5135176f3d16935b877d33f639.png)
=OFFSET(动态下拉菜单!$D$1,1,,COUNTA(动态下拉菜单!$D$2:$D$15)-COUNTBLANK(动态下拉菜单!$D$2:$D$15),1)
因为我们D列的唯一值,是用公式得到的,里面的“空单元格”不是名义上的“空”,而是由公式得到的空,所以不能直接通过COUNTIF(D2:D15,"<>")的方式得到有值的单元格个数。因此我们先使用了COUNTBLANK函数(空值单元格计数),统计空值单元格的数量,再用COUNTA函数统计非空单元格的数量,最后二者相减就得到了有值单元格的个数。将得到的结果作为OFFSET函数的第四参数(新区域的扩展行数)使用,就实现了动态引用有效数据的效果。如下图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/a7b34c8ed3b5c4fa21113312f0b53a17.png)
如果A列中增加了新的区域名称,那么G1的下拉菜单也会增加新的选项,让我们来一起看一下效果,我相信这是你需要的。
![](https://i-blog.csdnimg.cn/blog_migrate/a2d561e97c7078b032a65846d7058d14.jpeg)
绝技5:OFFSET函数在图表中的使用
![](https://i-blog.csdnimg.cn/blog_migrate/bcae43eb1d79edd12e2be2926bd25388.png)
上面的这张图表,相信大家都不陌生吧。参加工作的同学们都会有制作图表的经历,在上图中选择A1:B10区域,在工具栏——“插入”——柱形图,就完成了我们图例的内容。
如果我们删掉一行数据,那么柱形图中的系列图例就会少一个,可是如果增加一行数据的话,就需要更改图表数据源的范围,才能显示正确的图表。但总不能每次都更改呀,那就失去了我们使用Excel 高效快速的初衷。
此时,我们依然可以借鉴OFFSET函数来解决:
步骤一:使用OFFSET函数分别对“日期列”和“数量列”,制作自定义名称
名称管理器,我们上面有介绍,就不多说了。选中“日期列”,设置如下:
![](https://i-blog.csdnimg.cn/blog_migrate/e5e6f6a7818d19b154186b48646d640e.png)
引用位置函数:
=OFFSET(图表系列!$A$1,1,0,COUNTA(图表系列!$A$2:$A$1000),1)
因为原数据中并不存在公式得到的空单元格,所以这里不需要使用COUNTBLANK函数,直接用COUNTA函数统计出非空单元格的个数,作为OFFSET函数的第四参数(新区域的行数)即可。这里的A2:A1000,表示一个绝对大的区域,保证新输入的内容在这个范围内。
选中“数量列”,同理制作出数量的自定义名称,如下:
![](https://i-blog.csdnimg.cn/blog_migrate/113b95ad82b0bc3c1c3d92533db4fb77.png)
步骤二:在图表区域使用名称
这是OFFSET动态图表的关键所在,添加名称的位置是很重要的。
在绘图区,选择任意一个柱形,在编辑栏中你可以看到图标的函数写法(是不是第一次知道图表也有函数)。我们就在这里修改引用的范围。
![](https://i-blog.csdnimg.cn/blog_migrate/32731f60808d57a0df5f00219641473c.png)
我们只需改动区域的部分。
图表系列!$A$2:$A$10
图表系列!$B$2:$B$10
用自定义名称替换这两个红色的部分即可,切不可以将“图表系列!$A$2:$A$10”整体替换!
![](https://i-blog.csdnimg.cn/blog_migrate/203d0d731f7dfa2d6ad5f0a75594f943.png)
替换后按回车,函数就会像上图这样显示,OFFSET.xlsx是我们的工作薄名称。效果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/33fa091d99e0898bf0ec99778c380206.jpeg)
其他的图表类型也是可以的,大家可以试着操作一下,加深印象。