数据能瞬移?查找交叉值?OFFSET、MATCH函数来搞定

大家好，我是@OFFICE职场办公，专注办公软件知识，提升职场效率。

一：OFFSET函数是什么？

以指定的引用为参照系，通过给定的偏移量得到新的引用。指定的引用可以是一个单元格或单元格区域，返回的引用也是一个单元格或单元格区域。

例如将A1单元格按指定的偏移量（3,2）进行偏移，那么会返回单元格C4。

另外也可指定返回的单元格区域。例如上面例子A1单元格偏移后，指定的返回单元格区域为两行两列，那么返回值为单元格区域C4：D5。

二：OFFSET的语法结构是什么？

语法结构为：OFFSET(reference,rows,cols,[height],[width])

也就是：OFFSET(参照区域，偏移行数，偏移列数，【高度】，【宽度】）

其中：

1. Reference（参照区域）是指参照系的引用区域，必须为某一单元格或单元格区域。
2. Rows（偏移行数）是指从参照区域左上角单元格，往下或往上偏移的行数，其中正数表示往下，负数表示往上。
3. Cols（偏移列数）是指参照区域左上角单元格，往左或往右偏移的列数，其中正数表示往右，负数表示往左。
4. Height（高度）是指从偏移后的单元格开始往下选取的行数，必须为正数。
5. Width（宽度）是指从偏移后的单元格开始往右选取的列数，必须为正数。
6. 当高度和宽度省略时，返回的区域与参照区域范围相同。

三：OFFSET函数怎么用？

【例1】如何编写公式从A1单元格开始，进行偏移返回E6单元格内容。

思考：从A1到E6行数往下增加5行，列数往右增加4列，偏移量为（5,4），偏移后选取返回单元格E6，也就是零行零列的内容，可以将行高和列宽省略。

操作：公式表达为“OFFSET（A1,5,4）”。

【例2】若返回E6：G8的单元格区域呢？并求和呢？

思考：偏移量不变，返回区域的行数为3，列数为3，也就是高度为3，宽度为3；关于求和，只需要用SUM对返回区域进行引用。

操作：公式表达为“SUM(OFFSET（A1,5,4,3,3）)”

【例3】根据F2中输入的姓名，求算该同学的三科总成绩。

思考：

1. 以同学丁为例，丁位于A5单元格，三科成绩位于B5：D5单元格区域，也就是返回的单元格区域为B5：D5。
2. 以A1单元格为参照区域，成绩区域的首个单元格B5位于A1单元格下方4行，右方1列，偏移量为（4，1）。
3. 无论求算任何同学的成绩偏移列均为1，只需根据姓名动态变化偏移列即可，这里运用MACTH函数来完成。
4. MATCH函数用来返回查找值位于查找区域的相对位置。这里是A5单元格在A列中的相对位置，也就是5。由于偏移函数为4，所以需做减1处理。

操作：在G2单元格输入公式

“=SUM(OFFSET(A1,MATCH(F2,A:A,0)-1,1,1,3))”，回车

小结：OFFSET函数作为EXCEL中特有的偏移函数，往往需要结合其他函数进行组合，从而达到数据引用或统计分析的结果。

内容最后，记得关注一下，持续获得更多知识内容。