如何避免中斷的公式

如果 Excel 無法解決您嘗試建立的公式,您可能會收到如下所示的錯誤訊息:

Excel 的「此公式有問題」對話方塊的影像

很抱歉,這表示 Excel 無法瞭解您嘗試執行什麼工作,因此您可能只想從頭開始。

首先選取確定 ,或按 ESC 關閉錯誤訊息。

隨後您將回到公式出錯的儲存格,該儲存格會處於編輯模式,而 Excel 會醒目提示有問題的位置。 如果您仍然不知道該從該做什麼,而且想要從頭開始,可以再按一次ESC,或選取資料編輯欄中的取消按鈕,這將會將您帶出編輯模式。

資料編輯列取消按鈕的影像

如果您想要往前移動,下列檢查清單會提供疑難排解步驟,可協助您找出可能出錯的地方。

附註: 如果您使用的是Office 網頁版,您可能不會看到相同的錯誤,或可能不適用解決方案。

Excel 會念出各種重磅 (#) 錯誤,例如 #VALUE!、#REF!、#NUM、#N/A、#DIV/0!、#NAME?和 #Null!,表示公式中的某些專案無法正常運作。 例如,#VALUE! 錯誤是由不正確的格式或引數中不支援的資料類型所導致。 或者,您也會看到#REF! 錯誤。 疑難排解指引會針對每個錯誤而不同。

附註: #### 不是公式相關錯誤。 這只是表示欄的寬度不足以顯示儲存格內容。 只要將欄拉寬即可,或移至 [常用] > [格式] > [自動調整欄寬]。

[常用] > [格式] > [自動調整欄寬] 的影像

請參閱與您看到的井號錯誤對應的下列任一主題:

每次您開啟包含參照其他試算表中值的公式的試算表時,系統會提示您更新參照或保留其為目前狀態。

Excel 中的 [不完整的參考] 對話方塊

Excel 會顯示上述對話方塊,以確保當參照值變更時,目前試算表中的公式一定會指向最新的值。 您可以選擇更新參照,或者如果不想更新則請跳過。 即使您選擇不更新參照,您還是可以在需要時手動更新試算表中的連結。

您可以隨時停用對話方塊,避免在開機時顯示。 若要這麼做,請前往檔案> 選項>進>,並清除要求更新自動連結方塊。

[自動更新連結] 選項的影像

重要:  如果這是您第一次使用公式中中斷的連結,如果您需要解決中斷連結的複習,或如果您不知道是否要更新參照,請參閱控制更新外部參照 (連結) 的時間。

如果公式未顯示值,請按照下列步驟進行:

  • 請確定 Excel 已設定為在試算表中顯示公式。 若要這麼做,請選取公式 Tab, 然後選取公式 核群組中的顯示公式

    提示: 您也可以使用鍵盤快速鍵 Ctrl + ` (在 Tab 鍵上方的按鍵)。 當您這麼做時,您的欄會自動加寬以顯示公式,但別擔心,當您切換回標準模式時,欄會調整大小。

  • 如果上述步驟仍無法解決問題,可能是儲存格的格式已格式化為文字。 您可以在儲存格上按一下滑鼠右鍵,然後選取 [設定儲存格格式] > [一般] (或 Ctrl + 1),然後按 F2 > Enter 以變更格式設定。

  • 如果您有一個欄,其中有許多儲存格的格式已格式化為文字,您可以選取範圍、使用您所選擇的數位格式,然後前往資料>到欄> 完成。 這會將格式設定套用到所有選取的儲存格。

    [資料] > [資料剖析] 對話方塊的影像

當公式無法計算時,您必須檢查 Excel 中是否已啟用自動計算。 如果已啟用手動計算,公式將不會計算。 請遵循下列步驟檢查自動 計算

  1. 選取檔案 Tab, 選取選項,然後選取 公式 類別。

  2. 在 [計算選項] 區段的 [活頁簿計算] 底下,請確定已選取 [自動] 選項。

    [自動計算] 和 [手動計算] 選項的影像

有關計算的資訊,請參閱變更 公式的重新計算、反覆運算或精確度

當公式參照到其所在的儲存格時,會發生迴圈參照。 修正程式是將公式移至另一個儲存格,或將公式語法變更為避免迴圈參照的儲存格。 不過,在某些情況下,您可能需要循環參照,因為循環參照會使函數反覆運算,亦即重複運算直到符合特定的數值條件為止。 在這種情況下,您必須啟用移除或 允許迴圈參照

有關迴圈參照的資訊,請參閱 移除或允許迴圈參照

如果您的項目沒有以等號開頭,就不是一個公式,且將無法進行計算,這是一個很常見的錯誤。

當您輸入類似 SUM(A1:A10) 的內容時,Excel 會顯示文字字串 SUM(A1:A10) 而不是公式結果。 或者,如果您輸入 11/2,Excel會顯示日期,例如 2-Nov 或 11/02/2009,而不是 11 除以 2。

為了避免這種未預期的結果,函數的開頭一定要使用等號。 例如,輸入:=SUM (A1:A10) =11/2。

在使用函數的公式中,每一個左括弧皆須有右括弧,函數才能正確運作。 確認所有的括弧都成對出現。 例如,公式 =IF (B5<0) ,"Not valid",B5*1.05) 無法 作用,因為有兩個右括弧,但只有一個右括弧。 正確的公式如下所示:=IF(B5<0,"Not valid",B5*1.05).

Excel 函數需要引數,必須提供這些值才能讓函數運作。 只有少數幾個函數 (例如 PITODAY) 不需要引數。 檢查開始輸入函數時系統所顯示的公式語法,確認函數包含必要的引數。

例如,UPPER 函數只接受一個文字字串或儲存格參照為其引數:=UPPER("hello") 或 =UPPER(C2)

附註: 當您輸入時,函數的引數會列在公式下方的浮動函數參照工具列中。

[函數參考] 工具列的螢幕擷取畫面

此外,有些函數 (例如 SUM) 只需要數字引數,而其他函數 (例如 REPLACE) 則至少必須有一個引數使用文字值。 如果您使用錯誤的資料類型,函數可能會返回非預期的結果,或顯示 #VALUE! 錯誤。

如果您需要快速查詢特定函數的語法,請參閱 Excel 函數 (依類別) 清單。

請勿在公式中輸入格式為 ($) 或小數分隔符號 (,) 的數位,因為貨幣符號代表絕對參照,逗號是引數分隔 符。 您必須在公式中輸入 1000,而非 $1,000。

如果您在引數中使用格式化數位,則會看到未預期的計算結果,但您也會看到 #NUM! 錯誤。 舉個例說,如果您輸入 =ABS(-2,134) 這個公式來尋找 -2134 的絕對值,Excel 便會顯示 #NUM! 錯誤,因為 ABS 函數只接受一個引數,而且會看到 -2 和 134 為個別引數。

附註: 當您使用未格式化的數字 (常數) 輸入公式之後,就可以使用小數分隔符號和貨幣符號來格式化公式結果。 一般而言,將常數放在公式中並不是好主意,因為如果您日後需要更新,可能會很難找到常數,而且較容易輸入錯誤。 最好將常數放在儲存格中,這些常數會放在開放且容易參照的儲存格中。

如果儲存格的資料類型無法用於計算,公式可能不會傳回預期的結果。 舉個例說,如果您在格式化為文字的儲存格中輸入簡單的公式 =2+3,Excel 就無法計算您輸入的資料。 您只會在儲存格中看到 =2+3。 若要修正此問題,請將儲存格的資料類型從 [文字] 變更為 [通用格式],就像這樣:

  1. 選取儲存格。

  2. 選取首頁並選取箭號以展開數位或數位格式 (或按Ctrl + 1) 。 然後選取一

  3. 按 F2 讓儲存格進入編輯模式,然後按 Enter 接受公式。

在儲存格中以 [數值] 資料類型輸入的日期,會顯示為數值日期格式而不是日期。 若要以數字顯示日期,在 [數值格式​​] 庫中選取 [日期] 格式。

在公式中使用 x 做為乘法運算子是很常見的做法,但 Excel 只能在乘法接受星號 (*)。 如果您在公式中使用常數,Excel 會顯示錯誤訊息,並將 x 取代為星號 (*) 以修正公式。

訊息方塊詢問是否要將 x 取代為 * 以進行乘法計算

不過,如果您使用儲存格參照,Excel 會傳回 #NAME? 錯誤。

儲存格參照使用 x 而不是 * 進行乘法計算時出現 #NAME? 錯誤

如果您建立的公式包含文字,請用引號括住該文字。

例如,公式 ="Today is " & TEXT(TODAY(),"dddd, mmmm dd") 結合了文字 "Today is " 以及 TEXTTODAY 函數的結果,並傳回類似 Today is Monday, May 30 的句子。

在公式中,"Today is" 在結束引號前面有空格;這是為了在 "Today is" 和 "Monday, May 30" 兩個字詞之間提供您要的空格。 如果文字沒有用引號括住,公式可能會顯示 #NAME? 錯誤.

您可以在公式中結合 (或巢狀建構) 最多 64 層的函數。

例如,公式 =IF (SQRT (PI () ) <2,"小於 2!,"超過 2!) 3 個函數層級; PI 函數是在 SQRT 函數內巢中, 而 SQRT函數則位於 IF 函數

當您輸入另一個工作表中之值或儲存格的參照,而該工作表的名稱含有非字母字元 (例如空格) 時,請以單引號 (') 括住該名稱。

舉個例說,如果您要在活頁簿中傳回 Quarterly Data 工作表中 D3 儲存格的值,請輸入:='Quarterly Data'!D3。 如果沒有用雙引號括住工作表名稱,公式就會顯示 #NAME? 錯誤.

您也可以選取另一個工作表中的值或儲存格,以在公式中參照這些值或儲存格。 隨後 Excel 便會自動以雙引號括住工作表名稱。

當您輸入另一個活頁簿中之值或儲存格的參照時,請以方括號 ([]) 括住活頁簿名稱,後面再接著含該值或儲存格之工作表的名稱。

例如,若要參照 Excel 開啟之 Q2 Operations 活頁簿內 Sales 工作表上的儲存格 A1 到 A8,請輸入:=[Q2 Operations.xlsx]Sales!A1:A8。 如果沒有方括弧,公式會顯示 #REF! 錯誤.

如果未在 Excel 中開啟該活頁簿,請輸入檔案的完整路徑。

例如,=ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'!A1:A8)

附註: 如果完整路徑含有空格字元,請在路徑開頭和工作表名稱之後、驚嘆號之前,以單引號括住該路徑。

提示: 取得另一個活頁簿路徑最簡單的方法是開啟另一個活頁簿,然後從原始的活頁簿輸入 =,然後使用 Alt+Tab 來切換至另一個活頁簿。 選取工作表上您想要的任何儲存格,然後關閉來源活頁簿。 隨著需要使用的語法,您的公式會自動更新為顯示完整檔案路徑和工作表名稱。 您甚至可以複製及貼上路徑,並在任何需要之處使用。

將儲存格除以值為零 (0) 或沒有值的另一個儲存格,就會產生 #DIV/0! 錯誤.

若要避免此錯誤,您可以直接進行處理,並測試分母的存在。 您可以使用: 

=IF(B1,A1/B1,0)

這表示 IF(B1 存在,然後將 A1 除以 B1,相反則傳回 0)。

刪除任何專案之前,一定先檢查是否有參照儲存格、範圍、定義之名稱、工作表或活頁簿資料的公式。 接著在移除參照資料之前,可以將這些公式更換成其結果

如果您無法將公式更換成結果,請檢視這些錯誤及可能解決方案的相關資訊:

  • 如果公式參照的儲存格已被刪除或取代為其他資料,且公式會 #REF!,請選取含 #REF! 錯誤的儲存格。 在資料編輯列中,選取 #REF! ,然後將其刪除。 然後再次輸入公式的範圍。

  • 如果定義的名稱遺失,而使參照該名稱的公式傳回 #NAME? 錯誤,請定義一個參照所需範圍的新名稱,或者變更公式,使其直接參照該儲存格範圍 (例如 A2:D8)。

  • 如果工作表遺失,而使參照該工作表的公式傳回 #REF! 錯誤,這種錯誤就無法修正,因為已經刪除的工作表是無法復原的。

  • 如果是活頁簿遺失,則參照活頁簿的公式會保持不變,直到您更新公式為止。

    例如,如果公式是 =[Book1.xlsx]Sheet1'!A1,而已經沒有 Book1.xlsx,該活頁簿中所參照的值仍然可以使用。 但是,如果您編輯並儲存參照該活頁簿的公式,則 Excel 會顯示 [更新數值] 對話方塊,並提示您輸入檔案名稱。 選取取消,然後使用公式結果取代參照遺失之活頁簿的公式,以確保不會遺失此資料。

有時候,當您複製儲存格的內容時,您只想要貼上值,而不要貼上顯示在儲存格中 資料編輯列。

例如,您可能會想要將公式的結果值複製到另一個工作表上的儲存格。 或者,當您將結果值複製到工作表上的另一個儲存格後,您可能會想要刪除公式中使用的值。 這兩個動作都會導致儲存格參照錯誤 (#REF!) ,因為包含您用於公式之值的儲存格無法再參照。

您可以將公式的結果值 (不含公式)在目標儲存格中,來避免此錯誤。

  1. 在工作表中,選取內含您要複製之公式結果值的儲存格。

  2. 在首頁的按鈕上,選取剪貼 按鈕圖像

    Excel 功能區影像

    鍵盤快速鍵:按 CTRL+C。

  3. 選取貼上區左上角的儲存格。

    提示: 若要將選取範圍移動或複製到不同的工作表或活頁簿,請選取另一個工作表 Tab 或切換到另一個活頁簿,然後選取貼上區的左上角儲存格。

  4. 在[首頁> 鍵的 [剪貼本群組中,選取 [貼上 按鈕圖像>,然後選取 [貼上值;或按 Alt > E > S > V > Enter for Windows,或 Option > 命令 > V > V > Enter在 Mac 上。

若要了解複雜或巢狀公式如何計算最終結果,您可以評估這個公式。

  1. 選取您要評估的公式。

  2. 選取公式>評估公式

    [公式] 索引標籤上的 [公式稽核] 群組

  3. 選取評估 以檢查加號參照的值。 評估結果會以斜體字顯示。

    [評估公式] 對話方塊

  4. 如果公式的加號部分是參照另一個公式,請選取步驟In 以顯示評估方塊中的另一 個公式 。 選取步驟外 ,回到上一個儲存格和公式。

    參照 二次出現在公式中時,或如果公式參照另一個活頁簿中的儲存格,則無法使用步驟輸入按鈕。

  5. 繼續作業,直到公式的每一個部分都評估完畢。

    評估公式工具不一定會告訴您公式為何會中斷,但可協助指出哪裡。 對很難找到問題所在的較大公式而言,這會是相當實用的工具。

    附註: 

需要更多協助嗎?

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

另請參閱

Excel 公式概觀

偵測公式中的錯誤

Excel 函數 (按字母排序)

Excel 函數 (依類別排序)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×