您已輸入公式,但無法運作。 相反地,您會收到關於「循環參照」的訊息。 數百萬使用者有相同的問題,而問題發生的原因在於您的公式嘗試自行計算。 看起來會像這樣:
無法計算公式 =D1+D2+D3,因為公式本身即位於儲存格 D3,且嘗試計算本身所在的儲存格。 若要修正問題,您可以將公式移到另一個單元格。 按 Ctrl+X 剪下公式,選取另一個單元格,然後按 Ctrl+V 貼上。
另一個常見的錯誤是使用包含自身參照的函數;例如,儲存格 F3 包含 =SUM(A3:F3)。 以下是範例:
您也可以嘗試下列其中一種技巧:
-
如果您剛輸入一個公式,請從該儲存格開始檢查您是否參照到該儲存格本身。 例如,儲存格 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 後停止運算 (以先達成者為準)。 然而,您可以控制反覆運算的最高次數及可接受的誤差量。
-
選取 [公式] > [檔案 > 選項]。 如果您使用的是 Mac 版 Excel,請選取 [Excel ] 選單,然後選取 [喜好設定 > 計算]。
-
選取 [計算選項] 區段中的 [啟用反覆運算] 核取方塊。 在 Mac 上,選取 [使用迭代]。
-
若要設定 Excel 重新計算的最高次數,請在 [最高次數] 方塊中輸入反覆運算的次數。 反覆運算的次數愈高,Excel 計算工作表所需的時間愈長。
-
在 [最大誤差] 方塊中,輸入繼續進行反覆運算所需的最小值。 這是任何計算結果的最小誤差量。 數字越小,結果的精確度越高,但 Excel 計算工作表所需的時間也就越長。
反覆運算可能會有三個結果:
-
解會收斂,這表示得到穩定的最終結果。 這是理想的情況。
-
解會發散,這表示在反覆運算之間,目前結果與先前結果間的差異增加。
-
解會在兩個值之間變換。 例如,第一次反覆運算結果為 1,下一次反覆運算結果為 10,再下一次反覆運算結果為 1,以此類推。
需要更多協助嗎?
您可以隨時詢問 Excel 技術社群中的專家,或在社群中取得支援。
提示: 如果您是小型企業擁有者,且想深入了解如何設定 Microsoft 365,請造訪小型企業說明與學習。