vlookup這個函數在數據查找中可以說是一個名副其實的萬能函數,可能對於大家或多或少都有了解過這個函數。今天我們就來全面學習一下這個萬能函數的全部11種用法,讓你熟練的掌握這個函數的所有操作。
函數公式簡介:=vlookup(你要找的值,你要查找的區域,查找區域中往右第幾位,0)。
在這裡第四個參數0位精確查找,1為模糊查找,工作中常用的就是0。下面我們來學習一下這個函數的操作用法。
用法1:數據簡單查找案例:簡單查詢張三5月銷量
函數=VLOOKUP(B10,$A$1:$H$6,7,0)
解析:第一參數為要查詢的姓名張三,第二參數為數據源,第三參數為五月份張三的數據在從姓名起往右的第7列,第四參數為0也就是精確查找這個值。
用法2:Vlookup搭配Column函數自動變列計算案例:搭配column函數自動變化第三參數批量查詢楊三和王吧4-6月數據。
函數=VLOOKUP($B10,$A$1:$H$6,COLUMN(C10)+3,0)
解析:第一參數還是需要查詢的人張三,注意B10單元格需要把列固定;第二參數函數對應區域不變;第三參數運用column返回當前列的方式自動變化,因為當前單元格為C10返回值為3,而4月數據為從左往右第6列,所以需要再加3。
用法3:vlookup搭配match函數進行精確查找案例:搭配match函數自動變化第三參數批量查詢楊三和王吧4-6月數據。操作技巧跟column函數類似。
函數=VLOOKUP($B10,$A$1:$H$6,MATCH(C$9,$A$1:$H$1,0),0)
解析:同colunm函數類似,主要變更第三參數。第三參數MATCH(C$9,$A$1:$H$1,0)=6,代表求出4月、5月、6月月份在標題行中所在的位置。
用法4:vlookup經典的向左查詢案例:根據工號在數據源中向左查找對應的姓名
函數={=VLOOKUP(C10,IF({1,0},$C$2:$C$6,$B$2:$B$6),2,0)}
解析:向左查詢需要用到IF函數來進行二次判斷。參數IF({1,0},$C$2:$C$6,$B$2:$B$6),1代表條件成立C列能查到工號時,查詢的區域從$C$2:$C$6到$B$2:$B$6,0代表條件不成立也就是C列查找不到工號時候,查詢的區域從$B$2:$B$6到$C$2:$C$6。
用法5:vlookup查詢出現錯誤值時為空處理案例:數據源中沒有這個人數據時,出現錯誤值時用空白單元格替換掉
函數=IFERROR(VLOOKUP(C9,$B$1:$G$6,6,0),"")
解析:IFEEROR函數為錯誤值處理函數,當出現錯誤值時可以用其他內容代替。函數參數為IFERROR(有錯誤值,執行下一步)。沒有錯誤值時正常顯示。
用法6:vlookup函數最快速度製作工資條案例:運用vlookup函數從數據源中最快的製作工資條
函數=VLOOKUP($G96,$A$96:$E$104,COLUMN(B1),)
解析:根據數據源做工資條,主要的技巧有2個:1、我們往下拖動數據的時候,需要選中一行空白的單元格行,然後再往下移動即可;2、工資條前方提前輸入數字1,這樣往下拖動可以自動更換,然後在數據源中做輔助列1、2、3....,通過數字進行匹配查找。
用法7:vlookup函數計算個人所得稅案例:通過計算好的工資金額直接計算對應的個人所得稅稅率
函數=(G28-3500)*VLOOKUP(G28-3500,C28:E34,2)-VLOOKUP(G28-3500,C28:E34,3)
解析:這裡的重點在於,需要先通過前面的金額區間對應的做好輔助列。以上稅率為2018.10.1日變更前稅率。
用法8:vlookup函數進行通配符(任意值)查找案例:查找名稱中帶有鎮流器產品的數量
函數=VLOOKUP("*"&F41&"*",$B$41:$D$47,3,0)
解析:*代表任意字符,運用&符號進行連接代表鎮流器三個字前可以是任意內容。
用法9:如何用vlookup函數實現多條件查詢案例:求出日期和單號兩個條件下的倉庫入庫數量
函數:{=VLOOKUP(G2&H2,IF({1,0},A:A&B:B,C:C),2,0)}
解析:關鍵點在於查找的條件值需要用&符號將兩個條件連接成一個條件,同時第二參數運用IF判斷函數同樣的用&符號將查詢區域進行連接。最後用CTRL+SHIFT+ENTER三鍵數值的方式進行計算。
用法10:vookup函數進行多數據求和案例:求出對人員1-6月總銷售金額
函數=SUM(VLOOKUP(B11,$A$2:$G$8,{2,3,4,5,6,7},0))
解析:vlookup函數求和重點有2個。1、第三參數查找的數據區域我們需要將每個月對應的列用{}數值的方式進行全部查找;2、當查找出來數據之後,最後用sum函數進行求和,最後用CTRL+SHIFT+ENTER三鍵數值的方式進行計算。
用法11:vlookup函數如何進行一對多查詢案例:通過姓名查找出當天的所有門禁刷卡數據
函數=IFERROR(VLOOKUP(ROW(A1),A:D,4,0),"")
解析:重點在於需要在數據源中做一列輔助列,函數為COUNTIF(B$2:B2,G$2),作為輔助列後通過vlookup函數查找對應的行數進行數據查找,往下拖動的時候就可以匹配出所有的數據。IFERROR函數的用途在於當所有數據已經查找完,往下拖動出現錯誤值時用空格代替。
現在你學會如何使用這個vlookup函數了嗎?
閱讀更多 Excel函數與VBA實例 的文章