有人在知乎提过一个问题: 如果只学 3 个 Excel 函数,你会推荐哪 3 个?我写过一个回答,后来粘贴到本公众号了: 如果只学 3 个 Excel 函数,你会推荐哪 3 个?
在知乎该问题下很多朋友推荐的是VLOOKUP、SUMIF、COUNTIF等函数。这三个函数是日常工作中最常用的,分别对应工作中最常用的需求:引用、条件求和、条件计数。如果只推荐三个必学函数的话,个人认为,必学的不是VLOOKUP,也不是SUMIF,绝对不是!那都是常用函数,功能单一且有限。最关键的是,它们完全可用下面三个函数来替代,这三个函数可以完成前面三个常规函数的功能,并且还有其它作用,它们功能更全面,用这三个函数可以解决工作中更多的需求,所以,让我选的话,必学的三个函数是: SUMPRODUCT函数:最佳劳模 LOOKUP函数:查找神器 AGGREGATE函数:统计多面手
接下来,将分别进行介绍。 本篇介绍查找引用的LOOKUP函数 1、语法格式: =LOOKUP(查找值,查找区域,结果区域) 注意: 第二参数查找区域、第三参数结果区域,要求是单行或单列。 2、示例 需要注意以下几点: ★ 查找区域(第二参数)的值要求按升序排列,否则结果可能不正确,如下图I15单元格公式计算出的普京的数学成绩就是错的:★ 查找区域(第二参数)和结果区域(第三参数)可以不在同一行,甚至可以不在同一个工作表。如果不在同一行,一定要注意是否对应。★ LOOKUP不但可以垂直查找(替代VLOOKUP),还可横向查找(替代HLOOKUP)以上只是入门级的基本用法,如果LOOKUP只有上面这些本领,那完全不足以替代VLOOKUP。因为LOOKUP需要查找区域按升序排列,这会极大的限制其使用范围。但是,我们要深信,劳动人民的智慧是无穷的,这点小小的限制可轻松突破。 1、乱序时查找引用 上面公式做的是数组运算,可能不好理解,大家将上面公式第二个参数当成一个整体就是了。 要理解上面的公式请复制下面的地址到浏览器,参看写的旧文《深入理解LOOKUP:LOOKUP函数的查找原理》 http://blog.sina.com.cn/s/blog_4e6c2b960102w59a.html 理解不了上面的公式也没关系,我们将其提炼为一个模型公式: =LOOKUP(1,0/(条件判断),结果区域)
遇到数据是乱序时,直接套用上面的公式来查找。
2、多条件查找引用 前面我们介绍的只是单元条件查找,工作中很多时候需要多条件查找引用,这个时候就是LOOKUP大显身手的时候。 将上面的模型公式修改一下,就是多条件查找: =LOOKUP(1,0/((条件判断1)*(条件判断2)*(条件判断3)),结果区域) 示例1:多条件查找 将上面的多条件查找引申以下: 示例2:查找指定商品的最新单价 示例3:根据收件地址查找出所在的省份 需要注意的是 如果地址街道中如果含其他省份的名称可能会出错,如E2单元格查找结果就是错的,这是LOOKUP+FIND公式的查找原理所决定的。 直接上图,就不码字了 示例1:提取最末级科目 示例2:提取字符串中的数字 公式解释参见旧文《公式-LOOKUP(1,-LEFT(A1,ROW($1:$10)))详解》 http://blog.sina.com.cn/s/blog_4e6c2b960102w6fd.html 示例3:查找第一个文本、数值 示例4:查找最后一个文本、数值 示例5:查找大于X的最小值、小于X的最大值 另外,还可用LOOKUP与其他函数结合来提取唯一值列表、筛选唯一值(倒序)、筛选唯一值(顺序)、唯一值、倒数第N条、指定第几条(单条件)、指定第几条(多条件)、用公式筛选明细,等等,这些在年底即将出版上市的《偷懒2》中均有介绍和实际案例,在此就不赘述了。
最后,一句话点评: LOOKUP函数相对于VLOOKUP函数而言,不加V的比加了V的更牛,谁用谁知道。
|