如何避免 Excel 中的公式出錯

如果 Excel 無法解析你正在建立的公式，可能會收到像這樣的錯誤訊息：

不幸的是，這代表 Excel 無法理解你的意圖，所以你需要更新公式或確保你正確使用函式。

回到有壞掉公式的儲存格，該儲存格會進入編輯模式，Excel 會標示出問題所在。 如果你還是不知道該怎麼做，想重新開始，可以再按 一次 ESC ，或在公式列中選擇 取消 按鈕，這樣就能脫離編輯模式。

如果你想繼續前進，以下清單提供了故障排除步驟，幫助你找出可能出錯的地方。 選取標題以深入了解。

您是否在公式中使用正確的清單分隔符號?

具有一個以上參數的公式會使用清單分隔符號來分隔其參數。 使用哪個分隔符號可能會根據您的作業系統區域設定和 Excel 設定而有所不同。 最常見的清單分隔符號是逗號「,」和分號「;」。

如果公式中的任何函式使用了錯誤的分隔符號，公式便會失效。

欲了解更多資訊，請參閱： 當列表分隔符未正確設定時的公式錯誤

你有看到 Excel 公式中有磅數錯誤 (#) 嗎？

Excel 會產生各種磅數 (#) 錯誤，例如 #VALUE！、#REF！、#NUM、#N/A、#DIV/0！、#NAME？，以及 #NULL！ 表示你的配方中某些東西運作不正常。 例如，#VALUE! 錯誤發生的原因是不正確的格式設定或引數中有不支援的資料類型。 或者，您將看到 #REF! 錯誤，前提是公式參照的儲存格已刪除或已取代為其他資料。 每個錯誤的疑難排解指南都會不同。

參考以下與您所見的磅數誤差相關的主題：

• 修正 #NUM! 錯誤
• 修正 #VALUE! 錯誤
• 修正 #N/A 錯誤
• 修正 #DIV/0! 錯誤
• 修正 #REF! 錯誤
• 修正 #NAME? 錯誤
• 修正 #NULL! 錯誤

修正 Excel 公式中斷裂的連結

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

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

您可以隨時停用對話方塊，避免在開機時顯示。 要做到這點，請到 檔案 > 選項 > 的進階 > 一般，並清除 「請求更新自動連結 」的選項。

公式在 Excel 中顯示的是語法而非數值

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

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

  秘訣

  您也可以使用鍵盤快速鍵 Ctrl + ` (在 Tab 鍵上方的按鍵)。 這樣做後，欄位會自動放大顯示公式，但別擔心，當你切回正常視圖時，欄位會自動調整大小。

• 如果上述步驟仍無法解決問題，有可能是已將儲存格的格式設定為文字。 你可以右鍵點擊儲存格，選擇格式 化儲存格 > 一般 (或 Ctrl + 1) ，然後按 F2 > Enter 。

• 如果你有一欄包含大量格式為文字的儲存格，你可以選擇範圍，套用你想要的數字格式，然後前往 「資料 > 文字至欄位 > 結束」頁面。 這會將格式設定套用到所有選取的儲存格。

  

如果公式在 Excel 中無法計算，請啟用自動工作簿計算

當公式無法計算時，您必須檢查是否已在 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，「無效」，B5*1.05) 。

語法中是否有所有必要引數？

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

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

此外，有些函式，如 SUM，僅需數值參數，而其他函式，如 REPLACE，則至少需要一個參數的文字值。 如果您使用錯誤的資料類型，函數可能會傳回未預期的結果，或顯示 #VALUE! 錯誤。

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

處理 Excel 公式中未格式化的數字

不要在公式中輸入用美元符號 ($) 或小數分隔符 (，) 的數字，因為美元符號表示 絕對參考 ，逗號則是參數分隔符。 您必須在公式中輸入 1000，而非 $1,000。

如果你在參數中使用格式化的數字，你會得到意想不到的計算結果，但你也可能看到 #NUM！ 錯誤。 舉個例說，如果您輸入 =ABS(-2,134) 這個公式來尋找 -2134 的絕對值，Excel 便會顯示 #NUM! 錯誤，因為 ABS 函數只接受一個引數，而且它會將 -2 和 134 視為不同的引數。

參考的儲存格是否屬於正確的資料類型？

如果儲存格的資料型別無法用於計算，你的公式可能無法回傳預期結果。 舉例來說，如果你在一個格式為文字的儲存格輸入一個簡單的公式 =2+3，Excel 就無法計算你輸入的資料。 您只會在儲存格中看到 =2+3。 要解決這個問題，請將儲存格的資料類型從 Text 改成 General ，如下：

1. 選取儲存格。
2. 選取 [常用]，然後選取箭號以展開 [數字] 或 [數字格式] 群組 (或按 Ctrl + 1)。 然後選取 [一般]。
3. 按 F2 讓儲存格進入編輯模式，然後按 Enter 接受公式。

你在使用 Number 資料型別的儲存格輸入的日期，可能會以數字日期值而非日期顯示。 若要以數字顯示日期，在 [數值格式​​] 庫中選取 [日期] 格式。

乘法是否沒有包含 * 符號？

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

訊息不過，如果你使用儲存格參考，Excel 會回傳一個 #NAME？錯誤。

是否沒有使用引號括住公式中的文字？

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

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

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

公式中的函數是否超過 64 個？

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

例如，公式 =IF (SQRT (PI () ) <2，「小於二！」，「多於二！」) 有三個功能層級; PI 函數 嵌套於 SQRT 函數中，SQRT 又嵌套於 IF 函數中。

工作表名稱是否用單引號括住？

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

舉個例說，如果您要在活頁簿中傳回 Quarterly Data 工作表中 D3 儲存格的值，請輸入：='Quarterly Data'!D3。 如果沒有在選票名稱周圍加上引號，公式就會顯示 #NAME？錯誤。

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

修正 Excel 公式中外部工作簿路徑

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

例如，要參考 Excel 中開啟的 Q2 營運工作簿中銷售表中的儲存格 A1 到 A8，輸入： =[Q2 Operations.xlsx]Sales！A1：A8。 如果沒有方括號，公式會顯示 #REF！錯誤。

如果 Excel 裡沒有開啟工作簿，請輸入完整的檔案路徑。

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

您是否將數值除以零？

將儲存格除以值為零 (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. 在 主頁 分頁的 剪貼簿 群組中，選擇 複製 。
   
   鍵盤快速鍵：按 CTRL+C。

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

   秘訣

   若要將選取範圍移動或複製到不同的工作表或活頁簿，請選取其他工作表索引標籤，或切換到其他活頁簿，然後選取貼上區的左上角儲存格。

4. 在主頁分頁的剪貼簿群組中，選擇「貼上」，然後選擇「貼上值」，或在 Windows 按 Alt > E > S > V > ENTER，Mac 則按 Option > Command > V > V Enter>。

如果您有巢狀公式，評估公式時請一次進行一個步驟

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

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

2. 選擇 公式>評估公式。
   

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

4. 如果公式中加底線的部分是參照另一個公式，請選取 [逐步執行]，在 [評估] 方塊中顯示另一個公式。 若要返回前一個儲存格與公式，請選取 [跳出]。
   Step In 按鈕在公式中第二次出現時無法使用，或當公式指向其他工作簿中的儲存格時。

5. 繼續作業，直到公式的每一個部分都評估完畢。
   [評估公式] 工具不一定會告訴您公式為何出錯，但可以協助指出錯誤之處。 對很難找到問題所在的較大公式而言，這會是相當實用的工具。

   注意

   • IF 和 CHOOSE 函數的部分不會被評估，#N/A 錯誤可能會出現在評估框中。
   • 空白的參照在 [評估] 方塊中會顯示為零值 (0)。
   • 某些函數會每次工作表變更時重新計算。 這些函數，包括 RAND、AREAS、INDEX、OFFSET、CELL、INDIRECT、ROWS、COLUMNS、NOW、TODAY 和 RANDBETWEEN，可能會造成 [評估公式] 對話方塊顯示的結果與工作表儲存格中實際結果不同。

需要更多協助嗎？

你隨時可以向 Excel 技術社群 的專家詢問，或在 社群中獲得支援。

另請參閱

Excel 公式概觀

Excel 學習 & 幫助