2017年7月14日 星期五

Excel:在使用 VLOOKUP 時不顯示 #N/A 或錯誤訊息

Excel:在使用 VLOOKUP 時不顯示 #N/A 或錯誤訊息:

VLOOKUP 函數是一個縱向(Vertical)查找函數,它是按列查找,最終返回該列所需查詢列序所對應的值。正常的 VLOOKUP 在來源資料(Table Array)找到參照值(Lookup Value),便會顯示資料。但有在使用 VLOOKUP、HLOOKUP、LOOKUP 或 MATCH 函數時,由於來源資料(Table Array)找不到參照值(Lookup Value),會導致結果顯示錯誤訊息(Error Message),#N/A 是錯誤最常見的。

Excel VLOOKUP 函數

在下面情況下,查閱表格中未列出「ABC」,所以 VLOOKUP 傳回 #N/A 錯誤。
Excel VLOOKUP 函數出現#N/A 錯誤:

解決方案: 
1】確認查閱值存在於來源資料中,或在公式中使用錯誤處理常式 (例如 IF)。 

例如:=IF(ISNA(VLOOKUP(A10,A3:B7,2,FALSE)),0,VLOOKUP(A10,A3:B7,2,FALSE)) 表示
            =IF(您的公式確認有誤,則顯示 0。反之,則顯示公式的結果)
Excel使用ISNA + VLOOKUP 函數,顯示錯誤訊息

也可以使用 “”不顯示任何內容,或用您自己的文字取代:=IFERROR(FORMULA(),”此處為錯誤訊息”) 

例如:=IF(ISNA(VLOOKUP(A10,A3:B7,2,FALSE)),””,VLOOKUP(A10,A3:B7,2,FALSE)) 表示
            =IF(您的公式確認有誤,則不顯示 。反之,則顯示公式的結果) 
Excel ISNA + VLOOKUP 函數,不顯示錯誤訊息

2】使用 COUNTIF 先去檢查 Table_array ( Column A ) 是否有 Lookup_value ,沒有的話顯示直接顯示 ” no match “,出現的話才顯示有關項目: 

例如:=IF(COUNTIF(A2:A6,A10)>0, VLOOKUP(A10,A3:B7,2,FALSE),"") 
            =IF(您的公式確認有誤,則顯示 VLOOKUP 公式,反之,結果則不顯示)
Excel ISNA + VLOOKUP 函數,不顯示錯誤訊息

例如:=IF(COUNTIF(A2:A6,A10), VLOOKUP(A10,A3:B7,2,0),"") 
            =IF(您的公式確認有誤,則顯示 VLOOKUP公式,反之,結果則不顯示)
Excel ISNA + VLOOKUP 函數,不顯示錯誤訊息

Excel IS 函數:
函數
函數傳回 TRUE結果
ISBLANK
Value 指的是空白儲存格
ISERR
Value 指的是 #N/A 之外的任何一種錯誤值
ISERROR
Value 指的是任何一種錯誤值 (#N/A#VALUE!#REF!#DIV/0!#NUM!#NAME? #NULL!)
ISLOGICAL
Value 指的是邏輯值
ISNA
Value 指的是錯誤值 #N/A (無法使用的數值)
ISNONTEXT
Value 指的是任何非文字的項目。(請注意:如果數值參照到空白儲存格,則此函數也會傳回 TRUE)
ISNUMBER
Value 指的是數字
ISREF
Value 指的是參照
ISTEXT
Value 指的是文字
 


沒有留言:

張貼留言