函數講解
功能:
VLOOKUP是最常用的查找和引用函數,依據給定的查閱值,在一定的數據區域中,返回與查閱值對應的想要查找的值。
語法:
- =VLOOKUP(查閱值,包含查閱值和返回值的查找區域,查找區域中返回值的列號,精確查找或近似查找)
參數:
- 查閱值,也就是你指定的查找關鍵值
- 如本示例中,查閱值是F3單元格“林三”,我們要在“姓名”一列中查找“林三”得分,“林三”就是查找的關鍵值。
- 包含查閱值和返回值的查找區域。一定記住,查閱值應該始終位於查找區域的第一列,這樣 VLOOKUP 才能正常工作。
- 例如,本示例中,查找區域是$B$2:$D$15,查閱值“林三”所在的“姓名”B列,就是該區域的首列,而且該區域還包括返回值“得分”所在的D列。
- 查找區域中返回值的列號。
- 例如,本示例,查找區域$B$2:$D$15中,首列“姓名”是第一列,返回值“得分”是第三列,所以列號是“3”。
- 精確查找或近似查找。
- 如果需要精確查找返回值,則指定 FALSE或者0;如果近似查找返回值,則指定TRUE或者1;如果該參數省略,則默認為近似匹配 TRUE 或近似匹配。
- 本示例中是“0”,為精確查找。
一、需基本查找
在G3單元格輸入公式:
=VLOOKUP(F3,$B$2:$D$15,3,0)
確定,即可查找到“林三”的得分;
公式向下填充,即可查找到“陸七”的得分。
如本示例中,查閱值是F3單元格“林三”,我們要在“姓名”一列中查找“林
本示例公式解釋:
注意:查找區域的絕對引用:
在公式中,第二個參數“查找區域”,使用的是絕對引用$B$2:$D$15。
絕對引用的作用是:公式填充到其他行列時,該區域不變。
本示例,查找完“林三”的得分,公式向下填充,再去查找“陸七”得分,查找區域始終不應改變,應該是包含所有姓名與得分的B2:D15區域,所以,該區域絕對引用。
二、多行多列查找
比如,以下數據:
要求查找多人多條信息,這種情況,就需要靈活改動VLOOKUP函數參數,實現用一個公式返回多行多列數據。
公式實現過程如下:
我們可以看出,幾行幾列數據,是用一個公式完成的,該公式是:
=VLOOKUP($B18,$C$2:$G$15,COLUMN(B1),0)
公式向下,向右填充,記得到所有要求查找的返回值。
三、區間查找
如下圖:
採購數量不同,所得折扣也不同,如右側的折扣表。
公式“=VLOOKUP(B2,$E$3:$F$6,2)”,省略了第四個參數,即查找方式,省略就代表把第四個參數設置成TRUE或1,即是近似查找。
近似查找返回值是:比查閱值小且最接近的查詢區域首列中的區間值所對應的返回值。
本示例中
- 比“20”小的值且最接近20的是0,所以返回0對應的區間值“0%”;
- 比“225”小的值且最接近225的是200,所以返回200對應的區間值“8%”。
區間查找有一最最重要的注意事項:
查找區域的區間值必須是從小到大排列,否則查找不到正確結果。
本示例,區間值0、100、200、300是從小到大依次排列的。
四、等級評定
如下圖:
公式“=VLOOKUP(B2,{0,"不合格";60,"合格";70,"良好";85,"優秀"},2)”,省略了第四個參數,即是近似查找。
其中,{0,"不合格";60,"合格";70,"良好";85,"優秀"}是下圖數組的變相寫法:
等級查找是區間查找的特殊方式,也可以寫成區間查找的公式:
五、模糊查找
如下圖:
查找G型號系列產品的銷量,可以把查找值用通配符表示。此種方法可以查找字符串中含有某個關鍵值的對應返回值。
六、多條件查找
如下圖:
查找倉庫二鍵盤的銷量,查找條件必須符合倉庫是“倉庫二”、商品是“鍵盤”兩個條件。
公式:
=VLOOKUP(E2&F2,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)
按“CTRL+SHIFT+ENTER”鍵確認,即得結果。
第一個參數:
E2&F2,用文本連接符,將E2單元格“倉庫二”與F2單元格“鍵盤”,連接在一起,形成新的查詢條件:倉庫二鍵盤。
第二個參數:
IF({1,0},A2:A13&B2:B13,C2:C13),生成一個新的查詢區域:
第三個參數:2,新的查找區域裡,返回值在第二列。
第四個參數:0,精確查找。
七、逆向查找
VLOOKUP函數要求查詢值必須位於查詢區域的首列。比如,下圖中的數據:
原數據區,“部門”位於“姓名”的左側,而要求按照姓名去查詢部門,那直接用VLOOKUP函數進行查找,是查不到結果的。
我們需要構建一個新的查詢數據區,將“姓名”置於“部門”的左側。這種新的查詢數據區,可以通過IF和CHOOSE兩個函數來實現。
IF幫助VLOOKUP實現逆向查詢
在E2輸入公式:
=VLOOKUP(D2,IF({1,0},B1:B10,A1:A10),2,0),
結果如下圖:
其中:
IF({1,0},B1:B10,A1:A10),構造出姓名在前,部門在後的新的查詢區域,如下圖:
CHOOSE幫助VLOOKUP實現逆向查詢
也可在E2輸入公式:
=VLOOKUP(D2,CHOOSE({1,2},B1:B10,A1:A10),2,0),
結果如下圖:
八、跨表引用數據
如下圖,將表1中的消費記錄,按名稱,引用到表2中:
其實,跨工作表的引用,和同一工作表數據引用方法是一樣的,只不過多了一步點擊工作表的名稱,即選擇工作表而已。
引用過程如下:
切記:
在選了引用位置“sheet1!A1:B13”以後,不要再去點回sheet2,除非公式後面需要sheet2中的數據。在公式編輯過程中,鼠標的點擊位置會隨時記錄。
閱讀更多 韓老師講office 的文章