如何在 Excel 中查找循環引用

使用Microsoft Excel時，您可能偶爾會遇到所謂的循環引用。這是當單元格中的公式引用回自身時，導致計算過程中的死循環。

要在 Excel 中查找循環引用，請使用“公式”選項卡下的“錯誤檢查”工具來識別具有循環依賴性的單元格。

本文介紹如何在 Excel 中查找循環引用並修復它們。您還將學習如何在處理大型或複雜工作表時避免它們。

目錄

• 什麼是循環引用
• 循環引用錯誤消息窗口
• 如何在 Excel 中查找循環引用
• 如何使用 Excel 狀態欄查找循環引用
• 如何在 Excel 中查找隱藏的循環引用
  • 如何使用 Trace Dependent 工具
  • 追踪先例工具
• 如何在 Excel 中刪除循環引用的 2 種方法
  • 2.使用替代公式
• 防止循環引用的 5 個技巧
  • 1.選擇範圍時要小心
  • 2. 仔細計劃你的公式
  • 3.使用替代公式
  • 4. 將復雜的公式分解成更小的部分
  • 5.邊走邊解決問題
• 如何啟用迭代計算以及為什麼要這麼麻煩？

什麼是循環引用

當單元格中的公式計算取決於單元格自身的值時，Excel 中就會出現循環引用。這會創建一個循環並導致不正確或不可預測的結果。

這通過一個簡單的例子更容易理解。

假設單元格 A1 和 B1 中有兩個數字。您想要在單元格 A3 中使用將兩個數字相加的公式。單元格 A3 中的公式應如下所示：

=A1 + B1

但是，如果您犯了一個錯誤，將 C1 包括在計算中，您現在就有了一個循環引用：

=A1 + B1 + C1

在處理兩個單元格時，這可能是一個明顯需要避免的問題。但是，當您處理大範圍的單元格時，更容易出錯。

值得慶幸的是，Excel 旨在通過在檢測到循環引用時顯示警告消息來處理此類情況。

但是，在某些情況下，您可能會在沒有收到任何警報的情況下遇到循環引用。因此，了解如何手動查找和解決這些問題非常重要。

循環引用錯誤消息窗口

默認情況下，Excel 會在您將公式輸入單元格時監視循環引用。當您在有問題的單元格中按回車鍵時，彈出窗口將顯示循環引用錯誤消息。

這是說明文字：

存在一個或多個循環引用，其中公式直接或間接引用它自己的單元格。這可能會導致他們計算錯誤。嘗試刪除或更改這些引用或將公式移動到不同的單元格。

當您看到此對話框時，您應該單擊“確定”並使用本文中的方法來解決問題。

但是，您不需要立即修復錯誤。您可以繼續使用電子表格，以便稍後解決問題。

在這種情況下，您需要知道在關閉警告框後如何在 Excel 中查找循環引用。

如何在 Excel 中查找循環引用

Excel 有一個內置的錯誤檢查工具，可以發現多種錯誤，包括循環引用。您可以在“公式”選項卡上訪問該工具。

此圖顯示了公式審核組中的位置。

現在您知道如何找到該工具了，下面是使用它的詳細步驟：

1. 轉到公式選項卡。

1. 單擊公式審核組中的錯誤檢查下拉菜單。

2. 從下拉列表中選擇“循環引用”。

3. 單擊鏈接可將光標跳到有問題的第一個單元格。

下圖顯示該工具在單元格 G1 中發現了一個循環錯誤。顯示的單元格是超鏈接。

當您單擊該鏈接時，您的光標將移動到該單元格。這對於大量數據非常有用。

解決了第一個循環引用後，您可以返回到錯誤檢查工具以在工作表上查找下一個循環引用。

使用本文後面部分中的步驟解決問題，並使用該工具轉到下一個問題。

如果沒有更多的循環問題，下拉菜單中的“循環引用”菜單項將變灰。你沒有進一步的工作要做。

如何使用 Excel 狀態欄查找循環引用

發現循環引用的另一種方法是查看 Excel 工作表底部的狀態欄。

當工作表中存在一個或多個問題時，狀態欄會顯示一個問題單元格引用的位置。

例如，如果單元格 C1 中存在循環引用並且電子表格沒有其他問題，則狀態欄將顯示為：“循環引用：C1”。

這張圖片告訴您在哪裡可以看到顯示。

如何在 Excel 中查找隱藏的循環引用

Excel中的隱藏循環引用是內置錯誤功能無法立即檢測到的循環引用。換句話說，Excel 不顯示標準警告。

當一個單元格通過一系列中間單元格間接引用另一個單元格並最終引用回原始單元格時，就會發生這種情況。

Excel 有兩個工具可幫助您跟踪關係並調查這些問題：

1. 跟踪依賴工具

2. 追踪先例工具

如何使用 Trace Dependent 工具

有經驗的 Excel 用戶利用 Trace Dependents 工具來調查這些問題並修復循環引用。通過選擇一個或多個單元格並使用此工具，Excel 將顯示相關單元格。

該工具位於“公式”選項卡上的“公式審核”部分。

要使用該工具，請按照下列步驟操作：

1. 單擊要為其跟踪依賴項的一個或多個單元格。

