首页技术日记正文内容

OFFSET函数的使用方法

技术日记

更新时间：2026-04-03 22:25:30 72

admin 管理员组

文章数量: 1184232

2024年3月21日发(作者：python的reverse)

OFFSET函数的使用

语法：

OFFSET(引用,行偏移,列偏移,行数,列数)

结果：

以引用的左上单元格为基准，按指定的行偏移、列偏移、行数、列数返回一个新的引用。

其中：

引用：为对单个单元格或一个连续的单元格区域的引用，否则返回错误值 #VALUE!。

直接的引用方式应按照“工具》选项》常规》设置”中是否选择了“R1C1引用样式”，

如用INDIRECT函数返回引用，则可以不管当前设置的引用方式。

点击H8格参见INDIRECT函数的使用INDIRECT函数的使用

行偏移：指返回区域的左上单元格相对于引用的左上单元格(我称之为基准)的行偏移数，向上为负值向下

如与基准为同一行，此参数可省略。

列偏移：指返回区域的左上单元格相对于基准的列偏移数，向左为负值向右为正，同一列为0，此时该参

行数、列数：指返回区域的行数和列数。如与引用的行数或列数相同，则相应的参数可省略。

提示：行偏移,列偏移,行数,列数4个参数的省略一般要用“,”号占位，只在后面没有了不省略的参数才可以

注意返回区域的位置，可以超过引用的区域大小，但如超出工作表边缘，函数 OFFSET 返回错误值

OFFSET 可用于任何需要将引用作为参数的函数。

下面是对函数的详细分析

A.对本表、第1个参数引用为单个单元格

1A10=OFFSET($A$20,1,1,4,2)，以多单元格数组公式的方式输入

2B11B11怎样输入多单元格数组公式请见H8格的链

3C12C12函数返回的是A20下偏一行右偏一列开始的

A1W1W也就是B21：C24区域。

B2W2W

C313

同样的公式，不以数组公式的方式输入怎么就错了？

#VALUE!#VALUE!

那是因为返回引用的函数返回的是一个区域引用，不以数组方式直接输入在单元格

#VALUE!#VALUE!

自动按隐含的交叉区域来显示，很显然，OFFSET函数返回的B21:C24区域与

#VALUE!#VALUE!

红色公式区域B27:C30是没有交叉的。

#VALUE!#VALUE!

请参考G30格链接的例子。数组的特殊用途

提示：将OFFSET函数返回的引用再用于其他函数中是不会有隐含交叉的问题的。(N和T函数除外，那只

其他返回引用的函数如INDIRECT、INDEX等也有同样的情况出现，在使用中要注意。

如果函数返回的是一行的区域引用，隐含的交叉区域如下：

#VALUE!

B11有效值的单元格是OFFSET函数返回的引用区域(B21:C21)与公式所在列

如果函数返回的是一列的区域引用，隐含的交叉区域如：

B.对本表、第1个参数引用连续的多单元格区域

=OFFSET($A$20:$B$22,1,1,4,2)与上面的OFFSET($A$20,1,1,4,2)相同，因为指定了后面的4个参数。

B11这时实际只用到引用的$A$20:$B$22区域的左上$A$20格，所以结果一样，那么引

C12在后2个参数有省略时有用，请比较

1W第一式{=OFFSET($A$20:$B$22,1,1,,2)}第二式{=OFFSET($A$20,1,1,,

2WB11B11

C12B11

1WB11

此式省略第4个行数参数，返回的行数此式省略第4个行数参数，返回

同$A$20:$B$22的行数为3行，实际返回实际返回从B21起的1行2列区域

从B21起的3行2列。公式的规则，后面的行中是自动

请注意不以数组公式的方式输入，同样有A中所说的隐含交叉的问题。

如果返回的引用中有空单元格(不是指有空字符""的单元格)，结果会转为0。

B110

C120

1W0

C.对本工作薄其他表的引用

只要在OFFSET函数第1个参数引用中加上工作表名(如Sheet2!A1)，就同本表的引用一样。

特殊的是不以数组公式的方式输入时，隐含的交叉区域比较有意思，请选择下面的4个参数看变化：

公式为=OFFSET(Sheet2!$A$58,$A$57,$B$57,$C$57,$D$57)

行偏移列偏移行数列数

1323

#VALUE!

#VALUE!#VALUE!#VALUE!

E59

E60

#VALUE!

F59

F60

#VALUE!

#VALUE!D59

#VALUE!D60

#VALUE!

上面有效值是函数返回的引用与公式所在单元格的行及列交叉处的值，也就是隐含的交叉是不分工作表

好在不影响将返回的引用继续用于其他函数中计算，可以不管它！

D.对其他工作薄指定表的引用

见下例对'c:excelhomefunctionINDIRECT函数的使用'工作薄中sheet2表的引用

#VALUE!#VALUE!

公式为{=OFFSET('C:excelhomefunction[INDIRECT函数的使用.xls]Sheet2'!$A$1

#VALUE!#VALUE!

请注意引用的工作薄必须打开，否则重算后就返回错误，你可关闭引用的工

#VALUE!#VALUE!

提示：当引用的工作薄打开后，公式中的引用会自动去掉路径的。

E.当第1个参数是一个单元素数组所指定的引用时

公式为{=OFFSET(INDIRECT("G"&{76}),2,1,3,2)}

#VALUE!

公式正常，与不是数组的引用相同。

F.当第1个参数是多元素数组所指定的引用时

公式{=OFFSET(INDIRECT({"G76","H77","I75"}),2,1,3,3)}

#VALUE!

结果比较怪！分别返回了3个引用区域的某一列，按引用区域的顺序依次返回第1、2、3列。

如计算此公式返回的3个引用区域的总和，直接用SUM函数是错误的。

144只计算了第1个返回引用区域的总和

可以用SUMIF函数计算出3个引用区域的总和340

请注意总和是对3个引用区域分别算的，重叠的单元格是重复计算的。=144+150+46

本文标签：引用返回函数区域数组

版权声明：本文标题：OFFSET函数的使用方法内容由网友自发贡献，该文观点仅代表作者本人，转载请联系作者并注明出处：http://www.roclinux.cn/p/1711003010a583666.html，本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。