873

三篇文章搞定VLOOKUP這個迷人小精靈-中級篇

示範檔

範例檔

873.XLSX

結果檔

873F.XLSX

Excel函數界,有400多位成員,只有VLOOKUP是公認的大眾情人。這麼多年來,即使有更強大的函數組合能夠實現它的功能,也從來沒有誰能真正取代它的江湖地位。

在歷年的文章

837 將平時成績表中註記轉換成分數-VLOOKUP函數

835 不會這5Excel函數別說您熟練使用Excel VLOOKUP函數(字串匹配函數)

Q129 VLOOKUP與合併儲存格(不同格數合併,5格內)

Q36 有關VLOOKUP問題

中提到這次錦子老師將為大家揭開VLOOKUP的神秘面紗,初級、中級、高級一應俱全,值得收藏。

2VLOOKUP中級——成為大神的必經之路

VLOOKUP除了簡單的搜尋外,還可以實現一些更複雜的搜尋匹配。

案例1:多列搜尋快速輸入公式

多欄搜尋時,其他參數不變,公式「=VLOOKUP(L2,$A$2:$J$71,?,0)」,最重要的是要修改第三個參數的值,因為欄在變化,第3個參數也在發生變化。

職稱在員工代號後的第二欄,那麼第三個參數應該是3,公式:「=VLOOKUP(L2,$A$2:$J$71,3,0)」;

部門在員工代號後的第三欄,那麼第三個參數應該是4,公式:「=VLOOKUP(L2,$A$2:$J$71,4,0)」。

我們可以將第幾欄用其他函數的計算結果來實現,一般情況下最常用的有兩種方法:

11=COLUMN(A1)2=COLUMN(B1)……

21=MATCH(A1,$A$1:$J$1,0)2=MATCH(B1,$A$1:$J$1,0)……

最終公式有兩種:

1=VLOOKUP($L2,$A$2:$J$71,COLUMN(B2),0)

2=VLOOKUP($L2,$A$2:$J$71,MATCH(B$1,$A$1:$J$1,0),0)

案例2:數值區間模糊搜尋(計算每個月薪的所得稅金額)

注意:搜尋的數值範圍必須由小到大排列,輸出結果是和搜尋值最接近但比它小的那個值。

K2儲存格公式:「=F2*VLOOKUP(F2,$M$5:$N$8,2,1)」,再將公式到K3:K71儲存格,結果如下圖所示。

案例3:模糊條件模糊搜尋

VLOOKUP的第一參數可以自動進行模糊匹配,將以E3開頭的儲存格找到並匹配其對應的儲存格內容。

N11儲存格公式:「=VLOOKUP(M11,$B$2:$K$71,5)」,如下圖所示

案例4、使用萬用字元精確搜尋

VLOOKUP的第一個參數還支援萬用字元“*”,使用萬用字元後相當於確定了搜尋條件,可以實現精確搜尋,搜尋結果也是返回首次滿足條件的記錄的相應值。

N11儲存格公式:「=VLOOKUP("*"&M11&"*",$B$2:$K$71,5,0)

 

 

 


arrow
arrow

    錦子老師 發表在 痞客邦 留言(0) 人氣()