弱水三千,只取一瓢 – Lookup經典用法三例

  Vlookup是大家都常用的函數,可對於Lookup函數,很多人可能還沒用過或者不太熟悉。其實Lookup在查找數據方面有很強大的功能,可以範圍查找,也可以精確查找,可以從數字文本混合的字符串中分離出數字。各位大神們已經總結了各種各樣的Lookup用法,但是任憑弱水三千,我只取一瓢飲。我們今天不講複雜的,就講幾個稍微簡單、常用的、便於理解的用法。今天講的這一「瓢」Lookup的用法,如果你還學不會,那就把你的電話給我,我想找你談談心。。。

閒言少敘,言歸正傳。

Lookup函數語法

向量形式:LOOKUP(lookup_value, lookup_vector, [result_vector])

說明:

第一個參數是查找值,第二個參數是從哪裡查找,最後一個參數是返回值所在的區域;

第二個參數、第三個參數區域大小必須相同,比如都是3行1列的區域或1行3列的區域;

公式在查找區域(第二個參數)中查找小於或等於第一個參數的最大值,如果找不到,則返回#N/A錯誤;

第二參數最好按照升序排列。

數組形式:LOOKUP(lookup_value, array)

說明:

返回的是第二個參數最後一行或者最後一列的值;

如果array的行數大於或等於列數,則函數在第一列中搜尋,返回最後一列的數值;如果array的行數小於列數,則函數在第一行中搜尋,返回最後一行的數值;

第二參數最好按照升序排列。

查找原理

二分法(具體解釋可以百度)

經典用法一:範圍查找

示例:根據學生分數評定等級。

公式:=LOOKUP(A4,{0,60,80,90},{‘不及格’,’及格’,’良好’,’優秀’})

解釋:查找分數40,第二個參數{0,60,80,90}裡面沒有40,就查找小於40的最大值,也就是0,0在第一個位置,返回第三個參數中的第一個元素「不及格」。

查找分數60,第二個參數中有60,在第二個位置,就返回第三個參數中相同位置的元素,即「及格」。

弱水三千,只取一瓢 - Lookup經典用法三例

經典用法二:查找最後一個數值或文本

示例:顯示最後一條記錄。

公式1:=LOOKUP(9E 307,A2:A10)

公式2:=LOOKUP(‘座’,B2:B10)

解釋:9E307就是9*10^307,是一個很大的數,通常實際應用數據中不會大於這個數。這樣的話,查找的數據區域內的數值都小於這個值,根據Lookup查找的原理,一直往後找,就找到了最後一個值。日期型數據本質上也是數字,所以也可以用這個個公式查找。「座」也是一個較大的文本,在數據範圍內查找這個字,就找到了最後一個值。

弱水三千,只取一瓢 - Lookup經典用法三例

經典用法三:多條件匹配查找

示例:在以下區域中根據日期和銷售員兩個條件,返回對應的銷量。

弱水三千,只取一瓢 - Lookup經典用法三例

弱水三千,只取一瓢 - Lookup經典用法三例

公式:=LOOKUP(1,0/((A2:A9=E2)*(B2:B9=F2)),C2:C9)

解釋:

A2:A9=E2返回結果是以下數組:

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

B2:B9=F2返回結果是以下數組:

{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

兩個數組相乘,得出以下結果:

{0;1;0;0;0;0;0;0}

用0除,得出以下結果,這是一個由0和錯誤值組成的數組:

{#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

Lookup查找,忽略參數中的錯誤值,找到以上數組中的0,在第二個位置,返回第三個參數中的第二個元素,也就是數量3132;

找不到對應的值時,顯示錯誤值。

注意:((A2:A9=E2)*(B2:B9=F2))的乘積作為分母,所以在最外層是括號括起來。如果忽略了這一點,可能就無法得到想要的結果。

買三贈一:分離數字

弱水三千,只取一瓢 - Lookup經典用法三例

公式1:=-LOOKUP(0,-LEFT(B1,ROW(1:10)))

公式2:=-LOOKUP(0,-RIGHT(B2,ROW(1:10)))

公式3:=LOOKUP(9E 307,–MID(B3,MIN(FIND({0;1;2;3;4;5;6;7;8;9},B3&1234567890)),ROW(INDIRECT(‘1:’&LEN(B3)))))

解釋:公式1和2種,直接用了ROW(1:10),這是簡便的寫法,因為字符串中的數字位數不超過10。也可以換成如公式3種那樣的寫法ROW(INDIRECT(‘1:’&LEN(B3))),這是根據字符串長度生成一個序列數。

基於以上用法,甚至再配合其他函數,還可以演變出很多變化。這裡就不再一一列舉,大家感興趣的話可以琢磨琢磨。