如何避免公式出錯

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

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

很抱歉,這表示 Excel 無法辨識您嘗試執行的動作,所以您可能只需離開這裡並從頭開始。

首先,按一下 [確定] 或按 ESC 以關閉錯誤訊息。

隨後您將回到公式出錯的儲存格,該儲存格會處於編輯模式,而 Excel 會醒目提示有問題的位置。如果此時您仍不知道如何處理而想從頭開始,您可以再按一次 ESC,或按一下資料編輯列中的 [取消] 按鈕以結束編輯模式。

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

如果您此時不確定如何處理或需要哪一類的協助,建議您在 Excel 社群論壇中搜尋類似問題,或自行發佈問題。

連結至 Excel 社群論壇

如果您想要繼續進行,以下檢查清單提供的疑難排解步驟可協助您釐清公式運算出錯的原因。

Excel 會擲出各種雜湊 (#) 錯誤 (例如,#VALUE!、#REF!、#NUM、#N/A、#DIV/0!、#NAME? 及 #NULL!),以指出公式發生錯誤的地方。例如,#VALUE! 錯誤發生的原因是不正確的格式設定或引數中有不支援的資料類型。或者,如果公式參照的儲存格已刪除或已取代為其他資料,則會看到 #REF! 錯誤。每個錯誤的疑難排解指南都會不同。

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

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

針對您所看到的雜湊錯誤,請參閱下列對應的主題:

每次您開啟試算表時,若其中包含參照其他試算表中的值的公式,將會提示您更新參照或保留其現狀。

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

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

您可以隨時停用對話方塊,避免在開機時顯示。若要這樣做,請移至 [檔案] > [選項] > [進階] > [一般],然後取消選取 [自動更新連結]。在 Excel 2007 中,則請按一下 [Office 按鈕] > [Excel 選項]。 Office 2007 按鈕

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

重要: 如果這是您第一次處理公式裡的中斷連結、需要解決中斷連結的進修課程,或您不知道是否要更新參照,請參閱控制更新外部參照 (連結) 的時間

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

  • 請確定已將 Excel 設定為在試算表中顯示公式。若要這樣做,請按一下 [公式] 索引標籤,在 [公式稽核] 群組中,[顯示公式]。

    提示: 您也可以使用鍵盤快速鍵 Ctrl + ` (在 Tab 鍵上方的按鍵)。當您執行此動作時,欄將會自動變寬以顯示您的公式,但請不用擔心,當您切換回一般檢視時,您的欄將會調整大小。

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

  • 如果您已將一欄中大範圍的儲存格的格式設定為文字,您可以選取該範圍,套用您選擇的數值格式,然後移至 [資料] > [資料剖析] > [完成]。這會將格式設定套用到所有選取的儲存格。

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

當公式無法計算時,您必須檢查是否已在 Excel 中啟用自動計算。若已啟用手動計算,公式將無法計算。請按照下列步驟檢查 [自動計算]:

  1. 按一下 [檔案] 索引標籤,然後按一下 [選項],再按一下 [公式] 類別。

  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+3,Excel 就無法計算您輸入的資料。您只會在儲存格中看到 =2+3。若要修正這個問題,請將儲存格的資料類型從 [文字] 改為 [通用格式],如下所示:

  1. 選取儲存格。

  2. 按一下 [常用] > [數字格式] 旁的箭號 (或按 Ctrl + 1),然後按一下 [通用格式]。

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

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

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

訊息方塊詢問是否要將 x 取代為 * 以進行乘法計算
常數相乘使用 x 而非 * 的錯誤訊息

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

儲存格參照使用 x 而不是 * 進行乘法計算時出現 #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,"Less than two!","More than two!") 有 3 層函數:PI 函數包含在 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. 選取貼上區左上角的儲存格。

    提示: 若要將選取的項目移動或複製到不同的工作表或活頁簿,請按一下其他工作表索引標籤或切換到其他活頁簿,然後選取貼上區左上角的儲存格

  4. 在 [常用] 索引標籤上的 [剪貼簿] 群組中,按一下 [貼上] 按鈕影像 ,再按一下 [貼上值],或在 Windows 中按 Alt > E > S > V > Enter,或在 Mac 中按 Option > Command > V > V > Enter。

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

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

  2. 按一下 [公式] > [評估值公式]。

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

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

    [評估值公式] 對話方塊

  4. 如果公式中加底線的部分是參照另一個公式,請按一下 [逐步執行],在 [評估] 方塊中顯示另一個公式。按一下 [跳出] 以返回前一個儲存格與公式。

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

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

    [評估值公式] 工具不一定會告訴您公式為何出錯,但可以協助指出錯誤之處。對很難找到問題所在的較大公式而言,這會是相當實用的工具。

    附註: 

    • IFCHOOSE 函數的某些部分不會評估,而且 #N/A 錯誤可能會顯示在 [評估] 方塊中。

    • 空白的參照在 [評估] 方塊中會顯示為零值 (0)。

    • 只要工作表一變更就重新計算的函數, 這些函數 (包括 RANDAREASINDEXOFFSETCELLINDIRECTROWSCOLUMNSNOWTODAYRANDBETWEEN 函數) 可能會造成 [評估值公式] 對話方塊顯示的結果與工作表儲存格中實際結果不同。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

請參閱

Excel 公式概觀

偵測公式中的錯誤

Excel 函數 (按字母排序)

Excel 函數 (依類別)

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×