偵測公式中的錯誤

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

附註: 本主題包含可以協助您修正公式錯誤的技術。並未完整列出所有方法供您修正每個可能的公式錯誤。如需特定錯誤的說明,您可以在 Excel 社群論壇中搜尋類似於您的問題,或張貼您自己的問題。

連結至 Excel 社群論壇

了解如何輸入簡單的公式

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

=3+1

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

公式組件

公式組件

  1. 函數︰內建於 Excel,函數是執行特定計算的工程公式。例如,PI() 函數會傳回 pi 的值:3.142...

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

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

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

    附註: 部分函數需要引數。引數是特定函數用來執行計算的值。必要時,引數會放在函數的括號 () 之間。PI 函數不需要任何引數,這也就是它空白的原因。某些函數需要一或多個引數,並可以騰出空間給額外的引數。您必須使用逗號或分號 (;) 分隔引數,依您的位置而定。

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

SUM 函數

=SUM(A1:A10) 是單一引數的範例。

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

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

請務必確定

其他資訊

每個函數開頭都使用等號 (=)

如果漏了等號,您鍵入的內容可能會顯示為文字或日期。舉個例說,如果您鍵入 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 會將逗號視為分隔字元。如果您希望顯示數字時,連同顯示千分位或百萬分位分隔符號,或是貨幣符號,請在輸入數字之後,設定該儲存格的格式。

舉個例說,如果您要將儲存格 A3 中的值與 3100 相加,而您輸入的公式是 =SUM(3,100,A3),這時 Excel 會將 3 和 100 這兩個數字相加,然後再將兩數相加之後的和,與 A3 中的值相加,而不是將 3100 與 A3 中的值相加 (這會是 =SUM(3100,A3))。或者,如果您輸入的公式是 =ABS(-2,134),則 Excel 會顯示錯誤,因為 ABS 函數只接受一個引數:=ABS(-2134)

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

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

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

  1. 按一下 [檔案] > [選項] > [公式]。

    在 Excel 2007 中,按一下 Microsoft Office 按鈕 Office 按鈕影像 > [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 就會在公式旁顯示錯誤。

      例如,Excel 在套用這項規則時,會在公式 =SUM(D2:D4) 旁插入錯誤,因為儲存格 D5、D6 和 D7 都相鄰於公式中所參照的儲存格,以及包含公式的儲存格 (D8),而那些儲存格中都包含應該已在公式中參照的資料。

      當公式略過範圍中的儲存格時,Excel 會顯示錯誤
    • 解除鎖定內含公式的儲存格:未鎖定公式以進行保護。依預設,會鎖定工作表上的所有儲存格,這樣子當工作表受到保護時即不能變更儲存格。這有助於避免不小心刪除或更改公式等意外的錯誤。此錯誤表示儲存格已設定為解除鎖定,但工作表未受到保護。檢查並確認您要或不要鎖定儲存格。

    • 參照空白儲存格的公式:公式包含對空白儲存格的參照。這可能會造成意外的結果,如下列範例所示。

      假設您要計算儲存格下列各欄數字的平均值。如果第三個儲存格是空白,它就不在計算範圍內,所以結果是 22.75。如果第三個儲存格是 0,則該儲存格是在計算範圍內,結果就是 18.2。

      當公式參照到空白儲存格時,Excel 會顯示錯誤訊息
    • 輸入表格的資料無效:表格中有驗證錯誤。移至 [資料] 索引標籤 > [資料工具] 群組 > [資料驗證],檢查儲存格的驗證設定。

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

  2. 如果是手動計算的工作表,請按 F9 重新計算。

    如果未顯示 [錯誤檢查] 對話方塊,則按一下 [公式] 索引標籤 > [公式稽核] > [錯誤檢查] 按鈕。

  3. 如果先前忽略了任何錯誤,請執行下列操作,再次檢查那些錯誤:按一下 [檔案] > [選項] > [公式]。

    在 [錯誤檢查] 區段中,按一下 [重設被忽略的錯誤] > [確定]。

    錯誤檢查

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

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

    將 [錯誤檢查] 方塊移動到資料編輯列正下方。
  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)

