Excel 循环引用

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

当您的公式尝试引用自身并且Excel的迭代计算功能被关闭时，您会遇到这个问题。该公式可能如下所示 –

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

什么是循环引用

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

直接循环引用

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

当您按下回车键时，将弹出一个窗口并显示一个警告消息。

点击这个警告弹出窗口上的“确定”以关闭它，Excel将返回0。

间接循环引用

间接循环引用是指不同的公式以循环方式依赖于彼此的单元格数据。下面是一个间接循环引用的示例。

为此，我们首先在C1单元格中设置一个数值（20）。

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

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

步骤3： 现在，在E5单元格的值上再进行一次乘法运算，并将结果存储在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会为一个或多个循环引用警告用户。

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

如果您点击“确定”，所选的B8单元格将被填充为值0，以保留结果。

什么是迭代计算

迭代计算 允许用户执行循环引用，并使用结果单元格引用重新计算数据。因此，您可以在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会准确地带您到包含循环引用的单元格所在位置。
   

方法2：直接从状态栏查找

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

如果Excel工作表中的任何单元格存在循环引用，您将在Excel工作表的底部左下角看到 循环引用及其单元格编号 的字样。

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

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

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

忽略sheet1的数据。

如何去除循环引用

Excel没有任何快捷键或预定义的方法来从Excel表格中去除循环引用。您需要借助上述步骤在Excel表格中逐个找到循环引用并手动删除它们。您可以完全删除创建循环引用的公式，或者用另一个公式替换它们。

为什么应该避免使用循环引用

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

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

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

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

当您有一个包含太多公式的大型数据电子表格时，循环引用存在很高的风险。一旦出现循环引用，可能导致计算错误。

不同类型的循环引用

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

• 无意的循环引用
• 有意的循环引用
• 隐藏的循环引用

您会发现大多数循环引用是 无意的 。Excel会自动为用户搜索无意的循环引用。

还有 有意的循环引用 。这些类型的循环引用通常由有经验的用户创建，并用于进行迭代计算。

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

如何找到隐藏的循环引用

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

但是，如果您通过启用迭代计算来允许循环引用，则不会显示此警告消息。即使在“公式”选项卡中的“错误检查”选项中，您也无法找到循环引用。