|
编按：哈喽，大家好！前面说到一直处于下风的LOOKUP，终于在第四回合的较量中，扳回一局。今天比拼的项目是横向和逆向查询。看样子，这似乎对纵向查询的VLOOKUP不太友好啊！LOOKUP又能否乘胜追击，再赢一轮呢？让我们拭目以待！ —————————————— 面对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一一匹配，找到等于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) 公式说明 本例中我们观察到查询值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) 公式说明 在该系列文章中，我们首次使用到经典的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结构。这是一个非常高能的函数用法，说来你可能不信，小花用了整整一周的时间来思考如何更好地讲解这一知识点，希望能给小伙伴们带来帮助！ ＊＊＊＊部落窝教育－excel查询函数技巧＊＊＊＊ 原创：小花/部落窝教育（未经同意，请勿转载） |
|
|
来自： 部落窝教育BLW > 《部落窝excel/VBA》
