Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less
本文介绍 Microsoft Excel 中 OFFSET 函数的公式语法和用法。
说明
返回对单元格或单元格区域中指定行数和列数的区域的引用。 返回的引用可以是单个单元格或单元格区域。 可以指定要返回的行数和列数。
语法
OFFSET(reference, rows, cols, [height], [width])
OFFSET 函数语法具有下列参数：
Reference 必需。 要基于偏移量的引用。 引用必须引用单元格或相邻单元格区域;否则，OFFSET 返回#VALUE！ 错误值。
Rows 必需。 需要左上角单元格引用的向上或向下行数。 使用 5 作为 rows 参数，可指定引用中的左上角单元格为引用下方的 5 行。 Rows 可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）。
Cols 必需。 需要结果的左上角单元格引用的从左到右的列数。 使用 5 作为 cols 参数，可指定引用中的左上角单元格为引用右方的 5 列。 Cols 可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）。
高度 可选。 需要返回的引用的行高。 Height 必须为正数。
宽度 可选。 需要返回的引用的列宽。 Width 必须为正数。
备注
如果工作表边缘上的行和 cols 偏移引用，OFFSET 返回#REF！ 错误值。
如果省略 height 或 width，则假设其高度或宽度与 reference 相同。
OFFSET 实际上并不移动任何单元格或更改选定区域；它只是返回一个引用。 OFFSET 可以与任何期待引用参数的函数一起使用。 例如，公式 SUM(OFFSET(C2,1,2,3,1)) 可计算 3 行 1 列区域（即单元格 C2 下方的 1 行和右侧的 2 列的 3 行 1 列区域）的总值。
示例
复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果，请选中它们，按 F2，然后按 Enter。 如果需要，可调整列宽以查看所有数据。
=OFFSET(D3,3,-2,1,1) | 显示单元格 B6 中的值 (4) | 4 |
=SUM(OFFSET(D3:F5,3,-2, 3, 3)) | 对范围 B6：D8 进行总和 | 34 |
=OFFSET(D3, -3, -3) | 返回错误值，因为引用的是工作表中不存在的区域。 | #REF! |
数据 | 数据 | |
4 | 10 | |
8 | 3 | |
3 | 6 |
Need more help?
上一期为大家介绍的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不太相关的，但这也是常态，在日常工作中，我们可能会遇到问题，需要结合各种函数，各种方法来完成它。所以我介绍多种方法给到大家，希望对大家的日常办公有所帮助。今天就介绍到这里了，赶快找摩拜，骑车回家啦^_^
微信公众号：SaveUTime
SUT学习交流群：615356012
关注公众号，提高效率，节约您的时间！