中国统计网

登录

首页

分类浏览

专题

专栏作者

问答

线下活动

企业招聘

app下载

投稿

注册




史上最全MATCH函数应用教程
头像 李锐 数据工具
数据工具

11.jpg


MATCH函数是Excel中广泛应用的查找引用函数,除自身具有返回查找数据的相对位置的功能外,MATCH函数还能结合众多的函数,在工作中展现Excel的强大威力,比如著名的INDEX+MATCH组合就能轻松搞定很多VLOOKUP的高级应用案例,可见MATCH函数无疑属于职场办公必备函数。


为了让大家认识MATCH函数那些不为人知的强大功能,本文贴合办公实际场景,整理了多种MATCH函数的应用方法,除了原理和基础性讲解外,还提供了使用场景介绍,帮助读者加深理解,便于在自己的实际工作中直接借鉴和使用。



由于正文字数限制,本教程给出Excel案例和公式解法,对公式的原理解析和详细说明请点击本文底部的“阅读原文”获取。


适用对象:本文面向的读者包括所有需要用到查找引用数据的用户,无论是初入职场的应届毕生生,还是在职场拼杀多年的白领精英,都将从本文找到值得学习的内容。


软件版本:本文的写作环境是Window10家庭版操作系统上的简体中文版Excel 2013。


本文绝大多数内容也适用于Excel的早期版本(2010、2007和2003),或者英文版和繁体中文版,所以读者大不必因自用版本不同而过多担心。



本文学习要点(强烈推荐收藏本教程)


1、MATCH函数语法解析及基础用法


2、MATCH函数根据模糊条件查找


3、MATCH函数查找特殊符号的方法


4、MATCH函数提取最后一个文本数据的行号


5、MATCH函数按条件提取最后一个数据


6、MATCH函数统计不重复数据个数


7、MATCH函数统计两列数据的相同个数


8、MATCH函数按多条件计数统计


9、MATCH函数根据用餐时间自动判断餐别


10、MATCH函数按条件多权重排序



01  MATCH函数语法解析及基础用法


MATCH用于返回要查找的数据在区域中的相对位置。下面介绍她的语法和参数用法。


语法


MATCH(lookup_value,lookup_array, [match_type])


用通俗易懂的方式可以表示为


MATCH(要查找的数据, 查找区域, 查找方式)


MATCH 函数语法具有下列参数:


第一参数:要在lookup_array中匹配的值。例如,如果要在电话簿中查找某人的电话号码,则应该将姓名作为查找值,但实际上需要的是电话号码。

第一参数可以为值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。


第二参数:要搜索的单元格区域。


第三参数:可选。数字 -1、0 或 1。match_type参数指定 Excel 如何将 lookup_value与lookup_array中的值匹配。此参数的默认值为 1。

下表介绍该函数如何根据 match_type参数的设置查找值。


对于非高级用户可以略过这部分直接看后面的示例,因为99%的情况下,第三参数只用0就足以应付日常工作需求啦!



wxid_s436oqlnlj4h22_1484108781524_29.png


MATCH函数返回匹配值在第二参数中的位置,而非其值本身。例如,MATCH("b",{"a","b","c"},0)返回 2,即“b”在数组 {"a","b","c"} 中的相对位置。


匹配文本值时,MATCH 函数不区分大小写字母。


如果 MATCH函数查找匹配项不成功,它会返回错误值 #N/A。


看了这么多文字,很多同学是不是有点晕?


上两个简单易学的示例,让你秒懂MATCH函数~


先来看个纵向查找定位的案例吧


12.jpg


C2输入以下公式。


=MATCH("王红强",A:A,0)


第一参数:要查找的数据,这里是“王红强”


第二参数:在哪里查找,这里是在A列查找,所以写A:A


第三参数:按完全匹配查找,写0


连起来就是在A列中查找和“王红强”完全匹配的数据,并返回其位置。


公式结果为8,表示找到数据并且数据在第8行。


明白了纵向查找,再来看个横向查找定位的案例


13.jpg


C2输入以下公式。


=MATCH("梨",1:1,0)


第一参数:要查找的数据,这里是“梨”


第二参数:在哪里查找,这里是在第一行中查找,所以写1:1


第三参数:按完全匹配查找,写0


