js match函数的使用方法 js中match函数 收藏 0

函数功能的智能应用——MATCH函数

文/孔述

许多函数单独来看，基本看不出有何意义，但在特定环境下（与其他函数组合、镶嵌、配合），用得好却能发挥绝妙的作用。因此，对函数的真正理解，要从其原始的字面意思表达和具体应用中才能体会。

Match字面看，其义大致如下：

作为英语单词，主要用作名词、动词。作名词时意为“比赛，竞赛；火柴；敌手，旗鼓相当的人；相配的人”，作动词时意为“比得上，敌得过；使成对，使相配，使相称”等。那么，在函数使用意义呢？

“悠然心会，妙处难与君说”

B列是一组数据（OK、2、你好、☯）。现在，要查找目标（E2单元格）的实际所在的位置。

当目标锁定☯时，自动定位的位置在B6；

当目标锁定“你好”时，自动定位的位置在B5；

其他数据同理。目标格是活动输入窗口，即可以下拉选择为B列中“OK、2、你好、☯”任一一个数据。

上图中目标的定位原理，就是利用函数=MATCH(E3,B2:B6,)+1。说明：E3的值在B2：B6区域中的位置，+1是因为第一行空出但需要计入；当然也可用：=XMATCH(E3,B:B,)。XMATCH相当于MATCH更新版本。

也就是说，只要目标锁定，函数就能准确查到目标所处的位置。这与我们日常使用的定位系统何其相似？！只要输入目的地，利用GPS/北斗定位系统，只要有信号就能定位到！

这样看，MATCH函数与其说是查找，不如说是“定位”。拓展一下，这样的数据很多，若给出目标（条件），函数岂不能自己根据你指定条件去智能定位？

若此思路的逻辑成立，那么，凡是需要查找或定位的需求都可以用MATCH函数解决。

根据姓名（H2）查找成绩（I2），等级查询等同理。

齐十一，结果为11，自动判断分数区间。并以绿色高显。

继续随机查—“孙六”，对应数据瞬间显示并高显，并验证（正确，以红色显示）

这里就是利用了match的定位功能以及与OFFSET的强大组合。函数公式=OFFSET($A$1,MATCH(H2,$B$3:$B$11,0)+1,0)

MATCH 一般与INDEX、OFFSET组合完成强大的定位、查找甚至智能计算功能，即俗称的“黄金搭档”。

其基本用法：

“此中有真意，欲辨已忘言。”

既然MATCH函数有强大定位功能，那么，若按照指定条件也能定位指定区间。进而对该区间定进行一系列计算功能，从而实现一些快速的识别、汇总、统计、比对及其他智能功能。

1-12月份各税费统计表，要快速计算任意税种、任意起止（正常起始月份小于终止月份）月份间的汇总数。只需下图点选指定月份、指定税种，即可瞬间完成。

起始月份点选

J4=SUM(OFFSET(A1,MATCH(I2,A2:A13,0),MATCH(K2,B1:E1,0),(MATCH(J2,A2:A13,0)-MATCH(I2,A2:A13,0)+1))) 实质为=sum（区域），这个区域就是由MATCH与OFFSET 共同完成。因此，重点来看看这个区域怎么完成。

OFFSET由参数（原点或原参照区，行，列，高，宽）完成区域的构建；原点一般选A1；关键在行，列，高，宽的选取；这个问题可以简化归结到由MATCH完成，仅以行来说明；

行：MATCH(I2,A2:A13,0)，根据指定条件I2，在月份列中的位置确定；

列：MATCH(K2,B1:E1,0)，根据指定条件K2，在税种列中的位置确定；

但上面仅完成简单的点移动，尚未构建成面（区域）。要构建成区域，还要指定移动高度。

高：原理为起止月份之差。

止月：由J2决定MATCH(J2,A2:A13,0) 上例=9

起月：由I2决定MATCH(I2,A2:A13,0) 上例=1

起止月位置之差（要算头尾，如9-1=8，但1-9月实际为9个月，因此要加1）即：(MATCH(J2,A2:A13,0)-MATCH(I2,A2:A13,0)+1)

