「i」學習

我們在使用Excel建立公式時，多多少少都會遇到計算結果出現錯誤值的情況，這是因為在Excel中，如果公式無法正確評估結果，就會顯示錯誤值，例如： #####、#DIV/0！、#N/A、#NAME？、#Null！、#NUM！、#REF！和 #VALUE！。而每個錯誤值的背後都有不同的錯誤原因，使用者必須要先了解原因才能知道如何排除問題。所以這次要來分享Excel出現錯誤值的原因和更正方式，有需求或有興趣的人請不要錯過這次的分享唷~

「#####」的錯誤原因和更正方式

• 錯誤原因：如果Excel儲存格的欄位因為不夠寬而無法顯示儲存格中的所有字元，或者是儲存格中包含負數日期或時間值時，Excel 就會顯示這個錯誤值。 例如，過去日期減掉未來日期的公式 (如 =06/15/2008-07/01/2008) 就會算出負數日期值。
• 更正方式：
  • 無法顯示所有字元：請在儲存格的標題右欄按兩下滑鼠左鍵(如下圖箭頭所指處)，以自動調整儲存格的欄寬。 

• 儲存格中包含負數日期或時間值：使用公式來正確減去日期或加減時間，以避免負日期或時間值結果。

「#DIV/0！」的錯誤原因和更正方式

• 錯誤原因：在Excel中，將數字除以零 (0) 時，Excel儲存格就會顯示 #DIV/0! 錯誤。例如：輸入「=5/0」 的簡單公式，或是當公式所參照的儲存格有 0 或空白時，就會發生此情況。

• 更正方式：
  • 確定函數或公式中的除數不是零或空白儲存格。
  • 隱藏 #DIV/0! 錯誤的方式：使用 IF 函數評估分母是否存在。 分母若為 0 或沒有值，則顯示 0 或沒有值做為公式結果，而不是顯示 #DIV/0! 錯誤值。例如，如果傳回錯誤的公式是 =A2/A3，可使用 「=IF(A3,A2/A3,0)」 以傳回 0，或使用 =「IF(A3,A2/A3,””) 」以傳回空白字串。也可以顯示自訂訊息，像是：「=IF(A3,A2/A3,”Input Needed”)」。

「#N/A」的錯誤原因和更正方式

• 錯誤原因：
  • 當函數或公式找不到要求尋找的項目時，Excel 就會顯示 #N/A 錯誤。 例如：使用像是 VLOOKUP 的函數，查閱的項目在查閱範圍內未有符合的項目。
  • #N/A 錯誤最常見的原因是與 XLOOKUP、VLOOKUP、HLOOKUP、LOOKUP 或 MATCH 函數有關 ，因為公式找不到參照值，查閱值不存在於來源資料中。

• 更正方式：確認查閱值存在於來源資料中，或在公式中使用錯誤處理常式 (例如 IFERROR)。 例如：「=IFERROR(FORMULA(),0) 」表示： =IF(您的公式確認有誤，則顯示 0。反之，則顯示公式的結果) ，也可以使用「“”」不顯示任何內容，或用其他文字取代：「=IFERROR(FORMULA(),”此處為錯誤訊息”)」

「#NAME？」的錯誤原因和更正方式

• 錯誤原因：Excel 無法識別公式中的文字時，就會顯示這個錯誤。 例如：範圍名稱或函數名稱的拼字不正確。

• 更正方式：
  • 若要避免在公式名稱中拼錯字，請使用 Excel 的公式精靈。 在儲存格或資料編輯列中輸入公式名稱時，下拉式清單中會顯示一份與所輸入文字相符的公式清單，一旦輸入完公式名稱以及左括號，公式精靈就會以動態顯示文字顯示該公式的語法。
  • 也可以使用函數精靈來避免語法錯誤，選取含有公式的儲存格，然後在 [公式] 索引 標籤上按 [插入函數] 的按鈕來Insert 函數。

「#Null！」的錯誤原因和更正方式

• 錯誤原因：指定兩個不相交的交集處時，Excel 就會顯示這個錯誤。 例如：在C2:C3 和 E4:E6 區域沒有相交，因此輸入公式 「=SUM(C2:C3 E4:E6)」 就會傳回 #NULL! 錯誤。
• 更正方式：
  • 在公式中參照連續的儲存格範圍時，使用冒號 (:) 來分隔第一個儲存格與最後一個儲存格。 例如：SUM(A1:A10) 參照到包含儲存格 A1 至儲存格 A10 的範圍。
  • 當參照兩個不相交的區域時，使用逗號 (,) 做為聯集運算子。 例如：如果公式加總兩個範圍，請確定用逗號分隔這兩個範圍 (SUM(A1:A10,C1:C10))。
  • 以「錯誤原因」中的例子說明，只要在 C 和 E 範圍之間放置逗號「=SUM (C2：C3,E4：E6)」，就可修正#NULL! 錯誤 。

