偵測 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) 是多個引數的範例。

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

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

請務必確定	其他資訊
每個函數開頭都使用等號 (=)	如果省略等號，您輸入的內容可能會顯示為文字或日期。 例如，如果您輸入 SUM (A1：A10) ，Excel 會顯示文字字串 SUM (A1：A10) ，而不會執行計算。 如果您鍵入 11/2，則 Excel 會顯示日期 2-Nov (假設儲存格格式為 [一般])，而不是進行 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'！答 1：
在工作表名稱之後加驚嘆號 (!) 以便在公式中進行參照	例如，若要傳回同一個活頁簿內，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 旁插入錯誤 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) IF
修正 #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. 選取要監看的儲存格。
   若要選取工作表上含有公式的所有儲存格，請移至 [首頁>編輯> ] 選取 [ 尋找] & [選取 ( ]，或者您可以使用 Ctrl+G 或在 Mac 上使用 Control+G ，) > 移至 特殊>公式。

   

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

3. 選取 [新增監看]。

   

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

   

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

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

   注意

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

從監看視窗移除儲存格

1. 如果 [監看視窗] 工具列並未顯示，請前往 [公式]>[公式稽核> ]，選取 [監看視窗]。

2. 選取要移除的儲存格。
   若要選取多個儲存格，請按 CTRL，然後選取儲存格。

3. 選取 [刪除手錶]。

   

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

有時候，要了解巢狀如何計算最後的結果是很困難的，因為其中包含許多中間計算及邏輯測試。 不過，透過使用 Windows 版 Excel 中的 評估公式 ，您可以看到巢狀公式的不同部分已按照公式的計算順序進行評估。 例如，當您能夠看到下面這些中間結果時，就會比較容易理解公式 =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. 在 Windows 版 Excel 中，選取要評估的儲存格。 您一次只能評估一個儲存格。
2. 移至 [公式]> [公式稽核>][評估公式]。
3. 選取 [評估] 來檢查加底線之參照的值。 評估結果會以斜體字顯示。
   如果公式的底線部分是另一個公式的參照，請選取 [ 逐步執行 ] 以在 [評估 ] 方塊中顯示另一個公式。 若要返回前一個儲存格與公式，請選取 [跳出]。
   參照第二次出現在公式中，或公式參照不同活頁簿中的儲存格時，[逐步執行] 按鈕就無法用於參照。
4. 繼續選取 [評估 ]，直到評估公式的每個部分為止。
5. 若要再次查看評估，請選取 [重新啟動]。
6. 若要結束評估，請選取 [關閉]。

需要更多協助嗎？

您隨時都可以詢問 Excel 技術社群 中的專家，或在 社群中取得支援。

另請參閱

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

如何避免公式出錯