《Excel财务日常数据与实例讲解》_从理论到实践的一大步_1.1.6 使用OFFSET 函数动态引用数据

【摘要】 本书摘自《Excel财务日常数据与实例讲解》一书中第1章，第1.6节，作者是韩小良、贾春雷。

1.1.6 使用OFFSET 函数动态引用数据

从工作表中抓取数据的方式各种各样，而由于工作表数据结构的特殊性，很多情况下无 法直接使用常用的函数来获取数据，此时，不妨使用OFFSET 函数来创建数据区域的动态引用， 并结合其他函数做数据查找引用。

OFFSET 函数的功能是从一个基准单元格出发，向下(向上)偏移一定的行、向右(向左) 偏移一定的列，到达一个新的单元格，然后引用这个单元格，或者引用一个以这个单元格为 顶点、指定行数、指定列数的新单元格区域。

OFFSET 函数的语法如下。

=OFFSET (基准单元格，偏移行数，偏移列数，新区域行数，新区域列数)

这里有几个注意点。

● 如果省略了最后两个参数(新区域行数，新区域列数), OFFSET 就只是引用一个单 元格，得到的结果就是该单元格的数值。

● 如果设置了最后两个参数(新区域行数，新区域列数), OFFSET引用的是一个新单 元格区域。

● 偏移的行数如果是正数，是往下偏移；偏移的行数如果是负数，是往上偏移。

● 偏移的列数如果是正数，是往右偏移；偏移的列数如果是负数，是往左偏移。

例如，以A1 单元格为基准，向下偏移5行，向右偏移2列，就到达单元格C6, 如果没 有忽略最后两个参数，或者设置为1,那么OFFSET 函数的结果就是单元格C6 的数值。此时 OFFSET 公式如下。

=OFFSET(A1,5,2)

或者

=OFFSET(A1,5,2,1,1)

以 A1 单元格为基准，向下偏移5行，向右偏移2列，就到达单元格C6, 这里如果再给 定第4个参数是3,第5个参数是5,那么OFFSET 函数的结果就是新的单元格区域C6:G8, 它以偏移到达的单元格C6 为左上角单元格，扩展了3行高、5列宽，是一个新的单元格区域。 此时OFFSET 公式如下。

=OFFSET(A1,5,2,3,5)

但是，每个费用数据区域所处的位置是不同的，大小(行数)也是不同的。现在的问题是： 如何获取每个费用的数据区域，以便能够使用VLOOKUP 函数从不同的数据区域中查找数据?

可以先用MATCH 函数确定每个费用在B 列的位置，再用COUNTIF 函数从A 列中计算 出每个费用的行数，最后使用OFFSET 函数提取出这个区域即可。

这个公式是很综合的，联合使用了VLOOKUP、MATCH、INDEX、COUNTIF、OFFSET、 IFERROR。 在这个公式中：

● MATCH(SB3,2月'!$B$3:$B$48,0)用来确定费用项目的位置。

● COUNTIF(2月'SA:$A,INDEX(2 月!$A:$A,MATCH($B3,2月'!$B:$B,0)))用来统计每个项目下部门的个数(也就是该项目数据区域的行数),由于是在A 列里使用科目代码来统计， 而汇总表中是下面名称，因此又联合使用了MATCH 函数和INDEX 函数来获取该项目名称所 对应的科目代码，即表达式INDEX(2 月'$A:$A,MATCH(SB3,2 月'$B:$B,0))。

● OFFSET 函数部分就是获取每个项目的数据区域。

● 由于某些项目下没有某个部门，公式会出现计算错误值，因此使用IFERROR 函数处 理掉这样的错误值。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。