连起来就是在第一行中查找和“梨”完全匹配的数据,并返回其位置。


公式结果为4,表示找到数据并且数据在第4列,即D列。


是不是很简单,现在你已经学会了MATCH函数最基础的应用啦


先休息一下吧,后续还有更精彩的应用案例等着你~



02  MATCH函数根据模糊条件查找


上一节中咱们学习了MATCH函数最基础的用法(按条件完全匹配查询),但在工作中很多时候会遇到查询条件并不那么明确,只能根据部分已知条件模糊查询。


下面就结合一个案例,展示MATCH函数根据模糊条件查找的功能。


14.jpg


上图中A列是各年份的产品批号,包含2016和2017年,现在要查询2017年的批号从哪行开始出现。


在C2单元格输入以下公式。


=MATCH("*2017*",A:A,0)



03  MATCH函数查找特殊符号的方法


上一节教程中,我们学习了MATCH函数按照模糊条件查询的方法,但其只适用于普通字符的字符串,当要查找的数据包含一些特殊字符(比如星号*问号?波浪符~)时,原公式结果就会出错了。


那么,遇到这几种特殊符号,我们如何应对呢?


下面就结合一个实际案例进行讲解。


15.jpg


上图中A列是数据源区域,放置了很多激活码,由数字和符号构成,现在需要查找激活码“*5~?”的位置。


如果使用常规的查找方法,比如以下公式,会返回错误结果。


错误公式:=MATCH("*5~?",A:A,0)


那么,应该如何修正这个公式,才能得到正确结果呢?


在C2单元格输入以下公式。


正确公式:=MATCH("~*5~~~?",A:A,0)



04  MATCH函数提取最后一个文本数据的行号


之前几节的学习中,我们掌握了MATCH的基本查找方法,根据模糊条件查找的方法以及查找内容包含特殊符号的处理方法。


今天,咱们一起来看一个逆向查找提取文本数据位置的案例。


16.jpg


上图中A:B列是数据源,放置着员工编号和业务员姓名,要提取B列最后一个文本所在行号。


在D2输入以下公式。


=MATCH(CHAR(1),B:B,-1)



05  MATCH函数按条件提取最后一个数据


之前几节教程中,我们掌握了MATCH函数的各种查找方法,还学到了逆向提取数据位置的处理方法,今天再来学一招条件查找下的MATCH技术。


老规矩,先上案例,对照讲解。


17.jpg


上图中左边是数据源,包含业务员的编号、姓名和销售额,现在要查找最后一个销售额大于50000的业务员所在行号。


在E2输入以下数组公式,按<Ctrl+Shift+Enter>组合键。


=MATCH(1,0/(C:C>50000))



06  MATCH函数统计不重复数据个数


在之前的教程里,我们介绍的都是单独应用MATCH函数的方法,其实MATCH函数跟其他函数配合使用,可以产生更大威力。


今天咱们先来介绍一个统计不重复值个数的方法。


上案例,看下图


18.jpg


在上图中左侧是数据源,包含值班日期和值班人员的姓名,其中值班人员有重复,即有的人值班过多天,现在要计算参与值班的人数(排重后)。


在D2输入数组公式,按<Ctrl+Shift+Enter>组合键。


=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(1:7)))



07  MATCH函数统计两列数据的相同个数


曾经有人问过我一个问题:


Excel中哪个函数的威力最大?


Excel中的什么功能最强大?


不知道你遇到这种提问会如何回答,我除了介绍了一些Excel的强大功能(可以去我的知了问答获取答案)外,还告诉他,关键在于Excel在谁的手里使用,其实没有最牛的功能,只有最牛的人!


有时候,看似很简单的函数,只要能灵活运用,平凡的函数也能用出花来。


这节课程里,咱们就来看一个比对两列数据差异的案例,出场的两位都是大家耳熟能详的函数,一个是COUNT,一个是MATCH,但别小看他俩,不信你先看看这个题目自己能否搞的定吧。


19.jpg


上图中左侧是数据源,包括某班级两门考试前10名学生的数学成绩和语文成绩,因为每科成绩单独排名,所以数学的前10名与语文的前10名学生并不完全一致。


现在要做的是,对比两个科目的前10名学生,找出相同的个数。


