昨天,兰色刷到一个好笑的视频。台湾省一主持人说:为什么近期这么多人去电影院看《长津湖》,是因为大陆一直停电,没电可用了,所以....
兰色又想起之前台湾省“名嘴”黄世聪在节目中声称“大陆人吃不起榨菜”,引发网友群嘲。大陆人真的吃不起榨菜吗?
兰色今天做了一个表格,在用“榨菜”搜索时,发现返回的价格竟然2000元一斤,好贵啊,真的吃不起了。
=LOOKUP(A11,A2:B6)
上表中榨菜对应的单价是3元,所以肯定是B11的公式出错了。lookup不行,换vlookup函数试试,好象也错了:
=VLOOKUP(A11,A2:B6,2)
熟悉Vlookup函数的同学一眼就可以看出:兰色,你的Vlookup函数公式少了最后一个参数 (false)。
嘿嘿,的确如此。因为Vlookup函数缺省最后一个参数时,为匹配查找。所以结果会出错。
只是,兰色故意把公式写错,是想考考你:Lookup和Vlookup公式最后的结果为什么是2000?而不是3000、2、1900?
估计真正理解的人很少很少,因为这涉及Excel函数公式的一个顶级知识点:二分法
在Excel中,Lookup是二分法查找,而Vlookup、Match函数缺少最后一个参数时,都会启动二分法查找。
本例中,二分法查找首先会从A4单元格开始查找(二分位,计算方法是=INT((总行数+1)/2))
通过对比汉字大小,结果是 榨菜>鱼翅 (可以用code提取数字代码验证),这会让二分法继续向下查找(如果二分位汉字>榨菜,则会向上继续二分位查找),把下半部分的区域一分为二,确定二分位后进行对比。结果是查到最后一行也是小于榨菜,所以最终查找到的值是B列最后一行的值2000。
兰色曾详细介绍过二分法的原理,同学们可以在本公众号搜 二分法查找到教程。这里不再详述。
对于看的已头晕眼花的新手,只需要记住兰色一句话:
当查找的值大于一列中所有值的时候,二分法就会返回最后一行的值。
所以,也就有了下面超实用的公式,可以动态返回最后一行的数字:
=LOOKUP(9E+307,A:A)
注:9E307是Excel中接近最大数字的值
当然,用vlookup 也可以
=VLOOKUP(9E+307,A:A,1)
如果返回最后一个汉字,可以用
=LOOKUP( '座',A:A)
如果能明白这个原理,我们最常用的多条件查找公式你也可以理解了。
=LOOKUP(1,0/((A2:A5=A9)*(B2:B5=B9)),C2:C5)
把符合条件的值变成0(不符合条件的变成了错误值),然后再用一个大于0的数(这里的1可以为任一大于0的数字)查找,就会返回最后一个0(符合条件)所对应的值。
兰色说:Excel公式之所以错,是因为不应该用二分法时用了二分法,而台湾省名嘴之所以认为大陆人吃不起榨菜,同样是因为不当的二分法眼光,过去的大陆和如今飞速发展的大陆,岂可同时而语!
联系客服