OFFSET函数

OFFSET引用函数OFFSET(reference,rows,cols,[height],[width])以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。基本原理基点reference公式：=OFFSET(A5,2,2,5,3)=$C$7:$E$11偏移后基点COLUMNS:2HEIGHT:5WIDTH:3以上图示为OFFSET常规偏移示例，函数结果为引用（REF）。注意事项1、首先必须注意，reference必须为引用（包括函数产生的三维引用），不能为常量或数组。2、行偏移_rows、列偏移_cols为必输项，如省略必须用,逗号进行占位，缺省值为0(即不偏移)；行数_height和列数_width为可选项，可省略，缺省视为与基点相同。常规应用一、基点与行数、列数的关系。通常情况下，如果基点以单元格区域作为引用范围，如果不指定行数或列数，系统默认是引用区域的行数和列数来确定结果。公式1=OFFSET(A5:C5,2,2)公式结果$C$7:$E$7公式2=OFFSET(A5,2,2,,3)公式结果$C$7:$E$7由于引用基点为A5:C53列区域，同时公式1中未指定行数或列数，所以最终结果仍然为3列区域，公式1与公式2结果相同，两者等价。公式3=OFFSET(A5:C9,2,2)公式结果$C$7:$E$11公式4=OFFSET(A5,2,2,5,3)公式结果$C$7:$E$11同理，公式3与公式4结果相同，两个公式等价。二、行数与列数对引用基点的影响。在上面的公式1和公式3中，都是引用基点为单元格区域引用，同时都未设置行数与列数的情况下的结果。但有时，基点为区域引用时，用户同时又指定了行数或(和)列数，那么用户必须清楚了解实际引用的基点区域，才能得出正确结果。例如已经定义名称DATA：=OFFSET应用基础!$P$32:$R$36例1：利用名称作为基点，得到右图的浅蓝色子区域公式：=OFFSET(DATA,1,,2)公式结果$P$33:$R$34检验：6由于公式中指定了最终结果的行数(2)，未指定列数，那么公式实际上以P32:R32区域作为引用基点，公式等同于：公式1=OFFSET(P32:R32,1,,2)公式2=OFFSET(P32,1,,2,3)检验：6例2：利用名称作为基点，得到右图中红色字体区域公式：=OFFSET(DATA,2,1,3,2)公式结果$Q$34:$R$36检验：6由于公式中指定了最终结果的行数(3)和列数(2)，那么系统只能将DATA中的左上角单元格(P32)作为引用基点，并不是整个DATA区域。公式等同于：=OFFSET(P32,2,1,3,2)公式结果$Q$34:$R$36检验：6ROWS:2以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。2、行偏移_rows、列偏移_cols为必输项，如省略必须用,逗号进行占位，缺省值为0(即不偏移)；行数_height和列数_width为可选项，可省略，缺省视为与基点相同。通常情况下，如果基点以单元格区域作为引用范围，如果不指定行数或列数，系统默认是引用区域的行数和列数来确定结果。由于引用基点为A5:C53列区域，同时公式1中未指定行数或列数，所以最终结果仍然为3列区域，公式1与公式2结果相同，两者等价。序号姓名工资但有时，基点为区域引用时，用户同时又指定了行数或(和)列数，那么用户必须清楚了解实际引用的基点区域，才能得出正确结果。1A1002B2003C3004D400由于公式中指定了最终结果的行数(2)，未指定列数，那么公式实际上以P32:R32区域作为引用基点，公式等同于：5E500由于公式中指定了最终结果的行数(3)和列数(2)，那么系统只能将DATA中的左上角单元格(P32)作为引用基点，并不是整个DATA区域。基点员工编号员工姓名性别年龄A0711张三男10A0795李四男20A0733王五女30B1234赵六男40A2345张大男50A0123刘二女60偏移B1123田七男50公式=offset(B1,7,2,-3,3)检验：9等价于：=OFFSET(B1,7-3+1,2,3,3)解释：1、通常情况下，用户都是输入正数来控制产生引用的行数和列数，偏移方向为向下取行数或向右取得列数。2、值得提醒的是，EXCEL支持用户在行数_height和列数_width参数中输入负数来产生引用。其中：负行数向上取行数，负列数向左取列数。2、值得提醒的是，EXCEL支持用户在行数_height和列数_width参数中输入负数来产生引用。其中：负行数向上取行数，负列数向左取列数。姓名语文数学英语总成绩求和最大值姚良德102.5103.0106.5312.0312.0106.5宋国爱115.0105.5138.5359.0359.0138.5刘猛122.5145.5135.5403.5403.5145.5郑弟琴138.0141.0133.5412.5412.5141.0宋建84.5143.5129.5357.5357.5143.5岑仕廷129.5130.0131.5391.0391.0131.5方井田119.5145.5107.0372.0372.0145.5李兴义127.597.0114.5339.0339.0127.5罗朝周138.0141.0110.0389.0389.0141.0杨昌艳147.5106.0147.5401.0401.0147.5合计3736.51368.0一、理解引用基点例1：公式1：1923.5=SUM(OFFSET(A1,2,1,5,3))公式2：1923.5=SUM(OFFSET(A1:C1,2,1,5))返回区域：B3:D7验证公式：1923.5二、理解偏移量例1：公式1：3736.5=SUM(OFFSET(A11,-9,1,10,3))公式2：3736.5=SUM(OFFSET(D1,1,-2,10,3))返回区域：返回B2:D11区域的二维引用验证公式：3736.5例2：公式1：3736.5=SUM(SUBTOTAL(9,OFFSET(B1,ROW(1:10),,,3)))公式2：3736.5=SUM(SUBTOTAL(9,OFFSET(B1:D1,ROW(1:10),)))返回区域：返回B2:D11区域的三维引用验证公式：3736.5三、理解高度和宽度参数公式1：1923.5=SUM(OFFSET(A1,6,1,-5,3))公式2：1923.5=SUM(OFFSET(A1,2,3,5,-3))返回区域：B3:D7验证公式：1923.5