如何避免公式出錯

如何避免公式出錯

附註: 我們想要以您的語言,用最快的速度為您提供最新的說明內容。本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。讓這些內容對您有所幫助是我們的目的。希望您能在本頁底部告訴我們這項資訊是否有幫助。此為英文文章出處,以供參考。

如果 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)

附註: 您會看到函數的引數列於浮動的函數參照工具列中 (在您輸入的公式底下)。

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

此外,某些功能,例如加總,需要、 數值的引數,而其他函數,例如取代,需要及其引數中至少一個文字值。如果您使用錯誤的資料類型時,可能會傳回預期的結果函數,或顯示#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 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

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

×