|
前几天在QQ群里大家提起了LOOKUP函数，可能有些朋友还不太了解那些固定用法。那今天我们就来聊聊这个函数吧！ LOOKUP有点脾气，弄不好就不听使唤了。根据Excel的帮助文件显示，使用这个函数时，查找区域的数据必须按升序排序，否则得到的可能不是正确的值。另外，LOOKUP它是使用“二分法”机制进行查找的，查找速度比其他函数要快。随便搜个关于二分法的帖子，你会看得一头雾水晕过去。 事实上，LOOKUP却被大家经常使用，还被誉为查找之王，看来它既可爱又可恨。其实我们不要去执迷“二分法”，不会二分法照样可以学会LOOKUP；不用对数据排序，照样能找出正确的值。 首先，我们来看看LOOKUP用于查找最后一个值的套路，函数语法如下： LOOKUP(查找的值,查找的区域) 这里要掌握的一点是，我们必须把查找的值尽可能的设置到很大，目的就是要让它比查找区域中的所有值都要大，这样LOOKUP就会把查找区域的最后一个值作为查找结果。 如下图所示，现在要查找最新的工资结存余额，如果用VLOOKUP函数可不是一件容易做到的事，但这在LOOKUP的眼里却是So Easy。 根据上面的函数套路，设置公式为： =LOOKUP(9E 307,E:E) 这里面的“9E 307”是什么东东？它是一个科学记数法，意思就是9乘以10的307次方，是一个很大的数字，上面表格的任意一个余额都不可能比它大的。当然，你也可以把这个查找的值设置为100万或者1亿，只要它比查找区域的任意一个数字大就行了。当我们设置了一个非常大的数字时，LOOKUP在查找区域里(也就是E列)查不到一个与查找值相等或比它大的数字时，它就会把查找区域最后的那个值作为查找的结果。国庆假期刚过完不久，工资只剩200元了，日子苦着呢...... 如果查找区域的数据全是文本的话，那我们只需要把查找的值设置为一个很大的文本值即可。如下图所示，要查找最后的值班人员是谁，这同样也是LOOKUP的拿手好戏。 公式套路：=LOOKUP(''座'',B:B) 这里面的“座”是一个很大的文本，汉字按首字的拼音排序，一般的文本都比它小，所以我们把查找的值设置为“座”就可以了。上面的公式中，在B列这个区域根本找不到比“座”更大的文本，唯有把最后的值作为查找结果了。原来国庆假期最后一天是猪八戒值班呢！ 怎样，从上面的粟子来看，当我们需要查找数据区域的最后一个值时，管它LOOKUP用什么二分法还是八分法查找机制，也管它查找区域的数据是按升序、降序还是乱序来排列，这些在我们眼中都不值一提。呵呵...... 上面只是LOOKUP的其中一个用法而已，下面还有一个更牛的经典套路： LOOKUP(1,0/((条件1)*(条件2)*(条件3)*(条件n)),返回值的所在区域) 为什么这里要用1和0，它们分别代表什么意思？别慌，其实这个就是前面公式的扩展版而已。这里把查找的值直接给设置为1，那查找的区域绝对不能比它大了。比1小的数有无数个，例如0、-1、-2......，而在这个公式中把它设置为0。但公式的查找区域并没有直接设置为0，而是一串字符 0/((条件1)*(条件2)*(条件3)*(条件n)) ，下面我们慢慢来解剖它。除此之外，公式还增加了第三个参数，它指定了返回值的所在区域。 光说不练假把式，我们通过下面的粟子来消化一下吧。现在要找出性格温柔，而且爱好是画画的妹纸姓名。LOOKUP不管是从左到右、从右到左、从上到下还是从下到上的查找，通杀。把上面那个牛B的经典公式搬下来套用： =LOOKUP(1,0/((C2:C6=''温柔'')*(B2:B6=''画画'')),A2:A6)  第一个参数的查找值设置为1，那我们来看看第二个参数它是怎样变成0的。首先，第一个条件是C2:C6=''温柔''，意思就是在C2:C6这个范围逐个单元格去判断是否等于“温柔”。下面来验证一下判断结果，我们在编辑栏选中公式的条件1这部分内容。 接着按下【F9】键，如果条件成立的返回“TRUE”，不成立的返回“FALSE”。从结果看，只有第三个也就是C4单元格是成立的。 第二个条件是B2:B6=''画画''，意思就是在B2:B6这个范围逐个单元格去判断是否等于“画画”。用相同的方法，我们选中公式的条件2这部分内容，按【F9】键，结果只有第三个也就是B4单元格是成立的。 分别对两个条件区域进行判断后，其结果如下： 0/({FALSE;FALSE;TRUE;FALSE;FALSE}*{FALSE;FALSE;TRUE;FALSE;FALSE})  最后用0去除以上面两个条件的判断结果，目的就是把这些结果转换成最终的查找区域数据，以便于LOOKUP作进一步查找。 你注意到了没有，在两个条件的中间有一个星号(*)，它是乘以的意思，这表示0要同时除以横向的两个判断结果。两个判断区域，从上到下，横向的两个结果为一对，一共有五对结果，0要分别与它们进行运算，其过程如下： 0/FALSE/FALSE；0/FALSE/FALSE；0/TRUE/TRUE；0/FALSE/FALSE；0/FALSE/FALSE 在Excel的运算中，FALSE相当于0，而TRUE相当于1。通过运算处理后，如果同时满足两个条件的值会转为0，否则会变成一个错误值，因为0除以FALSE，也就等于0除以0会出错的，这个地球人都知道。 通过上面的操作可知，0的作用就是用于数据转换。好了，现在查找的值是1，查找区域的值分别是：#DIV/0!、#DIV/0!、0、#DIV/0!、#DIV/0!。而LOOKUP还不傻，它会忽略错误值的，所以查找区域只有第三个值0小于查找的值1。我们在公式中指定了返回值的所在区域，0在查找区域中是第三个，它横向对应A2:A6第三个也就是A4单元格的值，凤姐。 如果把上面公式的1和0换成2和1是否也可行呢，例如： =LOOKUP(2,1/((C2:C6=''温柔'')*(B2:B6=''画画'')),A2:A6) 又或者换成3和2： =LOOKUP(3,2/((C2:C6=''温柔'')*(B2:B6=''画画'')),A2:A6) 答案：行。为什么？ 就这么一句：用LOOKUP查表，把查找值搞大，把区域值搞小，偏偏让你找不了。 其实清风本人对那个二分法不感冒，但现在同样可以用好LOOKUP，相信你也会的。 |
|
|
