在Microsoft Excel中查找循环引用的7种方法

如果您在 Microsoft Excel 中打开工作表时经常看到循环引用错误消息，但不知道如何在 Excel 中查找循环引用，则无需担心。阅读此快速简便的 Excel 教程，了解在 Excel 中查找循环引用的方法。

为了防止数据分析结果不准确，当您在公式语法中使用不正确的参数时，Excel 会显示错误消息。虽然所有公式错误都会显示易于注意的错误消息，例如 DIV/0！、VALUE！、REF！等，循环引用错误不会显示任何此类迹象。

与其他显而易见的公式错误不同，在 Excel 中检测循环引用错误并不那么简单。打开工作表时，错误不会立即突出显示。相反，您需要熟悉在 Excel 中发现循环引用的特定方法。

Excel 中的循环引用是什么？

Excel 中的循环引用是指公式直接或间接引用其自身的单元格，形成循环，从而妨碍 Excel 计算精确结果的能力。

当单元格依赖于其自身的值时，就会出现这种情况，从而形成连续循环。当检测到循环引用时，Excel 通常会显示警告，因为它会导致计算不准确。

解决循环引用需要调整公式或单元格引用以中断循环，确保计算准确且稳定。但是，在工作表中使用循环引用可能存在数学原因。例如，请看以下情况：

在估计贷款偿还金额或反复调整变量以达到预期结果时。
单元格的值会根据动态的外部因素影响其自身的计算，创建循环引用可能是捕获这些动态关系的一种解决方法。

Excel 仅在包含循环引用的单元格中显示 0。您可以将其视为某些公式的结果。如果您知道如何在 Excel 中查找循环引用，则可以测试单元格中的 0 值是合法的还是循环引用的结果。

在 Excel 中查找循环引用的原因

使用手动干预查找循环引用的主要原因是为了避免在应包含有效数字或字符串作为值的单元格中出现 0 值。

当您处理包含数百和数千个计算字段和引用的 Excel 工作表时，您很容易错过伪装成零的循环引用。

如果您从 Excel 选项对话框中启用了迭代计算，则问题通常会变得很严重。当迭代计算处于活动状态时，Excel 不会显示初始循环引用错误消息。

在状态栏上查找循环引用 Excel

这 Excel 状态栏 是在您打开的工作表中查找循环引用最明显的地方。

Excel 状态栏通过显示单元格地址明确显示循环引用的位置，例如 C2 在上面的例子中。

但是，如果您需要在 Excel 工作表中查找多个循环引用，则此方法不适用。您必须解决显示的循环引用错误，然后 Excel 状态栏才会显示下一个错误。

使用追踪箭头查找循环引用 Excel

您可以使用 追溯先例 和 追踪依赖项 工具可直观地突出显示包含循环引用的单元格。如果您的工作表包含多个循环引用，您可以使用此方法快速找到所有循环引用。无需解决一个错误即可转到下一个错误。

突出显示可疑单元格范围后，您需要激活“追踪先例”和“从属项”工具的箭头。如果您看到一条带有两个箭头且边缘有实心点的线，则循环引用位于您选择的两个单元格中的任意一个中。

通过单击包含公式的单元格来突出显示单元格范围。如果您通过单击包含文本字符串或非计算数字的单元格来选择单元格范围，则此方法不起作用。

现在，转到公式选项卡并点击 追溯先例 按钮里面 配方审核 块。此外，单击 追踪依赖项 按钮。

现在您应该会在突出显示的单元格区域上看到一条带箭头和实心点的线。出现箭头和实心点的单元格包含循环引用。

要从工作表中删除箭头，请点击 移除箭头 按钮。

使用错误检查查找循环引用

这 错误检查 工具是另一种在 Excel 中查找循环引用的可视化和交互式方法。

此方法适用于您已解决循环引用但又意外添加了新循环引用的工作表。因此，Excel 不会显示循环引用错误，但错误检查工具会直接带您到包含错误的单元格。

打开工作表并转到公式 > 配方审核 块。点击 错误检查 下拉菜单并将光标悬停在 循环引用 选项。您应该看到与循环引用错误关联的单元格地址。

使用评估公式工具查找循环引用

这 评估公式 该工具还显示所选单元格是否包含循环引用。

只需突出显示可疑单元格并按 Alt 激活功能区菜单。然后按 米 去公式标签，最后按 五 打开的钥匙 评估公式 对话。

以上评价按钮，如果公式有循环引用错误，您应该会看到一条消息。

