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单元格
②点击【数据】→【数据验证】,在弹出的【数据验证】对话框中,按如下设置