在 Excel 中偵測公式錯誤

公式除了會傳回非預期的結果外，有時候也會產生錯誤值。 以下是一些可協助您尋找和調查錯誤原因並決定解決方法的工具。

了解如何輸入簡單的公式

公式就是對工作表中的值執行計算的方程式。 公式是以等號 (=) 開頭。 例如，下列公式會計算 3 加 1。

=3+1

公式中也可以包含下列任一或所有項目：函數、參照、運算子和常數。

公式的部分

1. 函數：Excel 包含函數，是執行特定計算的工程公式。 例如，PI() 函數會傳回 pi 的值：3.142...

2. 參照︰參照至個別儲存格或儲存格範圍。 A2 會傳回儲存格 A2 中的值。

3. 常數：直接輸入公式的數字或文字值，例如 2。

4. 運算子︰^ (插入號) 運算子會進行乘冪運算，而 * (星號) 運算子則會進行乘法運算。 使用 + 與 – 來相加或相減值，而使用 / 來相除。

   注意

   部分函數需要引數。 引數是特定函數用來執行計算的值。 必要時，參數會置於函式括號 () 之間。 PI 函式不需要任何參數，所以它是空白的。 有些函式需要一個或多個參數，且可能留有空間給額外的參數。 您必須使用逗號或分號 (;) 分隔引數，依您的位置而定。

例如，SUM 函數只需要一個引數，但可以容納總計 255 個引數。

=SUM (A1：A10) 是一個單一參數的例子。

=SUM(A1:A10, C1:C10) 是多個引數的範例。

更正輸入公式時常見的錯誤

下表摘要列出使用者輸入公式時可能會犯的一些最常見錯誤，並說明修正那些錯誤的方法。

