1、LOOKUPLOOKUP 先从最简单的开始:LOOKUP的数组用法LOOKUP(lookup_value,array)【LOOKUP(查找值,数组)】 为简单起见,下面只讨论array为一列的情况,如:LOOKUP(“AS“,$A1:$A10) 举例复习一下LOOKUP函数的最简单的情况,如下面的这个 12 12LOOKUP(“12“,$A8:$13) 12 23我们注意到:这个数组是按升序排列的; 34有两个12,一个是数值型的,一个是文本型的,而LOOKUP查找的是第二个即文本型的 AS DF 首先,我们要注意LOOKUP函数的以下几点特性: 1.正规的LOOKUP用法,要求数组的第一列(
2、或第一行)必须按升序排列 2.如果找到lookup_value,则正常返回; 3.如果在数组中找不到查找值lookup_value,则返回比它小的最大数值; 4.如果查找值lookup_value小于数组的最小值,则返回#N/A; 5.查找值的类型应与数组中数据的类型相匹配,对于不匹配的数据,LOOKUP会忽略 特别提醒:只只有有数组按升升序序排列,以上特性2-4才能显现 下一步,如果数组不按升序排列而是乱序的,会怎么样? 这就要先搞清楚,LOOKUP是按什么策略进行查找的 onkey在这方面作了一个很好的工作。 他认为LOOKUP是使用二分法进行查找的。具体查找流程如下: 这里,数组变成了两
3、种值:#DIV/0!和1 (这也是用1来除的目的!) 12根据特性5,LOOKUP函数忽略错误值,即只查找1 23而根据上述流程图,lookup_value1,所以它返回最后一个1,即A91的值 可见,2可以改成任一个大于1的数字,但不能1 比如,我们试把B84公式改为LOOKUP(1,1/),结果为S 这是错误的 原因?还是仔细看看流程图,根据流程图,它查找的结果确实应该是S! 顺便说一下,公式比公式快得多! = 至此,下面这个就比较好理解了: 多条件查询: 品名规格单价查询:品名规格要求:从左表中查出 品名=“AA“且规格=“1*10“的物品的单价 AA1*51AA1*10方法有很多如,S
4、UM数组,SUMPRODUCT,INDEX+MATCH,等等 AA1*104而这里用的是LOOKUP: AC2*32 AB2*55 4 LOOKUP(2,1/(A105:A110=E105)*(B105:B110=F105),C105:C110) AE2*52可以根据前面的分析,一层层的搞懂这个公式的含义。 AA4*823 与SUMPRODUCT相比,LOOKUP能返回文本,而SUMPRODUCT 不行 试试把C106改成一个字符看看? = 【附】 LOOKUP在查找重复值中的用法: “查找重复值”是一个老话题了,各式方法多彩纷呈。 最近bosco_yip推出一个LOOKUP的解法: (这个方
5、法不用数组公式,但要使用一个辅助单元格。) 品名 AAAA AAAE ACAB我们可以借此更好的理解LOOKUP,特别是体会 1/( ) 这种用法在各种场合中的效用! ABAC AE#N/A AA#N/A 先从最简单的开始:LOOKUP的数组用法LOOKUP(lookup_value,array)【LOOKUP(查找值,数组)】 有两个12,一个是数值型的,一个是文本型的,而LOOKUP查找的是第二个即文本型的 8月16日注: 已经发现此处不对。更大 的文本似乎应是“,代码 41385 公式目的:查找数组区域(A62:A66)中最后一个数值(非空、非文本、非逻辑值、非错误值) 1.lookup
6、_value9.99999999999999E+307 是excel允许输入的最大数字(见帮助) 2.让LOOKUP查找一个最大的数,而这个数比数组中所有的数都大,会怎么样? 根据上面的流程图:它会从中间开始查向下一直查到最后一个! 但最后一个依然小于9.99999999999999E+307 它就返回A(posi),即A66的值。达到了公式的目的! 式中,REPT(“龠“,255)表示255个“龠“,而这个“龠“是目前已知的最“大”的文本值。 顺便提一句,文本型的最小值是“,即空值;数字型最小值是-9.99999999999999E+307 =INDEX(A62:A66,MAX(MATCH(
7、“龠龠龠龠龠龠龠龠龠龠龠龠龠龠龠龠龠龠龠龠“,9.99999999999999E+307,A62:A66) 目的:与上式相同,也是查找A84:A92中的最后一个数值(非空、非文本、非逻辑值、非错误值) 在公式中鼠标选中这一段,按下F9,整个公式变为: LOOKUP(2,#DIV/0!;#DIV/0!;1;1;1;#DIV/0!;1;1;#DIV/0!,A84:A92) 这里,数组变成了两种值:#DIV/0!和1 (这也是用1来除的目的!) 根据特性5,LOOKUP函数忽略错误值,即只查找1 而根据上述流程图,lookup_value1,所以它返回最后一个1,即A91的值 可见,2可以改成任一个
8、大于1的数字,但不能1 比如,我们试把B84公式改为LOOKUP(1,1/),结果为S 这是错误的 原因?还是仔细看看流程图,根据流程图,它查找的结果确实应该是S! 要求:从左表中查出 品名=“AA“且规格=“1*10“的物品的单价 方法有很多如,SUM数组,SUMPRODUCT,INDEX+MATCH,等等 而这里用的是LOOKUP: LOOKUP(2,1/(A105:A110=E105)*(B105:B110=F105),C105:C110) 可以根据前面的分析,一层层的搞懂这个公式的含义。 与SUMPRODUCT相比,LOOKUP能返回文本,而SUMPRODUCT 不行 试试把C106改成一个字符看看? 我们可以借此更好的理解LOOKUP,特别是体会 1/( ) 这种用法在各种场合中的效用!