文章来源: 小花学Excel
面对VLOOKUP的步步紧逼,LOOKUP终于在第四回合的较量中,凭借二分法遏住颓势。重整旗鼓后,LOOKUP吹响了反攻的号角,LOOKUP的1/0结构正式登场,犀利进攻,看VLOOKUP如何应敌!
ROUND 05 横向查询
▼
在数据查询中,我们也经常遇到另外一个问题,即查找范围分布在同一行而非同一列,这就是横向查询问题。如下图,我们要根据职位查找草帽海贼团中的人物姓名,我们会怎么做呢?
LOOKUP:砍瓜切菜,轻松EASY
这种问题对VLOOKUP来说可能充满挑战,但对LOOKUP而言,简直是如砍瓜切菜般轻松EASY!!!
=LOOKUP(B7,B2:K2,B3)
公式说明:
LOOKUP相对于VLOOKUP来说是更自由的函数,它对查询区域进行二分法匹配,并不要求查询区域是纵向排列的。用LOOKUP来完成横向查询时,其语句和纵向查询并无区别。但在横向查询时,目标区域可以简写为结果区域的首个单元格。这是因为,当LOOKUP的第三个参数被简写时,它会自动横向扩展结果区域直至与查询区域等长!
也就是说,本例中的=LOOKUP(B7,B2:K2,B3)与=LOOKUP(B7,B2:K2,B3:K3)等同。
VLOOKUP:内有贤臣,外有强援
面对LOOKUP的挑衅,不可一世的VLOOKUP函数绝不轻易认输,横向查询硬上也要上!请出转置函数TRANSPOSE来帮忙。
{=VLOOKUP(B7,TRANSPOSE($A$2:$K$3),2,0)}
公式说明
TRANSPOSE函数是一个转置函数,其返回结果与选择性粘贴中的转置相同。我们通过TRANSPOSE函数将横向区域转置为纵向区域,然后再VLOOKUP函数进行纵向查询。“每一个成功的函数背后都有另一个优秀的函数”,大概就是这个道理。注意,该公式为数组公式,需按Ctrl ShIFt Enter进行计算才能返回正确的结果。
当然,打仗亲兄弟,VLOOKUP有时也并不必要事事亲力亲为,有时请个外援能解决的事,何必仰人鼻息。虽说这样做有违决斗精神,但成王败寇,过程,Who care?
=HLOOKUP(B7,$A$2:$K$3,2,0)
公式说明
HLOOKUP函数是VLOOKUP函数的孪生兄弟,其功能和用法与VLOOKUP如出一辙,差别仅在于HLOOKUP是横向查询,即它是在查询范围的第一行匹配目标值,而不是第一列。本例中,HLOOKUP函数将B7与查询区域第一行A2:K2一一匹配,返回与H2所在行对应的查询区域第2行单元格H3值。
第五回合
横向查询,VLOOKUP虽然有TRANSPOSE这样的帮手为内应,更兼亲兄弟HLOOKUP函数这样的外援,仍是难以扭转败局。此番,LOOKUP胜在简单、胜在可缩写,胜在横纵皆宜!!
ROUND 06 逆向查询
▼
前述应用场景中,查询区域都有一个共同点,即结果区域或结果行列始终在查询区域或匹配行列的右侧或下方,这很符合VLOOKUP的要求,它总能匹配首列返回指定列。但很多时候,匹配区域并不总是在结果区域的右侧,例如:
VLOOKUP:天赋不足,嵌套来补
此时,VLOOKUP函数是不是就黔驴技穷了?当然不是,IF({1,0},....)了解一下!
=VLOOKUP(D2,IF({1,0},B2:B9,A2:A9),2,0)
公式说明
本例中我们观察到查询值D2所需匹配的列“恶魔果实”在结果列“人物”的右侧,我们无法正常使用VLOOKUP“匹配首列返回第N列”来完成。所以此时解决问题的思路就是如何让B列“恶魔果实”出现在A列“人物”的左侧,进而可以作为VLOOKUP查询范围的“首列”。解决这一问题的方法就是IF({1,0},....)结构。我们可以从下面三个方面来理解它:
1,IF函数是逻辑函数,它的基本语句是=IF(logical_test,value_if_true,value_if_false);
2,数值1表示TRUE,0表示FALSE;
3,{1,0}表示由1和0组成的数组。
综上,IF({1,0},....)的首个条件是TRUE和FALSE组成的数组,而IF(TRUE和IF(FALSE又分别返回value_if_true和value_if_false,即IF({1,0},....)的返回值是value_if_true和value_if_false组成的数组。
接下来,小花套用上图具体分解一下。
IF({1,0},B2:B9,A2:A9)
={IF(1,B2:B9,A2:A9),IF(0,B2:B9,A2:A9)}
={IF(TRUE,B2:$B9,$A2:A9),IF(FALSE,B2:B9,A2:A9)}
={B2:B9,A2:A9}
={"橡胶果实","路飞";"花花果实","罗宾";"黄泉果实","布鲁克";"人人果实","乔巴";"手术果实","罗";"磁铁果实","基德 ";"霸王龙果实","X·德雷克";"城堡果实","卡彭·贝基"}
它的作用是为VLOOKUP构建一个虚拟的查询范围B2: A9,其中B2:B9在A2:A9的左侧。紧接着,VLOOKUP发挥所长,完成查询工作。
LOOKUP:木有压力,纯属炫技
当然,这类所谓逆向查询,对于LOOKUP函数是不存在任何困扰的,查询区域和结果区域分离,给了LOOKUP很大的便利。但是LOOKUP的另一属性经常困扰使用者,那就是其自带的模糊查询要求——查询区域必须升序排列,否则公式几乎都会出错!这一烦恼使得很多小伙伴倾向于使用VLOOKUP来解决问题。借着逆向查询这个轻松取胜的回合,小花要为LOOKUP正名:首列不升序,一样可以查询,LOOKUP没有死角!
=LOOKUP(1,0/(B2:B9=D2),A2:A9)
公式说明
本例是该系列文章中,我们首次使用到经典的LOOKUP(1,0/(条件)......结构。不夸张地说,该结构是史诗级的,它主要用到以下知识点:
1,LOOKUP函数自带数组运算,无需按Ctrl Shift Enter。该结构中的条件通常表示为“单元格区域=目标单元格”的形式,通过数组运算,相等返回TRUE,不相等返回FALSE,0/TRUE=0/1=0,O/FALSE=0/0=#DIV/0!;即LOOKUP(1,0/(条件)......结构在计算过程中,参数2查询区域是由0和#DIV/0!组成的数组{0,#DIV/0!,#DIV/0!,0...};
2,LOOKUP的匹配过程会自动忽略错误,即参数2运算过程中的#DIV/0!将被忽略,仅保留所有的0,即{0,0,0...};
3,LOOKUP采用二分法查询,返回最后一个小于或等于目标值的匹配列值所对应的结果;LOOKUP(1,0/(条件)......结构的查询目标值为1,查询区域是N个0组成的有序数组,所以,最后一个0所对应的值即为公式返回结果。反推,即LOOKUP(1,0/(条件)......结构总是返回最后一个满足条件的值。
本例中的条件为B2:B9=D2,仅B2等于D2,返回TRUE,其余返回FALSE。即0/(B2:B9=D2)的查询区域结果为{0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},LOOKUP忽略错误后仅有B2对应的结果为0,公式返回B2对应的A列人物名“路飞”。
第六回合
把VLOOKUP吓出一身汗的逆向查询问题,却成了LOOKUP炫技的背景板,高下立现。
本文中,我们引入了LOOKUP的1/0结构这个查询函数圈不可不会的经典套路。这是一个非常高能的函数用法,说来你可能不信,小花用了整整一周的时间来思考如何更好地讲解这一知识点,希望能给小伙伴们带来帮助!
Copyright © 2024 妖气游戏网 www.17u1u.com All Rights Reserved