offset和match函数的使用方法 offset+match函数 收藏 0

这个函数不简单，能看懂的都是Excel高手，可以轻松实现动态求和

今天跟大家分享一下offset函数的使用方法，这个函数在Excel中的应用非常的广泛，可以用于制作动态图表，构建动态的引用区域，实现动态求和等等，可以说是Excel高手必备的函数之一。

想要从零学习Excel，这里↑↑↑↑

Offset函数:offset是一个偏移函数，它以一个单元格为基点进行偏移得到一个新的偏移区域

语法：=OFFSET(reference, rows, cols, [height], [width])

我们需要注意的是offset函数获取的是一个数据区域，并不是一个具体的结果，比如在这里我们想要使用offset函数获取下图黄色的数据区域，只需要将函数设置为OFFSET(A1,3,2,4,2)即可

这个函数就表示OFFSET函数会以A1单元为基点，先向下偏移3行来到A4单元格（张飞）然后再向右偏移2列来到C4单元格,随后以C4单元格为原点在行方向向下引用4行数据，在列方向向右引用2行数据，这个就是函数的偏移过程。

因为offset获取的是一个数据区域，我们无法直接看到这个偏移的结果是不是正确的，这个时候可以考虑将offset函数嵌套在sum函数中，对偏移结果区域求和，通过求和结果来判断偏移结果是不是正确的。

跟大家拆分一个offset最经典的应用实例，就是实现动态求和，如下图，我们想要通过更改姓名与月份，获取这个人在指定时间段的数据之和。

在这里我们只需要将公式设置为：=SUM(OFFSET(A1,MATCH(A16,A2:A11,0),MATCH(C16,B1:I1,0),1,MATCH(D16,B1:I1,0)-MATCH(C16,B1:I1,0)+1))，即可得到正确的结果。

跟大家简单地讲解下这个函数的参数与运算过程

第一参数：A1，这个就是offset函数偏移的基点

第二参数：MATCH(A16,A2:A11,0)，它的作用是查找嫦娥这个姓名在数据源中姓名这一列的位置，结果为6，就表示基点会从A1开始向下偏移6行，来到A7单元格

第三参数：MATCH(C16,B1:I1,0)，他的作用是查找开始月份（5月）在表头这一行中的位置，结果为5，就表示函数会A7单元格开始向右偏移5行，来到F7单元格，也正好是嫦娥5月份的数据

第四参数：1，因为在这里数据仅仅只有1行，我们将行数设置为1即可

第五参数：MATCH(D16,B1:I1,0)-MATCH(C16,B1:I1,0)+1)，用于确定引用数据区域的列数，首先我们使用MATCH(D16,B1:I1,0)来查找一下结束月份（8月）在表头的位置，他的结果是8，MATCH(C16,B1:I1,0)计算的是开始月份（5月）在表头，二者相减结果为3，但是在表格中5月到8月它是包含4列数据的，所以我们还需要为结果加1才可以得到正确的偏移区域。

最后我们再使用offset函数对这个偏移的结果求和就会得到嫦娥5月到8月的数据之和，更改姓名与月份，这个时间就会自动的发生变化，非常的方便，如下图所示

以上就是今天分享的全部内容，offset函数虽然参数比较多，结果比较抽象，但是我们只需要理解它各个参数的含义，也是可以轻松掌握的。

我是Excel从零到一，关注我，持续分享更多Excel技巧

你离学好MATCH函数只差这篇文章！就算加班2个钟也要学好它

MATCH函数是Excel主要的查找函数之一，在某些方面有特殊的应用，比如说和VLOOKUP/INDEX/OFFSET/INDIRECT这几个函数结合使用，可以解决很多问题。下面是一些MATCH函数的用法举例。

一、MATCH函数语法。

作用：返回指定数值在指定数组区域中的位置。

语法：=MATCH(lookup_value, lookup_array, [match_type])。

=MATCH(要查找的值，指定查找的区域或数组，查找的匹配方式)。

注意：这里匹配方式可选的参数为-1，0，1。默认为1。

如果为1或省略，表示MATCH函数会查找小于或等于查找值的最大值。

如果为0，表示MATCH函数会查找等于查找值的第一个值。

如果为-1，表示MATCH函数会查找大于或等于查找值的最小值。

二、VLOOKUP+MATCH组合。

下图中，我们要根据H2单元格的姓名和I2单元格的季度查找到对应的销量，用VLOOKUP+MATCH函数组合，J2单元格公式我们可以这样写：=VLOOKUP(H2,$B$1:$F$7,MATCH(I2,$B$1:$F$1,0),0)。

三、INDEX+MATCH组合。

下图中，我们要根据H2单元格的姓名查找到对应的部门，用INDEX+MATCH函数组合，I2单元格公式我们可以这样写：=INDEX($B$1:$B$7,MATCH(H2,$C$1:$C$7,0))。

四、OFFSET+MATCH组合。

下图中，我们要根据H2单元格的姓名和I2单元格的季度查找到对应的销量，用OFFSET+MATCH函数组合，J2单元格公式我们可以这样写：=OFFSET(B1,MATCH(H2,$B$2:$B$7,0),MATCH(I2,$C$1:$F$1,0))。

五、INDIRECT+MATCH组合。

下图中，我们要根据H2单元格的姓名和I2单元格的季度查找到对应的销量，用INDIRECT+MATCH函数组合，J2单元格公式我们可以这样写：

=INDIRECT(ADDRESS(MATCH(H2,$B$1:$B$7,0),MATCH(I2,$B$1:$F$1,0)+1))。

六、日期转季度。

下图中我们要根据A列的日期判断属于哪个季度的，B2单元格的公式我们可以这样写：=MATCH(MONTH(A2),{1,4,7,10})&\”季度\”。然后将公式下拉至B13单元格即可。

七、按指定数字重复。

下图中，我们要根据B列指定的次数重复A列的字符，D1单元格的公式我们可以写成：

=INDEX(A:A,MATCH(ROW()-1,SUMIF(OFFSET(B$1,,,ROW($1:$5)),\”<>\”))+1)&\”\”，该公式输入完之后需要按“Ctrl+Shift+Enter”三键结束公式，然后将公式下拉至D10单元格即可。

八、计算不重复产品个数。

下图中，我们要根据A列的产品名称，计算出不重复的产品个数，相同的产品只记为1个，D4单元格的公式我们可以写成：=SUM(N(MATCH(A2:A10,A2:A10,0)=ROW(1:9)))。该公式输入完之后需要按“Ctrl+Shift+Enter”三键结束公式。

九、提取不重复值。

下图中，我们要根据A列的产品名称，提取出不重复的产品产品，相同的产品只提取1个，F2单元格的公式我们可以写成：=INDEX(A:A,SMALL(IF(MATCH(A$2:A$10,A$2:A$10,0)=ROW($1:$9),ROW($2:$10),4^8),ROW(A1)))&\”\”。该公式输入完之后需要按“Ctrl+Shift+Enter”三键结束公式。然后将公式下拉至D10单元格即可。

MATCH函数的用法，今天就讲到这里了，如果您还知道有其它的用法，可以在评论区留言跟大家一起分享哦！

觉得文章不错请转发和点赞，给小编鼓励和支持，谢谢您！

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com