請務必確定	其他資訊
每個函數開頭都使用等號 (=)	如果你省略等號，輸入的內容可能會顯示為文字或日期。 例如，如果你在 A1：A10) 輸入 SUM (，Excel 會顯示文字串 SUM (A1：A10) ，並不會執行計算。 如果您鍵入 11/2，則 Excel 會顯示日期 2-Nov (假設儲存格格式為 [一般])，而不是進行 11 除以 2 的計算。
所有的左右括號都必須成對	確認所有的括號都成對出現 (左括號和右括號)。 當你在公式中使用函數時，每個括號必須放在正確位置，這樣函數才能正常運作。 例如，公式 =IF (B5<0) ，「Not valid」，B5*1.05) 無法運作，因為有兩個閉尾括號且只有一個開括號，而每個括號應該只有一個。 公式應該如下： =IF (B5<0，「無效」，B5 * 1.05) 。
使用冒號指出範圍	當您參照儲存格範圍時，請使用冒號 (:) 來分隔範圍中的第一個儲存格參照和最後一個儲存格參照。 例如，=SUM(A1:A5) 而非 =SUM(A1 A5) (這會傳回 #NULL! 錯誤)。
輸入所有必要的引數	有些函數需要搭配某些引數使用。 同時，請確定沒有輸入過多的引數。
輸入正確的引數類型	有些函數 (例如 SUM) 必須搭配數值引數使用。 有些函數 (例如 REPLACE) 則必須至少在一個引數使用文字值。 如果你使用錯誤的資料類型作為參數，Excel 可能會回傳意想不到的結果或顯示錯誤。
巢狀結構不超過 64 層函數	您可以在一個函數中輸入 (或以巢狀方式建構) 最多 64 層的函數。
將其他工作表名稱以單引號括住	如果某個公式指向其他工作紙或工作簿上的值或儲存格，而該工作簿或工作紙的名稱包含空格或非字母順序的字元，你必須用單引號 ( ') 包裹其名稱，就像 ='Quarterly Data'！D3，或='123'！A1。
在工作表名稱之後加驚嘆號 (!) 以便在公式中進行參照	例如，若要傳回同一個活頁簿內，Quarterly Data 工作表中 D3 儲存格的值，請使用下列公式：='Quarterly Data'!D3。
包含外部活頁簿的路徑	確認每個外部參照都包含活頁簿名稱和其路徑。 一個活頁簿的參照應包括該活頁簿的名稱，而且必須以方括號 ([Workbookname.xlsx]) 括住。 該參照也必須包含活頁簿中的工作表名稱。 如果您要參照的活頁簿未在 Excel 中開啟，您仍可在公式中包含該活頁簿的參照。 請提供檔案的完整路徑，如下列範例所示：=ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'!A1:A8)。 此公式會傳回其他活頁簿中包含 A1 到 A8 儲存格的範圍內的列數 (8)。 註： 如果完整路徑包含空格字元，如前例所示，你必須在路徑開頭、工作紙名稱後、驚嘆號) 前以單引號 (包圍路徑。
輸入無格式的數字	當您在公式中輸入數字時，請勿設定數字的格式。 舉個例說，如果您要輸入 $1,000 這個值，請在公式中輸入 1000。 如果您將逗號當做數值的一部分輸入公式，Excel 會將逗號視為分隔字元。 如果您希望顯示數字時，連同顯示千分位或百萬分位分隔符號，或是貨幣符號，請在輸入數字之後，設定該儲存格的格式。 例如，如果你想在 A3 的值上加上 3100，並輸入公式 =SUM (3,100，A3) ，Excel 會把數字 3 和 100 相加，然後將這個總和加到 A3 的值，而不是把 3100 加到 A3，這樣 =SUM (3100，A3) 。 或者，如果你輸入公式 =ABS (-2,134) ，Excel 會顯示錯誤，因為 ABS 函數只接受一個參數： =ABS (-2134) 。

更正公式中的常見問題

您可以實作特定規則，檢查公式中的錯誤。 這些規則並不保證您的工作表完全沒有錯誤，但是對於尋找一般錯誤卻大有助益。 您可以個別開啟或關閉任何規則。

您有兩種方式可以標示及修正錯誤：(像拼字檢查一樣) 一次檢查一項錯誤，或是當您在工作表中輸入資料時立即檢查。

你可以透過 Excel 顯示的選項解決錯誤，或選擇 忽略錯誤。 如果您忽略特定儲存格中的錯誤，則該儲存格中的錯誤將不再出現於進一步錯誤檢查中。 但是您可以重設先前忽略的所有錯誤，以再次顯示問題。

開啟或關閉錯誤檢查規則

1. 在 Windows 上，請前往 「檔案>選項>公式」，或
   在 Mac 上使用 Excel，請選擇 Excel 選單 > 中的偏好設定 > 錯誤檢查。

2. 在 [錯誤檢查] 底下，選取 [啟用背景錯誤檢查]。 發現的任何錯誤會在格子左上角標示一個三角形。

   

3. 若要變更標示發生錯誤之三角形的色彩，請在 [使用此色彩標示錯誤] 方塊中選取所要的色彩。

4. 在 [Excel 檢查規則] 底下，選取或清除下列任何一項規則的核取方塊：

   • 包含導致錯誤的公式的儲存格：公式未使用預期的語法、參數或資料型態。 錯誤值包括 #DIV/0！、#N/A、#NAME？、#NULL！、#NUM！、#REF！，以及 #VALUE！。 這些誤差值各自有不同的成因，且解決方式也各異。

     注意

     如果你直接在儲存格輸入錯誤值，它會被儲存為該錯誤值，但不會被標記為錯誤。 但如果其他儲存格中的公式參照該儲存格，公式就會從該儲存格傳回錯誤值。

   • 表格中有不一致的計算結果欄公式：計算結果欄可能會包含與主要欄公式不同的個別公式，這會造成例外狀況。 當您執行下列任一動作時，都會造成計算結果欄例外狀況：

     • 在計算結果欄儲存格中輸入公式以外的資料。
     • 在計算出的欄位儲存格輸入公式，然後使用 Ctrl +Z 或在快速存取工具列中選擇復原。
     • 在已包含一或多個例外狀況的計算結果欄中，輸入新的公式。
     • 將資料複製到不符合計算結果欄公式的計算結果欄。 如果複製的資料包含公式，這個公式會覆寫計算結果欄中的資料。
     • 移動或刪除另一個工作表範圍上由計算結果欄其中一列所參照的儲存格。

   • 其中所包含的年是以 2 個數字表示的儲存格：將儲存格所含的文字日期用於公式時，世紀的解譯可能會有誤。 例如，=YEAR("1/1/31") 這個公式中的日期可能是 1931，也可能是 2031。 您可以使用這個規則來檢查不明確的文字日期。

   • 格式化為文字或以單引號開頭的數字：儲存格含有儲存為文字的數字。 這種情形通常發生於從其他來源匯入資料時。 以文字形式儲存的數字可能會導致意外的排序結果，因此最好將它們轉換成數字。 ‘=SUM(A1:A10) 會視為文字。

   • 與範圍中其他公式不一致的公式：公式與附近其他公式的模式不相符。 在許多情況下，相鄰公式的差異僅在於參考文獻。 以下四個相鄰公式的例子中，Excel 在 D4 格子中 A10：C10 () 的 =SUM 旁邊顯示錯誤，因為相鄰公式會增加一列，而該公式則增加 8 列——Excel 預期公式 =SUM (A4：C4) 。

     

     如果公式中使用的參考資料與相鄰公式中的不一致，Excel 會顯示錯誤。

   • 省略範圍中部分儲存格的公式：公式可能不會自動包含您在原始資料範圍與包含公式的儲存格之間所插入資料的參照。 這項規則會比較公式中的參照以及包含該公式之儲存格相鄰的實際儲存格範圍。 如果相鄰的儲存格包含其他值且非空白，Excel 就會在公式旁顯示錯誤。
     例如，當套用此規則時，Excel 會在公式 =SUM (D2：D4) 旁插入錯誤，因為 D5、D6 和 D7 格子與公式中引用的格子相鄰，以及包含 D8) (公式的格子，而這些格子包含了本應被公式引用的資料。

     

   • 解除鎖定內含公式的儲存格：未鎖定公式以進行保護。 預設情況下，工作表上的所有儲存格都是鎖定的，因此當工作表受到保護時，這些儲存格就無法更改。 這有助於避免不小心刪除或更改公式等意外的錯誤。 此錯誤表示儲存格已設定為解除鎖定，但工作表未受到保護。 請確認你不希望牢房被鎖上。

   • 參照空白儲存格的公式：公式包含對空白儲存格的參照。 這可能會造成意外的結果，如下列範例所示。
     假設您要計算儲存格下列各欄數字的平均值。 如果第三個格子是空白，則不包含在計算中，結果是 22.75。 如果第三個儲存格是 0，則該儲存格是在計算範圍內，結果就是 18.2。

     

   • 輸入表格的資料無效：表格中有驗證錯誤。 請前往資料工具的「資料工具」分>頁>「資料驗證」來檢查該儲存格的驗證設定。

逐一更正常見公式錯誤

1. 選取您要檢查錯誤的工作表。

2. 如果是手動計算的工作表，請按 F9 重新計算。
   若未顯示錯誤 檢查 對話框，請選擇「 公式>」 公式 稽核>錯誤檢查。

3. 如果你之前忽略了任何錯誤，可以透過以下步驟再次檢查：前往 檔案>選項>公式。 在 Mac 上使用，請選擇 Excel 選單 > 中的偏好設定 > 錯誤檢查。
   在 錯誤檢查 區塊中，選擇「 重置忽略錯誤>OK」。

   

   注意

   重設被忽略的錯誤時，會重設使用中活頁簿內所有工作表中的所有錯誤。

   秘訣

   如果您在資料編輯列底下移動 [錯誤檢查] 對話方塊，這樣可能會有幫助。

   

4. 選擇對話框右側的動作按鈕之一。 可用的動作會依各錯誤類型而不同。

5. 選取 [下一步]。

個別更正常見公式錯誤

1. 在儲存格旁邊，選擇 「錯誤檢查 ，然後選擇你想要的選項。 可用的命令會依各錯誤類型而不同，第一個項目會描述錯誤。
   如果你選擇 忽略錯誤，該錯誤會在每次連續檢查中被標記為忽略。

   

更正 # 錯誤值

若公式無法正確評估結果，Excel 會顯示錯誤值，例如 #####、#DIV/0！、#N/A、#NAME？、#NULL！、#NUM！、#REF！，以及 #VALUE！。 每種錯誤類型都有不同的原因和解決方案。

下表包含一些文章連結，其中詳細說明這些錯誤，以及快速入門的簡短描述。

主題	描述
更正 #### 錯誤	如果欄不夠寬而無法顯示儲存格中的所有字元，或儲存格中包含負數日期或時間值，Excel 就會顯示這個錯誤。 例如，過去日期減掉未來日期的公式 (如 =06/15/2008-07/01/2008) 就會算出負數日期值。 小提示： 試著雙擊欄位標頭之間自動擬合儲存格。 如果顯示 ### 是因為 Excel 無法顯示所有字元，這會修正。
修正 #DIV/0! 錯誤	當某個數字的除數為零 (0) 或不包含值的儲存格時，Excel 就會顯示這個錯誤。 小提示： 新增一個錯誤處理器，如以下範例，為 =IF (C2，B2/C2,0)
修正 #N/A 錯誤	函數或公式無法使用某個值時，Excel 就會顯示 #N/A 錯誤。 如果你用的是像 VLOOKUP 這樣的函式，你想查詢的內容在查找範圍內有匹配嗎？ 大多數時候不會。 請嘗試使用 IFERROR 隱藏 #N/A。 在此情況下，您可以使用： =IFERROR (VLOOKUP (D2，$D$6：$E$8,2，TRUE) ,0)
修正 #NAME? 錯誤	Excel 無法識別公式中的文字時，就會顯示這個錯誤。 例如，區間名稱或函式名稱可能會拼錯。 註： 如果你使用函式，請確保函式名稱拼寫正確。 此時 SUM 拼寫錯誤。 把「e」移除，Excel 會自動修正。
修正 #NULL! 錯誤	指定兩個不相交的交集處時，Excel 就會顯示這個錯誤。 交集運算子是在公式中分隔參照的空格字元。 註： 確保你的範圍分得正確——C2：C3 和 E4：E6 區域沒有相交，所以輸入公式 =SUM (C2：C3 E4：E6) 會返回 #NULL！ 錯誤。 在 C 和 E 範圍之間加逗號會修正 =SUM (C2：C3，E4：E6)
修正 #NUM! 錯誤	公式或函數中有無效的數值時，Excel 就會顯示這個錯誤。 您是使用反覆執行的函數，例如 IRR 或 RATE 嗎？ 如果是這樣，很可能會發生 #NUM! 錯誤很可能是因為函式找不到結果。 請參閱說明主題中的解決步驟。
修正 #REF! 錯誤	儲存格參照無效時，Excel 就會顯示這個錯誤。 例如，你可能有被其他公式引用的刪除儲存格，或是你把移到其他公式引用的儲存格上貼上。 您是否不小心刪除了列或欄？ 我們刪除了 =SUM(A2,B2,C2) 這個公式中的 B 欄，看看會發生什麼事。 你可以用 復原 (Ctrl+Z) 來撤銷刪除、重建公式，或者使用像這樣連續的範圍參考：=SUM (A2：C2) ，當欄位 B 被刪除時會自動更新。
修正 #VALUE! 錯誤	如果您的公式包含了含有不同資料類型的儲存格，Excel 可能會顯示此錯誤。 您是搭配不同的資料類型使用數學運算子 (+, -, *, /, ^) 嗎？ 如果是這樣，請嘗試改為使用函數。 在這種情況下，=SUM (F2：F5) 會修正這個問題。

使用監看視窗監看公式及其結果

當工作表上看不到儲存格時，你可以在「觀看視窗」工具列中觀看這些儲存格及其公式。 [監看視窗] 可讓您便於在大型工作表中檢查、稽核或確認公式計算及結果。 使用 [監看視窗] 後，就不需要重複地捲動或移至工作表的不同部分。

這個工具列可以像其他工具列一樣移動或停靠。 例如，您可以將其固定在視窗的底部。 工具列會持續追蹤下列的儲存格內容︰1) 活頁簿、2) 工作表、3) 名稱 (如果儲存格有對應的具名範圍)、4) 儲存格位址、5) 值，以及 6) 公式。

