|
LOOKUP函数的查询原理详解 LOOKUP的查询功能非常厉害，经常用它进行多条件查找、区间查找、最接近值查找、匹配查找、跨文件查找等，但它的运算原理却不是三言两语就能说明白的，本文对此进行了详解解释。 按惯例先来一个示例： 【例】下图中左表和右表只有第5行的序号不同，但在第10行查找B002对应的数据时结果却不同。甚至左表中查找到的是序号B001的数据。 公式： B10 =LOOKUP(A10,A2:B8) E10 =LOOKUP(D10,D2:E8) 这是为什么呢？函数运算错了吗？请继续向下看，看明白了LOOKUP的运算原理就知道为什么会出现这样的结果了。 LOOKUP的查找原理是按照二分法来工作的。 一、什么是二分法 在一个数组序列中从前向后一个一个的查找，是遍历法。而二分法顾名思义是从中间开始查找的，它是从一个数组序列的二分位处开始查找，如果查找不到再从下一个二分位处查找，直到查找到和他大小相同或比它小的数。 二、基本原理。 想了解二分法，必须了解下面2个原理。 1、二分位的判定 二分位就是中间的位置，下面的例子中，数组序列有7个数（LOOKUP函数的第2个参数的总行数），那么第4个数就是中间的位置。 =LOOKUP(A10,A2:B8) 如果有12个数呢，则第6个位置是二分位。二分位的位置可以用下面的公式来计算： =INT((总行数+1)/2) 2、查找方向确定 当在二分位查找不到时，接下来该怎么查找呢？ 当上一次二分位值大于查找的值时，向上继续查找，在二分位上面区域找出新的二分位，直到找出符合条件的值。如下图中，先从第5行查，因为B003>B002,所以就向上继续查，上面区域A2:A4区域的二分位值是A3，而A3的值是A001，小于查找的B002，继续向下查找。 继续用上面的图，由于第2次查找后的结果是A001，小于B002，继续向下进行查找，下面的数据中只有1个B001，因此就把B001所对应的数据'100'作为最终返回值。 再用1个例子进行说明，如下图所示： 三、lookup示例解析 1、区间查找 【例1】 如下图所示，是典型的区间查找公式。查找销售额45万对应的提成比率是5%。 查找过徎： 从第1个二分位处（B4）处开始查找，30<> 45比40大，但45小于50，取比50小的最接近的值。 2、查找最后一个值 【例2】 如下图所示，查找表中'武松'最后一次出差地点。 分析： 这里不能直接用Lookup查找'武松'，否则按二分位查找，结果不一定是最后一个。所以就用0除的方法把符合条件的变成0，不符合条变成错误值： =0/(A2:A9=E2) 结果是 {0; #DIV/0!;#DIV/0!;0; #DIV/0!;#DIV/0!;0;#DIV/0!} 由于lookup会过滤掉错误值，所以在lookup眼中，上面的数组是这样的： {0;0;0} 然后用1查找最后一个0的位置 = LOOKUP(1,0/(A2:A9=E2),C2:C9) 这里1是任一个大于0的值，因为大于0，所以用二分法查找时，会一直向下查找，直到最后一个0值。 总结： 1. LOOKUP的二分法查找，是跳跃式的查找。因为函数的语法要求第2个参数必须按升序排列，所以它总是认为第2个参数是已经排好序的，也就是说它总认为被查找的1组值是从小到大排列，如果遇到小的就向下找更大的，如果遇到大的就向上找更小的。 2. LOOKUP函数可以正向查找，也可以反向查找。 3. Lookup的经典查找模式： Lookup(1,0/((条件1)*(条件2)*(条件n)),返回值) |
|
|
