offset函数高级应用.xls

文档介绍：OFFSET引用函数
OFFSET(reference,rows,cols,[height],[width])
以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。
基本原理
基点 reference 公式: =OFFSET(A5,2,2,5,3)=$C$7:$E$11
ROWS:2
偏移后基点

COLUMNS:2 HEIGHT:5



WIDTH: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:C5 3列区域,同时公式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中,都是引用基点为单元格区域引用,同时都未设置行数与列数的情况下的结果。
但有时,基点为区域引用时,用户同时又指定了行数或(和)列数,那么用户必须清楚了解实际引用的基点区域,才能得出正确结果。
例如已经将整个P32:R36区域定义为名称 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 检验: 6






























序号姓名工资
1 A 100
2 B 200
3 C 300
4 D 400
5 E 500







OFFSET引用函数
OFFSET(reference,rows,cols,[height],[width])
以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。
基本原理
基点 reference 公式: =OFFSET(A5,2,2,5,3)=$C$7:$E$11
ROWS:2
偏移后基点

COLUMNS:2 HEIGHT:5



WIDTH:3
以上图示为OFFSET常规偏移示例,函数结果为引用(REF)。
注意事项
1、首先必须注意,reference必须为引用(包括函数产生的三维引用),不能为常量或数组。
2、行偏移_rows、列偏移_cols为必输项,如省略必须用","逗号进行占位,缺省值为0(即不偏移);行数_height和列数_width为可选项,可省略,缺省视为与基点行数和列数相同。

常规知识点
一、基点与行数、列数的关系。
通常情况下,如果基点以单元格区域作为引用范围,如果