Excel的匹配函数全应用-OK资源网

今天会和大家分享日常使用频率最高匹配函数用法，谈到匹配函数，首先想到的就是VLOOKUP，嗯，今天就是要分享 VLOOKUP和他的小伙伴们的应用。

本次长图文信息主要从VLOOKUP使用常见错误，VLOOKUP模糊匹配的应用以及VLOOKUP假模糊匹配的应用三个方向分享，至于什么是假模糊匹配呢，先卖个关子，今天晚些时候再介绍。

因为主角是VLOOKUP函数，先介绍一下VLOOKUP函数的基本用法，VLOOKUP函数常见的用法就是精确匹配，什么是精确匹配呢，就是根据某个单元格的内容返回相应的值。基本用法就是接4个参数，分别是，找什么-你要查找的内容，在哪里找-在哪个区域查找，这个区域的第一列需包含第一个参数的内容，返回第几列-返回的列是区域中的第几列，而不是表的第几列，空-最后一个参数很容易，被遗忘。重复一遍，找什么，在哪里找，返回第几列，最后一个参数为空。

第一部分：VLOOKUP 常见错误

本次课程不讨论拼写错误，直接和大家分享 VLOOKUP 常见错误一。第一个错误是什么呢？刚刚说VLOOKUP的精准匹配是四个参数，且第四个参数为空，因为第四个参数为空，导致编写函数的时候经常会遗漏掉，所以第一个常见错误就是漏掉了一个参数，如果只有三个参数，Excel会认为你是在做模糊匹配查找，他会给你返回值，但是却不是你真正需要的，这类错误很多时候是无法通过返回值快速知道错误的。先上图总结一下：

接下来将精准匹配已经编辑好公式的第一个单元格右下角双击，公式自动填充，发现后面两个显示的是n/a，在这里要和各位朋友说明，这个符号不代表公式错误，只是代表没有查到对应的返回值。

接下来就要找出问题在哪。目前我们看到明明有李智恩在，为什么无法匹配出来？送大家一句非常有哲理的一句话：“你看见的真的是你看见的吗？”你看见的相等的单元格真的相等吗？怎么判断呢？

在这里分享一个小技巧，以后每次V不出来的时候，这个小技巧都是排除故障的第一步。这个简单的方法就是用等号连接两个单元格，看返回值。

看到返回值是false，证明两个单元格不等。不管你看着多么相似，但是在Excel的逻辑里面都是不等的。

如果看着相同，但是结果不同，则双击鼠标进入单元格，查看是否有隐藏字符，如果发现有隐藏字符，把字符删掉即可，就可以有正确的返回值。

本案例的错误比较简单，只是一个常见字符问号隐藏了。工作中最常见的是空格，所以每次我拿到从数据库导出来的数据，在作分析之前，基本上第一步都是ctRl+F，查找内容输入空格，替换成什么那里什么都不输入，这样可以把Excel表中的空格批量替换。另一种更麻烦的错误就是乱码，换言之是Excel不可识别的乱码，此时我都会全选数据粘贴到UE中，然后把乱码替换掉再粘贴回Excel表。UE是一个比较好用的文本处理软件，在这里不是做软广，感兴趣的朋友可以上网百度一下，这个软件比电脑自带的txt阅读器和记事本都好用很多。

用同样方法判断左面的新垣结衣是否等于右面的新垣结衣，返回值是相等，那为什么没有返回正确的值呢？我们双击公式，发现他第二个参数，引用的区域变了，区域内没有要匹配的值，所以无法返回正确的。

在这里简单说一下绝对引用和相对引用的区别，我们工作中默认的都是相对引用，比如你找什么，第一个单元格输入找中岛美嘉，然后双击，发现第二个单元格找什么变成了石原里美，第三个单元格找的内容变成了新垣结衣。这种自动填充带来的变化就是相对引用的效果。但是VLOOKUP函数第二个参数是一个区域，我们不想让他有变化，那我们就需要变成绝对引用，什么是绝对引用呢，就是绝对的，无论你怎么变，这个参数都不会变化。

怎么实现绝对引用的应用呢？编辑好函数后，选中这个参数，然后按住F4键，恩，就是流星花园的那个F4。说完这句话感觉直接暴露了我是90后的事实。有的电脑特殊一点，如果你按F4没有效果，可以试试Fn+F4键，按后前面多了个美元的$符号，绝对引用设置完成。

最后再分享一个小技巧，很多朋友不知道什么时候使用绝对引用，什么时候用相对引用。你输入函数的时候，如果你选择的是一个区域，99.583%的概率应该用绝对引用。不好意思，这个百分比是我自己瞎编的。

最后一个错误就是我们不太经常遇到但是如果遇到就不太容易发现的。

发现左边的数字6并不等于右边的数字6，在这里和大家分享一个基础知识。常见的Excel 存储形式为时间、文本和数字，Excel 内核存储方式只有文本和数字两种，时间是归属于数字存储，在这里就不展开了。

很重要的一点是如果都是数字6，分别用文本和数字两种形式存储，二者是不等的，这个很重要。

