偵測公式中的錯誤

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

請務必確定	其他資訊
每個函數開頭都使用等號 (=)	如果您省略等號，您輸入的內容可能會顯示為文字或日期。 舉個例說，如果您鍵入 SUM(A1:A10)，Excel 會顯示文字字串 SUM(A1:A10)，而不是執行計算。 如果您鍵入 11/2，Excel 會顯示日期「11月2日」 (假設儲存格格式為 [一般])，而不是進行 11 除以 2 的計算。
所有的左右括號都必須成對	確認所有的括號都成對出現 (左括號和右括號)。 當您在公式中使用函數時，每個括弧都必須處於正確的位置，函數才能正確運作。 例如，=IF(B5<0),"Not valid",B5*1.05) 這個公式就無法正常運作，因為它有兩個右括號，卻只有一個左括號 (左右括號都應該只有一個)。 正確的公式應該是：=IF(B5<0,"Not valid",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 會將逗號視為分隔字元。 如果您希望顯示數字時，連同顯示千分位或百萬分位分隔符號，或是貨幣符號，請在輸入數字之後，設定該儲存格的格式。 例如，如果您想要將 3100 加到單元格 A3 中的值，而您輸入公式 =SUM (3,100，A3) ， Excel 將數位 3 和 100 相加，然後將總計加到 A3 的值，而不是將 3100 加到 A3，這會是 =SUM (3100，A3) 。 或者，如果您輸入的公式是 =ABS(-2,134)，則 Excel 會顯示錯誤，因為 ABS 函數只接受一個引數：=ABS(-2134)。

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

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

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

1. 若是 Windows 上的 Excel，請移至 [檔案 > 選項 > 公式]，或
   針對 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 =SUM(A10:C10) 這個公式旁顯示錯誤，因為與它相鄰的公式都是以一列為單位遞增，且以 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，請 選取 [喜好設定] > [> 錯誤檢查] 中的 [Excel] 功能表。

   在 [ 錯誤檢查] 區段中，選取 [重設略過的錯誤 > 確定]。

   

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

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

   

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) 會修正此問題。

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

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

將儲存格新增至監看視窗

1. 選取要監看的儲存格。

   若要選取工作表上含有公式的所有單元格，請移至 [常用 > 編輯 > 選取 [尋找 & 選取 ( ]，或者您可以在 Mac 上使用 Ctrl+G 或 Control+G ，) > 移至 [特殊 ] > 公式。

   

2. 移至 [公式 稽核] > [公式稽核] > 選取 [監看視窗]。

3. 選取 [新增監看式]。

   

4. 確認您已選取所有要 watch 的儲存格，然後選取[新增]。

   

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

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

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

從監看視窗移除儲存格

1. 如果沒有顯示 [監視視窗] 工具列，請移至 [公式 > 公式稽核 > 選取 [監視視窗]。

2. 選取要移除的儲存格。

   若要選取多個單元格，請按 CTRL，然後選取儲存格。

3. 選取 [刪除監看式]。

   

有時候，要了解巢狀如何計算最後的結果是很困難的，因為其中包含許多中間計算及邏輯測試。 但是，使用 [評估值公式] 對話方塊後，您可以看到軟體如何依照公式的計算順序，評估出巢狀公式的不同部分。 例如，當您看到下列中繼結果時，公式 =IF (AVERAGE (D2：D5) >50，SUM (E2：E5) ,0) 更容易瞭解：

在 [評估值公式] 對話方塊中	描述
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	初始顯示巢狀公式。 AVERAGE 函數和 SUM 函數都是 IF 函數內的巢狀函數。 儲存格範圍 D2:D5 包含 55、35、45 和 25 等值，因此 AVERAGE(D2:D5) 函數的結果等於 40。
=IF(40>50,SUM(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. 選取要評估的儲存格。 您一次只能評估一個儲存格。

2. 移至 [公式稽核 > 公式 > 評估公式]。

3. 選取 [評估] 來檢查加底線之參照的值。 評估結果會以斜體字顯示。

   如果公式加上底線的部分是參照其他公式，請選取 [步驟入] 以在 [ 評估 ] 方塊中顯示另一個公式。 若要返回前一個儲存格與公式，請選取 [跳出]。

   參照第二次出現在公式中，或公式參照不同活頁簿中的儲存格時，[逐步執行] 按鈕就無法用於參照。

4. 繼續選取 [評估 ]，直到評估公式的每一個部分為止。

5. 若要再次查看評估，請選取 [重新啟動]。

6. 若要結束評估，請選取 [關閉]。