excel表中offset指令（Excel路程导航函数OFFSET函数）

函数介绍

在英文中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。

普通用法

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

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

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

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

2.区域选择

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

3.区域选择

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

隔列取数

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

我们看下公式参数:

=OFFSET(基准点,行，列)

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

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

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

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

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

多列数据合并为一列

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

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

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

我们可以从上面找到规律，行偏移是 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)

一列分为多列

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

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

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

从上面可以看出，只有行对应移动，列为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,)

OFFSET MATCH 实现跨区域选择

如上图，统计每个员工的季度销售额：

实现步骤如下：

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

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

3.最后取3列

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

总结

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

,