昨天,蘭色刷到一個好笑的影片。臺灣省一主持人說:為什麼近期這麼多人去電影院看《長津湖》,是因為大陸一直停電,沒電可用了,所以。。。。
蘭色又想起之前臺灣省“名嘴”黃世聰在節目中聲稱“大陸人吃不起榨菜”,引發網友群嘲。大陸人真的吃不起榨菜嗎?
蘭色今天做了一個表格,在用“榨菜”搜尋時,發現返回的價格竟然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公式之所以錯,是因為不應該用二分法時用了二分法,而臺灣省名嘴之所以認為大陸人吃不起榨菜,同樣是因為不當的二分法眼光,過去的大陸和如今飛速發展的大陸,豈可同時而語!
工作中最常用的Excel函式公式,全印在一張超大的滑鼠墊上(送40集配套影片),點我檢視詳情