|
336 |
VLOOKUP函數的12種常見錯誤、原因及解決方法 |
||||
|
示範檔 |
無 |
範例檔 |
336.XLSX |
結果檔 |
無 |
VLOOKUP函數是一個非常好用的查找函數,但由於種種原因,在實際使用時會遇到種種讓人搞不明白的錯誤。於是本文就把常遇到的VLOOKUP錯誤問題來一次大整理,希望能對同學們有用。
n 函數參數使用錯誤
第1種:第2個參數範圍設置錯誤之一。
如下圖所示,根據姓名查找年齡時產生錯誤。
錯誤原因:VLOOKUP函數第二個參數是查找範圍,該範圍的第1欄有一個必備條件,就是查找的資料(A12),必須對應於範圍的第1欄。本例中是根據姓名查找的,那麼,第二個參數姓名必須是在範圍的第1欄位置,而上述公式中姓名列是在表格A1:E6範圍的第2欄。所以公式應改為:
=VLOOKUP(A12,B2:E8,3,0)
第2種:第2個參數範圍設置錯誤之二。
如下圖所示,根據姓名查找職務時產生查找錯誤。
錯誤原因:本例是根據姓名查找職務,可大家注意一下,第2個參數B2:D8根本就沒有包括E欄的職務,當然會產生錯誤了。所以公式應改為:
=VLOOKUP(A12,B2:E8,4,0)
第3種:第4個參數少了或設置錯誤。
如下圖所示,根據工號查找姓名。
錯誤原因:VLOOKUP第四個參數為0時表示精確查找,為1或省略時表示模糊查找。如果忘了設置第4個參數則會被Excel誤以為是故意省略,按模糊查找進行。當範圍也不符合模糊查找規則時,公式就會傳回錯誤值。所以公式應改為。
=VLOOKUP(A12,A2:D8,2,0) 或 =VLOOKUP(A12,A2:D8,2,)
注意:當參數為0時可以省略,但必須保留「,」號。
二、數值格式不同,造成查找錯誤
第4種:查找為數值,被查找範圍為文字型數值。
如下圖所示,根據工號查找姓名,查找出現錯誤。
錯誤原因:在VLOOKUP函數查找過程中,文字型數值和數值型數值會被認為不同的字元。所以造成無法成功查找。
解決方案:把查找的數值在公式中轉換成文字型,然後再進行查找。即:
=VLOOKUP(A12&"",A2:E8,2,0)
第5種:查找格式為文本型數位,被查找區域為數值型數位。
例5:如下圖所示根據工號查找姓名,查找出現錯誤
錯誤原因:同4
解決方法:把文字型數值轉換成數值型。即:
=VLOOKUP(A12*1,A2:E8,2,0)
三、參照方式使公式複製後產生錯誤
第6種:沒有正確的使用參照方式,造成在複製公式後範圍發生變動引起錯誤。
如下圖所示,當B12的公式複製到B13和B14後,B13公式傳回錯誤值。
錯誤原因:由於第二個參數A2:E8是相對參照,所以向下複製公式後會自動更改為A3:E9,而A13中的工號A01所在的列,不在A3:E9範圍中,從而造成查找失敗。
解決方案:把第二個參數的參照方式由相對參照改為絕對參照即可。
B12公式改為:=VLOOKUP(A12,A2:E8,2,0)
四、多餘的空格或不可見字元
第7種:資料表中含有多餘的空格。
如下圖所示,由於A欄工號含有多餘的空格,造成查找錯誤。
錯誤原因:多一個空格,用不帶空格的字元查找當然會出錯了。
解決方案:
1、手工替換掉空格。建議用這個方法;
2、在公式中用TRIM函數替換空格而必須要用資料公式形式輸入。
即:輸入公式:「=VLOOKUP(A12,TRIM(A1:E8),2,0)」後,按Ctrl + Shift + Enter鍵,輸入後陣列形式為 {=VLOOKUP(A12,TRIM(A1:E8),2,0)}
第8種:類空格但非空格的字元。
在表格內存在大量的「空格」,但又用空格無法替換掉時,這些就是類空格的不可見字元,這時可以「以其人之道還之其人之身」,直接在儲存格中複製不可見字元粘貼到替換視窗,替換掉即可。
第9種:不可見字元的影響
例:如下圖所示的A欄中,A欄看不見不存在空格和類空格字元,但查找結果還是出錯。
出錯原因:這是從網頁或資料庫中導入資料時帶來的不可見字元,造成了查找的錯誤。
解決方案:在A欄後插入幾欄空欄,然後對A欄進行分欄操作(資料 >資料工具 > 資料剖析),即可把不可見字元分離出去。
第10種:反向查找VLOOKUP不支援產生的錯誤。
如下圖所示,在這個表格中根據姓名查找工號,結果傳回了錯誤。
錯誤原因:VLOOKUP不支持反向查找。
解決方法:
用IF函數重組區域,讓兩列顛倒位置。
=VLOOKUP(A12,IF({0,1},A2:A8,B2:B8),2,0)
用INDEX+MATCH組合實現
=INDEX(A2:A8,MATCH(A128,B2:B8,0))
第11種:萬用字元引起的查找錯誤
如下圖所示,根據範圍查找提成傳回錯誤值。
錯誤原因:「」用於查找萬用字元,如果在VLOOKUP公式中出現,會被認為特定用途,非真正的「」。如在表格中查找3*6 ,356,376也被查找到。
如果精確查找3*6,需要使用,如下圖所示。
解決方法:用就可以表示查找了。所以公式可以修改為
=VLOOKUP(SUBSTITUTE(A8,"",""),A2:B4,2,0)
第12種:VLOOKUP函數第1個參數不直接支援陣列形式產生的錯誤
如下圖所示,同時查找A和C產品的和,然後用SUM求總和。
錯誤原因:VLOOKUP第一個參數不能直接用於陣列。
解決方法:利用N/T+IF結構轉化一下陣列,如果不瞭解N/T+IF結構用法,可以參考本落格其他VLOOKUP範例。
公式修改為:
=SUM(VLOOKUP(T(IF({1},A9:B9)),A2:B6,2,))
部落格相關範例
|
2019.09.27 |
|
|
2019.09.20 |
2208Excel函數用法-COUNT、COUNTIF、IF、IFERROR、MATCH、MID、OFFSET、RIGHT、VLOOKUP |
|
2019.08.17 |
|
|
2018.12.14 |
|
|
2018.08.18 |
|
|
2018.08.17 |
|
|
2018.08.16 |
|
|
2018.04.25 |
|
|
2017.09.15 |
|
|
2017.06.23 |
|
|
2017.06.21 |
|
|
2017.06.17 |
|
|
2017.06.01 |
|
|
2017.05.25 |
|
|
2017.05.24 |
|
|
2017.05.10 |
|
|
2017.05.08 |
|
|
2017.05.02 |
|
|
2017.04.29 |
|
|
2017.04.28 |