那如何让他们相等呢，方法有很多，看一下gif图，在这里介绍我比较喜欢的一种，文本转数字就是进行一次数字运算，比如乘以1，加0均可；数字转文本就是进行一次文本运算用&连接符连接一个空的文本就可以强制转换了。从图片中可以发现excel也知道大家容易遇到这样的错误，所以一般情况下数字会右对齐，文本会左对齐，但是如果你把所有单元格都选择了居中对齐，excel自带的提示效果就消失了。

从数据库导出的数字很多时候都是用文本形式进行存储的，但是本案例是如何输入右面的数字6为文本的，介绍一个方法，先输入英文下的单引号，然后输入数字，此时的数字就是以文本形式存储的。

在这里再拓展一个应用，就是平时输入身份证号码至excel的时候，如果直接输入数字，excel会默认是数字形式存储，会导致身份证后几位信息缺失，所以正确输入身份证号码的方法就是先输入英文下的单引号，然后输入数字，这样子身份证号码就以文本形式进行存储了。

最后将常见的所有错误概况一下

第二部分：真模糊匹配应用

现在我有了女神的评分，然后就计划开展下一步动作，大于等于8分的可以约吃饭，小于8分大于等于6分的可以微信互动，小于6分的朋友圈点个赞表示存在即可。

这种根据某个数字区间返回一个固定值的时候就要使用模糊匹配啦。最常用的其实就是学生打分、绩效考评。模糊匹配与精确匹配用法有何不同呢？

恩，你知道微软不会随便弄第四个参数的，每个小参数都是有作用的，如果是模糊匹配，第四个参数是1，精确匹配就是空。模糊匹配就是这么简单。

那本案例如何实现呢？如何根据评分返回对应的行动呢？看一下动图。

同样是编写VLOOKUP函数，找什么呢，找我的评分，在哪里找呢，有一个评分对应行动的辅助表，返回第几列呢？返回第二列，最后一个参数是重点，要写1。大家看一下返回值是不是我们的预期值。

有人要问了，为什么查找8，返回的是第三行约吃饭呢？说一下Excel的实现逻辑。模糊匹配是找到和第一个参数最接近，但小于等于那个数对应的返回值。重复一下，模糊匹配的返回值是找到和第一个参数最接近，但是小于等于的那个数对应的返回值。比如新垣结衣，评分是8，在待查找列表和他最接近，且小于等于他的数字也是8，所以返回值是吃饭。石原里美，评分是4，和她最近进，且小于等于他的是0.所以返回值是朋友圈点赞，这样子就知道为什么模糊匹配能返回我们想要的结果了吧。好的，以上就是模糊匹配的用法。！

咳咳，当然不是啦，哪有这么容易放过各位朋友。模糊匹配的关键不是函数的用法，而是如何根据刚刚说了一串的文本条件（大于8分怎样，大于六分怎样的文本）转化为excel可识别的辅助列表。

大家看一下辅助表的创建过程。之前的每一个文字描述区间都转化为一行数据，辅助表有两个条件：1、每个数字区间的下限（最小值）作为第一列的判断条件，对应的返回值作为第二列2、第一列的数字必须从小到大排序（否则会出现什么错误可以自己试一下）！

第三部分：假模糊匹配

刚刚精确匹配是有一个先决条件，就是要查找和被查列表的内容必须是一致的，但是平时做数据处理，数据分析的朋友会知道，很多数据是包含关系，而不是简简单单的相等。

左边是女神的全名，右面只有部分文字，左面的文本包含右边的文本，怎么查找匹配出来呢？其实这种情况在工作中经常遇到。分享方法前先介绍Excel两个通配符。什么是通配符呢？就是无论什么都可以通通匹配上的字符！

英文下的问号代表的是可以匹配一个长度的任意字符；星号是可以匹配任意长度的任意字符。补充一点，如果Excel中遇到标点字符什么的，不知道用全角还是半角，就想想Excel是美国人弄得，他们不懂汉字，所以肯定是英文半角字符啦。还有通配符不单单可以用在本场景，还可以用在筛选、高级筛选、各类函数编写上。如果你听我刚刚说的使用场景没有一丝丝反应，那请关注我的后续课程哦。为什么需要用到通配符呢，因为左右两边不等，加上通配符，左右两边就相等了。

要查找含有雅这个字全名对应的评分，需要将第一个参数做个变化，将他前后各加一个*号，什么意思呢？就是前面可以有任意长度的任意字符，后面有任意长度的任意字符，中间包含一个雅字，能匹配上就可以。第二个参数找什么，第三个参数返回第二列，第四个参数为空，然后就实现我们的需求啦。

再分享一下使用英文问号的例子。看一下我们的需求，我们想把姓李但是两个字的查找出来，如果此时继续用星号返回的就是第一能姓李的人，无论是几个字，只要出现了就会返回。

问号因为是只有一个长度，所以用李加一个问号就可以把两个字的李娜对应评分匹配出来，而李智恩因为是三个字的，所以就没有匹配，如果想找姓李，三个字的，我想你们知道怎么实现的。

最后进入了本次微课的难点所在!

