Excel 中的循环引用

通常，当您在单元格中输入公式来计算某些数据时，您会提供其他单元格的引用。但有时，您会发现当您在 Excel 工作表中输入公式时，它不起作用。当您在公式中使用结果单元格引用时，就会发生这种情况。因此，您会收到关于发生循环引用的警告消息。

当您的公式尝试引用自身且称为“**迭代计算**”的 Excel 功能可能关闭时，您会遇到此问题。公式可能看起来像这样 -

在这里，C3 单元格包含一个公式，该公式对三个单元格执行加法，包括 C3 单元格在公式中进行计算。因此，循环引用是指计算自身引用的公式。

本章讨论的主题

本章讨论的主题如下 -

• 什么是循环引用？
• 循环引用示例
• 什么是迭代计算？
• 启用或禁用迭代计算
• 检测循环引用
• 如何移除循环引用？
• 为什么应避免循环引用
• 循环引用的类型
• 如何查找隐藏的循环引用

什么是循环引用？

当公式直接或间接引用其自身单元格时，就会发生循环引用。

直接循环引用

当公式在执行任何操作时直接包含结果单元格的引用时，就会发生直接循环引用。在以下示例中，公式写在 C3 单元格中，并在公式中包含其自身单元格 (C3) 的引用。

当您按 Enter 键时，会弹出一个窗口显示警告消息。

单击此警告弹出窗口上的“确定”按钮将其关闭，Excel 将返回 0。

间接循环引用

当不同的公式以循环方式依赖于彼此单元格的数据时，就会发生间接循环引用。以下是间接循环引用的示例。

为此，我们首先在 C1 单元格中取一个数值 (20)。

步骤 1： 现在，我们将此值乘以 2 并将其存储在 E5 单元格中。这样，E3 单元格就引用了 C1 单元格。

步骤 2： 按 Enter 键，您将在 E5 单元格中获得计算结果。

步骤 3： 现在，对 E5 单元格的值再执行一次乘法运算，将其乘以 2 并存储在 A5 单元格中。此处，A3 引用了 D3 单元格。

步骤 4： 按 Enter 键，您将在 A5 单元格中获得计算结果。

到目前为止，此工作表中没有循环引用。现在，如果我们对 A5 单元格的数据执行任何操作，其结果将存储在 C1 单元格中，这将创建一个间接循环引用。

步骤 5： 删除 C1 单元格中的上一个值，并在其中写入以下公式。

=A5*2

步骤 6： 按 Enter 键，将生成循环引用错误消息。

步骤 7： 在此警告消息上，单击“确定”按钮。您将看到它返回 0，并且还会显示创建的循环引用。

步骤 8： 转到“**公式**”选项卡，然后在“公式审核”组中单击“**显示公式**”选项。

步骤 9： 它将启用所有三个单元格中使用的公式。请参见下图 -

步骤 10： 双击任何一个单元格；您将看到这三个单元格之间的循环引用。

E3 单元格引用 C1，C1 单元格引用 A3 单元格，A3 单元格引用 E3。

循环引用示例

在下面的示例中，我们计算了六个月销售额的总和 (单元格 B2:B7)，并将结果存储在 B8 单元格中。此计算没有问题，并且计算顺利完成。

但如果您意外地选择了包含结果的 B8 单元格引用而不是 B7。因此，我们执行的计算包括结果单元格，如下所示 -

当您按 **Enter** 键时，它会给出警告，因为这种计算是不可能的。Excel 会向用户警告一个或多个循环引用。

当您单击此弹出警告上的“**确定**”按钮时，此警告将关闭。您也可以选择“**帮助**”选项获取更多信息。

如果您单击“确定”，则值 0 将填充到选定用于保存结果的 B8 单元格中。

什么是迭代计算？

“**迭代计算**”允许用户执行循环引用，并通过结果单元格引用重新计算数据。因此，您可以允许在 Excel 工作表中进行循环引用。

当公式包含结果所在的单元格时，就会发生循环引用。它可以是直接的，也可以是间接的。有时，您可能需要循环引用。但是，当您意外使用循环引用时，它会返回 0，这是一个意外的结果。

Excel 提供迭代计算功能，允许用户执行重复计算，直到满足特定条件。您可以启用 Excel 中的迭代计算选项以使循环引用成为可能。

启用或禁用迭代计算

在某些情况下，迭代计算会重复重新计算，直到满足特定条件。如果您想使用循环引用，您必须在 Excel 的 **文件** 菜单中启用 **启用迭代计算**。默认情况下，此选项 (启用迭代计算) 是关闭的（未勾选）。

步骤 1： 在 Excel 中单击“**文件**”选项卡。

