Excel函数学习之LOOKUP函数的二分法原理

轻明姑娘_3114

轻明姑娘_3114

2022-08-10

4417人浏览

转载

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

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

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

1.png

一、二分法查找原理

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

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

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

提示:如果查找范围数据个数是单数,中间位置就是(个数+1)÷2,例如11行的话,中间位置就是(11+1)÷2=6;如果数据个数是偶数,中间位置就是(个数)÷2。

2.png

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

3.png

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

4.png

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

第一次查找:中间值(第9个数据)是18,查找值5小于18,因此向上在A2~A9中找;

5.png

第二次查找:上面的8个数据,中间值(第4个数据)是8,查找值5小于8,继续向上在A2~A4中找;

6.png

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

7.jpg

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

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

8.png

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

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

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

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

9.png

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

Excel公式工具(专业版)
Excel公式工具(专业版)

从自然语言描述生成Excel公式,诊断表格错误,支持VLOOKUP、条件求和等常用函数。

下载

10.png

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

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

11.jpg

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

12.jpg

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

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

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

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

三、LOOKUP进行数据提取

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

13.jpg

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

14.png

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

15.jpg

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个结果:

16.jpg

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

17.jpg

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

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

18.jpg

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

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

相关学习推荐:excel教程

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

0

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

0

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

0

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

0

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

0

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

0

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

160

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Excel 教程
Excel 教程

共162课时 | 45.9万人学习

成为PHP架构师-自制PHP框架
成为PHP架构师-自制PHP框架

共28课时 | 3.7万人学习