左面有长泽雅美，右面是“他是长泽雅美，美丽大方”，要查找的内容的一部分在被查找列表，怎么能将评分匹配出来呢？

在分享本方法之前，先和各位朋友分享两个小函数及他们的用法。

首先介绍一下Find函数，Find函数在文本函数中经常遇到，顾名思义，她是查找的意思，用于查找某个文本在另外一个文本中的位置。接三个参数，找什么，在哪里找，从第几个字符开始。返回值是数字，表示查找的内容在文本中处于第几个字符。如果第一个参数不在第二个参数之中，就会返回一个错误。

给朋友们演示一下函数的操作过程，大家看一下动图。有一段文本“阿忠凯是最年轻的帅气的讲师”，我想找帅气这个文本在不在很长的那段文本内，就编写函数=Find，找什么呢，找“帅气”这两个字，在哪里找呢？在很长的那段文本里面找，从第几个字符开始呢？从第一个字符开始，所以输入1。看一下返回值是9，证明帅气这两个字在那个很长的文本内，且第九个字符是帅字。在这么说明一下，Find函数中无论中英文字符，都算是1个长度的字符。

我找丑陋这两个字是否在长文本内，同样输入=find，找什么呢，找丑陋，在哪里找呢，在长文本内找，从第几个字符开始呢？从第一个字符。回车键后返回值报错，证明没有找到。

以上就是Find函数的基本用法。最后强调一下哈，我是不会承认我自己编写的这个例子，我平时为人除了逗逼，正常还是很谦逊低调内敛有涵养的！恩，我就是这么谦虚的。

说了这么久，重新放一下函数简介的图片。

介绍完Find函数，再和大家分享一下Lookup函数，Lookup何许人也，他其实是VLOOKUP函数的大表哥，两个人各有千秋。Lookup其实有很多经典高端用法，本期只介绍他众多用法中的一个，后续可以另行开课介绍，但是我是否有机会呢，就看今天各位是否点赞了！

本次把lookup函数当做最普通的匹配函数使用即可。分为三个参数，找什么，在哪里找，如果找到了返回什么。我们用lookup函数来实现最初的应用。想通过姓名找到对应明星的评分。本案例是想找中岛美嘉的评分，怎么实现呢？

这个方法是比较巧妙的，所以需要各位朋友睁大双眼观看哈！大家我们输入第一个参数找什么呢？输入的是1，在哪里找呢，是用0除以姓名那一列等于中岛美嘉，第三个参数是填写返回什么值，我们把评分的那一列选中。以上就是函数的基本编写原则。

接下来和大家分享函数的拆分原则和思路。我们从内向外拆分，先拆分B2:B7=E2，这个是姓名那一列等于中岛美嘉，有前面VLOOKUP排错，大家也知道两个单元格相等判断，返回的是真假，这样子除了第三行数据外，其它的返回值都是false，只有中岛美嘉返回的是tRue。在Excel中，TRue等同于1，False等同于0。

用0除以TRue和False的数组，则0除以1的时候还是返回0，但是正常分母是不可以为0的，所以其他值返回的都是错误。

回归Lookup大函数，找什么呢，找1，在哪里找呢，发现第二个参数是个数组，除了数组中的第三位是0，其它都是报错的。之前分享过，lookup在这里就是模糊匹配用法，所以电脑只能认为第三行数据是电脑找到的，则返回了评分数组中的第三位，也就是5。

以上就是Lookup函数和Find函数的基本用法！不知道各位是否已经迷茫、彷徨、怀疑人生了呢！

莫急，下面有两个函数组合嵌套来折磨你！

过了这么久，我相信各位朋友已经忘了我们最初的约定了，哦不，忘了我们最初的案例要求了。先看一下案例图片。

左面是姓名和评分列表，右面是很长的一段话，如果右面很长的一段话中的某个字段在左面，我就把对应的评分查找出来。我先实现本案例的应用。先给出解决方案，再解析函数。

各位朋友发现了没有，其实还是刚刚分享过的Lookup函数的应用，只不过第二个参数由单元格相等判断变成了Find函数的应用。

我接下来从函数内部至外部解析并分享一下。先将Find函数的内容代入，可以知道把B2至B7不同的名字放到了Find 函数的第一个参数，很长的他是长泽雅美，美丽大方放到了第二个参数，第三个参数是1.

对应的返回值大家可以看一下，前面新垣结衣，石原里美，中岛美嘉都都找不到，所以返回值是错误，只有第四个是长泽雅美能找到，所以返回值是3。

然后用0除以Find函数的返回值，发现只有第四个参数是0，其它的返回值依旧是错误。

最后将所有返回值代入Lookup函数，就可以看到lookup函数第一个参数是1，第二个参数是一个数组（前三个都是错误，第四个是0的数组），第三个参数就是不同的评分。刚刚分享过Lookup函数和模糊匹配用法类似，他是找到和第一个参数最接近，但是小于等于他的那个数对应的返回值。那第一个参数是1，比他小的整个列表只有0能与之匹配上，那返回值就是0对应的6,6作为了整个函数的返回值。