2. 轉到 Excel 功能區中的“公式”選項卡。

3. 單擊“公式審核”組中的“跟踪依賴項”按鈕。

4. 查看示踪箭頭或顯示相關性的箭頭。

Excel 顯示從活動單元格值到依賴它的任何單元格的箭頭。下圖中的藍色箭頭表示單元格 D6 對單元格 B2 具有依賴性。

追踪先例工具

先例與從屬相反。Trace Precedents 工具允許您選擇一個帶有公式的單元格並查看它所依賴的所有單元格，即先例。

該工具位於“公式”選項卡上的“公式審核”部分。

要使用該工具，請按照下列步驟操作：

1. 單擊包含要調查的公式的單元格。

2. 轉到 Excel 功能區中的公式選項卡。

3. 單擊“公式審核”組中的“跟踪先例”按鈕。

4. 查看 Excel 顯示的跟踪箭頭。

下圖中的藍色箭頭顯示單元格 D6 取決於單元格 A2:B5 的範圍。

使用這兩種工具可以幫助您找到不是很明顯的 Excel 循環引用。下一步是解決所選單元格中的問題。然後您可以繼續在其他單元格中搜索相同的問題。

如何在 Excel 中刪除循環引用的 2 種方法

解決循環引用的方法主要有兩種：

• 打破循環引用

• 使用替代公式

讓我們解釋一下這兩種方式。

1. 打破循環引用

打破 Excel 中的循環引用是解決該問題的有效方法。這需要您識別循環引用中涉及的單元格並相應地修改它們的公式。

為此，請按照前面描述的步驟查找單元格地址。

然後您應該檢查公式欄中的公式。查找對單元格本身的引用。

在我們的第一個循環引用示例中，單元格 C1 中的加法公式包含對單元格本身的引用：

=A1 + B1 + C1

這可以通過刪除最後的添加來解決。解決此問題後，您將繼續尋找其他循環引用並解決它們。

2.使用替代公式

循環引用的一些最常見原因是在求和或平均單元格時。錯誤是將結果單元格包含在公式中。

在我們之前的示例中，公式使用加法運算符來計算一系列單元格的總和。另一種方法是使用 SUM Excel 函數。

要修復該示例，請將公式替換為：

=總和(A1:B1)

同樣，您可以使用類似 =(A1+B1)/2 的公式對范圍進行平均。但是，AVERAGE 函數不太可能導致錯誤。

=平均（A1：B1）

防止循環引用的 5 個技巧

為防止 Microsoft Excel 中的循環引用，必須清楚地了解公式如何在 Excel 工作表中相互交互。

通過識別單元格之間的關係並了解這些連接如何協同工作，您可以避免創建循環公式。

以下是幫助您防止循環引用的五個最佳提示。

1.選擇範圍時要小心

根據我的經驗，循環引用的最常見原因之一是用戶快速選擇範圍以包含在他們的公式中。

垂直和/或水平滑動以對單元格進行分組時，很容易再選擇一個與正確範圍相鄰的單元格。如果該單元格也是結果單元格，則您無意中創建了循環引用。

在處理大量數據時，例如運行卡方檢驗時，通常會犯此錯誤。解決方案是謹慎選擇範圍。

2. 仔細計劃你的公式

在輸入公式之前，請考慮計算背後的邏輯以及它們應如何跨單元格流動。

您可以使用圖表工具或白板提前繪製出計算圖。

這可以幫助您識別可能導致循環引用的任何可能的循環。

3.使用替代公式

某些函數，如INDIRECT或OFFSET，在使用不當時可能會無意中創建循環引用。

考慮使用提供類似功能而沒有循環引用風險的替代函數。

4. 將復雜的公式分解成更小的部分

與其創建具有多個嵌套函數的單個複雜公式，不如考慮將計算拆分為更小、更易於管理的部分。

這不僅使您的工作簿更易於理解和維護，而且還減少了引入循環引用的機會。

5.邊走邊解決問題

在處理大型或複雜的工作簿時，監視循環引用也很重要。與其在匯集大量數據和公式之前忽略問題，不如在每個錯誤出現時修復它。

您可以選擇暫時忽略循環引用警告消息。但是，您應該使用錯誤檢查功能來及時趕上現有的循環引用。

這使它們更容易修復並防止它們對您的工作簿產生影響。

如何啟用迭代計算以及為什麼要這麼麻煩？

您通常希望避免在工作表中出現循環引用。但是，在某些情況下它們是必要的。

例如，一些工程或科學計算需要使用以前計算的結果。這稱為迭代解決方案。

每個循環都會改進計算結果，直到達到特定的閾值或目標。

在這些情況下，必須在 Excel 設置中啟用迭代計算以允許預期的循環引用。這允許軟件重複計算指定的次數，收斂到一個解決方案。

請按照以下步驟在 Excel 文件中使用循環引用：

1. 單擊文件選項卡。

2. 選擇選項。

3. 選擇公式類別。

4. 在“計算選項”部分下，選中“啟用迭代計算”框。

5. 更改最大迭代次數框中的數字或保留默認值 100。

6. 根據您的要求使用最大更改框來減少迭代。