谈恋爱不香吗？非要来学excel，佩服！

你好，我是刘卓。欢迎来到我的公号，excel函数解析。明天就是520了，你是准备撒狗粮呢还是吃狗粮呢？

最让我感到意外的是，有个小伙伴说：“与找对象相比，更愿意成为excel大佬。”而且她还是个女的。我就奇了怪了：难道谈恋爱没有excel香吗？

今天来分享下offset函数的用法，它是个引用函数，功能十分强大，尤其是动态引用数据和多维引用。

-01-

函数说明

offset以指定的引用为参照系，通过给定偏移量返回新的引用。返回的引用可以是单个单元格或者是单元格区域，也可以指定返回的行数和列数。语法如下，有5个参数：

OFFSET(reference, rows, cols, [height], [width])

第1参数Reference: 必需。作为偏移量参考的起始引用区域。 该参数必须是对单元格或相邻单元格区域的引用。否则, OFFSET返回#VALUE! 。

第2参数Rows：必需。相对于起始引用区域的左上角单元格，向上或向下偏移的行数。如果是正数，向下偏移；如果是负数，向上偏移。如果省略，必须用半角逗号占位，默认为0，也就是不偏移。

第3参数Cols：必需。相对于起始引用区域的左上角单元格，向右或向左偏移的列数。如果是正数，向右偏移；如果是负数，向左偏移。如果省略，必须用半角逗号占位，默认为0，也就是不偏移。

第4参数height：可选。要返回的引用区域的行数。如果是正数，向下扩展；如果是负数，向上扩展。如果是0，返回错误值#REF!。

第5参数width：可选。要返回的引用区域的列数。如果是正数，向右扩展；如果是负数，向左扩展。如果是0，返回错误值#REF!。

注意：

1.如果偏移后的引用区域超过了工作表的边缘, 则offset返回#REF! 。

2.如果省略height或width，则视为其高度或宽度与第1参数的高度或宽度相同。

3.如果第2到第5参数不是整数，则会截尾取整。

-02-

示例解释

下图的公式返回的引用区域为C4:D6。其中A1单元格为参照的基点，rows参数为3，表示以A1为基点向下偏移3行，到A4单元格；cols参数为2，表示从A4开始向右偏移2列，到C4单元格。height参数为3，width参数为2，表示以C4为左上单元格，向下向右扩展3行2列，得到C4:D6的区域。

下图公式中的rows参数省略，用逗号留出位置，相当于0，也就是行不偏移。cols参数为2，从A1向右偏移2列，到C1。height参数为3，width参数为2，就是以C1为左上单元格，向下向右扩展3行2列，得到C1:D3的区域。

下图的公式中，第1参数是多行多列的区域A1:B2，第4，5参数省略，也就是height和width省略。那么height和width就是第1参数的高度与宽度，也就是2行2列。

以A1:B2的左上单元格A1为基点，向下偏移4行，到A5；然后从A5向右偏移2列，到C5。最后以C5为左上单元格，向下向右扩展2行2列，得到C5:D6的区域。

下图的公式中，第2到第5参数都是负数。以E8为基点，向上偏移3行，到E5；然后从E5向左偏移2列，到C5；最后以C5为右下单元格，向上扩展3行，向左扩展2列，得到B3:C5的区域。

-03-

具体应用

1.动态汇总销售额

下图是某公司销售部各业务员在不同月份的销售数据。根据A21单元格的姓名，和B21，D21单元格的起止月月份，汇总该业务员在指定期间的销售额。

在F21单元格输入下面的公式：

=SUM(OFFSET(A11,MATCH(A21,A12:A18,),B21,,D21-B21+1))

MATCH(A21,A12:A18,)查询A21的姓名在A12:A18的位置，结果为4。

offset以A11为基点，向下偏移4行到A15。向右偏移的列数为起始月份值2，也就是从A15向右偏移2列到C15。扩展的行数省略，与A11的行数一样，还是1。扩展的列数为终止月份减起始月份再加1，也就是3。最后得到的区域是C15:E15。用sum求和计算出结果。

这个公式的起始月份不能超过终止月份，否则会出错。如果想要起始月份大于终止月份也能得到正确的结果，公式该怎么写？

2.统计新入职员工前三个月培训时间

下图是某公司1~6月新入职员工的培训记录，新员工从入职第1个月开始，每月需要进行培训。计算各员工前三个月的培训共时长。

比如第1个新员工卢广汉的入职时间是5月，前3个月就是5，6，7月。但是数据只截止到6月，所以只算5月和6月的总时长。

再比如曾海就是算1，2，3月的总时长。简单来说就是从第1个非空单元格开始，向右扩展3个单元格，但又不能超过6月。

在H26单元格输入下面的公式，按ctrl+shift+enter三键结束。

=SUM(OFFSET(A26,,MATCH(,0/B26:G26,),,3) B26:G26)

MATCH(,0/B26:G26,)查询第一个非空单元格的位置，得到5。

offset以A26为基点，rows参数省略就是行不偏移，列向右偏移5列到F26。height参数省略，就是A26的高度，扩展1行。扩展的列数为3列。得到的区域是F26:H26。此区域超出了B26:G26的范围，如果直接用sum求和，会在H26产生循环引用而无法正常计算。

所以用offset返回的区域F26:H26和B26:G26使用交叉引用的方式(两个区域中间用空格相连)，得到它们的交集F26:G26，最后用sum求和。

此题还可以用公式=SUM(OFFSET(E26:G26,,-COUNTA(B26:D26)))来完成。

3.多行多列转单列

下图左表是5行2列的区域，现将其转为单列，结果如D列所示。在D37单元格输入公式=OFFSET(A$37,(ROW(A1)-1)/2,MOD(ROW(A1)-1,2))，向下填充。

做这种题目用的是倒推法，根据结果往回推。D37的结果引用的是A37，D38的结果引用的是B37，···，D45的结果引用的是A41，D46的结果引用的是B41。如F列所示。

F列的单元格是怎么来的呢？可以用offset从A37偏移而来，A37是从A37偏移0行0列，B37是从A37偏移0行1列等等。如G列所示。

最后我们发现要偏移的行的数字是有规律的，0,0,1,1,2,2,3,3,4,4。可以用int函数得到。要偏移的列的数字也是有规律的，0,1,0,1,0,1,0,1,0,1。可以用mod函数得到。最后组合起来就ok了。

链接：

https://pan.baidu.com/s/1XiQl2q_Pm0Rl2xNaWPppMw

提取码：mbgm