001 OFFSET 函数初识

001 OFFSET 函数初识

 

OFFSET函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用，返回的引用可以为一个单元格或单元格区域，其语法为：OFFSET(reference, rows, cols, [height], [width])，翻译过程中文就是：OFFSET（参照,偏移行,偏移列,行高,列宽）

 

▌案例

如图A1:D7是一个数据区域，我们在这个数据区域中，以A1单元格为参照，返回一个新的单元格。

在A9单元格写入公式=OFFSET(A1,4,3,1,1)
含义是：以A1单元格为参照，向下偏移4行，向右偏移3列，返回一个1行、一列的单元格区域，即D5.

 

如果函数的最后两个参数为1，可以省略，默认为返回一行或一列或一个单元格；如果都不为1，则返回的是一个区域。

▌案例

公式=OFFSET(C2,5,3,4,3) 就是以C2为基点，向下偏移5行，向右偏移3列，新引用的行数是4行，新引用的列数是3列，最终得到对F7:H10单元格区域的引用，如图所示：

 

 

OFFSET函数如果是引用一个区域，则需要以数组公式的方式输入，以公式=OFFSET(C2,5,3,4,3) 为例：

①输入公式之前选中一个4行3列的区域

②输入公式=OFFSET(C2,5,3,4,3)，然后按Ctrl、Shift、Enter输入公式

 

当然，仅仅是引用区域，是没有太大用处的，OFFSET函数的神奇之处在于，通过引用构造动态区域，从而完成复杂的数据汇总、高级动态图表、多级下拉菜单等！

 

002 OFFSET 应用1-动态求和区域

▌案例

如图所示，根据AB两列的个月的销售数据，求最近三个月的销售额之和，要注意的是AB两列的数据时会变动的，也就是说过一个就增加一个月的数据。

 

 

在D3单元格写入公式

=SUM(OFFSET(A1,COUNTA(A:A)-3,1,3))

 

▌解读

=SUM(OFFSET(A1,COUNTA(A:A)-3,1,3))

用鼠标选中OFFSET函数部分，然后按F9，可以预览结果，OFFSET函数返回的就是最近三个月的销售额。

 

 

▌解读

=SUM(OFFSET(A1,COUNTA(A:A)-3,1,3))

①COUNTA(A:A)

是求A列非空单元格的数量，用这个数量-3，就得出来了最近三个月数据的起始位置。

②OFFSET(A1,COUNTA(A:A)-3,1,3)

就是以A1单位格为参照，向下偏移COUNTA(A:A)-3行，向右偏移1列，然后行数为4行一列的区域，这个区域如图所示：

对这个区域求和，的出来的结果正是最近三个月的销售额之和

 

003 OFFSET 应用2-二级下拉菜单

▌案例

如图所示，根据AB两列的城市列表，在黄色区域设置二级下拉菜单，即在黄色区域选择不同的省份，城市下拉菜单中出现的是对应省份的城市。

▌步骤

Step1：设置一级下拉菜单

一级菜单很简单，多数同学应该都会了，我直接放上结果：

Step2：创建二级菜单名称

①定义名称，点击【公式】→【定义名称】，弹出新建名称对话框

②在【新建名称】对话框中，【名称】处填写“二级菜单”

引用位置填写如下公式：

=OFFSET($A$1,MATCH($D$3,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12, D$3),1)

 

Step3：设置二级下拉菜单

①将鼠标定位于E3单元格

②点击【数据】→【数据验证】，在弹出的【数据验证】对话框中，按如下设置