二维码 购物车
部落窝在线教育欢迎您!

VLOOKUP&LOOKUP双雄战(四):在横向和逆向查询上的血拼!

 

作者:小花来源:部落窝教育发布时间:2019-05-09 16:32:32点击:1558

分享到:
0
收藏    收藏人气:1人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!前面说到一直处于下风的LOOKUP,终于在第四回合的较量中,扳回一局。今天比拼的项目是横向和逆向查询。看样子,这似乎对纵向查询的VLOOKUP不太友好啊!LOOKUP又能否乘胜追击,再赢一轮呢?让我们拭目以待!

 

面对VLOOKUP的步步紧逼,LOOKUP终于在第四回合的较量中,凭借二分法遏住颓势。重整旗鼓后,LOOKUP吹响了反攻的号角,LOOKUP1/0结构正式登场,犀利进攻,看VLOOKUP如何应敌!

ROUND 05  横向查询

在数据查询中,我们也经常遇到这样的问题,查找范围分布在同一行而非同一列,即横向查询问题。如下图,我们要根据职位查找草帽海贼团中的人物姓名,应该怎么做呢?

5.1:横向查询——剑士索隆

LOOKUP:砍瓜切菜,轻松EASY

这种问题对VLOOKUP来说可能充满挑战,但对LOOKUP而言,简直是如砍瓜切菜般轻松EASY!!!

=LOOKUP(B7,B2:K2,B3)

5.2:横向查询——LOOKUP砍瓜切菜

公式说明

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)}

5.3:横向查询——VLOOKUP的贤臣

公式说明

TRANSPOSE函数是一个转置函数,其效果与选择性粘贴中的转置相同。我们通过TRANSPOSE函数将横向区域转置为纵向区域,然后再用VLOOKUP函数进行纵向查询。每一个成功的函数背后都有另一个优秀的函数,大概就是这个道理。注意,该公式为数组公式,输入公式后需按Ctrl+Shift+Enter三键能返回正确的结果。

当然,打仗亲兄弟,VLOOKUP也没必要事事亲力亲为,有时请个外援能解决的事,何必仰人鼻息。虽说这样做有违决斗精神,但成王败寇,过程,Who care?

=HLOOKUP(B7,$A$2:$K$3,2,0)

5.4:横向查询——VLOOKUP的强援

公式说明

HLOOKUP函数是VLOOKUP函数的孪生兄弟,其功能和用法与VLOOKUP如出一辙,差别仅在于HLOOKUP是横向查询,即它是在查询范围的第一行匹配目标值,而不是在第一列。本例中HLOOKUP函数将B7与查询区域第一行A2:K2一一匹配,找到等于B7H2,返回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)

6.1:逆向查询——VLOOKUPIF({1,0}

公式说明

本例中我们观察到查询值D2所需匹配的列恶魔果实在结果列人物的右侧,我们无法正常使用VLOOKUP“匹配首列返回第N来完成。所以此时解决问题的思路就是如何让B恶魔果实出现在A人物的左侧,进而将B列作为VLOOKUP查询范围的首列。解决这一问题的方法就是IF({1,0},....)结构。我们可以从下面三个方面来理解它:

1.IF函数是逻辑函数,它的基本语句是=IF(logical_test,value_if_true,value_if_false)

2.数值1表示TRUE0表示FALSE

3.{1,0}表示由10组成的数组。

综上,IF({1,0},....)的首个条件是TRUEFALSE组成的数组,而IF(TRUEIF(FALSE又分别返回value_if_truevalue_if_false,即IF({1,0},....)的返回值是value_if_truevalue_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)

6.2:逆向查询——LOOKUP(1,0/(条件)......结构

公式说明

在该系列文章中,我们首次使用到经典LOOKUP(1,0/(条件)......结构。不夸张地说,该结构是史诗级的,它主要用到以下知识点:

1.LOOKUP函数自带数组运算,无需按Ctrl+Shift+Enter。该结构中的条件通常表示为匹配列区域=目标单元格的形式,通过数组运算,相等返回TRUE,不相等返回FALSE。再用数字0除以运算结果,0/TRUE=0/1=0O/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,查询区域是N0组成的有序数组,所以,最后一个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小于目标值1,所以公式返回B2对应的A列人物名路飞

第六回合,把VLOOKUP吓出一身汗的逆向查询问题,却成了LOOKUP炫技的背景板,高下立现。

结束语

本文中,我们引入了查询函数圈不可不会的经典套路——LOOKUP 1/0结构。这是一个非常高能的函数用法,说来你可能不信,小花用了整整一周的时间来思考如何更好地讲解这一知识点,希望能给小伙伴们带来帮助!

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

VLOOKUP&LOOKUP双雄战(一)VLOOKUP&LOOKUP双雄战(一):VLOOKUP的漂亮开局

VLOOKUP&LOOKUP双雄战(二)《VLOOKUP&LOOKUP双雄战(二):LOOKUP剑走偏锋

VLOOKUP&LOOKUP双雄战(三)《VLOOKUP&LOOKUP双雄战(三):LOOKUP守得云开见月明