將儲存格新增至監看視窗

1. 選取要監看的儲存格。
   要選取工作表中所有帶有公式的儲存格，請到「首頁>編輯>」選擇「尋找」&「選擇 (」，或者你可以在 Mac 上使用 Ctrl+G 或 Control+G) >「前往特殊>公式」。

   

2. 前往 公式>公式審核> ，選擇 觀看視窗。

3. 選擇 新增觀看。

   

4. 確認你已經選中所有想觀看的儲存格，然後選擇 新增。

   

5. 若要變更 [監看視窗] 欄寬，請拖曳欄名右側邊界。

6. 若要顯示 [監看視窗] 工具列中某一項目參照的儲存格，請按兩下該項目。

   注意

   只有當其他活頁簿開啟時，[監看視窗] 工具列中才會顯示包含其他活頁簿之外部參照的儲存格。

從監看視窗移除儲存格

1. 如果手錶視窗工具列沒有顯示，請前往 公式>公式審核> ，選擇 觀看視窗。

2. 選取要移除的儲存格。
   要選擇多個儲存格，請按 Ctrl 鍵，然後選擇儲存格。

3. 選擇 刪除手錶。

   

一次一個步驟來評估巢狀公式

有時候，要了解巢狀如何計算最後的結果是很困難的，因為其中包含許多中間計算及邏輯測試。 不過，透過 Excel for Windows 的 Evaluate Formula ，你可以看到巢狀公式的不同部分依照計算順序被評估。 例如，公式 =IF (平均 (D2：D5) >50，SUM (E2：E5) ,0) 當你可以看到以下中間結果時，會更容易理解：

