EXCEL技巧三十五: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(引用区域,行数,列数,[高度],[宽度])

  其中的参数意义如下:

EXCEL技巧三十五: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来计算符合条件的有多少个。

下图所示的工作表命名为:名称实例,其中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),然后下拉,就可以求出各个班级的平均分。

  

爱华网本文地址 » http://www.413yy.cn/a/25101017/362325.html

更多阅读

乱说《琼林》之三十八:五岳五湖

乱说《琼林》之三十八:五岳五湖整理者:山东-碧水、河北-青心、浙江-寒梅张家港-凯文妈、广东-向梅-------------------------------------------------

EXCEL技巧四十七:sumif和countif函数应用

第一部分:sumif函数的使用方法  Sumif函数是按给定条件对指定单元格求和。  首先,我们通过一个简单的数据来认识Sumif函数怎么使用。下图是一个工作表源数据。  使用SUMIF函数求出A列大于15的单元格,与它对应的B列单元格求和

EXCEL技巧四十六:substitute函数的运用

第一部分:substitute函数的用法介绍  Substitute这个单词就是替换的意思。substitute函数是属于什么函数,如何使用呢?substitute函数有点类似于excel中的查找替换命令,但却更灵活好用。  substitute函数属于文本查找类函数,就是查

声明:《EXCEL技巧三十五:OFFSET函数的应用》为网友情蒗分享!如侵犯到您的合法权益请联系我们删除