OFFSET-1

by chenjun Excelhome.net
同$A$20:$B$22的行数为3行，实际返回 实际返回从B21起的1行2列区域 从B21起的3行2列。 公式的规则，后面的行中是自动 请注意不以数组公式的方式输入，同样有A中所说的隐含交叉的问题。 如果返回的引用中有空单元格(不是指有空字符""的单元格)，结果会转为0。 B 11 0 C 12 0 1W 0 C.对本工作薄其他表的引用 只要在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!
I.复杂的用多元素数组参数的每参数返回多个单元格引用 此时OFFSET函数按每个数组元素分别返回一个多单元格区域的引用，不可能在单元格中显示全部值 就是用N和T函数也只能显示每个多单元格区域的第一个值，目前只发现SUMIF和COUNIF函数可计算这 提示：据我理解，对返回多个多单元格区域引用的结果可以继续计算的函数有一个特点，就是那个函数 必须用引用而不能用数组常数(或计算出的数组)。按这个规则，DSUM、DCOUNT、DCOUNTA函数 我已确定DSUM可以，其他的请网友自己试验。 一个可继续对这样的引用结果计算的例子： 求出右面区域中每隔一行中大于10的数的和并返回一个数组 第1行 15 结果正确！ 第3行 23 再检验此计算公式返回数组的第2个大值 第5行 26 23 结果也正确！ 第7行 0 J.OFFSET 函数常用的场合 定义动态的区域名称并用于其他函数中 在“插入》名称》定义”中定义动态的名称 data =OFFSET(OFFSET!$L$119,1,,COUNT(OFFSET!$L$119:$L$65536)) 求总和 66 求数值单元格个数 7 平均值 9.42857 试试在L列中继续输入数值看变化。 求sheet2和sheet3表中姓名为张三的工资总和 3000 结合我在INDIRECT函数的使用中的关于工作表名的几个宏表函数名称定义的方法，见F126格中的链接 就可以按指定的工作表间求出总和。 INDIRECT函数的使用 不写了，希望对大家有用 Excelhome.net chenjun 2004年元月10日
数组公式的方式输入 数组公式请见H10格的链接 下偏一行右偏一列开始的4行2列区域
#VALUE! 公式=OFFSET($A$20,1,1,4)，省略第5个参数，那么返回引用
B C 1 2
#VALUE!
有效值的单元格是OFFSET函数返回的引用区域(B21:B24)与公
方式直接输入在单元格中时 的B21:C24区域与
公式正常，与不是数组的引用相同。 F.当第1个参数是多元素数组所指定的引用时 公式{=OFFSET(INDIRECT({"G76","H77","I75"}),2,1,3,3)}
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
http://club.excelhome.net/dispbbs.asp?BoardID=3&ID=31546&replyID=&skin=0
T函数的使用 移数，向上为负值向下为正，同一行为0，
，同一列为0，此时该参数可省略。 参数可省略。 了不省略的参数才可以空缺不写； 数 OFFSET 返回错误值 #REF! 。
。(N和T函数除外，那只能返回区域引用的第一个值)
21:C21)与公式所在列的交叉单元格的值。
定了后面的4个参数。 所以结果一样，那么引用连续单元格区域有什么用呐？
OFFSET($A$20,1,1,,2)}
第4个行数参数，返回的行数同$A$20为1行，
从B21起的1行2列区域，按多个单元格数组 则，后面的行中是自动复制第一行的数据。
1 2 3 4 5 6 7
A1 A2 A3 A4 A5 A6 A7
30 31 32 33 34 35 36
结果比较怪！分别返回了3个引用区域的某一列，按引用区域的顺序依次返回第1、2、3列。 如计算此公式返回的3个引用区域的总和，直接用SUM函数是错误的。 144 只计算了第1个返回引用区域的总和 可以用SUMIF函数计算出3个引用区域的总和 340
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! E59 E60 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! F59 F60 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! D59 #VALUE! D60 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE!
提示：当引用的工作薄打开后，公式中的引用会自动去掉路径的。 E.当第1个参数是一个单元素数组所指定的引用时 公式为{=OFFSET(INDIRECT("G"&{76}),2,1,3,2)}
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
的4个参数看变化：
含的交叉是不分工作表的，只按行列位置。
使用.xls]Sheet2'!$A$1,1,0,3,2)} 可关闭引用的工作薄再按F9键试试。
12 13 14 15 16 17 18
B1 B2 B3 B4 B5 B6 B7
1 A 4 B C D 5
A 1 2 3C 4D 5
6 7 8
9 B 10 中显示，此时按每个数组元素返回的单个单元格引用 A 6 1 7 2 8
OFFSET函数的使用
语法： OFFSET(引用,行偏移,列偏移,行数,列数) 结果： 以引用的左上单元格为基准，按指定的行偏移、列偏移、行数、列数返回一个新的引用。 其中： 引用：为对单个单元格或一个连续的单元格区域的引用，否则返回错误值 #VALUE! 。 直接的引用方式应按照“工具》选项》常规》设置”中是否选择了“R1C1引用样式”， 如用INDIRECT函数返回引用，则可以不管当前设置的引用方式。 点击H10格参见INDIRECT函数的使用 INDIRECT函数的使用 行偏移：指返回区域的左上单元格相对于引用的左上单元格(我称之为基准)的行偏移数，向上为负值向下 如与基准为同一行，此参数可省略。 列偏移：指返回区域的左上单元格相对于基准的列偏移数，向左为负值向右为正，同一列为0，此时该参 行数、列数：指返回区域的行数和列数。如与引用的行数或列数相同，则相应的参数可省略。 提示：行偏移,列偏移,行数,列数4个参数的省略一般要用“,”号占位，只在后面没有了不省略的参数才可以 注意返回区域的位置，可以超过引用的区域大小，但如超出工作表边缘，函数 OFFSET 返回错误值 OFFSET 可用于任何需要将引用作为参数的函数。 下面是对函数的详细分析 A.对本表、第1个参数引用为单个单元格 1A 10 =OFFSET($A$20,1,1,4,2)，以多单元格数组公式的方式输入 2B 11 B 11 怎样输入多单元格数组公式请见H10格的链 3C 12 C 12 函数返回的是A20下偏一行右偏一列开始的 A 1W 1W 也就是B21：C24区域。 B 2W 2W 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)与公式所在列 如果函数返回的是一列的区域引用，隐含的交叉区域如： B.对本表、第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个参数有省略时有用，请比较 1W 第一式{=OFFSET($A$20:$B$22,1,1,,2)} 第二式{=OFFSET($A$20,1,1,, 2W B 11 B 11 C 12 B 11 1W B 11 此式省略第4个行数参数，返回的行数 此式省略第4个行数参数，返回
单元格中显示全部值 COUNIF函数可计算这样的返回结果。 个特点，就是那个函数的某个参数 UNT、DCOUNTA函数应该是可以的， 1 15 9 2 3 23 12 5 11 45 12 34 15 10 11 23 1 0 0 6 9 13 14 15 数值 10 20 12 13 5 4 2
第1 行 第2 行 第3 行 第4 行 第5 行 第6 行 第7 行 第8 行
wk.baidu.com
请注意总和是对3个引用区域分别算的，重叠的单元格是重复计算的。=144+150+46 还可以用COUNTIF函数按条件计数，计算3个返回区域的大于14的单元格数目 12 也是分别计数的，重叠的单元格重复计数=5+6+1 提示：目前只发现这两个函数可以处理多个数组元素的引用。(涉及三维引用) G.其他参数为单元素数组时 看看公式{=OFFSET($K$94,,,3,{2})} #VALUE! #VALUE! 与INDIRECT函数一样，此时返回的 A 6 #VALUE! #VALUE! 是三维的数组，不能直接在单元格中 1 7 #VALUE! #VALUE! 象右边这样表现，但不影响用于 2 8 其他函数中，如求返回的引用中的平均值正确！ 4.8 =AVERAGE(OFFSET($K$94,,,3,{2})) H.其他参数为多元素数组，且每个元素返回一个单元格引用时 公式{=OFFSET($K$94,{0;1;2},{0,1})}( 省略了行数和列数参数)不能正常在单元格中显示，此时按每个数 #VALUE! #VALUE! 是按三维方式排列的，可用N和T函数转换后变为普通的数组，见右面
上面有效值是函数返回的引用与公式所在单元格的行及列交叉处的值，也就是隐含的交叉是不分工作表 好在不影响将返回的引用继续用于其他函数中计算，可以不管它！ D.对其他工作薄指定表的引用 见下例对'c:\excelhomefunction\INDIRECT函数的使用'工作薄中sheet2表的引用 #VALUE! #VALUE! 公式为{=OFFSET('C:\excelhomefunction\[INDIRECT 函数的使用.xls]Sheet2'!$A$1 #VALUE! #VALUE! 请注意引用的工作薄必须打开，否则重算后就返回错误，你可关闭引用的工作薄再