在 [評估值公式] 對話方塊中	描述
=如果 (平均 (D2：D5) >50，SUM (E2：E5) ,0)	初始顯示巢狀公式。 AVERAGE 函數和 SUM 函數都是 IF 函數內的巢狀函數。 儲存格範圍 D2:D5 包含 55、35、45 和 25 等值，因此 AVERAGE(D2:D5) 函數的結果等於 40。
=如果 (40>50，和 E2：E5) ,0 ()	儲存格範圍 D2:D5 包含 55、35、45 和 25 等值，因此 AVERAGE(D2:D5) 函數的結果等於 40。
=IF(False,SUM(E2:E5),0)	由於 40 並未大於 50，因此 IF 函數第一個引數中的運算式 (logical_test 引數) 是 False。 IF 函數會傳回第三個引數的值 (value_if_false 引數)。 SUM 函數並不會進行評估，因為它是 IF 函數的第二個引數 (value_if_true 引數)，它只有在運算式為 True 時才會傳回。

1. 在 Windows 版 Excel 中，選擇你想評估的儲存格。 您一次只能評估一個儲存格。
2. 前往公式>>審核評估公式。
3. 選取 [評估] 來檢查加底線之參照的值。 評估結果會以斜體字顯示。
   如果底線部分是參考其他公式，請選擇「 Step In 」在 評估 框中顯示該公式。 若要返回前一個儲存格與公式，請選取 [跳出]。
   參照第二次出現在公式中，或公式參照不同活頁簿中的儲存格時，[逐步執行] 按鈕就無法用於參照。
4. 繼續選擇 「評估 」，直到公式的每個部分都被評估完畢。
5. 要再次查看評估，請選擇 重新啟動。
6. 要結束評估，請選擇 關閉。

需要更多協助嗎？

你隨時可以向 Excel 技術社群 的專家詢問，或在 社群中獲得支援。

另請參閱

顯示公式與儲存格間的關聯

如何避免公式出錯