excel教程--OFFSET函数的使用

OFFSET函数的使用bychenjun语法：Excelhome.netOFFSET(引用,行偏移,列偏移,行数,列数)结果：以引用的左上单元格为基准，按指定的行偏移、列偏移、行数、列数返回一个新的引用。其中：引用：为对单个单元格或一个连续的单元格区域的引用，否则返回错误值#VALUE!。直接的引用方式应按照“工具》选项》常规》设置”中是否选择了“R1C1引用样式”，如用INDIRECT函数返回引用，则可以不管当前设置的引用方式。点击H8格参见INDIRECT函数的使用INDIRECT函数的使用行偏移：指返回区域的左上单元格相对于引用的左上单元格(我称之为基准)的行偏移数，向上为负值向下为正，同一行为0，如与基准为同一行，此参数可省略。列偏移：指返回区域的左上单元格相对于基准的列偏移数，向左为负值向右为正，同一列为0，此时该参数可省略。行数、列数：指返回区域的行数和列数。如与引用的行数或列数相同，则相应的参数可省略。提示：行偏移,列偏移,行数,列数4个参数的省略一般要用“,”号占位，只在后面没有了不省略的参数才可以空缺不写；注意返回区域的位置，可以超过引用的区域大小，但如超出工作表边缘，函数OFFSET返回错误值#REF!。OFFSET可用于任何需要将引用作为参数的函数。下面是对函数的详细分析A.对本表、第1个参数引用为单个单元格1A10=OFFSET($A$20,1,1,4,2)，以多单元格数组公式的方式输入2B11B11怎样输入多单元格数组公式请见H8格的链接3C12C12函数返回的是A20下偏一行右偏一列开始的4行2列区域A1W1W也就是B21：C24区域。B2W2WC313同样的公式，不以数组公式的方式输入怎么就错了？#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,,2)}2WB11B11C12B111WB11此式省略第4个行数参数，返回的行数此式省略第4个行数参数，返回的行数同$A$20为1行，同$A$20:$B$22的行数为3行，实际返回实际返回从B21起的1行2列区域，按多个单元格数组从B21起的3行2列。公式的规则，后面的行中是自动复制第一行的数据。请注意不以数组公式的方式输入，同样有A中所说的隐含交叉的问题。如果返回的引用中有空单元格(不是指有空字符的单元格)，结果会转为0。B110C1201W0C.对本工作薄其他表的引用只要在OFFSET函数第1个参数引用中加上工作表名(如Sheet2!A1)，就同本表的引用一样。特殊的是不以数组公式的方式输入时，隐含的交叉区域比较有意思，请选择下面的4个参数看变化：公式为=OFFSET(Sheet2!$A$58,$A$57,$B$57,$C$57,$D$57)行偏移列偏移行数列数1323#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!D59E59F59#VALUE!#VALUE!#VALUE!#VALUE!D60E60F60#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!上面有效值是函数返回的引用与公式所在单元格的行及列交叉处的值，也就是隐含的交叉是不分工作表的，只按行列位置。好在不影响将返回的引用继续用于其他函数中计算，可以不管它！D.对其他工作薄指定表的引用见下例对'c:\excelhomefunction\INDIRECT函数的使用'工作薄中sheet2表的引用#VALUE!#VALUE!公式为{=OFFSET('C:\excelhomefunction\[INDIRECT函数的使用.xls]Sheet2'!$A$1,1,0,3,2)}#VALUE!#VALUE!请注意引用的工作薄必须打开，否则重算后就返回错误，你可关闭引用的工作薄再按F9键试试。#VALUE!#VALUE!提示：当引用的工作薄打开后，公式中的引用会自动去掉路径的。E.当第1个参数是一个单元素数组所指定的引用时公式为{=OFFSET(INDIRECT(G&{76}),2,1,3,2)}#VALUE!#VALUE!1A130#VALUE!#VALUE!2A231#VALUE!#VALUE!3A332公式正常，与不是数组的引用相同。4A433F.当第1个参数是多元素数组所指定的引用时5A534公式{=OFFSET(INDIRECT({G76,H77,I75}),2,1,3,3)}6A635#VALUE!#VALUE!#VALUE!7A736#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!结果比较怪！分别返回了3个引用区域的某一列，按引用区域的顺序依次返回第1、2、3列。如计算此公式返回的3个引用区域的总和，直接用SUM函数是错误的。144只计算了第1个返回引用区域的总和可以用SUMIF函数计算出3个引用区域的总和340请注意总和是对3个引用区域分别算的，重叠的单元格是重复计算的。=144+150+46还可以用COUNTIF函数按条件计数，计算3个返回区域的大于14的单元格数目12也是分别计数的，重叠的单元格重复计数=5+6+1提示：目前只发现这两个函数可以处理多个数组元素的引用。(涉及三维引用)G.其他参数为单元素数组时看看公式{=OFFSET($K$94,,,3,{2})}#VALUE!#VALUE!与INDIRECT函数一样，此时返回的A6#VALUE!#VALUE!是三维的数组，不能直接在单元格中17#VALUE!#VALUE!象右边这样表现，但不影响用于28其他函数中，如求返回的引用中的平均值正确！4.8=AVERAGE(OFFSET($K$94,,,3,{2}))H.其他参数为多元素数组，且每个元素返回一个单元格引用时公式{=OFFSET($K$94,{0;1;2},{0,1})}(省略了行数和列数参数)不能正常在单元格中显示，此时按每个数组元素返回的单个单元格引用#VALUE!#VALUE!是按三维方式排列的，可用N和T函数转换后变为普通的数组，见右面#VALUE!#VALUE!#VALUE!#VALUE!I.复杂的用多元素数组参数的每参数返回多个单元格引用此时OFFSET函数按每个数组元素分别返回一个多单元格区域的引用，不可能在单元格中显示全部值就是用N和T函数也只能显示每个多单元格区域的第一个值，目前只发现SUMIF和COUNIF函数可计算这样的返回结果。提示：据我理解，对返回多个多单元格区域引用的结果可以继续计算的函数有一个特点，就是那个函数的某个参数必须用引用而不能用数组常数(或计算出的数组)。按这个规则，DSUM、DCOUNT、DCOUNTA函数应该是可以的，我已确定DSUM可以，其他的请网友自己试验。一个可继续对这样的引用结果计算的例子：求出右面区域中每隔一行中大于10的数的和并返回一个数组第1行15结果正确！第3行23再检验此计算公式返回数组的第2个大值第5行2623结果也正确！第7行0J.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.netchenjun2004年元月10日INDIRECT函数的使用行偏移：指返回区域的左上单元格相对于引用的左上单元格(我称之为基准)的行偏移数，向上为负值向下为正，同一行为0，列偏移：指返回区域的左上单元格相对于基准的列偏移数，向左为负值向右为正，同一列为0，此时该参数可省略。行数、列数：指返回区域的行数和列数。如与引用的行数或列数相同，则相应的参数可省略。提示：行偏移,列偏移,行数,列数4个参数的省略一般要用“,”号占位，只在后面没有了不省略的参数才可以空缺不写；注意返回区域的位置，可以超过引用的区域大小，但如超出工作表边缘，函数OFFSET返回错误值#REF!。=OFFSET($A$20,1,1,4,2)，以多单元格数组公式的方式输入#VALUE!公式=OFFSET($A$20,1,1,4)，省略第5个参数，那么返回引用的列数同引用A20的列数即1怎样输入多单元格数组公式请见H8格的链接B有效值的单元格是OFFSET函数返回的引用区域(B21:B24)与公式所在行的交叉单元格的值。函数返回的是A20下偏一行右偏一列开始的4行2列区域C12#VALUE!那是因为返回引用的函数返回的是一个区域引用，不以数组方式直接输入在单元格中时自动按隐含的交叉区域来显示，很显然，OFFSET函数返回的B21:C24区域与提示：将OFFSET函数返回的引用再用于其他函数中是不会有隐含交叉的问题的。(N和T函数除外，那只能返回区域引用的第一个值)有效值的单元格是OFFSET函数返回的引用区域(B21:C21)与公式所在列的交叉单元格的值。=OFFSET($A$20:$B$22,1,1,4,2)与上面的OFFSET($A$20,1,1,4,2)相同，因为指定了后面的4个参数。这时实际只用到引用的$A$20:$B$22区域的左上$A$20格，所以结果一样，那么引用连续单元格区域有什么用呐？第二式{=OFFSET($A$20,1,1,,2)}此式省略第4个行数参数，返回的行数同$A$20为1行，实际返回从B21起的1行2列区域，按多个单元格数组公式的规则，后面的行中是自动复制第一行的数据。特殊的是不以数组公式的方式输入时，隐含的交叉区域比较有意思，请选择下面的4个参数看变化：上面有效值是函数返回的引用与公式所在单元格的行及列交叉处的值，也就是隐含的交叉是不分工作表的，只按行列位置。公式为{=OFFSET('C:\excelh