|
编按：哈喽，大家好！今天是部落窝函数课堂的第6课，我们将认识人送外号“动态统计之王”的OFFSET函数！OFFSET函数是一个非常实用的函数，它在下拉菜单、动态图表、动态引用等操作中都具有不可替代的作用。毫不夸张的说Excel表格的高效，有相当一部分的功能来源于OFFSET。今天就跟着小编一起来认识一下它吧！（由于教程篇幅较长，将分为上下两篇，本篇为上篇。） ＊＊＊＊＊＊＊＊＊ 【前言】OFFSET函数是判断Excel函数使用者是否进阶的一个重要函数之一。在实际工作中，如果你需要对工作中的数据文件进行系统化、自动化的建模，那么势必会使用这个函数。 【功能及语法】OFFSET函数的功能是，以指定的引用为参照系，通过给定的偏移量返回新的引用。 语法：OFFSET(reference,rows,cols,[height],[width]) reference：是原基础点 rows ：是要偏移的行数，正数向下，负数向上，零不变。 cols：是要偏移的列数，正数向右，负数向左，零不变。 [height]：是基础点偏移后，纵向扩展几行，正数向下扩展，负数向上扩展。 [width]：是基础点偏移后，横向扩展几列，正数向右扩展，负数向左扩展。 如果不使用第四个和第五个参数（但不可以为零），则新引用的区域和原基础点大小一致。 原基础点可以是一个单元格，也可以是一个区域。 刚刚接触OFFSET函数的同学，想要理解上面这些参数，可能存在一定的难度，那么我们用一个图解的方式来给大家说明一下吧。
相信大家看这个图都花费了不少时间吧。我们可以先按照上图的指引，将数据填入OFFSET函数中，实际操作一下，来看看是否和新区域的地址一致呢？ 先来测试下第一个例子，看看正数为参量的运行结果：
通过验算，对黄色 “新区域”中的值进行求和，等于256，与单元格C15中的值一致，结果正确。如果同学们想模拟这个数据，也可以选中C15单元格，再通过工具栏中“公式——公式审核——公式求值”的功能，就能更加直观的看到OFFSET的返回值。（在函数中使用F9也是可以的，选中公式中OFFSET的函数部分，再按F9即可，这里就不多讲了。）
再来测试下第二个例子，看看负数为参量的运行结果：
大家可以用“公式求值”的方式，自己测试一下，看看OFFSET函数区域的返回值。 那么知道了OFFSET的基本运行原理之后，它在实际的工作中就可以帮助我们进行很多的操作和运算，而且有了这个函数的参与，可以实现excel中很多自动化的效果。下面让我们一起来看看OFFSET函数在实际操作中起到的强大作用！ 一、初级常规用法 作为其他函数的区域引用，应该是OFFSET函数最基础的用途了。OFFSET函数并不是移动了单元格区域，而是返回了一个偏移扩展后的区域地址。因此所有将引用区域作为参数的函数，都可以利用OFFSET函数的返回值，例如我们上面的例子SUM（OFFSET（）），再比如下面这个例子：
函数原理和上面的用法相同，我们就不再赘述了，依然是利用OFFSET函数返回的区域作为MAX函数的参数。 二、进阶常规用法 绝技①：模拟转置TRANSPOSE函数
我们在使用TRANSPOSE函数前，需要先选择相应大小的转置区域，而且还需用CTRL+SHIFT+ENTER三键结束公式，比较繁琐。 这里我们可以使用OFFSET函数来模拟这个转置的效果，如上图所示。 A11单元格函数： =OFFSET($A$1,COLUMN()-1,ROW()-11) 函数解析： 转置数据其实就是一个“行转列”、“列转行”的过程，再说具体点就是行号与列号互换的问题。在原数据中的第一列“姓名”列，转置后变成了新区域中的第一行。同理“姓名”列中每行的行号，就成为了转置后的列号。使用OFFSET的原理，就是偏移取值的时候，调换行列号的引值范围。 ★ 比如A11单元格，COLUMN()=1，1-1=0，那么OFFSET的第二参数为0，说明原基础点的行数不偏移（OFFSET的第二参数表示行偏移量，不熟悉的话看看前面的内容哟！）。ROW()=11，11-11=0，OFFSET的第三参数为0，说明列数也不偏移，所以引用的是原基础点A1单元格的值。 ★★ 把函数向右拉动填充，B11单元格，COLUMN()=2，2-1=1，那么OFFSET的第二参数为1，说明原基础点的行数向下偏移一个位置。ROW()=11，11-11=0，OFFSET的第三参数为0，说明列数不偏移，所以B11单元格引用的是基础点A1向下偏移后的A2单元格的值。 ★★★ 把A11单元格的函数向下拉动填充，A12单元格，COLUMN()=1，1-1=0，行数不偏移。ROW()=12，12-11=1，OFFSET的第三参数为1，说明列数从基础点A1向右偏移一个位置，引用的是B1单元格的值（我们公式中的A1之所以使用绝对引用，是因为我们所有的单元格都是以A1为基础点）。 以此类推，当我们使用鼠标下拉右拉填充公式之后，借助COLUMN和ROW函数帮我们定位出各个单元格的偏移量，由此达到了转置的效果。 绝技②：模拟VLOOKUP函数的反向查询功能
VLOOKUP函数的反向查询大多是借助数组完成的，但因为数组的原因，在数据量较多的情况下，函数可能会卡顿，所以很多同学也会使用INDEX函数来代替。那么今天就再丰富一下大家的知识量，我们用OFFSET函数来处理这类问题。 C12单元格函数： =OFFSET($A$1,MATCH("D2568",$B$2:$B$7,0),) 函数解析： 我们以单元格A1作为原基础点，需要返回的值与原基础点在同一列，所以我们只需要考虑OFFSET函数的行偏移量，不用考虑列偏移量。因为员工编号一般都是具有唯一性的值，所以我们采用MATCH函数得到编号“D2568”在区域B2:B7中的序号，返回值4作为OFFSET函数的行偏移量，带入到OFFSET函数中，=OFFSET($A$1,4,)。列偏移省略默认为0，扩展宽度和扩展高度省略默认为1 （即一个单元格），是不是就是A5单元格啦！ 绝技③：数据重置升级版——重排数据结构
在F2:H2区域输入公式后，下拉填充数据，就得到了右面的一维数据表。这种重排数据的问题，在实际工作中应该不少见吧！那么同学们会选择什么方法解决呢？作者反而觉得OFFSET函数的思路更加的简洁清晰。 函数解析： 第一步：得到连续出现的姓名 F2单元格函数： =OFFSET($A$1,INT((ROW(F1)-1)/3)+1,) 因为科目一共有三个，所以可以确定同一个姓名需要出现三次，那么当我们下拉F2单元格填充函数的时候，就要保证OFFSET函数的行偏移量每3个单元格的参数值都是一样的。这里就需要有一个“除数取整”的数学思维了，我们列个图来辅助说明：
从图中我们可以看出一组序号，通过INT((序号-1)/3)+1的转换后，就可以得到右侧的序列（如果有4个科目，那就把3改成4，依此类推）。将这个序列号放入OFFSET函数的第二参数，作为行偏移的标准，就可以得到我们姓名列的效果了。 第二步：给同一个人分配不同的科目 G2单元格函数： =OFFSET($A$1,,MOD(ROW(G1)-1,3)+1) 因为我们F列中的每个姓名都出现了三次，这就决定了语文、数学、英语这三个科目需要顺序、循环地罗列出来，同第一步的思路一样，用“除数求余”的数学思维来达到效果。
如上图所示，序号通过MOD函数的转换，得到一个顺序、循环罗列的序号。将该序号作为OFFSET函数的第三参数列偏移量，就可以顺序、循环的引出原数据的科目内容。 第三步：通过姓名和科目，模拟INDEX函数，在原数据中引出成绩 H2单元格函数： =OFFSET($A$1,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)) 分别用MATCH函数，得到数据在相关区域中所对应的序号，作为OFFSET的偏移量，分别放入第二、三参数中。从基准点A1单元格偏移后的单元格，就是我们需要的成绩值。 通过上面的内容，我们不难发现OFFSET函数，往往都是和MATCH函数连用。因为MATCH函数可以找到关键字在一个数列中的序号，所以我们经常利用这个函数来确定OFFSET函数的偏移量。 【编后语】如果你是刚学习OFFSET函数的同学，我相信这个函数对于你来说应该不好理解，可我还是建议你，一定要多练习，要学会它。不要怕出错，从错误中可以发现很多的问题，也可以巩固你对一个函数的认知。 ＊＊＊＊＊＊＊＊＊ 下一篇OFFSET函数的文章我们将进行一些高级的用法说明，绝对是你工作中会使用到的，所以如果你今天没有看懂，不要急，再看再体会，当然也可以来找老师，我们一起努力学会它。 ＊＊＊＊部落窝教育－excel函数OFFSET应用＊＊＊＊ |
|
|
来自： 部落窝教育BLW > 《部落窝excel/VBA》
