EXCEL使用技巧集合-OFFSET函数的使用

OFFSET函数的使用by chenjun

语法：Excel

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

结果：

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

其中：

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

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

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

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

行偏移：指返回区域的左上单元格相对于引用的左上单元格(我称之为基准)的行偏移数，向上为负值向下如与基准为同一行，此参数可省略。

列偏移：指返回区域的左上单元格相对于基准的列偏移数，向左为负值向右为正，同一列为0，此时该参行数、列数：指返回区域的行数和列数。如与引用的行数或列数相同，则相应的参数可省略。

提示：行偏移,列偏移,行数,列数4个参数的省略一般要用“,”号占位，只在后面没有了不省略的参数才可以注意返回区域的位置，可以超过引用的区域大小，但如超出工作表边缘，函数 OFFSET 返回错误值 OFFSET 可用于任何需要将引用作为参数的函数。

下面是对函数的详细分析

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

1A10=OFFSET($A$20,1,1,4,2)

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

3C12C12函数返回的是

A1W1W也就是

B2W2W

C313

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

#VALUE!#VALUE!那是因为返回引用的函数返回的是一个区域引用，不以数组方式直接输入在单元格#VALUE!#VALUE!自动按隐含的交叉

#VALUE!#VALUE!红色公式区域

#VALUE!#VALUE!请参考G30数组的特殊用途

其他返回引用的函数如INDIRECT

#VALUE!B11有效值的单元格是

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,, 2W B11B11

C12B11

1W B11

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

从B21起的3行2列。公式的规则，后面的行中是自动请注意不以数组公式的方式输入，同样有A中所说的隐含交叉的问题。

0。

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

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

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

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

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

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

函数的使用'工作薄中sheet2表的引用

{=OFFSET('C:\excelhomefunction\[INDIRECT函数的使用.xls]Sheet2'!$A$1 E.当第1个参数是一个单元素数组所指定的引用时

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

1、2、3列。

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

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

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

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

还可以用COUNTIF函数按条件计数，计算3个返回区域的大于14的单元格数目

12也是分别计数的，重叠的单元格重复计数=5+6+1

提示：目前只发现这两个函数可以处理多个数组元素的引用。(涉及三维引用)

G.其他参数为单元素数组时

INDIRECT函数一样，此时返回的

4.8=AVERAGE(OFFSET($K$94,,,3,{2}))

H.其他参数为多元素数组，且每个元素返回一个单元格引用时

省略了行数和列数参数)不能正常在单元格中显示，此时按每个数

N和T函数转换后变为普通的数组，见右面

此时OFFSET函数按每个数组元素分别返回一个多单元格区域的引用，不可能在单元格中显示全部值

就是用N和T函数也只能显示每个多单元格区域的第一个值，目前只发现SUMIF和COUNIF函数可计算这提示：据我理解，对返回多个多单元格区域引用的结果可以继续计算的函数有一个特点，就是那必须用引用而不能用数组常数(或计算出的数组)。按这个规则，DSUM、DCOUNT、DCOUNTA函数我已确定DSUM可以，其他的请网友自己试验。

一个可继续对这样的引用结果计算的例子：

10的数的和并返回一个数组

第1行

第3行2个大值

第5行23结果也正确！

第7行

定义动态的区域名称并用于其他函数中

在“插入》名称》定义”中定义动态的名称

data=OFFSET(OFFSET!$L$119,1,,COUNT(OFFSET!$L$119:$L$65536))

求总和66求数值单元格个数7平均值9.42857

试试在L列中继续输入数值看变化。

求sheet2和sheet3表中姓名为张三的工资总和

3000

结合我在INDIRECT函数的使用中的关于工作表名的几个宏表函数名称定义的方法，见F126格中的链接就可以按指定的工作表间求出总和。INDIRECT函数的使用

不写了，希望对大家有用

chenjun

2004年元月10日