offset函数的使用方法,关于excel函数offset的用法?

Excel中的OFFSET函数的正确使用姿势是怎样的?

OFFSET函数确实是一个非常厉害的函数,他在下拉菜单、动态图表、动态引用等操作中,具有不可替代的作用,本文让你从入门到精通完全掌握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单元格

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


「精进Excel」系头条签约作者,关注我,如果任意点开三篇文章,没有你想要的知识,算我耍流氓!

关于excel函数offset的用法?

语法=offset(基点,向下偏移行数,向右偏移列数,引用区域的高,引用区域的宽)基点,可以是一个单元格,也可以是一个连续单元格区域,但offset会把区域的最左上单元格作为基点;引用区域的高,即行数引用区域的宽,即列数假设有A1:D10这样的一个数据区域=offset(A1,1,1,1,1)就是从A1开始,向下移一行即第2行,向右移1列,即B列,这时获得的单元格引用为B2,这个B2就是新引用单元格区域的基点,这个区域的高为1行,宽为1列!亦即该公式的结果就是B2=offset(A1,1,1,2,3)同理,这个公式的结果是:以B2为基点,2行3列的单元格区域,也就是B2:D3(欲看到这个公式的所有结果,应该先选择一个2行3列的空白区域,输入此公式,最后按ctrl+shift+enter)请在excel中实践一下,体会体会,再看excel帮助,就有理解了(顺便说一句,千万不要对excel帮助感冒,那是好东西哦!)