「#NUM！」的錯誤原因和更正方式

• 錯誤原因：當公式或函數中有無效的數值時，Excel就會顯示這個錯誤。 例如：無法以貨幣格式輸入像 $1,000 的值，因為貨幣符號已用做為絕對參照標記，而逗號在公式中是用做為引數分隔符號。
• 更正方式：若要避免在「錯誤原因」的例子中所造成的 #NUM! 錯誤，請將數值輸入成未格式化的數字，例如：改輸入 1000。

「#REF！」的錯誤原因和更正方式

• 錯誤原因：當公式參照的儲存格無效時，會顯示 #REF！ 錯誤。 當參照儲存格的公式遭到刪除或被貼上的內容覆蓋時，最常發生這種情形。
• 更正方式：使用 [復原] (Ctrl+Z)功能復原刪除動作、重新建立公式，或者使用連續的範圍參照能夠修正此錯誤，假設刪除了 「=SUM(A2,B2,C2)」 這個公式中的 B 欄，只要使用連續的範圍參照「=SUM(A2:C2)」，即可在 B 欄刪除時自動更新。

「#VALUE！」的錯誤原因和更正方式

• 錯誤原因：公式包含了含有不同資料類型的儲存格，Excel 可能會顯示此錯誤。

• 更正方式：假設儲存格中包含了英文字母、數字等不同類型的資料，卻使用數學運算子 (+, -, *, /, ^)進行運算時，即會出現錯誤。請改為使用函數，如「=SUM(F2:F5)」即可修正此問題。

以上簡單分享，有興趣深入了解的人可進一步參考：Microsoft之偵測公式中的錯誤說明，希望此次分享能幫助Excel使用者對Excel錯誤值有多一點的認識~

§延伸閱讀：Excel不可不知的基本功-「換列」方式

§延伸閱讀：3秒內完成多欄位的數值加總以及圖表-Alt

§延伸閱讀：目視化管理活動倒數天數!

§延伸閱讀：計算到期日的年月-EDATE

§延伸閱讀：A到Z或Z到A排序沒問題，那中文字排序呢

§延伸閱讀：快速找出重複的值

§延伸閱讀：下拉式選單

§延伸閱讀：在Excel建立主、次下拉式選單

§延伸閱讀：如何hightlight點選中的儲存格

§延伸閱讀：快速移除重複性資料，再多也不怕!

§延伸閱讀：如何選取資料表的單一欄、列或全選

§延伸閱讀：3秒內畫出直條圖的兩個方法

§延伸閱讀：在Excel中快速輸入當天日期、當下時間

§延伸閱讀：如何使用Excel執行隨機抽樣

§延伸閱讀：在Excel中固定資料的欄、列-凍結窗格

§延伸閱讀：使用Excel篩選出符合雙條件的資料範圍-進階篩選

§延伸閱讀：找出更動Excel中特定的儲存格時會受影響的儲存格-追蹤從屬參照

§延伸閱讀：將Excel儲存格的兩個或多個文字字串合併成一個字串-「CONCATENATE」 或「&」

§延伸閱讀：自動將符合特定條件的儲存格更改字體顏色及填滿底色-設定格式化的條件

§延伸閱讀：在Excel設定重複列印標題列或標題欄

§延伸閱讀：在Excel的附註插入圖片

§延伸閱讀：在Excel中計算出與目標值的差距-目標搜尋

§延伸閱讀：在Excel中篩選出不重複的記錄

§延伸閱讀：限制Excel儲存格不能輸入重複的資料

§延伸閱讀：如何在Excel中複製、貼上篩選後的儲存格

§延伸閱讀：快速找出Excel多欄資料中的相異之處-「Ctrl」+「\」

§延伸閱讀：如何在Excel儲存格輸入以「0」為開頭的數字

§延伸閱讀：如何在合併Excel儲存格後保留所有的值

§延伸閱讀：如何在Excel儲存格中繪製對角線並輸入文字

§延伸閱讀：10秒內快速分開Excel同一個儲存格內的文字-快速填入

§延伸閱讀：使用Excel快速計算出資料中所有的小計