如果您的 Excel 工作表包含多个公式单元格，请使用去对话框。按 Ctrl + G 提出去对话框并点击 特别的 按钮打开更多条件。

在那里，点击公式选项可突出显示工作表中所有包含公式的单元格。

选定单元格后，转到家 > 字体并点击填色命令突出显示工作表中的所有公式单元格。现在，使用 评估公式 如前所述的工具。

禁用 Excel 功能以查找循环引用

当 Excel 的迭代计算功能处于活动状态时，打开工作簿时 Excel 不会显示循环引用错误消息。

因此，如果您怀疑工作簿包含循环引用错误，但没有警告消息，请打开 Excel 选项 对话来自文件 > 选项。

在 Excel 选项 对话框中，转到公式类别，然后取消选中 启用迭代计算 功能。单击好的按钮。

现在，关闭并重新打开 Excel 工作簿以查看循环引用警告消息。

审核选择公式以查找循环引用

公式如下如果，抵消，间接，和，查找，指数等经常导致 Excel 工作表中出现循环引用。因此，如果您找到包含上述公式的所有单元格，并使用 评估公式 定位循环引用的工具。

提出 查找和替换 对话框并查找如果公式。为此，请输入如果在“查找内容”字段中，然后单击 找到所有。

菜单将显示在 查找和替换 包含所有单元格地址引用的对话框如果公式。

通过按 Ctrl + A 里面的钥匙 查找和替换 结果菜单。Excel 将突出显示工作表上所有引用的单元格。

现在，使用填色工具，方法是导航至家标签 > 字体命令块。

现在，对其他可疑配方重复上述过程，例如抵消，间接，和， ETC。

按照 基于公式评估工具 在Excel中找出循环引用的方法。

使用 Excel VBA 查找循环引用 Excel

到目前为止解释的所有方法中，这 基于 Excel VBA 方法是最快的。您只需运行一个宏，Excel 就会突出显示所有包含循环引用错误的单元格。

要创建 VBA 宏来突出显示所有循环引用，请按 Alt + F11 提出 Excel VBA 编辑器。点击插入按钮并选择模块。在弹出的空白模块中，复制并粘贴以下VBA脚本：

Sub HighlightCircularReferences()
    Dim cell As Range
    Dim formula As String
    Dim circular As Boolean
    
    ' Loop through each cell in the active sheet
    For Each cell In ActiveSheet.UsedRange
        ' Check if the cell has a formula
        If cell.HasFormula Then
            ' Get the formula of the cell
            formula = cell.Formula
            
            ' Check if the formula contains circular references
            If HasCircularReference(cell) Then
                ' Highlight the cell in light red
                cell.Interior.Color = RGB(255, 200, 200)
            End If
        End If
    Next cell
End Sub

Function HasCircularReference(cell As Range) As Boolean
    Dim circular As Boolean
    Dim precedent As Range
    
    ' Check each precedent of the cell
    For Each precedent In cell.Precedents
        ' Check if the precedent refers back to the original cell
        If precedent.Address = cell.Address Then
            circular = True
            Exit For
        End If
    Next precedent
    
    ' Return whether circular references were found
    HasCircularReference = circular
End Function

点击节省按钮 Excel VBA 编辑器 界面。

这 微软 Excel 对话框将打开。点击不到达 另存为 对话。

在 另存为 对话框中，点击 保存类型 下拉，选择 Excel 宏启用工作簿 (XLSM) 文件类型，然后单击节省。

你现在可以关闭 Excel VBA 编辑器。

按 Alt + F8 推出 Excel 宏 对话框并选择 突出显示循环引用 宏。命中跑步执行脚本。

Excel 将自动突出显示所有包含循环引用错误的单元格。该宏仅适用于 Excel 工作簿的活动工作表。要查找另一个工作表中的循环引用，请转到该工作表并再次运行该宏。

结论

您可以按照上面提到的任何一种方法来查找工作表中包含循环引用的单元格，以解决公式错误。

如果你是初级和中级 Excel 用户，请尝试涉及 状态栏， 追踪箭头， 错误检查， 评估公式，和
禁用迭代计算。

我还提到了一些必须密切跟踪的公式，以防出现循环引用错误。

最后，如果您是专家用户或 Excel VBA 爱好者，您应该尝试基于 VBA 宏的方法。

并非所有循环引用都是错误。大多数情况下，您应该解决生成零值的循环引用。

如果您发现这篇文章有用，请不要忘记在下面发表评论！

在Microsoft Excel中查找循环引用的7种方法