如何成为Excel大神—Offset函数

大家好，我是永不止步的老牛

今天我们来了解下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函数的用法，你离大神又进了一步。