一直以来,卢子强调了无数遍,普通人无需知道这个套路的来龙去脉,但偏偏还是有一堆人要纠结。那好吧,这次就全讲了。 很久很久以前,Excel界有2大函数大神,山菊花和狗尾草,两人合称花草。山菊花幽默风趣,狗尾草富有逻辑性,各有其优点。那一年(大概20年前),狗尾草凭借着LOOKUP函数的经典查找模式一举成名,不要迷恋二分法,二分法只是一个传说。抛开这个传说,一起来见证LOOKUP函数的神奇。 这个LOOKUP函数有什么好学的,帮助都提到,如果区域没升序会可能导致出错,既然这样,那作用明摆着就很小。
帮助把LOOKUP当垃圾看,但却被Excel爱好者们发掘出各种各样的功能,甚至有人把LOOKUP函数比喻成查找之王。LOOKUP函数不因被帮助埋没而别人遗弃,反而受到高手的追捧。一起来见证LOOKUP的神奇吧。 1.根据番号精确查找俗称。
卢子喜欢将这个称为以大欺小法,就是用1查找0。 0/($A$2:$A$14=D2)的作用就是将符合条件的值转换成0,其他转换成错误值。在这里认识下有独孤九剑之称的F9键。 哪里不懂抹哪里,公式理解so easy。 现在$A$2:$A$14=D2这部分不理解,直接在编辑栏抹黑,按F9键,就看到原来这部分是番号的逐一比较,如果满足就显示TRUE,否则显示FALSE。 了解后,记得按Esc键或Ctrl+Z组合键返回,否则公式就变了。 一次看完0/(条件),得到的是由0和错误值转成的数组。 LOOKUP函数喜欢以大欺小,用1查找0,也就是查找小于或者等于他的值,因为在查找的时候,自动忽略错误值,所以能找到0。 知识扩展: LOOKUP函数在查找的时候,如果有多个对应值,会返回最后一个满足条件的对应值。也就是说用1查找0,如果有多个0的情况下,会查找到最后一个0所对应的值。现在用俗称来找番号,H276对应了多个番号,但查找的结果只返回最后一个,也就是40983。 如果还不理解,没关系,先记住这个模式!以后用多了自然会理解,就如书读百遍,其义自现一样。 2.屏蔽错误值错误值查找。
这个跟VLOOKUP函数一样,都是用IFERROR函数进行容错处理。 3.按顺序返回多列对应值。
利用混合引用的特点,B$2:B$10向右拖动,就变成C$2:C$10,从而改变返回区域。
4.按不同顺序返回对应值。 这个就有点麻烦,要怎么才能确定返回的区域呢? OFFSET函数可以偏移得到一个区域,比如订单数就是A列偏移2列就可以得到。
俗称就是A列偏移1列就可以得到。
前面提到MATCH函数可以获取字段的排位,通过排位就知道需要偏移多少列。
如订单数是排第3位,但只需偏移2列就可以得到,也就是说排位-1就的到偏移列。
这样返回区域就定下来。
套用以大欺小法,得到:
其实公式说难也难,说易也易。只要掌握窍门,就跟堆积积木一样,将一个个积木组合成你需要的模型。 5.根据番号逆序俗称。 在LOOKUP函数的字典中,没有逆向这个词。管你什么方向,对LOOKUP函数通通都一样。
6.根据俗称跟订单号两个条件查询完成情况。 多条件跟单条件对LOOKUP函数而言都是一样的,一个通式闯天下,只需将条件用&组合起来即可。
当然也可以直接就套用那个通用公式,多个条件用*连接起来。
7.根据俗称的第一个字符查找番号。 从左边提取字符用LEFT函数,第一个也就是提取1位。
如果仅仅是提取1位,这个1可以省略,默认情况下就是1。
组合起来就是:
最后记住这个通用公式:
有了这个经典查找的通用公式,神马查找都So easy! 通过了两大查找函数LOOKUP与VLOOKUP,曾经也间接让卢子风光了一把,但生活仍需继续。 知识扩展: 有的时候查找的值并不一样是在开头,而是位置不确定,只是包含在里面。如根据镇流器ID查询数量,多个ID用/隔开,输入其中一个ID查找数量,这时就不能用LEFT函数,要用FIND函数进行判断是否包含在里面。 作者:卢子 |
|
来自: weimiao > 《OFFICE应用》