大家好,我是永不止步的老牛 今天我们来了解下Excel的Offset函数的实用用法 Offset函数的语法是: Offset(reference, rows, cols, [height], [width]),其中 Reference:表示要引用的原点, 是一个单元格,也可以是一个单元格区域 Rows:表示原点(Reference)的行偏移数量, 正数向下,负数向上,默认为0 Cols:表示原点(Reference)的列偏移数量, 正数向右,负数向左,默认为0 Height:表示返回引用的高度, 正数表示向下多少行,负数表示向上多少行, 默认为1,可以省略,不能为0 Width:表示返回引用的宽度, 正数表示向右多少列,负数表示向左多少列, 默认为1,可以省略,不能为0 什么意思呢?就是说你可以通过这个函数在指定一个原点(Reference),然后往下偏移Rows行,再往右偏移Cols列,以这开始,返回Width列Height行的引用区域 我们用这张图来理解一下 =OFFSET(B4,4,5,1,1) 这个公式表示从原点B4,往下偏移4行,往右偏移5列,然后返回1行1列 这里有几点要特别说明一下 如果行数和列数偏移量超出工作表边缘,函数 OFFSET 返回错误值 #REF! 假如把公式改成=OFFSET(B4,5000000,5,1 ,1),就会返回错误 Rows、Cols、Height和Width都有默认值 Rows和Cols的默认值是0,就是说不写具体数值的话,就表示从原点没有偏移 选中G15:H17,输入公式OFFSET(B4, , ,3,2),然后同时按Ctrl+Shift+Enter结束 Height和Width的默认值是1,就是说不写具体数值的话,返回行列偏移后的那个单元格 选中G15,输入公式OFFSET(B4,4 ,5 ,,),返回的就是G8 Height和Width可以省略,则假设其高度或宽度与 reference 相同 省略和默认大家要分清楚, 默认是数字可以不写,逗号要有, 省略是数字和逗号都不写 Height和Width可以省略时, 其数值就和reference的一样, 就是说如果reference是个单元格,那么Height和Width就是1, 如果reference是B1:C6, 那么Height就是6,Width就是2, 因为reference的高度是6,宽度是2 Height和Width可以是负数 这里说一下,网上很多文章说这两个参数不能是负数,是可以为负数的,负数代表方向相反,往下的变成往上,往右变成往左 下来我们用实例展示一下Offset函数的用法 1、 根据选择的考试场次I14,计算该场次近3次考试外语平均成绩,结果放在I15中 选中I14,输入公式: =AVERAGE(OFFSET(B4,3,MATCH(I14,C4:N4,0)-2,1,3)) 表示以B4为原点,往下偏移3,往右偏移MATCH(I14,C4:N4,0)-2,返回1行3列 MATCH(I14,C4:N4,0)表示返回I14在C4:N4中的位置,近3次,就给它减2 我们前面说过, Height和Width可以是负数 负数代表方向相反,往下的变成往上,往右变成往左 那么我们可以在用MATCH(I14,C4:N4,0)找到场次的位置后,不减2, 而是用负号,告诉Offset往左边数,公式变成: =AVERAGE(OFFSET(B4,3,MATCH(I14,C4:N4,0),1,-3)) 结果是一样的 2、 根据选择的科目B13,获取十二次模考成绩,结果放在C13:N13中 选中C13,输入公式: =OFFSET(B4,MATCH($B$13,$B$5:$B$12,0),1,1,1) MATCH($B$13,$B$5:$B$12,0)表示返回B13在B5:B12中的位置, 就是找所选科目在第几行, 以B4为原点,往下偏移MATCH返回的位置,往右偏移1,返回1行1列 我们在用得到的数据生成个折线图 Offset和Match经常配合使用 3、 多行多列转换成一列, 我们需要将C3:H6的人员转换成一列,结果从K3开始放 以C3为原点,我们按每组4人算, 就是需要行偏移按0,1,2,3,循环, 列偏移是0,0,0,0,1,1,1,1,2,2,2,2…... 我们先写行偏移公式, 以C3计算,先得出行号,再给减去3,然后除以4取余, 就得到我们想要的结果 公式是=MOD((ROW(C3)-3),4) 下来写列偏移公式, 以C3计算,先得出行号,再给减去3,然后除以4取整, 就得到我们想要的结果 公式是=INT((ROW(C3)-3)/4) 然后带入到Offset函数里,最后的公式是 =OFFSET(C$3,MOD((ROW(C3)-3),4),INT((ROW(C3)-3)/4)) 4、 生成成绩条 我们需要根据学生成绩表生成成绩条, 要求每名学生的成绩条都有表头, 并用空行隔开,方便裁剪 因为要有空行分割, 每名学生的成绩条就需要3行, 为了计算方便,我们从A9输入公式 CHOOSE(MOD(ROW(),3)+1,A$1,OFFSET(A$1,ROW(3:3)/3,),) Offset负责取每条学生的数据 Choose负责按表头、学生成绩、空行循环显示 Ok,掌握了Offset函数的用法,你离大神又进了一步。 |
|