步骤 2： 在 Excel 后台的左侧，在“**更多…**”中单击“**选项**”。

步骤 3： 在左侧窗口中导航到“**公式**”选项卡，并在此处勾选“**启用迭代计算**”选项。

之后，单击“**确定**”以保存此更改。

这样，您可以启用迭代计算，一旦启用，用户就可以控制最大迭代次数和最大更改选项。您应该知道最大迭代次数和最大更改是多少。因此，这里是它们的详细描述 -

最大迭代次数 - 最大迭代次数是指 Excel 将重新计算多少次。您可以定义最大迭代次数。但是，如果迭代次数增加，计算将花费比以前更多的时间。

默认情况下，最大迭代次数为 100。您可以更改它。

最大更改 - 最大更改定义了迭代公式值之间的最大收敛度。当数字越小，结果就越准确。

默认情况下，最大迭代次数为 0.0001。您也可以更改它。

默认情况下，最大迭代次数为 100，最大更改为 0.0001。这意味着 Excel 将在 100 次迭代后或在更改小于 0.0001 后停止计算。

检测循环引用

当您不自己创建工作表而是从他人那里接收时，您不确定其中是否存在循环引用。因此，为了确保没有循环引用，您可以手动检测文件中的循环引用。

方法 1：从错误检查

为此，请按照以下步骤操作

1. 导航到“**公式**”选项卡，然后在“公式审核”组中单击“**错误检查**”。
   
2. 现在，从列表中选择“**循环引用**”，即“**公式**”>“**错误检查**”>“**循环引用**”。
   Excel 将显示包含循环引用的单元格引用。
   
3. Excel 将准确地将您带到 Excel 工作表中包含循环引用的那个单元格。

方法 2：直接从状态栏

检测 Excel 工作表中循环引用的另一种方法是在 Excel 状态栏中查找。这是查找 Excel 工作表中循环引用的最简单方法。

如果 Excel 工作表在任何单元格中包含循环引用，您将在 Excel 工作表的底部（左下角）看到“**循环引用”以及单元格编号**。

它还包含您 Excel 工作表中存在循环引用的单元格编号。

提示：如果循环引用不存在于当前活动工作表中，但在同一工作簿的另一个工作表中存在，则会显示“循环引用”而没有单元格编号。

例如， Sheet2 包含循环引用，但 Sheet1 不包含。因此，Sheet1 的状态栏将显示 -

忽略 sheet1 的数据。

如何移除循环引用？

Excel 没有快捷方式或预定义方法可以从 Excel 工作表中删除循环引用。您需要使用上述步骤逐个查找 Excel 工作表中存在的每个循环引用。然后，您需要逐个手动删除它们。您可以完全删除创建循环引用的公式，或者将其替换为另一个公式。

为什么应避免循环引用？

Excel 用户应避免使用循环引用。它会使 Excel 数据更加复杂，并会影响性能。每次打开包含循环引用的 Excel 工作表时，都会显示警告消息，如下所示 -

但是，如果您允许迭代计算（通过勾选“**启用迭代计算**”）。在这种情况下，它不会显示任何警告消息，您甚至无法从“公式”选项卡中的“错误检查”中找到循环引用。此外，由于循环引用，循环引用还可能导致其他一些问题。

例如，如果您选择一个带有循环引用的单元格，然后意外切换到公式编辑模式。另外，如果您在不更改公式的情况下按 **Enter** 键，它将返回 0。

注意：通过按 F2 键或双击选定单元格可以启用公式编辑模式。

当您拥有包含过多公式的大型数据电子表格时，循环引用具有很高的风险。一次循环引用可能导致计算错误。

不同类型的循环引用

您会在 Excel 中发现不同类型的循环引用，例如 -

• 意外的循环引用
• 预期的循环引用
• 隐藏的循环引用

您会发现大多数循环引用是**意外**的。Excel 会自动为用户搜索意外类型的实例。

也有**预期的循环引用**。这类循环引用通常由经验丰富的用户创建并用于进行迭代计算。

还有一种循环引用，即**隐藏的循环引用**。隐藏的循环引用很难检测，因此可能很危险。这种类型的循环引用的操作取决于另一个单元格的值。

如何查找隐藏的循环引用？

隐藏的循环引用很难检测。因此，我们将描述在 Excel 工作表中查找隐藏的循环引用的步骤。通常，当您打开包含循环引用的 Excel 工作表时，会显示警告消息，如下所示 -

但是，如果您通过启用迭代计算来允许循环引用，那么将不会显示此警告消息。您甚至无法从“公式”选项卡中的“**错误检查**”选项中找到循环引用。