函数介绍

在英文中OFFSET是偏移的意思,这个行数是以指定的单元格为基准点,通过指定的偏移量

得到新的单元格。

语法:

OFFET(reference,rows,cols,height,width)

1. reference :可以是一个坐标圆点,也可以是一个单元格区域,不可以省略

2. rows : 行偏移数,正数表示向下多少行,负数表示向上多少行。默认为1,可以省略,不能为0。

3.cols: 正数向右,负数向上。默认为1,可以省略,不能为0。

4.height: 表示高度,即所要返回的引用区域的行数,正数表示向下多少行,负数表示向上多少行。默认为1,可以省略,不能为0。

5.width: 表示宽度,即所要返回的引用区域的列数,正数表示向右多少列,负数表示向左多少列。默认为1,可以省略,不能为0。

普通用法

下面是学生每个月份的考试成绩:

excel表中offset指令(Excel路程导航函数OFFSET函数)(1)

1.取出姜松的2月份的成绩

公式: =OFFSET(B1,3,1)

excel表中offset指令(Excel路程导航函数OFFSET函数)(2)

相当于B1单元格向下移动三行,向右移动一列。正数表示向下或者向右。

2.区域选择

公式 = OFFSET(A2,2,2,2,2)

excel表中offset指令(Excel路程导航函数OFFSET函数)(3)

3.区域选择

公式 = OFFSET(A2,2,2,-2,-2)

excel表中offset指令(Excel路程导航函数OFFSET函数)(4)

隔列取数

需求:取出所有学生的2月、4月、6月的成绩

excel表中offset指令(Excel路程导航函数OFFSET函数)(5)

excel表中offset指令(Excel路程导航函数OFFSET函数)(6)

我们看下公式参数:

=OFFSET(基准点,行,列)

(1) 基准点我们可以选择 $A2, 要锁住行,向下时,会变为 $A2,$A3...,向右不变

(2) 行,我们取默认,位移为0,或者不填

(3) 列的移动我们就要变化了,可以使用COLUMN函数,相对于$A2,$A3,..姓名列,往右移动 2,4,6...,所以使用COLUMN(A1)*2

最终公式为:=OFFSET($A2,0,COLUMN(A1)*2)

excel表中offset指令(Excel路程导航函数OFFSET函数)(7)

所以写出正确公式的前提是,提前分离出哪些是变的,哪些是不变的

多列数据合并为一列

我们需要把公司各个部门列的姓名,转为一列数据,效果如下:

excel表中offset指令(Excel路程导航函数OFFSET函数)(8)

我们先分析一下公式怎么写

多列转为一列,行列都要变化,我们看下行偏移和列偏移

excel表中offset指令(Excel路程导航函数OFFSET函数)(9)

我们可以从上面找到规律,行偏移是 1-5 循环,而列偏移 是 每5个数都是一样的。

行偏移: =MOD(ROW(A5),5) 1

列偏移: =INT(ROW(A5)/5)-1

总公式为: =OFFSET($A$1,MOD(ROW(A5),5) 1,INT(ROW(A5)/5)-1)

excel表中offset指令(Excel路程导航函数OFFSET函数)(10)

一列分为多列

和上个案例正好相反,把一列分为列

excel表中offset指令(Excel路程导航函数OFFSET函数)(11)

我们可以拆解为以下几个要点:

下面的是行偏移量,相对应$A$1。

excel表中offset指令(Excel路程导航函数OFFSET函数)(12)

从上面可以看出,只有行对应移动,列为0;

1

1 5

1 10

1 15

2

2 5

2 10

2 15

3

3 5

3 10

3 13

4

4 5

4 10

4 15

5

5 5

5 10

5 15

上面表格我们可以总结出规律:

公式为 = ROW(A1) (COLUMN(A1)-1)*5

对于第一列: ROW(A1),ROW(A2) 返回的是 1,2,3......,COLUMN(A1),COLUMN(A2) ... 始终返回的是1.

对于第一行: ROW(A1) 是不变的,列为:COLUMN(A1),COLUMN(B1)..分别为 1,2,3...

总的公式为: =OFFSET($A$1,ROW(A1) (COLUMN(A1)-1)*5,)

excel表中offset指令(Excel路程导航函数OFFSET函数)(13)

OFFSET MATCH 实现跨区域选择

excel表中offset指令(Excel路程导航函数OFFSET函数)(14)

如上图,统计每个员工的季度销售额:

excel表中offset指令(Excel路程导航函数OFFSET函数)(15)

实现步骤如下:

1.先使用MATCH函数 根据姓名定位到行号

2.根据季度数值定位到右移多少列

3.最后取3列

总公式为: =SUM(OFFSET($B$1,MATCH(P3,$B$2:$B$22,0),Q3*3-2,1,3))

excel表中offset指令(Excel路程导航函数OFFSET函数)(16)

总结

OFFSET函数的用处很多,尤其是在动态数据源的构造方面,在拿到一个需求的时候,我们需要分析怎么实现,一步一步的分析,特别是负责的公式,不是一步就能到位的,需要分析变和不变,每一个小点怎么实现,最终组装成一个大而复杂的公式。

,