如何修正 #N/A 錯誤

#N/A 錯誤通常表示公式找不到已要求尋找的項目。

主要解決方案

#N/A 錯誤最常見的原因是與 VLOOKUP、HLOOKUP、LOOKUP 或 MATCH 函數有關 (如果公式找不到參照值)。例如,您的查閱值不存在於來源資料中。

查閱值不存在。儲存格 E2 中的公式是:=VLOOKUP(D2,$D$6:$E$8,2,FALSE)。找不到「Banana」這個值,因此公式回傳 #N/A 錯誤。
找不到來源資料中的項目

在此情況下,查閱表格中未列出「Banana」,所以 VLOOKUP 傳回 #N/A 錯誤。

解決方案:確認查閱值存在於來源資料中,或在公式中使用錯誤處理常式 (例如 IFERROR)。例如 =IFERROR(FORMULA(),0) 表示:

  • =IF(您的公式確認有誤,則顯示 0。反之,則顯示公式的結果)

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

如果您此時不確定如何處理或需要哪一類的協助,建議您在 Excel 社群論壇中搜尋類似問題,或自行發佈問題。

連結至 Excel 社群論壇

如果您想要繼續進行,以下檢查清單提供的疑難排解步驟可協助您釐清公式運算出錯的原因。

查閱值和來源資料是不同的資料類型。例如,您嘗試將 VLOOKUP 參照指定為數字,但來源資料儲存為文字。

不正確的值類型。這個範例說明由於查閱項目的格式為數值,但查閱資料表的格式為文字,而造成 VLOOKUP 公式回傳 #N/A 錯誤。
因資料類型不同而導致的 #N/A 錯誤

解決方案:確認資料類型相同。您可以透過選取儲存格或儲存格範圍來檢查儲存格格式,然後用滑鼠右鍵按一下並選取 [設定儲存格格式] > [數字] (或按 Ctrl+1),然後視需要變更數字格式。

顯示 [數值] 索引標籤並選取 [文字] 選項的 [設定儲存格格式] 對話方塊

提示: 如果您需要在整欄強制執行格式變更,請先套用您想要的格式,然後使用 [資料] > [資料剖析] > [完成]。

您可以使用 TRIM 函數移除任何前置或後置空格。下列範例使用巢狀 VLOOKUP 函數中的 TRIM,以移除 A2:A7 內的名稱中的前置空格,並傳回部門名稱。

在陣列公式中搭配 TRIM 使用 VLOOKUP 以移除前置/後置空格。儲存格 E3 中的公式是:{=VLOOKUP(D2,TRIM(A2:B7),2,FALSE)};必須按下 CTRL+SHIFT+ENTER 來輸入。

{=VLOOKUP(D2,TRIM(A2:B7),2,FALSE)}

附註: 這是陣列公式,而且必須按下 Ctrl+Shift+Enter 來輸入。Excel 會自動為您用括號 {} 括住公式。如果您嘗試自行輸入,Excel 會將公式顯示為文字。

根據預設,在表格中查閱資訊的函數必須以遞增排序儲存。不過,即使表格未排序,VLOOKUP 和 HLOOKUP 工作表函數仍包含指示函數尋找完全相符的 range_lookup 引數。若要尋找完全相符,請將 range_lookup 引數設為 FALSE。請注意,使用 TRUE (它會要求函數尋找大約符合的項目) 不僅會導致 #N/A 錯誤,也會傳回如下列範例中所示有錯誤的結果。

搭配 TRUE range_lookup 引述使用 VLOOKUP 可能產生錯誤結果的範例。
由於在未排序的表格中使用大約符合引數而導致 VLOOKUP 失敗

在此範例中,不僅「Banana」傳回 #N/A 錯誤,「Pear」也傳回錯誤價格。這是因為使用 TRUE 引數所致,它要求 VLOOKUP 尋找大約符合的項目,而不是尋找完全相符的項目。沒有接近相符「Banana」的項目,而照字母排列「Peach」在「Pear」前面。在此情況下,使用 VLOOKUP 與 FALSE 引數會傳回「Pear」的正確價格,但「Banana」仍會是 #N/A 錯誤,因為查閱清單中沒有對應的「Banana」。

如果您使用的是 MATCH 函數,請嘗試變更 match_type 引數的值來指定表格的排序順序。若要尋找完全符合的項目,請將 match_type 引數設定為 0 (零)。

若要修正此問題,請確認陣列公式所參照的範圍與輸入陣列公式的儲存格範圍具有相同的列數和欄數,或將陣列公式輸入較少或較多儲存格以符合公式中的範圍參照。

在此範例中,儲存格 E2 有參照不符的範圍:

包含不相符的範圍參照而造成 #N/A 錯誤的陣列公式範例。儲存格 E2 中的公式是:{=SUM(IF(A2:A11=D2,B2:B5))};必須按下 CTRL+SHIFT+ENTER 來輸入。

{=SUM(IF(A2:A11=D2,B2:B5))}

為了讓公式正確計算,必須變更公式,讓兩個範圍都變成 2 至 11。

{=SUM(IF(A2:A11=D2,B2:B11))}

附註: 這是陣列公式,而且必須按下 Ctrl+Shift+Enter 來輸入。Excel 會自動為您用括號 {} 括住公式。如果您嘗試自行輸入,Excel 會將公式顯示為文字。

在儲存格中輸入 #N/A 會造成 SUM 公式無法正確進行計算的範例。

在此情況下,5 月至 12 月有 #N/A 值,因此無法計算總計並傳回 #N/A 錯誤。

若要修正此問題,請檢查您所用函數的公式語法,並在傳回錯誤的公式中輸入所有必要的引數。這可能需要進入 Visual Basic 編輯器 (VBE) 檢查函數。您可以從 [開發人員] 索引標籤或使用 ALT+F11 存取 VBE。

若要修正此問題,請確認包含使用者定義的函數的活頁簿已開啟,而且函數能適當運作。

若要修正此問題,請確認該函數中的引數是正確的,並用於正確的位置。

若要修正此問題,請按下 Ctrl+Atl+F9 以重新計算工作表

如果您不確定如何使用正確的引數,您可以使用函數精靈協助您進行。選取有疑問的公式中的儲存格,然後移至功能區上的 [公式] 索引標籤,然後按 [插入函數]。

[插入函數] 按鈕。

Excel 會自動為您載入精靈:

公式精靈對話方塊範例。

當您按一下各個引數時,Excel 會逐一提供個別的適用資訊。

#N/A 相當實用!使用如下列的圖表範例資料時,使用 #N/A 是很常見的做法,因為 #N/A 值不會在圖表上繪製。以下分別是使用 0 與使用 #N/A 時圖表外觀的範例。

繪製 0 值的折線圖範例。

在上述範例中,您會看到 0 值已繪製並在圖表底部顯示為平坦的直線,然後迅速上升為顯示總計。在下列範例中,您會看到 0 值已被 #N/A 取代。

未繪製 #N/A 值的折線圖範例。

如需 #NA 錯誤出現於特定函數的詳細資訊,請參閱以下主題:

頁面頂端

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

請參閱

將儲存成文字的數值轉換成數值格式

VLOOKUP 函數

HLOOKUP 函數

LOOKUP 函數

MATCH 函數

Excel 公式概觀

如何避免公式出錯

使用錯誤檢查偵測公式中的錯誤

Windows 版 Excel 中的鍵盤快速鍵

Mac 版 Excel 中的鍵盤快速鍵

所有 Excel 函數 (依英文字母順序排列)

所有 Excel 函數 (依類別)

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×