先给出公式,下面再分步详细解析。


在E2单元格输入数组公式,按<Ctrl+Shift+Enter>组合键。


=COUNT(MATCH(B2:B11,C2:C11,0))



08  MATCH函数按多条件计数统计


上一节中,我们讲解了一个MATCH函数配合COUNT函数,轻松对两列数据进行比对并且统计其中相同值个数的案例。


这一节中,我们再来介绍一个这两个函数搭配上场的精彩应用。


多条件计数的需求在工作中经常遇到,处理方式很多,下面就来看这个案例中MATCH是如何搞定多条件统计的。



上图中左侧是数据源区域,包含出库日期、出库产品以及出库的经销商名称,现在需要统计其中两个经销商(大地集团和希望集团)的苹果的出库次数之和。


老规矩,先给出公式,再解析原理。


在E2单元格输入数组公式,按<Ctrl+Shift+Enter>组合键。


=COUNT(MATCH(B2:B8&C2:C8,"苹果"&{"大地集团","希望集团"},0))



09  MATCH函数根据用餐时间自动判断餐别


今天,我们结合一个实际案例,介绍一下灵活应用函数的思路和精彩。



上图中左侧是数据源,黄色区域是需要输入公式计算的单元格。


数据源中已知用餐日期和时间,需要根据用餐时间和规则自动判断餐别。


规则如下:


1、早餐时间:5:30-8:30


2、午餐时间:11:30-13:30


3、晚餐时间:17:30-20:30


还是老规矩,先给出公式,在分步介绍原理及解析。


在C2输入以下公式,将公式向右、向下拖拉填充到黄色区域。


=IF(MATCH($B2*48,{11;23;35})=COLUMN(A1),"√","")



10  MATCH函数按条件多权重排序


从前面九节课的学习中,我们已经知道,MATCH函数不但可以提取数据位置,而且可以按照条件查询,比对两列数据的差异,今天再来结合一个案例,展现MATCH函数按不同权重多条件排序的技术。



上图中左侧是数据源区域,包含各个代表队在某次赛事中取得的金牌、银牌、铜牌数量,现在需要根据各个代表队的奖牌数量计算其总名次。


名次的排名规则如下:


1、首先按金牌数量降序排列。


2、金牌数量一致的代表队,比较其银牌数量。


3、金牌和银牌数量都一致时,比较铜牌数量。


其实这就是一个分权重的多条件排序问题。


老规矩,先给出公式,再解析原理。


选定E2:E8单元格区域,输入区域单元格数组公式,按<Ctrl+Shift+Enter>组合键。


=MATCH(MMULT(B2:D8*10^{4,2,0},{1;1;1}),LARGE(MMULT(B2:D8*10^{4,2,0},{1;1;1}),ROW(1:7)),0)


End.


作者:李锐(中国统计网特邀认证作者)


本文为中国统计网原创文章,需要转载请联系中国统计网(小编微信:itongjilove),转载时请注明作者及出处,并保留本文链接。

我要评论
验证码
  • 0
暂无评论

关注公众号

中国统计网微信公众号

微信公众号:cntongji

今日热议

专栏作者

  • 黄成明
    统计网特邀认证作者
    黄成明 ,零售数据分析研究者,拥有15年的销售及数据分析经验,历经美国强生公司、妮维雅公司、雅芳公司和鼎盛时期的诺基亚公司。目前是数据化管理的咨询顾问和培训师。他独立研发了基于周销售权重指数的零售管理模型,可以有效地进行目标管理、销售预测、客流预估、促销评估、销售预警等。
  • 傅一平
    统计网特邀认证作者
    浙江移动大数据中心 数据管理部经理 博士 毕业于浙江大学 10多年数据从业经验,专注于大数据采集、处理、建模、管理、变现及产业等研究
  • tommy
    统计网特邀认证作者
    数据落地应用探索者
  • 秦路
    统计网特邀认证作者
  • 数据海洋
    统计网特邀认证作者
    数据应用的实践者!
  • 数据小兵
    统计网特邀认证作者
    数据分析知名博主,SPSS自学教练,博客www.datasoldier.net
立即下载
版权所有: 统计网. Copyright © 2016 itongji.cn All Rights Reserved. 备案号:ICP备15042641号-3