1
简称查全称
=VLOOKUP("*"&D2&"*",A:B,2,0)
=SUMIF(A:A,"*"&D2&"*",B:B)
2
全称查简称
既然有根据简称查全称的情况,自然也就有根据全称找简称的问题,这就是我要说的第二个例子。
如上图所示,A:B列依然是某班人员姓名和某科的高考成绩。D列是部分人员的全称,现在,需要根据A:B列的信息,查询D列人员的考试成绩。
E2公式如下:
=LOOKUP(,-FIND(A$2:A$10,D2),B$2:B$10)
公式解析▼
{#VALUE!;#VALUE!;-1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
LOOKUP忽略错误值,它的查找值为0,比查找范围内的任意数值均大,因而直接返回最后的数值所对应的查找范围(B2:B10)的数据(这个知识点可以参考LOOKUP篇函数教程)。
以D2单元格的空空女神为例,返回B4单元格的数据125。
……
说到这里,细心的同学也许已经发现,我举的两个例子,不管是简称查全称,还是全称找简称,都有一个最重要的规律,也就是每个简称都是全称完整的一部分,或者头部,或者尾部,或者中间。比如,大花是【大花美女】的头部,星光是【看见星光】的尾部。这么说,似乎有点奇怪,反正就是这么个意思。
但假设有这样一种情况,比如看见星光的简称是看星光,又如何通过简称找到全称看见星光呢?
之前的公式肯定是不成的。
打个响指,这就是我们要说的第三种情况。
3
乱序全字符串匹配
如上图所示,A:B列依然是某班人员和某科的高考成绩。D列是部分人员的简称,现在,需要根据A:B列的信息,在E列编写公式,查询D列人员的考试成绩。
比如,“透视表空空”和“讲透视表的那个空空”是匹配的——透 视 表 空 空,每一个字符都出现于字符串讲透视表的那个空空中。
E2公式如下:
=INDEX(B:B,MATCH(,MMULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26)),))
公式解析▼
-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10))
MID函数从D2单元格的第1(A)个位置至第26(AZ)个位置分别截取1个字符;FIND函数判断MID函数的返回结果在A1:A10单元格中是否存在,如果存在,返回位置序号,否则,返回错误值,最后通过ISERR函数搭配减法运算,将FIND函数的结果转化为-1和0,构成一个26列10行的矩阵数组。
MMULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26))
MMULT函数对矩阵数据进行计算,当D2单元格字符串的每一个字符都在A$1:A$10区域某个单元格中存在时,该行计算结果为0。
最后通过MATCH函数,取得MMULT函数返回结果首次为0的位置,再通过INDEX函数取值即可。
这个套路化公式的思路是判断所查询的字符串中每一个字符是否都在查找范围内存在,换句话说,就是玩一个文字归属计数游戏。
那么,思考题来了,既然有这种情况的简称找全称,自然也有这种情况的全称找简称,如果碰到下面这种情况,如何书写公式呢?
根据D:E列数据,在B列编写公式计算成绩..▼
侬自己想……
4
最大近似度匹配
第4个函数套路是处理最大近似度匹配……
如下图所示,需要根据A:B列的数据源,获取D列相关人名的语文考试成绩。
=IFERROR(VLOOKUP(D2,$A:$B,2,0),INDEX(B:B,RIGHT(MAX(MMULT(1-ISERR(SEARCH(MID(D2,TRANSPOSE(ROW(INDIRECT("1:"&LEN(D2)))),1),$A$2:$A$14)),ROW(INDIRECT("1:"&LEN(D2)))^0)/1%%+ROW($2:$14)),3)))
公式解析▼
公式首先采用精确匹配的方式进行匹配VLOOKUP(D2,$A:$B,2,0),匹配不到结果之后,再采用最大近似度匹配。
SEARCH(MID(D2,TRANSPOSE(ROW(INDIRECT("1:"&LEN(D2))
使用SEARCH函数判断D2字符串中的每一个值是否在数据源中存在(注意:SEARCH函数不区分字母大小写),生成一个矩阵数组。
然后使用MMULT函数统计矩阵中每一个字符串中字符出现的个数,再使用加权法,除以10000,同时用+ROW($2:$14)标记行号,末了使用MAX函数从中获取最大值。
最后使用INDEX函数根据行号按图索骥获取最终结果。
公式稍加修改,也可以实现指定标准的近似度匹配,比如80%的近似度匹配
……
别皱眉头,笑一笑。复杂的数组公式理解与否其实并不重要,重要的是……知道有这么回事,遇见问题直接复制公式粘贴使用——你开心就好,认真脸。
打个响指,最后再说两句,这年代拖堂的好老师真是不多了,给自己笔芯。
不管是采用模糊匹配,还是最大近似度匹配,都优先推荐先使用完全匹配查询,在查无结果的基础上,再选择其他匹配方式,以避免数据源存在完全匹配的结果,却优先匹配了模糊结果的问题。
此外,函数公式从来都不是万能的,VBA代码亦如是,正则也还是如此,关于简称和全称的查询和统计,最理想的情况自然还是制作一张匹配表,规范数据源,从源头上解决问题。
比如碰到下面这样的情况,若是没有个匹配表,那是真没辙。
看见星光 简称 帅哥
你说,如果没有个匹配表,谁知道看见星光和帅哥匹配呢?摊手,耸肩,看见星光自己天天照镜子都不知道啊?
拱手作别,下期再见。
联系客服