LOOKUP函数的强大功能.docx

Lookup / Vlookup 之PK赛事 主持：andysky2008年6月23日星期一 首先需要声明三点：近期基于“lookup和vlookup谁更有用”在群中与人多次辩论。曾有多人提出一个观点：大多数人工作中用vlooukp，而不用lookup，这证明vlookup比lookup有用。然而我的看法，仅根据用的人多少来判断某工具的价值是不合逻辑的。就像N年前的日心说、月心说等等辩论结果，并非大多数人认为对的它就是对了，某些情况下，“真理掌握在少数人手中”；至于为什么“vlookup”的用户要多于“lookup”用户？是“vlookup”的功能强于“lookup”？是“vlookup”使用起来比“lookup”快捷？是“vlookup”完成同等功能，公式短于“lookup”？我的答案是：非也！完全是前辈们的误导！造成后进们都只知天下有“vlookup”，而忽略更强大的“lookup”的存在。打一个比方：某人第一次去某街道吃面，A店和B店的面都做得不错，但是所谓“不错”仍在程度上存在较大的差异：A店的质量差于B店。但是该人随机性地挑选店家时，选择了A店，他食后自认感觉不错，便向左邻右舍、远亲近邻们推荐。结果是什么？所有信任他的人都去A店吃面，不知道世上还在更好的B店也在卖面，只要少数只相信自己新自尝试过才下断语的人去吃完A店后再吃B店，从而喜欢上了B店的面。从而也造成了A店、B店两股支持者（Fans）们对面店评价的差异。为什么多数人只知道A店？第一个面食者的误导！在群中为函数的用法而争论是许可的，也是必要的。有人劝大家不要争，我个人认为这对于函数的深层理解不利。学Excel虽比不上学术辩论，但是纷纷表达自己对函数的理解，及参数功能、参数用法的不同观点，有利于后来者借鉴，少走弯路！闲话表过，让我们来看看vlooukp的二三事。我们知道，工作中制表需要录入大量数据；我们也知道，数据存于工作表并非只为打印，很多时候需要后期处理，也需要作为其它数据的参考引用；我们更知道，引用数据，可以用vlookup来完成。可以说，在大多数人的工作中，vlookup为我们建下了汗马功劳， vlookup实在功不可没！那么vlookup竟究是什么？它有些什么功能？有什么参数？让我来解剖它。函数名：vlookup功能：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。在 VLOOKUP 中的 V 代表垂直。如果需要在表格或数值数组的首列查找指定的数值，而返回指定行处的数值，请用Hlookup.语法与参数：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)图一vlookup参数图示 其中第一参数“lookup_value”代表需要在第二参数Table_array的第一列去查找的数值，可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。注意：查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。 “Table_array”是第二参数，为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。对区域引用时，可以引用整列,excel会自动判断使用区域。这一点可以用下图来说明：图二整列引用与实际区域引用第二参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。注意：如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE，table_array 不必进行排序。这一点可以从下图看出数据升序排列时和乱序排列时公式结果的差异。图三有序与乱序时公式的结果差异 第三参数“Col_index_num”为table_array中待返回的匹配值的列序号。当它为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数 VLOOKUP 返回错误值值 #VALUE!；如果col_index_num大于table_array的列数，函数 VLOOKUP 返回错误值 #REF!。 第四参数“Range_lookup”为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。“如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。“这是Excel帮助中的说词，然而这种叙述是有误的，可以从下图证明（事实上Excel的帮助IF函数的叙述也是错误的，不知是微软错误还是英文版汉化时出错）。图四Vlookup的精确查找与模糊查找下面讲解vlookup的实用价值。如图，根据产品数据计数其总价值，总价值数量*单价。看到这个表，我们首先想到的是vlookup引用单价表中的单价数据，然后用数量*单价得到总价。公式如下：=B2*VLOOKUP(A2,E:F,2,)省略第四参数表示精确查找目标值。因为单价表中的数据没有按升序排列，所以必须用精确查找。否则将引用错误的单价。图五引用单价计算总价值从上面，我们感受到vlookup给我们工作带来的便利，它真是工作的好助手！然而，我们也必须面临一个事实，它有着很多的缺点：u 查找对象区和结果存放区必须在同一个表中，跨表就无法完成！以图五为例，如果品名仍然在E列，而单价在另一个工作表的A列，那么Vlookup将一筹莫展，你的工作不得不进入手工操作，你面临的是无尽地加班u 查找对象区和结果存放区必须是同一个平面，即起止行完全一样，否则也只能返回错误值！你必须再面临手工操作，再次加班的境地；u Vlookup的查找对象区和结果存放区是用一个参数来表示的，而不能分开成不同的数组。这就好像一件连衣裙，你喜欢它的衣服部分，而讨厌裙子时也只能无尽的忍受。因为你没有选择的余地。不像独立的衣服、裤子那么任意搭配。这限制了Vlookup的用武之地；u Vlooup只能查找目标值在查找对象右边的数据，如果目标在左边，vlookup再次展现它的无助；u Vlookup只能垂直查找，如果价格表是按行排列的，Vlookup依然显得那么可怜。而对于以上列举的种种疑难杂症，lookup都可以轻松解决！笑傲江湖，舍lookup其谁？那么lookup是什么呢？函数名：lookup功能：LOOKUP 有两种语法形式：向量和数组。向量：为只包含一行或一列的区域。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域，则可以使用函数 LOOKUP 的这种形式。向量形式的语法与参数（有三个参数）：LOOKUP(lookup_value,lookup_vector,result_vector) 第一参数“Lookup_value”为函数 LOOKUP 在第一个向量中所要查找的数值。它可以为数字、文本、逻辑值或包含数值的名称或引用。 第二参数“Lookup_vector”为只包含一行或一列的区域。它也可以为文本、数字或逻辑值。提示：Lookup_vector 的数值必须按升序排序：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。（如果区域的数据不是升序排列呢？当然也可以变通实现正确引用，后面将讲到！） 第三参数“Result_vector”只包含一行或一列的区域，其大小必须与 lookup_vector 相同。举例说明：在下表中，根据姓名到参照表中的查对姓名对应的成绩，第一参数是查找对象，第二参数查找区域，第三参数是目标存放区域。图六用lookup轻易获取成绩数据数组：lookup的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。如果需要查找的数值在数组的第一行或第一列，就可以使用函数 LOOKUP 的这种形式。数组形式的语法与参数（只有两个参数）：LOOKUP(lookup_value,array) 第一参数“Lookup_value”为函数 LOOKUP 在数组中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。注意：如果函数 LOOKUP 找不到 lookup_value，则使用数组中小于或等于 lookup_value 的最大数值。如果 lookup_value 小于第一行或第一列（取决于数组的维数）的最小值，函数 LOOKUP 返回错误值 #N/A。 第二参数“Array”为包含文本、数字或逻辑值的单元格区域，它的值用于与 lookup_value 进行比较。函数 LOOKUP 的数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。不同之处在于函数 HLOOKUP 在第一行查找 lookup_value，函数 VLOOKUP 在第一列查找，而函数 LOOKUP 则按照数组的维数查找。而LOOKUP等于包含了vlookup函数和hlookup函数的功能之和。它既可以横向查找，又可以纵向查找。那么它怎么取代hlookup，什么时候取代vlookup呢？这里数组的维数来决定。 如果数组所包含的区域宽度大，高度小（即列数多于行数），函数 LOOKUP 在第一行查找 lookup_value。取代hlookup。 如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数 LOOKUP 在第一列查找 lookup_value，取代vlookup。 函数 LOOKUP 总是选择行或列的最后一个数值。下面通过实例来体现数组形式的用法。下图中，lookup只需要两个参数，在目标区域的首列查找数据，返回最后一列中对应的值。公式：=LOOKUP(A2,D$1:E$9)相当于=VLOOKUP(A2,D$1:E$9,2,FALSE)图七数组形式的lookup查询1那么如果参照区域是横向的呢？vlookup无功而返，lookup是否力所能及？答案是肯定的，而且简单、快捷。请看下图：如果参考区域是横向的，那么公式仍然只需要两个参数，lookup会智能地判断在首列查找还是在首行查找。公式：=LOOKUP(A2,E$1:L$2)相当于=HLOOKUP(A2,E$1:L$2,2,FALSE)图八数组形式的lookup查询2如果参考区域是多行多列，返回结果不确定呢？lookup可以胜任吗？当然可以。只用利用offset函数产生一个动态的参数即可。图九lookup动态查找数据上图中使用这个公式：=LOOKUP(A2,OFFSET(D$1,1,8,MATCH(B$1,$D$1:$H$1,)其中match函数用来计算当前查找的值在目标区域中排位第几，然后将它做为offset函数的参数，产生一个动态的区域，而lookup总是返回最后一列的目标值。【双击打开实例文件】【双击打开动画演示】另外，大家是否有另一个担忧如果区域不是升序排列的呢？lookup还可能完成查找吗？当然，答案仍然是肯定的。下面举例说明：图十lookup处理乱序排列的数据从上图中，我们可以看到，参考数据区的姓名以乱序排列，而lookup是能不查找乱序数据的。怎么办？变通方式是：将查找区域值变成一个只有两种类型的值，例如五个数据#DIV/0!,0, #DIV/0!, #DIV/0!, #DIV/0!，其中四个是错误值，一个是0.那么我们将lookup的第一参数设为0，就可以找到它对应的位置。但是如何将当字变成错误值和0呢？请看思路：“张三丰”=“张三丰”,它的结果是true，表示两者相等，而0/true的结果等于0；“张三丰”=“李四光”结果为false，表示两者不等，而0/false的结果等于错误值，因为除法中0不能做为除数，强制计算那么结果就为#DIV/0!。有了思路就可以动手搭建公式了。1.以图十为例，首先用A2=D2:D9，它得到的结果是FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE2.然后用0/ (A2=D2:D9)得到一串错误值和一个唯一的零值：#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!3.有了这个结果所有问题都解决了，直接在其中查找0就行了。最后公式是：=LOOKUP(0,0/(A2=D$2:D$9),E$2:E$9)大家可以把这个普通公式当做一个定理来用，当目标区不是升序排列时（是升序也可以用）：=lookup(0,0/(查找值=查找区域),目标存放区域)说了这么多，还没有真正进入主题。Lookup与Vlookup之PK赛事进入初赛！我将用十个例证说明lookup的功能大于vlookup+hlookup的功能总和。 例证一：根据左省查找右省会，反之亦然。图十一 左右逢源=LOOKUP(0,0/(A1=A6:A9),B6:B9) =LOOKUP(0,0/(A2=B6:B9),A6:A9)如果是升序排列，可以=LOOKUP(A1,A6:B9)来简化公式【双击打开】以上两个公式可以左右查询（未排序，精确查找）。而vlookup办不到 例证二：根据上省查找下省会，反之亦然。图十一 下上求索=LOOKUP(0,0/(A1=B5:E5),B6:E6)=LOOKUP(0,0/(A2=B6:E6),B5:E5)以上两个公式可以上下查询（未排序，精确查找）。而vlookup办不到。 例证三：根据省查找省会，两个区域起止行不相同。图十二 贵践不分（起始不同步也可查）=LOOKUP(0,0/(A1=A6:A9),D8:D11)=LOOKUP(0,0/(A2=D8:D11),A6:A9)以上两个公式可以对不同起止列查找（未排序，精确查找）。而vlookup办不到； 例证四：根据省查找省会，两个区域在不同工作表。图十三 民族大溶合（不同工作表体系也可查）=LOOKUP(0,0/(A1=A6:A9),省会!A2:A5)=LOOKUP(0,0/(A2=省会!A2:A5),A6:A9)以上两个公式可以对不同工作表体系进行查找（未排序，精确查找）。而vlookup办不到； 例证五：计算A1单元格中字符串的最后一个数字所处的位置图十四 排位抽签=LOOKUP(9,-MID(A1,ROW(INDIRECT(1:&LEN(A1),1),ROW(INDIRECT(1:&LEN(A1)此公式可以计算A1单元格中字符串的最后一个数字所处的位置，而vlookup办不到； 例证六：根据图中ABCD等等代表的值计算A1表达式的结果。图十五 中英转换=SUMPRODUCT(LOOKUP(M