跟我学EXCEL系列文章0026

亲爱的小伙伴们，跟我学EXCEL系列福利来了，从初级一直到高级学习EXCEL系列文章，结合财务实际应用讲解，配合动图细节演示，通俗易懂，是一套比较系统的不可多见学习EXCEL的好文章。持续更新中！

本系列文章包括基础篇（包括技巧、函数）、进阶篇（主要是数据透视表）、高级篇（主要是Power Query）。

希望大家喜欢，欢迎提出宝贵意见和建议！

今天我们继续学习函数及相关内容。

二、EXCEL基础篇-函数19

25、查找和引用函数OFFSET

（1）、OFFSET函数概述

作为EXCEL中最烧脑的函数之一，从它的参数上就能略知一二，这个函数的函数表达式是=OFFSET(参照物,参数1,参数2,参数3,参数4)，一共五个参数。相对而言比一般的函数复杂一些，别的函数要不是参数少，要不是参数虽然多，但参数结构简单，有规律。

这个函数的主要用途是引用单个单元格或单元格区域，你也可以简单地理解为可以动态扩展区域的函数。我们知道，EXCEL中能动态扩展区域最主要的功能是前面讲过的超级表功能，但有些情况下超级表也解决不了，或者不方便使用超级表，就可以考虑使用OFFSET函数来动态扩展区域。

我们先从这个函数的表达式开始讲，这个函数的表达式是=OFFSET(参照物,参数1,参数2,参数3,参数4)，参照物一般为单个单元格，也就是起点，参数1是以参照物向下移动的行数，参数2是以参照物向右移动的列数，参数3是需要引用的区域的行数，参数4是需要引用的区域的列数。参数1、2、3、4是整数可以是正整数，也可以是负整数，零参数的时候可以省略。

这里要特别提示，如果是引用单个单元格一般我们不用这个函数，OFFSET稍显麻烦，前面讲了好多引用单个单元格的函数，选择使用即可。OFFSET选择的区域一般是嵌套使用。

我们用案例来感受一下OFFSET的用法。

（2）、案例一，动态扩展区域

截图一

截图一在不使用超级表的情况下来实现所有数值动态求和。

公式是=SUM(OFFSET($A$1,1,1,COUNTA($A:$A)-1,COUNTA($1:$1)-1))，其中的OFFSET($A$1,1,1,COUNTA($A:$A)-1,COUNTA($1:$1)-1)就是动态区域，五个参数，第一个参数$A$1是参照物单元格，也就是这个区域的起点单元格，因为起点不动，所以是绝对应用。第二个参数1意思是以$A$1单元格为起点向下偏移一行,第三个参数1意思是以$A$1单元格为起点向右偏移一列，这三个参数运行后的这个单元格就变为了"B2"，第四个参数“COUNTA($A:$A)”统计的是A列的非空单元格个数,COUNTA($A:$A)-1”减去1是因为数据行区域比表格行区域少一行，同理“COUNTA($1:$1）-1”是因为数据列区域比表格列区域少一列。

这样如果增加一行数据数据区域就会由B2:H6自动扩展为B2:H7，如果增加一列数据数据区域就会由B2:H6自动扩展为B2:I6。

我们来看动图。

（2）、案例二，引用单个单元格

虽然说OFFSET引用单个单元格不是重点功能，但我们还是用这个函数做一个引用单个单元格的案例，来体会一下OFFSET的用法。

A10是用数据验证选取城市，B7里用MATCH函数根据选取的城市得到行号，B10的公式是=OFFSET(B1,$B$7,,)，公式里的逗号可以是一个、两个、三个。

（3）、案例三，动态二级菜单

第一步，工作簿里有两个表，一个是信息表，也就是一二级菜单列表，一个是一二级菜单录入表。

第二步，设置一级菜单公式

公式是=OFFSET(信息表!$A$1,,,,COUNTA(信息表!$1:$1))，以信息表的A1单元格为参照物，向下向右偏移均为0，省略，向下引用行数为1，默认为1，省略也行，输入1也行，向右引用的列数，用COUNTA统计第一行的个数就是向右引用的列数。

F7设置好的一级菜单，OFFSET函数公式在高版OFFICE才能显示区域，低版本是显示不了的。将设置好的公式复制粘贴到数据验证下序列里的数据来源里。F13单元格是二级菜单区域也是同理。

第三步，设置二级菜单

公式是=OFFSET(信息表!$A$1,1,MATCH($B2,信息表!$A$1:$W$1,0)-1,COUNTA(OFFSET(信息表!$A$1,1,MATCH($B2,信息表!$A$1:$W$1,0)-1,1000,1)))。

这个二级菜单的公式比较长，会套用就行了，有兴趣的可自行研究研究这个函数。

以信息表的A1单元格为参照物，向下偏移值为1，向右偏移量是以一级菜单为准，用“MATCH($B2,信息表!$A$1:$W$1,0)-1”确定向右偏移的量，因为A1本身是1，所以公式里要减去1。

向下引用行数为COUNTA(OFFSET(信息表!$A$1,1,MATCH($B2,信息表!$A$1:$X$1,0)-1,1000,1))，这部分比较烧脑。逻辑是先用MATCH函数利用一级菜单的名称确定在信息表第一行里的列号，再用OFFSET函数确定一个行数为1000的区域，然后再用COUNTA函数统计这个区域里有多少非空单元格的个数，这样就动态确定了向下引用行数的数量了。

向右引用的列数默认是1，省略了。

整个公式复制粘贴到数据验证下序列里的数据来源里。