解決 Excel 使用 VLOOKUP 函數遇到 #N/A 錯誤

日期：

透過 Excel VLOOKUP 函數可以將不同資料範圍但相同「查閱值」 (索引值) 的資料對齊，方便彙整不同資料範圍的資料。但是有時候明明「查閱值」就存在於要查閱的資料範圍卻回傳 #N/A 錯誤。解決方式 1. 確認查閱值是否存在於查閱的資料範圍、2. 如果查閱值是數字，使用 TEXT 函數將數字轉成文字、3. 如果查閱值是文字，請檢查是否前後有額外空白或包含特殊字元。

Photo by Marc Chouinard from StockSnap


VLOOKUP 函數變數說明：

=VLOOKUP(查閱值, 查閱的資料範圍, 欄位編號, 是否完全符合)

步驟1: 確認「查閱值」存在於要查找的資料範圍

1. 選取 Excel 要查閱資料範圍的首欄

2. Excel 上方選單「編輯」-> 「搜尋」，輸入「查閱值」。確認「查閱值」存在於要查閱的資料範圍

步驟2: 如果查閱值是數字，請使用 TEXT 函數格式化

雖然原始資料看起來是數字，但是儲存格有文字或數字兩種格式，即使「查閱值」正確，仍然會造成 VLOOKUP 回傳 #N/A 錯誤。例如下例中儘管識別碼 2 存在於資料範圍 A~C 欄，但是回傳 #N/A 錯誤。



1. 在原始資料的首欄左方，新增一欄

雖然可以改變原始資料的儲存格格式，但是為了日後可以追蹤問題，另外新增一欄處理資料。

2. 識別碼都分別使用 TEXT 函數，將數值資料格式化

(1) 如果是整數：請輸入以下函數：

=TEXT(A欄, 0)
或 =TEXT(A欄, "0")

TEXT 函數將數字轉成文字，而第二個參數 0 則將四捨五入。但是因為原始資料是整數，所以四捨五入並不會修改原始數值。

(2) 如果是有小數點的數字：請輸入以下函數：

=TEXT(A欄, "0.0000000")

第二個參數 0.0.0000000 請搭配原始資料，避免修改原始數值。

3. VLOOKUP 函數的查閱值，也加上 TEXT 函數

=VLOOKUP(TEXT(查閱值, 0), 查閱的資料範圍, 欄位編號, 是否完全符合)

步驟3: 如果查閱值是文字，請檢查原始資料是否前後有額外空白或包含特殊符號

1. 如果文字前後包含額外空白，請使用 TRIM 函數去除前後空白與使用 CLEAN 函數去除部分特殊符號。

=VLOOKUP(TRIM(CLEAN(查閱值)), 查閱的資料範圍, 欄位編號, 是否完全符合) 

2. 如果查閱值是包含 ~ 符號的文字，查閱條件要修改成 ~~  (2021/3/6 補充)
=VLOOKUP(SUBSTITUTE(TRIM(CLEAN(查閱值)), "~", "~~"), 查閱的資料範圍, 欄位編號, 是否完全符合) 

如果不修改 ~ 為 ~~ ，並不會每次每次都會出現 #N/A 錯誤。但是會出現 #N/A 錯誤的文字都是包含 ~ 符號，因此不管有沒有包含 ~ 符號，就記得要取代。 

3. 自己經驗遇到原始資料包含無法列印的特殊符號 (Non-printable characters)，例如「位元組順序記號」。因為無法使用去除 TRIM 或 CLEAN 函數移除。必須從源頭重新產生原始資料。

參考資料


留言

張貼留言