如何避免中斷的公式

Excel 會傳回各種井字型大小 (#) 錯誤，例如 #VALUE！、#REF！、#NUM、#N/A、#DIV/0！、#NAME？和 #Null！，以指出公式中的某個專案無法正常運作。 例如，#VALUE！ 錯誤是引數中格式設定錯誤或不支援的資料類型所造成。 或者，您會看到#REF！ 錯誤。 疑難排解指導方針會因每個錯誤而有所不同。

請參閱下列任何對應到井字錯誤的主題：

• 修正 #NUM! 錯誤

• 修正 #VALUE! 錯誤

• 修正 #N/A 錯誤

• 修正 #DIV/0! 錯誤

• 修正 #REF! 錯誤

• 修正 #NAME? 錯誤

• 修正 #NULL! 錯誤

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

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

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

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

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

例如，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 取代為星號 (*) 以修正公式。

不過，如果您使用儲存格參照，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 層的函數。

例如，公式 =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)。

將儲存格除以值為零 (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. 在 [ 常用] 索引標籤的 [ 剪貼簿 ] 群組中，選取 [貼 上 ]，然後選取 [貼上值]，或按 Alt > E > S > V > Enter for Windows 或 Option > Command > V > V > Enter 。

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

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

2. 選取 [公式 > 評估公式]。

   

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

   

4. 如果公式加上底線的部分是參照其他公式，請選取[步入] 以在 [ 評估 ] 方塊中顯示另一個公式。 選取 [逐步執行 ] 返回上一個儲存格和公式。

   當參照第二次出現在公式中時，或如果公式參照到另一個活頁簿中的儲存格，就無法使用 [ 逐步 執行] 按鈕。

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

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

   附註: 

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

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

   • 每次工作表變更時，都會重新計算某些函數。 這些函數，包括 RAND、 AREAS、 INDEX、 OFFSET、 CELL、 INDIRECT、 ROWS、 COLUMNS、 NOW、 TODAY和 RANDBETWEEN 函數，可能會導致 [ 評估公式 ] 對話方塊顯示的結果與工作表儲存格中的實際結果不同。