=LOOKUP(lookup_value,lookup_vector,result_vector)
=LOOKUP(查找值,首列升序排列的查找区域,结果区域) 需要说明的是,使用此套路时,查找范围的首列必须升序排列。 举个例子。 如下图所示,需要根据F:G的评分标准,对A:C数据区域的得分作评级。大于等于0小于60为及格,大于等于60小于80为及格,大于等于80小于90为优良,大于等于90小于95为优秀,大于等于95为优异……也就是将查询区域划分为了多个区间,并升序排列。 C2单元格输入以下公式并向下复制填充即可:
F列是升序排列的查找区域,G列是其对应的结果区域。LOOKUP从查找区域中寻找小于等于查找值的最大值,比如寻找81,查询区域中(F列)小于该值的最大值是80,于是先获取结果80,然后再返回80所对应的G列结果'优良'。 ……可能需要再次强调的是,这种区间查询方式,要求查找区域的首列必须升级排列!有朋友可能会说F列没有升序排列啊?F2单元格的0比F1单元格的'分数'小。这没事,LOOKUP聪明的很,你不说他都知道F1是不是标题行。 本例也可以使用以下公式: =LOOKUP(B2,F:G) 这是由于当查找区域是多列,同时又省略结果区域时,LOOKUP会默认查找区域的首列(本例为F列)为查找区域,同时默认其最后一列(本例为G列)为结果区域。 注意,咱们说的是最后一列,而不是第2列。 例如,在D2单元格输入以下公式可以返回H列的评级
LOOKUP默认首列F列为查找区域,末列H列为结果区域。
=LOOKUP(一个比查找范围内所有同类型值都大的值,查找区域,结果区域) 该套路固定返回查找区域最后一个同类型数据所对应的结果。 比如,查找A列最后出现的文本:
'座'是文本型数据里极大的值,比绝大部分常见的文本值均大,因而该公式返回A列最后出现的文本值。 =LOOKUP('座',A:A) 当省略结果区域时,LOOKUP默认查找区域即为结果区域。 再比如,查找A列最后出现的数值:
9^9是9的9次方,一个极大的数值,比绝大部分数值均大,因而该公式返回A列最后一个数值。 觉得这个套路没有什么实用价值? 打个响指,那咱们就把这个套路换个形式延伸一下…… 如下图所示,需要根据A:B列的数据源,查询D2单元格指定人名的考试成绩。 =LOOKUP(1,0/(A1:A10=D2),B1:B10) (A1:A10=D2)部分判断A1:A10区域的值是否等于D2,返回由逻辑值TRUE和FALSE组成的内存数组:
使用0除以该数组,0/TRUE结果为0,0/FALSE结果为错误值#DIV/0!,也就返回一个由0和错误值构成的内存数组: {#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!} LOOKUP自带天赋忽略错误值,而查找值1又比查找范围内所有的0都大,因而该公式可以直接返回最后一个符合条件的值所对应的结果。 该公式总结一下,也就成了经典的LOOKUP单条件查询套路:
…… 把这个套路再延伸一下,就可以成为'并且'关系的多条件查询经典套路(注意括号的个数和位置): =LOOKUP(1,0/((条件区域1=条件值1)*(条件区域2=条件值2)*…(条件区域n=条件值n)),结果区域) 举个例子。 如下图所示,需要根据A:C列数据源,计算同时满足E列期次和F列姓名的数据。
(A$1:A$10=E2)*(B$1:B$10=F2)部分,使用乘法运算,判断两个条件是否同时成立,返回由逻辑值TRUE和FALSE组成的内存数组。 然后还是使用0除以该数组,得到一个由0和错误值构成的单列内存数组。 1作为LOOKUP的查找值比查找范围内所有的数值均大,因而该公式直接返回最后一个符合条件的查询结果。 此外,和INDEX+MATCH函数不同的是,由于LOOKUP天生支持数组运算,所以尽管该公式执行了多项运算(一次性计算了多个值),也并不需要使用数组三键来结束公式的输入——这就对公式的编写和维护带来了极大的便利。 …… 继续延伸,把上述套路的乘法换做加法,就可以成为'或'关系的多条件查询套路: =LOOKUP(1,0/((条件区域1=条件值1)+(条件区域2=条件值2)+…(条件区域n=条件值n)),结果) 同样举个例子。 如下图所示,需要根据A:C列数据源,查询E列的姓名满足A列别称或B列全称的成绩。
(A$1:A$10=E2)+(B$1:B$10=E2)部分,使用加法的形式,判断两个条件是否至少有一条是成立的,返回一个有逻辑值TRUE和FALSE构成的内存数组,剩下的计算步骤就又回到了原来的轨道。 …… 依然延伸,搭配FIND函数,就可以成为模糊匹配的查询套路: =LOOKUP(1,0/FIND(条件区,条件值),结果区域) 依然举个例子: 如下图所示,A列是简称,B列是成绩,需要根据D列的全称查找相应的成绩。
FIND(A$1:A$8,D2)部分,判断A1:A8的值在D2单元格是否存在,如果存在,返回数字序号,如不存在,返回错误值,比如此例返回内存数组: {#VALUE!;3;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} 用0除以该数组,生成一个由0和错误值构成的内存数组……后面的计算步骤还是又回到了原来的轨道。 放飞你的思维,继续延伸,你就可以得出'并且'关系下的多条件模糊匹配套路:
换成加法,也就成了或关系多条件模糊匹配套路: =LOOKUP(1,0/(FIND(条件区1,条件值1)+FIND(条件区2,条件值2)),结果区域) …… 结束语 所以,当INDEX+MATCH说他们比VLOOKUP强大百倍时,作为VLOOKUP的大哥LOOKUP只会冷笑一声,拢一拢衣袖,心想,白痴,我比你俩的组合强大的又何止百倍…… |
|
来自: hercules028 > 《excel》