如何避免公式出錯

如何避免公式出錯

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

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

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

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

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

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

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

Excel 會引發各種磅(#)錯誤,例如 #VALUE!、#REF!、#NUM、#N/A,#DIV/0!,#NAME?,以及 #Null!,以指出公式中的內容無法正常運作。 例如,#VALUE! 錯誤是由引數中格式不正確或不支援的資料類型所導致。 或者,您會看到 #REF! 如果公式所參照的儲存格已被刪除,或已被其他資料取代,就會發生錯誤。 疑難排解指南會因每個錯誤而有所不同。

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

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

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

每當您開啟的試算表中包含的公式參照其他試算表中的值時,系統會提示您更新參照,或將其保留原樣。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

當您在公式中使用函數時,每個左括弧都需要右括弧,函數才能正常運作,因此請確定所有括弧都是成對的一部分。 例如,公式= IF (B5<0),"無效",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,"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. 在 [常用] 索引標籤上,按一下 [剪貼簿] 群組中的 [按鈕影像 ],然後按一下 [貼],或按Alt > E > S > V > 輸入For Windows,或在 Mac 上使用Option > 命令 > v > enter

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

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

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

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

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

    [評估值公式] 對話方塊

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

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

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

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

    附註: 

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

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

    • 每次工作表變更時重新計算的函數。 這些函數(包括RAND區域INDEXOFFSETCELL間接、列ROWSNOWTODAYRANDBETWEEN函數)會導致 [評估公式] 對話方塊顯示與工作表上儲存格中實際結果不同的結果。

需要更多協助嗎?

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

另請參閱

Excel 公式概觀

偵測公式中的錯誤

Excel 函數 (按字母排序)

Excel 函數 (依類別)

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×