excel教程--OFFSET函数的使用.xls

文档介绍：OFFSET函数的使用 by chenjun
语法:
OFFSET(引用,行偏移,列偏移,行数,列数)
结果:
以引用的左上单元格为基准,按指定的行偏移、列偏移、行数、列数返回一个新的引用。
其中:
引用:为对单个单元格或一个连续的单元格区域的引用,否则返回错误值#VALUE!。
直接的引用方式应按照“工具》选项》常规》设置”中是否选择了“R1C1引用样式”,
如用INDIRECT函数返回引用,则可以不管当前设置的引用方式。
点击H8格参见INDIRECT函数的使用 INDIRECT函数的使用
行偏移:指返回区域的左上单元格相对于引用的左上单元格(我称之为基准)的行偏移数,向上为负值向下为正,同一行为0,
如与基准为同一行,此参数可省略。
列偏移:指返回区域的左上单元格相对于基准的列偏移数,向左为负值向右为正,同一列为0,此时该参数可省略。
行数、列数:指返回区域的行数和列数。如与引用的行数或列数相同,则相应的参数可省略。
提示:行偏移,列偏移,行数,列数4个参数的省略一般要用“,”号占位,只在后面没有了不省略的参数才可以空缺不写;
注意返回区域的位置,可以超过引用的区域大小,但如超出工作表边缘,函数 OFFSET 返回错误值#REF!。
OFFSET 可用于任何需要将引用作为参数的函数。
下面是对函数的详细分析
、第1个参数引用为单个单元格
1 A 10 =OFFSET($A$20,1,1,4,2),以多单元格数组公式的方式输入
2 B 11 B 11 怎样输入多单元格数组公式请见H8格的链接
3 C 12 C 12 函数返回的是A20下偏一行右偏一列开始的4行2列区域
A 1 W 1 W 也就是B21:C24区域。
B 2 W 2 W
C 3 13
同样的公式,不以数组公式的方式输入怎么就错了?
#VALUE! #VALUE! 那是因为返回引用的函数返回的是一个区域引用,不以数组方式直接输入在单元格中时
#VALUE! #VALUE! 自动按隐含的交叉区域来显示,很显然,OFFSET函数返回的B21:C24区域与
#VALUE! #VALUE! 红色公式区域B27:C30是没有交叉的。
#VALUE! #VALUE! 请参考G30格链接的例子。数组的特殊用途
提示:将OFFSET函数返回的引用再用于其他函数中是不会有隐含交叉的问题的。(N和T函数除外,那只能返回区域引用的第一个值)
其他返回引用的函数如INDIRECT、INDEX等也有同样的情况出现,在使用中要注意。
如果函数返回的是一行的区域引用,隐含的交叉区域如下:
#VALUE! B 11 有效值的单元格是OFFSET函数返回的引用区域(B21:C21)与公式所在列的交叉单元格的值。
如果函数返回的是一列的区域引用,隐含的交叉区域如:
、第1个参数引用连续的多单元格区域
=OFFSET($A$20:$B$22,1,1,4,2)与上面的OFFSET($A$20,1,1,4,2)相同,因为指定了后面的4个参数。
B 11 这时实际只用到引用的$A$20:$B$22区域的左上$A$20格,所以结果一样,那么引用连续单元格区域有什么用呐?
C 12 在后2个参数有省略时有用,请比较
1 W 第一式{=OFFSET($A$20:$B$22,1,1,,2)} 第二式{=OFFSET($A$20,1,1,,2)}
2 W B 11 B 11
C 12 B 11
1 W B 11
此式省略第4个行数参数,返回的行数此式省略第4个行数参数,返回的行数同$A$20为1行,
同$A$20:$B$22的行数为3行,实际返回实际返回从B21起的1行2列区域,按多个单元格数组
从B21起的3行2列。公式的规则,后面的行中是自动复制第一行的数据。
请注意不以数组公式的方式输入,同样有A中所说的隐含交叉的问题。
如果返回的引用中有空单元格(不是指有空字符""的单元格),结果会转为0。
B 11 0
C 12 0
1 W 0

只要在OFFSET函数第1个参数引用中加上工作表名(如Sheet2!A1),就同本表的引用一样。
特殊的是不以数组公式的方式输入时,隐含的交叉区域比较有意思,请选择下面的4个参数看变化:
公式为=OFFSET(Sheet2!$A$58,$A$57,$B$57,$C$57,$D$57)
行偏移列偏移行数列数
1 3 2 3
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE!