美文网首页
应用技巧十三:OFFSET函数和INDEX函数的应用

应用技巧十三:OFFSET函数和INDEX函数的应用

作者: 陶泽昱 | 来源:发表于2018-07-28 23:27 被阅读0次

    OFFSET函数和INDEX函数在日常工作中相对来说应用较多的函数,下面将介绍两种函数的用法。

    一、认识OFFSET函数

    OFFSET函数是Excel引用类函数中非常实用的函数之一,无论在数据动态引用还是在数据位置变换,甚至在函数高级应用的三维引用中,该函数的使用频率也非常高。由于此函数的功能强大,使用灵活,在很多综合应用示例中都能见到它的身影。

    OFFSET函数的语法如下:

    OFFSET(Reference,rows,cols,[height],[width])

    参数reference为函数引用基点,它必须是单元格引用,而不能是常量数组。

    注意:OFFSET函数的首参数reference为函数引用基点,它必须是单元格引用(可为公式生成的单一单元格区域引用),而不能是常量数组。

    函数返回的结果是以制定的引用为参数,最终通过给定偏移量得到一个新的单元格引用或单元格区域引用。

    在使用OFFSET函数时,如果省略参数height或参数width,则系统视为其高度或宽度与引用基点相同。如果引用基点所指定的基点是一个区域引用,当用户指定了参数height或参数width,则函数将以引用区域的左上角单元格为基点来进行区域偏移运算。

    为了便于更直观地理解,使用下面两个实例进行说明,如图1所示。

    例1:以下两个公式的作用完全相同,都返回区域$D$4:$E$8:

    =OFFSET(B2,2,2,5,2)            (公式1)

    =OFFSET(G11,-3,-2,-5,-2)        (公式2)

    由于公式2的行偏移、列偏移为负数,表明从基点向上、向左产生偏移,而引用行高height为-5,列宽width为-2,则表明从偏移点向上扩展5行,并向左扩展2列来取得结果,因此其返回的引用区域等价于公式1.

    例2:以下3个公式的作用也完全相同,都返回区域$D$4:$E$8:

    =OFFSET(B2,2,2,5,2)              (公式1)

    =OFFSET(B2:C6,2,2)              (公式2)

    =OFFSET(F10:G11,-2,-2,-5)        (公式3)

    在公式2中的引用基点为B2:C6单元格区域(5行2列),而函数中只制定的行、列偏移量,在这种情况下,公式将保持源区域大小不变按行列偏移量来生成新的引用区域,返回的结果等价于公式1。

    另外,在公式3中引用基点为F10:G11区域(2行2列),但在行高和列宽中仅制定了height为5行,因此在忽略列宽的情况下,引用源区域将忽略行高保留列宽(变为1行2列),再按指定行高参数向上扩展5行生成新的引用区域,返回的结果仍然等价于公式1。

    提示:通常来说,OFFSET函数的行数height或列数width都使用正数,表明从引用基点向下或向右偏移来生成新的引用。这两个参数也支持使用负数做为参数值,表明偏移方向相反,即从基点向上或向左偏移来生产引用。

    二、理解INDEX函数

    INDEX函数做为Excel常用的引用函数之一,主要根据用户在一个范围(区域引用或数组)中指定的行号和列号来返回一个值,如果源范围时区域则返回单元格引用,如果源范围是数组,则返回数组中的某个值。

    例如下面的公式返回A1:C10区域中第5行第2列的单元格引用B5:

    =INDEX(A1:C10,5,2)

    1. 根据指定条件提取数据

    例3:利用INDEX函数隔行提取数据

    如图2所示,左侧为一张数据表,其右侧表格的F3:G7区域利用INDEX函数来实现从左表中隔行提取数据。

    由于原表中的品名都在奇数行,F3单元格公式如下:

    =INDEX($C$3:$C$12,ROW(A1)*2-1)

    G3单元格公式如下:

    =INDEX($C$3:$C$12,ROW(A1)*2)

    公式主要利用ROW函数来生成公差为2的自然数序列,再利用INDEX函数取出数据。另外,还可以借助行列函数组合应用将其合并为一个公式,如:

    =INDEX($C$3:$C$12,(ROW(A1)*2-1)*2+COLUMN(A1))

    这个公式对于需要转换多行多列的数据(如每隔4行取出一组数据),就显得非常有用,在转换区域中只需要一个公式(将公式中*2修改为*4即可),无需每列单独编写公式。

    2. 在二维数据表中进行查找

    在Excel中,MATCH函数可以用于查找数据的位置,而INDEX函数则可以根据查找到的位置返回为实际的单元格引用。因此,在实际工作中常常可以使用二者的组合来完成条件查询计算。

    例4:多条件组合查询资料

    如图3所示,展示了某公司的产品价格表,要求根据B2单元格和B3单元格中的产品型号和规格条件,从价格表中查询产品价格。

    B4单元格的查询公式如下:

    =INDEX($E$2:$G$10,MATCH($B$2,$D$2:$D$10,0),MATCH($B$3,$E$1:$G$1,0))

    在公式中则直接使用两个MATCH函数分别针对产品型号和产品规格进行独立查找,最终将行号和列号返回给INDEX函数来返回查询结果。

    另外,B4单元格也可以使用vlookup函数来构造公式达到同样的目的,如:

    =VLOOKUP(B2,$D$2:$G$10,MATCH(B3,$D$1:$G$1,),)

    相关文章

      网友评论

          本文标题:应用技巧十三:OFFSET函数和INDEX函数的应用

          本文链接:https://www.haomeiwen.com/subject/jrwamftx.html