宽：仅为一税种，故略。

下图为一时间段内1-6区销售情况汇总表：

问题：快速统计指定起止日期间各区域的销售情况

=SUM(INDEX(B2:B29,MATCH($J$1,$A2:$A29,0)):INDEX(B2:B29,MATCH($J$2,$A2:$A29,0)))

对于1区：INDEX(B2:B29,MATCH($J$1,$A2:$A29,0))=130

INDEX(B2:B29,MATCH($J$2,$A2:$A29,0))=114

区域求和=SUM(B7:B21) 其他区依此类推。公式右拉即可。

那么，如果同时有双变量（时间段、区域）的情况又是怎样呢？

V2=SUM(OFFSET(A1,MATCH(T2,A2:A29,0),MATCH(V1,B1:G1,0),(MATCH(U2,A2:A29,0)-MATCH(T2,A2:A29,0)+1))) 原理同选定起止月份的税种求和，不再赘述。

纸上得来终觉浅，绝知此事要躬行

要理解和灵活运用MATCH函数，还得多结合实际，多用，多体会。看下例：

小型微利企业所得税享受条件智能判断

小型微利企业的判定标准主要包括以下三个条件‌：

1、‌年度应纳税所得额不超过300万元‌：这是衡量企业盈利情况的重要指标。

2、‌从业人数不超过300人‌：这一条件限制了企业的规模，确保其不会过于庞大。

3、‌资产总额不超过5000万元‌：这一条件进一步限制了企业的资产规模，确保其不会过于庞大或复杂。

这些标准旨在支持小型微利企业的发展，帮助它们在竞争激烈的市场环境中生存和成长。小型微利企业在税收、融资等方面通常能享受到一些优惠政策，以减轻其负担，促进其发展

其实，如何判断需要数据支撑。那么这些数据是可以定位计算的办法求出结果。

以从业人数为例：

实质为第1季对应的C5开始，到指定季度的Cn，区域求和，再求平均数，手工计算比较简单但是繁琐。函数公式则一拖到底。C6=ROUND(SUM(INDIRECT(\”$C$5:\”&ADDRESS(5,MATCH(C2,$C$2:$F$2,0)+2)))/C2,0)

公式解释：

如上所言区域求和：sum(C5:Cn)，即关键在这个n确定这个求和区间。注意到在第5行，用地址函数表达（ADDRESS）：=ADDRESS(5,MATCH(C2,$C$2:$F$2,0)+2)) 根据C2-F2在季节中的位置（第5行横向移动）来确定。由于从C列始，实际位置须加上（A、B这2列）故+2.

最终区间表达（引用函数INDIRECT）：=INDIRECT(\”$C$5:\”&ADDRESS(5,MATCH(C2,$C$2:$F$2,0)+2)) 简化为C5:Cn

资产计算同理，不再赘述。

这样当人数、资产、应纳所得额全部计算出来后，判断可交给逻辑函数(IF)。

C2=IF(AND(C6<300,C10<5000,C11<3000000),\”符合\”,\”不符合\”) 由于三条件必须同时符合。故用AND。

同时满足为“符合”，否则“不符合”。两本条件格式“不符合”以红色显示。

这样，只须拖动鼠标即可完成智能判断。

Vlookup函数的这7个应用技巧都不掌握，那就真的Out了

查询引用，用到最多的函数为Vlookup，但你真的会用吗？其实，Vlookup函数除了常规的查询引用外，还有多种使用技巧

一、Vlookup函数：功能及语法结构。
功能：在指定的数据范围内返回符合查询要求的值。
语法：=Vlookup(查询值，数据范围，返回值列数，匹配模式)。
其中匹配模式有两种，分别为“0”或“1”。其中“0”为精准匹配，“1”为模糊匹配。
目的：查询“商品”的“销量”。
方法：
在目标单元格中输入公式：=VLOOKUP(H3,B3:C9,2,0)。

二、Vlookup函数：反向查询。
目的：根据“编码”查询“商品”名称。
方法：
1、在目标单元格中输入公式：=VLOOKUP(I3,IF({1,0},C3:C9,B3:B9),2,0)。
2、Ctrl+Shift+Enter填充。
解读：
公式中的IF({1,0},C3:C9,B3:B9)的作用为形成一个以C3:C9为第一列、B3:B9为第二列的临时数组。

