移除或允許循環參照

您已輸入公式,但公式並未發揮作用, 反而出現了關於「循環參照」的訊息。已經有非常多使用者遇到相同的問題,這個問題的發生原因是公式嘗試計算自己本身,但您的反覆運算功能卻為關閉。看起來會像這樣:

一個公式將造成循環參照

無法計算公式 =D1+D2+D3,因為公式本身即位於儲存格 D3,且嘗試計算本身所在的儲存格。若要修正這個問題,您可以將公式移至另一個儲存格 (在資料編輯列中,按 Ctrl+X 剪下公式,選取另一個儲存格,然後按 Ctrl+V 貼上)。

另一個常見的錯誤是使用包含自身參照的函數;例如,儲存格 F3 包含 =SUM(A3:F3)。以下是範例:

您的瀏覽器不支援視訊。請安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

您也可以嘗試下列其中一種技巧:

  • 如果您剛輸入一個公式,請從該儲存格開始檢查您是否參照到該儲存格本身。例如,儲存格 A3 可能包含公式 =(A1+A2)/A3。像是 =A1+1 (在儲存格 A1 中) 的公式也會造成循環參照錯誤。

尋找錯誤時,請留意間接參照。間接參照會發生在當您將公式置於儲存格 A1 中,而 A1 又使用 B1 中的另一個參照回儲存格 A1 的公式時。如果這個狀況對您來說有點難懂,對 Excel 而言也不太能理解。

  • 如果您找不到錯誤,請按一下 [公式] 索引標籤,再按一下 [錯誤檢查] 旁邊的箭號,指向 [循環參照],然後按一下子功能表中列出的第一個儲存格。

    循環參照命令

  • 檢查儲存格中的公式。如果無法判斷儲存格是否為造成循環參照的原因,請按一下 [循環參照] 子功能表中的下一個儲存格。

  • 重複步驟 1 至 3 中的任何或所有步驟,繼續檢查並修正活頁簿中的循環參照,直到狀態列不再顯示 [循環參照] 為止。

祕訣   

  • 左下角的狀態列會顯示 [循環參照],以及某項循環參照的儲存格位址。

    如果其他工作表中有循環參照,但使用中工作表內沒有,狀態列只會顯示 [循環參照],而不會顯示儲存格位址。

  • 按兩下追蹤箭號,即可在循環參照中的儲存格之間移動。箭號會指出影響目前所選儲存格值的儲存格。若要顯示追蹤箭號,請按一下 [公式],然後按一下 [追蹤前導參照] 或 [追蹤從屬參照]。

    追蹤前導參照

了解循環參照警告訊息

Excel 第一次找到循環參照時,會顯示警告訊息。按一下 [確定] 或關閉訊息視窗。

當您關閉訊息時,Excel 會在該儲存格中顯示零或上一個計算值。您可能會問「等等,上一個計算值?」,沒錯。在某些情況下,公式在嘗試計算自身之前也可以順利執行。例如,使用 IF 函數的公式可能運作正常,但在使用者輸入引數 (公式正常運作所需的一段資料) 後就會計算其本身。發生此狀況時,Excel 即會保留最後一次成功計算的值。

如果您懷疑儲存格中有循環參照,但沒有顯示零,請嘗試下列作業:

  • 在資料編輯列中按一下公式,然後按 Enter。

重要    在許多情況下,如果您建立其他包含循環參照的公式,Excel 不會再顯示警告訊息。下列清單顯示部分 (但非全部) 會出現警告訊息的情況:

  • 在任何開啟的活頁簿中建立第一個循環參照執行個體

  • 在所有開啟的活頁簿中移除所有循環參照,然後建立新的循環參照

  • 關閉所有的活頁簿、建立新的活頁簿,然後輸入包含循環參照的公式

  • 開啟包含循環參照的活頁簿

  • 在沒有開啟任何其他活頁簿的情況下,開啟某個活頁簿,然後建立循環參照

了解反覆運算

有時候,您可能想要使用循環參照,因為循環參照會使函數反覆運算,亦即重複運算直到符合特定的數值條件為止。這可能會讓電腦的速度慢下來,因此 Excel 通常會關閉反覆運算。

除非您了解反覆運算功能,否則您可能不需要保留任何循環參照。如果您了解反覆運算功能,您可以啟用反覆運算,但您必須決定公式應重複計算的次數。當您開啟反覆運算而未變更最高次數或最大誤差的值時,Excel 將在達到 100 次反覆運算次數後,或在所有在反覆運算間得到的循環參照誤差值小於 0.001 後停止運算 (以先達成者為準)。然而,您可以控制反覆運算的最高次數及可接受的誤差量。

  1. 如果您使用的是 Excel 2010 或更新版本,請按一下 [檔案] > [選項] > [公式]。如果您使用的是 Mac 版 Excel,請按一下 [Excel] 功能表,然後按一下 [喜好設定] > [計算]。

    如果您是使用 Excel 2007,請按一下 [Microsoft Office 按鈕] Office 按鈕影像 、按一下 [Excel 選項],然後按一下 [公式] 類別。

  2. 在 [計算選項] 區段中,選取 [啟用反覆運算] 核取方塊。在 Mac 上,按一下 [使用反覆運算]。

  3. 若要設定 Excel 重新計算的最高次數,請在 [最高次數] 方塊中輸入反覆運算的次數。反覆運算的次數愈高,Excel 計算工作表所需的時間愈長。

  4. 在 [最大誤差] 方塊中,輸入繼續進行反覆運算所需的最小值。這是任何計算結果的最小誤差量。數字越小,結果的精確度越高,但 Excel 計算工作表所需的時間也就越長。

反覆運算可能會有三個結果:

  • 解會收斂,這表示得到穩定的最終結果。這是理想的情況。

  • 解會發散,這表示在反覆運算之間,目前結果與先前結果間的差異增加。

  • 解會在兩個值之間變換。例如,第一次反覆運算結果為 1,下一次反覆運算結果為 10,再下一次反覆運算結果為 1,以此類推。

頁面頂端

需要更多協助嗎?

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

另請參閱

Excel 中的公式概觀

如何避免公式出錯

尋找並校正公式中的錯誤

Excel 的鍵盤快速鍵及功能鍵

Excel 函數 (依英文字母順序排列)

Excel 函數 (依類別排序)

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×