admin 管理员组

文章数量: 887031


2023年12月24日发(作者:纯flutter开发的app)

OFFSET函数的应用

首先,认识一下OFFSET函数。

从下图说明来认识一下excel中OFFSET函数的用法。

在C7单元格,输入公式:=SUM(OFFSET(C2,1,2,3,1)),得到结果为18。这个公式是什么意思呢?就是计算C2单元格靠下1 行并靠右2 列的3 行 1 列的区域的和。

可以在公式编辑栏,选中OFFSET(C2,1,2,3,1) 部分,按F9键抹黑,得到运算结果为:{3;8;7},此时公式变为:=SUM({3;8;7})。从上图可以得知,就是利用OFFSET函数来得到一个新的区域,然后使用SUM函数求出这个新区域的和。

下面,介绍OFFSET函数的用法。

Offset函数主要应用在单元格区域的定位和统计方面,一般做数据透视表定义名称都需要用到Offset函数。Offset函数属于查找与引用类的函数。

OFFSET函数以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域,并可以指定返回的行数或列数。

OFFSET函数的语法是:OFFSET(reference,rows,cols,height,width),按照中文的说法即是:OFFSET(引用区域,行数,列数,[高度],[宽度])

其中的参数意义如下:

Reference:作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用;否则,函数 OFFSET 返回错误值 #VALUE!。

Rows:相对于偏移量参照系的左上角单元格,上(下)偏移的行数。如果使用 5 作为参数 Rows,则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方)。

Cols:相对于偏移量参照系的左上角单元格,左(右)偏移的列数。如果使用 5 作为参数 Cols,则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边)。

Height:高度,即所要返回的引用区域的行数。Height 必须为正数。

Width:宽度,即所要返回的引用区域的列数。Width 必须为正数。

学习使用OFFSET函数需要注意以下几点:

第一,如果行数和列数偏移量超出工作表边缘,函数 OFFSET 返回错误值

#REF!。

第二,如果省略 height 或 width,则假设其高度或宽度与 reference 相同。

第三,函数 OFFSET 实际上并不移动任何单元格或更改选定区域,它只是返回一个引用。

第四,函数 OFFSET 可用于任何需要将引用作为参数的函数。

下面,结合几个实例学习OFFSET函数的应用。

OFFSET函数通常与其它函数来嵌套使用。下图所示的区域为实例的源数据区域。

OFFSET函数应用第一题:如下图所示,计算上图B列姓名下面所有单元格的数量。

在F3单元格输入函数:=OFFSET(B2,0,0,COUNTA(B:B)-1),全选公式,按F9键,得到:={"张三";"王五";"李四";"闻一";"张二";"江八";"张六";"赵六";"钱子";"赵一";"孙六";"周末"},我们可以数一下个数,得到12个。

提示:COUNTA 函数就是返回参数列表中非空值的单元格个数。

接下来,在F5单元格输入公式:=COUNTA(OFFSET(B2,0,0,COUNTA(B:B)-1)),得多结果为12。

OFFSET函数应用第二题:如下图所示,计算最后的平均成绩。分三种情况:第一,求最后一位、最后三位、最后五位的平均成绩。

单击F8单元格,可以看到有一个向下的箭头,单击箭头可以看到有1、3、5三个选项。

在F9单元格输入公式:=AVERAGE(OFFSET(C1,COUNTA(C:C)-1,,-F8)),就可以得到F8单元格所显示的平均成绩。

公式分析:首先使用OFFSET函数来计算出行数的值,然后使用AVERAGE来除以OFFSET部分得到的值,就得到了最后几个的平均成绩。

公式中,其中COUNTA(C:C)部分是统计C列的非空单元格个数为13,从而可以得到COUNTA(C:C)-1的值为12,即从C1单元格向下偏移12行。

另外-F8的意思是:减去F8单元格中的值。

当改变F8单元格的值,就可以得到其余行数的平均成绩。

OFFSET函数应用第三题:如下图所示,要求统计出班级=H3,成绩>=I3的人数。

H3和I3单元格的值是条件。单击这两个单元格,右下角有一个向下的三角形,单击此按钮,可以显示选择其余的条件值。比如上图所示,就是统计出班级为3版,成绩在>=70分的人数有几人。

本题的统计结果,可以在I5单元格输入公式:=COUNTIF(OFFSET(C1,MATCH(H3,A2:A13,0),,COUNTIF(A2:A13,H3)),">="&I3),得到结果。

此题需要使用到COUNTIF和MATCH函数。COUNTIF函数是统计个数的函数,在第二期的技术讲座专门讲解了COUNTIF函数的使用,不会的朋友,可以参考文章《2011年第2期技术分享:COUNTIF函数经典应用技巧》。

公式分析:OFFSET的第二参数使用MATCH来精确查找位置。偏移的行数就由MATCH(H3,A2:A13,0)来控制,高度由COUNTIF(A2:A13,H3)来控制。然后把OFFSET函数的值用COUNTIF来计算符合条件的有多少个。

在第4期讲座中,简单老师从零讲解了OFFSET函数的使用,本文最主要通过一个实例来讲解OFFSET函数在定义名称方面的运用。

下图所示的工作表命名为:名称实例,其中A1:C13区域为源数据,本讲座要完成的作业是通过源数据,使用OFFSET函数定义一个叫“AA”的名称,然后完成下图1、2、3个班级的实考人数、总分、最高分、优秀数、平均分的统计。

本题最核心的一个问题就是定义AA名称。

单击菜单“插入”——“名称”——“定义”,在“名称”框中输入名称“AA”,然后在下面的引用位置输入:=OFFSET(名称实例!$A$1,MATCH(名称实例!$D17,名称实例!$A$2:$A$13,),2,COUNTIF(名称实例!$A$2:$A$13,名称实例!$D17))

引用位置输入公式的含义,解释如下。把上面这个公式分成两部分来查看,就很容易了。

第一部分:MATCH(名称实例!$D17,名称实例!$A$2:$A$13,),这个值是OFFSET的第二个参数,使用MATCH来精确查找位置。代表偏移的行数就由MATCH部分的值来控制。这个公式的意思就是查找D17单元格的值在A2:A13区域中的位置。

MATCH函数也是一个查找函数。MATCH 函数会返回中匹配值的位置而不是匹配值本身。在使用时,输入单值它就返回单值,输入多值就返回多值。MATCH函数在众多的数字中只查找第一次出现的,后来出现的它返回的也是第一次出现的位置。MATCH函数一般都和别的函数嵌套使用。

第二部分:COUNTIF(名称实例!$A$2:$A$13,名称实例!$D17),这个值是OFFSET的第四个参数,高度就由COUNTIF的值来控制。

定义完名称之后,在E17单元格输入公式:=COUNTA(AA),然后下拉,就可以求出各个班级的实考人数。

在F17单元格输入公式:=SUM(AA),然后下拉,就可以求出各个班级的总分。

在G17单元格输入公式:=MAX(AA),然后下拉,就可以求出各个班级的最分。

在J17单元格套用COUNTIF函数的公式:countif(区域,条件),输入公式:=COUNTIF(AA,">=85"),然后下拉,就可以求出各个班级的优秀人数。在这个公式中,我们假定优秀人数的分数为大于等于85分。

在K17单元格输入公式:=AVERAGE(AA),然后下拉,就可以求出各个班级的平均分。


本文标签: 函数 引用 公式 区域