三、Vlookup函数：多条件查询。
目的：根据“商品”名称和“型号”查询“销量”。
方法：
1、在目标单元格中输入公式：=VLOOKUP(I3&J3,IF({1,0},B3:B9&C3:C9,D3:D9),2,0)。
2、快捷键Ctrl+Shift+Enter填充。
解读：
1、当有多个查询的条件时，用连接符“&”连接在一起，对应的数据区域也用“&”连接在一起。
2、公式中IF({1,0},B3:B9&C3:C9,D3:D9)的作用为形成一个以B3:B9和C3:C9为第一列，D3:D9为第二列的临时数组。

四、Vlookup函数：多条件反向查询。
目的：根据“商品”的销售“地区”查询对应的“销量”。
方法：
1、在目标单元格中输入公式：=VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,D3:D9),2,0)。
2、快捷键Ctrl+Shift+Enter填充。
解读：
当有多个条件和数据范围时，对应的值用符号“&”连接。

五、Vlookup函数：屏蔽错误值。
目的：无查询匹配结果时，不显示错误代码#N/A， 将单元格的置空。
方法：
在目标单元格中输入公式：=IFERROR(VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,D3:D9),2,0),\”\”)。
解读：
Iferror函数的作用为：判断一个表达式是否有误，如果有误，则返回本身，否则返回指定的值；语法结构为：=Iferror(表达式,表达式有误时的返回值)。

六、Vlookup函数：批量查询。
目的：根据“商品”名称批量返回相关信息。
方法：
在目标单元格中输入公式：=VLOOKUP($I3,$B$3:$F$9,MATCH(J$2,$B$2:$F$2,0),0)。
解读：
1、巧妙利用Match函数获取返回值对应的列数。
2、注意参数的引用方式，不变为“绝对”、变为“相对”，也可以是“混合引用”。

七、Vlookup函数：一对多查询。
目的：根据对应的值返回多个查询结果。
步骤1：插入辅助列。
方法：
1、在“商品”列的前面插入“辅助列”。
2、输入公式：=COUNTIF(C$3:C3,C3)。
解读：
利用Countif函数统计“商品”在对应的区域出现的次数。

步骤2：根据“商品”名称查询对应的“型号”。
方法：
1、在目标单元格中输入公式：=IFERROR(VLOOKUP($J$3&ROW(A1),IF({1,0},C3:C9&B3:B9,D3:D9),2,0),\”\”)。
2、快捷键Ctrl+Shift+Enter填充。
解读：
公式主要运用了“逆向查询”和“屏蔽错误值”两种主要方法。

步骤3：根据“商品”名称和“型号”查询对应的销量。
方法：
1、在目标单元格中输入公式：=IFERROR(VLOOKUP($J$3&$K3,IF({1,0},C3:C9&D3:D9,E3:E9),2,0),\”\”)。
2、快捷键Ctrl+Shift+Enter填充。
解读：
公式主要应用了多条件的方法。

结束语：
文中从实际应用出发，针对不同的应用场景，对Vlookup函数的7种典型用法做了详细的解读，对于使用技巧，你Get到了吗？如果亲有更多的关于Vlookup的用法，欢迎在留言区留言讨论哦！

查询之王Vlookup函数都不掌握，那就真的Out了

查询引用，在Excel的使用中是非常广泛的，其中的Vlookup函数可以称之为查询引用之王，不仅仅是因为函数功能的强大，更重要的是提高了工作效率。

一、Vlookup函数功能及语法结构。

功能：在指定的数据范围中返回符合要求的值。

