本
文
摘
要
上一期为大家介绍的INDIRECT函数，我想大家都已经掌握了。以后再也不怕多级下拉菜单任务啦。其实该函数的用处还有很多，今天我将结合新的函数OFFSET，分别完成一个案例。案例开始讲解之前呢，我们一起看看OFFSET函数的介绍吧！
一 函数介绍
说明：在Excel中，OFFSET函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用，返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。
语法：OFFSET(reference, rows, cols, [height], [width])
参数：
reference：必需。 要以其为偏移量的底数引用。 引用必须是对单元格或相邻的单元格区域的引用，否则OFFSET 返回 错误值 #VALUE!。
rows：必需。 需要左上角单元格引用的向上或向下行数。 使用 5 作为 rows 参数，可指定引用中的左上角单元格为引用下方的 5 行。 Rows 可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）。
cols：必需。 需要结果的左上角单元格引用的从左到右的列数。 使用 5 作为 cols 参数，可指定引用中的左上角单元格为引用右方的 5 列。 Cols 可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）。
[height]：可选。 需要返回的引用的行高，Height 必须为正数。
[width]：可选。 需要返回的引用的列宽，Width 必须为正数。
二 案例介绍
案例一：
从基点开始，然后偏移到指定位置，然后可以选择一个单元格，也可以选择一个区域，如下图中的基点B5，向下5行，再向右3列，就到单元格E10区域，也就是B1单元格87所在单元格。
公式=SUM(OFFSET(B5, 5, 3, 6, 4))从E10单元格向下6行，向右4列的区域，也等于公式=SUM($E$10:$H$15)，大家可以点击【公式】→【公式求值】逐步显示计算结果。
公式=SUM(OFFSET(B5, 10, 6, -6, -4))从H15单元格向左4列，向上6行的区域，也等于公式=SUM($E$10:$H$15)，所以和上面的计算结果一致，都为1262。
案例二：
根据学生姓名或学号，计算学生的总成绩，如果姓名不唯一，需要根据学号来计算。由于我这里的姓名唯一，所以可以直接通过姓名计算总成绩。
公式：=SUM(OFFSET(B1, MATCH($J$2, $B$2:$B$21, 0), 1, 1, 6))，该公式虽然结合了三个函数，但还是很好理解的，最外面的SUM函数可以先不看，因为只是求和。里面OFFSET函数是从基点B1开始，然后通过MATCH函数找到姓名对应所在的行，然后再找到该行向右的6个单元格组成的区域，最后再求和即可。
案例三：
如果给定一列，我们需要转换为几行几列，大家会如何去做呢？如下图：
如果上面的一列15个数字，我需要转换为3行5列，怎么去做呢？看看小S是如何解决的，请看动态图：
如上的动态中，有一个查找和替换可能有小伙伴不理解，我在这里解释一下。我先按下键盘上的Ctrl + H键调出查找和替换对话框，然后在查找内容中输入回车符，Ctrl+Enter或者Alt+（1，0 数字键）。替换为空格，为什么要替换成空格呢，因为在分列的时候，分列功能可以根据空格进行分列。
当然上图中还需要使用一个隐藏功能，也就是内容重排，默认情况下，Excel 2007以后版本的该功能是被关闭的，需要我们手动开启。具体操作办法是：“文件”→“选项”→“自定义功能区”，在“从下列位置选择命令”里选择“所有命令”，在下面的命令列表中拖动找到“内容重排”点击“添加”即可，单击确定退出。然后我们就会在快速工具栏中看到“内容重排”按钮。当然也可以像我在动态图中演示的一样，可以放到快速访问工具栏中。
案例四：
还是上面的问题，把一列15个数字转换为3行5列，这次我使用函数来实现。
OFFSET函数：=OFFSET($A$1,ROW(A1)*5+COLUMN(A1)-6,)
INDIRECT函数：=INDIRECT("A"&ROW(A1)*5+COLUMN(A1)-5)
INDEX函数：=INDEX($A:$A,ROW(A1)*5+COLUMN(A1)-5)
有兴趣的小伙伴可以亲自试试转换为5行3列显示，或者其他的哦^_^
案例五：
如果我们只是希望尽快的把一列转换为多行多列，可以使用另一种变通的方法加以实现，不用使用剪贴板，不需要使用OFFSET等函数即可。
三 我的总结
今天这一期是Excel办公常用的十大函数的最后一个函数，不知道大家跟随小S的学习，是否已经充分掌握所有技能了呢。还记得前面我给大家演示的动态图表吗？有兴趣的小伙伴可以自己先制作一下，结合我介绍的函数完成它。当然在后面我也会单独拿出来给大家介绍如何制作的。
其实今天的案例有好几个是和今天的主角OFFSET不太相关的，但这也是常态，在日常工作中，我们可能会遇到问题，需要结合各种函数，各种方法来完成它。所以我介绍多种方法给到大家，希望对大家的日常办公有所帮助。今天就介绍到这里了，赶快找摩拜，骑车回家啦^_^
SUT学习交流群：615356012
