如何修正 #N/A 錯誤

如何修正 #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 社群論壇中搜尋類似問題,或自行發佈問題。

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

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

不正確的值類型。  這個範例說明由於查閱項目的格式為數值,但查閱資料表的格式為文字,而造成 VLOOKUP 公式回傳 #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)

附註: 2018 年 9 月 24 日 - 動態陣列公式 - 如果您有目前版本的 Microsoft 365,而且是測試人員 - 快發行通道,則您可以在輸出範圍左上角的儲存格中輸入公式,然後按 Enter 以確認公式為動態陣列公式。 否則,請先選取輸出範圍,在輸出範圍左上角的儲存格中輸入公式,然後按 Ctrl+Shift+Enter 以進行確認,以舊的陣列公式輸入公式。 Excel 會為您在公式的開頭和結尾處插入括號。 如需有關陣列公式的詳細資訊,請參閱陣列公式的指導方針和範例

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

搭配 TRUE range_lookup 引述使用 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))

附註: 2018 年 9 月 24 日 - 動態陣列公式 - 如果您有目前版本的 Microsoft 365,而且是測試人員 - 快發行通道,則您可以在輸出範圍左上角的儲存格中輸入公式,然後按 Enter 以確認公式為動態陣列公式。 否則,請先選取輸出範圍,在輸出範圍左上角的儲存格中輸入公式,然後按 Ctrl+Shift+Enter 以進行確認,以舊的陣列公式輸入公式。 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 公式概觀

如何避免公式出錯

偵測公式中的錯誤

Excel 的鍵盤快速鍵

所有 Excel 函數(按字母排序)

所有 Excel 函數 (依類別)

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×