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

来源:优页文档 作者:优页文档

编按:

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

 

面对VLOOKUP的步步紧逼,LOOKUP终于在第四回合的较量中,凭借二分法遏住颓势。重整旗鼓后,LOOKUP吹响了反攻的号角,LOOKUP的1/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一一匹配,找到等于B7的H2,返回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:逆向查询——VLOOKUP与IF({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表示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)

图片

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

公式说明

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

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

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

结束语

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


资讯来源说明:本文章来自网络收集,如侵犯了你的权益,请联系:puerppt#163.com进行删除。

PPT模板

  • 蓝色卡通蓝色防溺水安全教育PPT
  • 蓝色儿童安全教育学生防溺水主题PPT
  • 蓝色中小学防溺水安全教育PPT
  • 重温入党誓词党史学习教育党课PPT
  • 微党课重温入党誓词永远忠诚于党PPT
  • 入党誓词宣誓入党仪式背景PPT
  • 纪律处分条例pptPPT
  • 党课党建主题班会纪律处分条例pptPPT
  • 党课党员教育管理工作条例PPT
  • 预防溺水pptPPT

Excel模板

  • 单据粘贴单11
  • 出差申报单11
  • 出差申报单(2)1
  • 付款申请单11
  • 付款申请单(3)1
  • 入库单1
  • 领料单
  • 销售明细单1
  • 销售明细单(3)
  • 付款申请单(2)1

Word模板

  • 面试招聘
  • 社会招聘面试审批流程
  • 电话营销销售人员的招聘面试流程
  • 求职招聘登记表及面试记录表
  • 校园招聘面试结果评价表
  • 校园招聘求职者面试报告示范模板
  • 招聘面试评分记录表
  • 招聘面试评估表
  • 招聘面试评价表
  • 招聘面试记录表
优页文档

优页文档(www.youyedoc.com)是一家专注于分享高质量的PPT模板、Excel表格、Word模板的下载网站,1000+各行业优质设计师每日更新200+优质办公文档模板,满足各行业办公需求。海量office文档制作教程,致力于打造国内最大最权威的办公文档下载一站式服务平台

Copyright © 2021-2024 www.youyedoc.com. All Rights Reserved.   粤ICP备2021116258号

本站所有文档资源来源于互联网或作者上传,仅供学习研究使用,版权归作者所有,请勿用于商业用途,如果用于商业用途请联系作者,如果因为您将本站资源用于其他用途而引起的纠纷,本站不负任何责任。

如果本站内容无意中侵犯了您的版权,请联系youyedoc,我们会及时处理。