Excel函数学习之LOOKUP函数的二分法原理 excel函数公式教程

在之前的文章《Excel函数学习之LOOKUP函数的5种用法》，我们了解了LOOKUP函数的5种用法，估计有很多小伙伴都没看懂，今天给大家细致的讲解一下LOOKUP的二分法原理，了解原理后，再返回去看昨天的教程，相信你会对LOOKUP有别样的理解。

在之前的文章中，咱们学习了LOOKUP函数的各种套路，也多次提到了LOOKUP函数的查找是根据二分法来进行的，那么到底什么是二分法，今天就来聊聊这个问题。

还是用昨天的例子：按序号查找成绩，序号是升序排列的，公式=LOOKUP(J2,A2:D19)的结果正确。

一、二分法查找原理

二分法查找是把查找范围中的数据按照个数一分为二找到位于中间位置的一个数据，中间值，然后用我们的查找值和中间值做比较。当中间值等于查找值时，直接去获取结果；当中间值小于查找值时，则向下继续进行二分法查找比较（也就是在不含中间值在内的下方的那一半数据中继续进行二分法查找）；当中间值大于查找值时，则向上继续进行二分法查找比较（也就是在不含中间值在内的上方的那一半数据中继续进行二分法查找）。如果如此二分到最后一个数据都未找到等于查找值的数据：最后一个数据小于查找值的，那就以最后一个数据的位置去获取结果值；最后一个数据大于查找值的，那就再向上找一个位置最靠近最后一个数据的小于或等于查找值的数据，再以这个数据的位置去获取结果。

只是这样解释估计很多朋友都会犯迷糊，我们结合上面的例子，具体看看如何通过序号5，找到语文成绩79的。

第一次比较：查找范围A2~A19一共18个数据，中间位置是18÷2=9，即中间值是A10单元格的9。显然查找值5小于9，所以继续向上在A2~A9中查找；

提示：如果查找范围数据个数是单数，中间位置就是（个数+1）÷2，例如11行的话，中间位置就是（11+1）÷2=6；如果数据个数是偶数，中间位置就是（个数）÷2。

第二次比较：只有8个数据，中间位置是8÷2=4，中间值是A5单元格的4，查找值5大于4，所以继续向下在A6~A9中找。注意此时下面只有四个数，第一次查找时直接将9下面的数据都排除了。

第三次比较：4个数据，中间值是A7的6，查找值5小于6，所以向上找。此时只剩下一个数据A6单元格的5，与查找值一致，因此就得到5所对应的D列数据79。

仅仅通过这样一个例子要想明白二分法是很困难的，我们再看一个例子。将上表中的数据按照成绩降序排列，还是按序号5查找语文成绩，公式不做修改。因为序号这一列的顺序乱了，不是升序排列，结果就出现了错误，实际是79，公式得到的是94，这是怎么回事呢？还是通过二分法来看看吧。

第一次查找：中间值（第9个数据）是18，查找值5小于18，因此向上在A2~A9中找；

第二次查找：上面的8个数据，中间值（第4个数据）是8，查找值5小于8，继续向上在A2~A4中找；

第三次查找：上面的3个数据，中间值是1，查找值5大于1，向下找：

第四次查找：现在只有A4单元格一个数据7，查找值5小于7，因此以7为参考，向上找一个位置最靠近7，同时数值小于5或者等于5的数据，即A3单元格的1，由此获得对应的语文值就是94。

通过这两个例子，我想大家对于二分法应该有了一定的认识，关于这个原理，在函数说明里只有一句话的介绍：

在实际应用中，我们可以不用去纠结二分法到底是怎么回事，中间位置是什么，该往下还是往上找，这都是函数的工作，我们只需要记住一点：数据一定要升序排列，如果不能升序排列，那么就按照LOOKUP的精确查找套路去设计公式。

二、LOOKUP实现数据的四舍五入

二分法原理就介绍这么多，接下来需要解决之前遗留的两个问题。

在5月12日的文章中，我们用LOOKUP解决了一个四舍五入的问题，结果大家纷纷留言要个解释：

那么引起大家热议的这个公式到底是什么呢？看下图：

原来这个公式是用LOOKUP函数将一个数字百位以下全部舍去，实现了百分位取整。

在了解过二分法原理以后，是时候让LOOKUP还大家一个解释了。首先说明ROW（A:A）*100这部分。它其实就是得到了一组数字。为了让大家看明白，把A:A范围给小一点，我们用=ROW(A1:A31)*100作说明：

虽然单元格中只能看到一个100，实际上是31个数字，我们可以用f9功能键来看看具体内容：

ROW函数用于获取单元格的行号，ROW(A1:A31)*100就是用A1到A31单元格的行号分别乘以100，得到一组百位取整的数据{100;200;300;……3000;3100}。

然后LOOKUP上场了。它要在上述得到的已经百位取整的数组中查找A1。因为数组是按升序排列的，所以查找A1的实质就是在数组中找小于等于A1的最大值。

以2517.32为例，唯有2500是小于它的最大值，因此结果就是2500。有兴趣的朋友可以自己用二分法原理去试试，看看对不对。

其他数字的查找方式都是如此。这个公式之所以巧妙，就是把一个四舍五入的问题变成了查找引用的问题，真是妙！

三、LOOKUP进行数据提取

我们又利用LOOKUP进行数据的提取，因此有了一个5000字的约会：

这次又是什么问题呢？原题如图所示提取学号：

咱们用上图的第一个数据来解释。在N1单元格中输入“10张勇a”，然后在O1单元格中输入公式=-LOOKUP(1,-LEFT(N1,ROW(1:9)))提取学号。

LOOKUP不是引用函数吗，怎么又可以提取数字了？

LEFT函数的第二个参数使用了一个数组，ROW(1:9)相当于{1;2;3;4;5;6;7;8;9}。LEFT是把第一参数指定的数据从左边开始提取，提取的长度由第二参数来确定。 LEFT按照数组{1;2;3;4;5;6;7;8;9}提取，得到9个结果：

也就是从左边开始提取1位，2位……一直到9位。因为LEFT提取的结果都是文本类型，在LEFT前面加上负号，就可以把其中的文本型数字转为数值，文字变成错误值：

错误值被LOOKUP忽略，现在就变成了在{-1; -10}中找1。1比这组数据都大，根据二分法查找原理，二分后只能向下找，直到最后一位小于1的数据。因此，我们可以简单理解成：当查找值大于查找范围中所有数据（不管是否是升序排列）时，LOOKUP的实质就是在找最后一个数据。

其实将公式中的1变成0也是可以的，因为0也比所有的负数大：

当前最后一个数是-10，所以我们在LOOKUP前面再加个负号，就变成10了，也就是我们需要提取的数字。

对于初学者来说，以上两个案例中LOOKUP的用法过于高级，即便是通过这些介绍，估计也是一知半解，其实学习函数是有一个过程的，从不认识到了解，从了解再到掌握，这里需要大量的练习和思考。只要大家保持积极乐观的心态，能够体会到学习函数的乐趣的时候，就离成功不远了。