#N/A 錯誤

修正 #NAME? 錯誤

Excel 無法識別公式中的文字時,就會顯示這個錯誤。例如,範圍名稱或函數名稱可能拼錯。

附註: 如果您是使用函數,請確定函數名稱拼字正確。在此情況下,SUM 拼字不正確請移除 "e",Excel 將會修正這個問題。

當函數名稱有拼字錯誤時,Excel 會顯示 #NAME? 錯誤

修正 #NULL! 錯誤

指定兩個不相交的交集處時,Excel 就會顯示這個錯誤。交集運算子是在公式中分隔參照的空格字元。

附註: 請確定您的範圍正確分隔 - C2:C3 和 E4:E6 區域沒有相交,因此輸入公式 =SUM(C2:C3 E4:E6) 會傳回 #NULL! 錯誤。在 C 和 E 範圍內放入逗號將會修正問題 =SUM(C2:C3,E4:E6)

#NULL! 錯誤

修正 #NUM! 錯誤

公式或函數中有無效的數值時,Excel 就會顯示這個錯誤。

您是使用反覆執行的函數,例如 IRR 或 RATE 嗎?如果是這樣,很可能會發生 #NUM! 錯誤,因為函數無法找到結果。請參閱說明主題中的解決步驟。

修正 #REF! 錯誤

儲存格參照無效時,Excel 就會顯示這個錯誤。例如,您可能刪除了其他公式參照的儲存格,或將移動的儲存格貼在其他公式參照的儲存格之上。

您是否不小心刪除了列或欄?我們刪除了 =SUM(A2,B2,C2) 這個公式中的 B 欄,看看會發生什麼事。

使用 [復原] (Ctrl+Z) 復原刪除動作、重新建立公式,或者使用連續的範圍參照,例如:=SUM(A2:C2),這會在 B 欄刪除時自動更新。

當儲存格參照無效時,Excel 會顯示 #REF! 錯誤

修正 #VALUE! 錯誤

如果您的公式包含了含有不同資料類型的儲存格,Excel 可能會顯示此錯誤。

您是搭配不同的資料類型使用數學運算子 (+, -, *, /, ^) 嗎?如果是這樣,請嘗試改為使用函數。在此情況下,=SUM(F2:F5) 會修正此問題。

#VALUE! 錯誤

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

[監看視窗] 可輕易地監視工作表中使用的公式

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

附註: 一個儲存格只能有一個監看式。

將儲存格新增至監看視窗

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

    若要選取工作表上含有公式的所有儲存格,請在 [常用] 索引標籤上的 [編輯] 群組中,依序按一下 [尋找與選取] (或者您可以使用 Ctrl+G,或是 Mac 上的 Control+G) > 移至 [特殊目標] > [公式]。

    移至 [特殊目標] 對話方塊
  2. 在 [公式] 索引標籤上,按一下 [公式稽核] 群組中的 [監看視窗]。

  3. 按一下 [新增監看式]。

    按一下 [新增監看式],在您的試算表中新增監看式
  4. 確認您已選取所有您想要監看的儲存格,然後按一下 [新增]。

    在 [新增監看式] 中,輸入要監看的儲存格範圍
  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. 若要結束評估,請按一下 [關閉]。

附註: 

  • 公式中使用 IFCHOOSE 函數的部分不會進行評估。在這些情況下,[評估] 方塊中會顯示 [#N/A]。

  • 如果參照是空白,[評估] 方塊中會顯示零值 (0)。

  • 下列函數會隨著工作表的變更而重新計算,而且會使 [評估值公式] 對話方塊得出與儲存格所顯示之值不同的結果:RANDAREASINDEXOFFSETCELLINDIRECTROWSCOLUMNSNOWTODAYRANDBETWEEN

有任何特定問題嗎?

在 Excel 社群論壇張貼問題

協助我們改進 Excel

您是否有任何關於下一版 Excel 的改善方式的建議?如果有的話,請參閱 Excel User Voice 中的主題。

請參閱

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

影片:修正出錯的公式 (Excel 2010)

如何避免公式出錯

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×