语法结构：=Vlookup(查询值，查询范围，返回列数，匹配模式）。

其中匹配模式为“0”或“1”。“0”为精准匹配，“1”为模糊匹配。

目的：查询销售员对应的销量。

方法：

在目标单元格中输入公式：=VLOOKUP(H3,B3:D9,3,0)。

解读：

H3为查询值，B3:D9位查询范围，返回第3列的值，“0”为精准匹配模式。

二、Vlookup函数逆向查询。

一般情况下的查询操作都是“从左向右”查询，如果要“从右向左”查询，也是可以实现的。

目的：利用工号查询出对应的姓名。

方法：

在目标单元格中输入公式：=VLOOKUP(I3,IF({1,0},C3:C9,B3:B9),2,0)。

解读：

通过观察可以发现，和常规的查询不同之处在于多了IF({1,0}，其作用就是重新组成新的查询范围。

三、Vlookup函数多条件查询。

目的：从销售员的销售流水中查询指定地区的销售额。

方法：

1、在目标单元格中输入公式：=VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,E3:E9),2,0)。2、用Ctrl+Shift+Enter填充。解读：

1、多条件查询时，需要用“&”连接查询值，形成一个查询差选条件。2、利用IF函数对Vlookup函数的第二个参数进行重组，形成新的数据查询区域。3、因为数据区域是数组形式，所以需要用Ctrl+Shift+Enter填充。

四、Vlookup+Iferror函数：巧妙隐藏无匹配的值。

在多条件查询的过程中，容易遇到数据匹配时没有查询结果的情况，此时返回#N/A，如果要对其隐藏，就需要用到Vlookup+Iferror函数的组合形式。

方法：

1、在目标单元格中输入公式：=IFERROR(VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,E3:E9),2,0),\”\”)。

2、Ctrl+Shift+Enter填充。解读：

Iferror函数的作用为：判断指定的表达式是否有误，如果有误，返回指定的值，否则返回表达式本身。而公式当中为了隐藏错误代码用了\”\”，暨空值。

五、Vlookup函数“一对多”查询。

目的：根据销售员的姓名或工号查询出对应的销售记录。

方法：

5-1、插入辅助列。

方法：

1、在“姓名”列的前面插入一列，并命名为“辅助列”。

2、在“辅助列”中输入公式：=COUNTIF(C$3:C3,$J$3)。

5-2、查询销售员对应的地区。

方法：

在目标单元格中输入公式：=IFERROR(VLOOKUP(ROW(A1),B$3:G$9,6,0),\”\”)。

5-3、查询销售员对应的销量。

方法：

1、在目标单元格中输入公式：=IFERROR(VLOOKUP($J$3&$K3,IF({1,0},C3:C9&G3:G9,F3:F9),2,0),\”\”)。

2、Ctrl+Shift+Enter填充。

5-4、Vlookup函数一对多差选效果展示。

方法：

选择相应的销售员，其对应的“地区”和“销量”自动更新。

六、Vlookup+Match组合函数：精准定位查询。

在查询引用中，经常会遇到返回指定列数据的情况，此时，必须用Match函数对其所在的列进行精准定位。

方法：

在目标单元格中输入公式：=VLOOKUP($I3,$B$2:$F$9,MATCH(J$2,$B$2:$F$2,0),0)解读：

Match函数的作用为：返回指定值在指定范围中的相对位置，其语法结构为=Match(定位置，定位范围，定位模式)。

七、Vlookup+Column组合函数：批量查询。

如果需要批量返回数据，则可以使用Vlookup+Column组合函数来实现。

方法：

在目标单元格中输入公式：=VLOOKUP($I3,$B$3:$F$9,COLUMN(C3)-1,0)。

解读：

Column函数的作用为：返回对应单元格的列数，“-1”为修正值。

八、Vlookup+Sum组合函数：查询指定值的和值。

方法：

1、在目标单元格中输入公式：=SUM(VLOOKUP($L$3,$B$3:$I$9,{3,4,5,6,7,8},0))。

2、Ctrl+Shift+Enter填充。

九、Vlookup函数+通配符查询。

方法：

1、在目标单元格中输入公式：=SUM(VLOOKUP($L$3,$B$3:$I$9,{3,4,5,6,7,8},0))。

2、Ctrl+Shift+Enter填充。

结束语：

本文从Vlookup函数的功能及语法结构入手，对Vlookup函数的使用技巧进行了详细的解读，并和其它函数进行组合，是一篇不可获取的Vlookup函数实用干货哦！

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