修复错误后如何强制 Excel 重新计算循环依赖？

Question

我有一个复杂的财务工作表，其中故意使用了循环引用。Excel 在计算这些引用时表现很好，因为我启用了迭代计算。

问题：当我通过更改循环依赖中使用的单元格公式意外创建错误（例如#NUM 或#DIV/0）然后修复它时，Excel 无法重新计算工作表。

问题很容易通过简单的场景重现，其中税收=税率*（收入+税收）：

修复C1中的错误后，C2保留原来的错误，不会重新计算。

使用Calculate now没有帮助。我发现恢复功能工作表的唯一方法是编辑（F2）C2 公式，然后点击Enter。

但是这个解决方案不适用于我的复杂工作表：我一次得到几十个错误单元格，并且找不到我应该“刷新”的单元格（F2然后（Enter）。

我该如何解决这个问题？我只想强制 Excel真的修复错误后重新计算一切。

Answer 1

唯一的解决方案是使用 =IFERROR([cell formula here], 0) 填充模型。这是我能想出解决此迭代问题的唯一方法。其背后的原理是 Excel 将尝试多次迭代工作表计算。第一次迭代将导致每个导致错误的单元格都为 0。第二次迭代将正确启动。

Answer 2

一个好的解决方案是在您的模型中添加一个“断路器”。

这是一个包含下拉布尔值（TRUE/FALSE）的单元格，循环引用通过 IF 语句依赖于它。

一个典型的例子是，在杠杆收购模型中，你可能有一个断路器：“利息取决于平均余额？”如果为假，利息支出将取决于年初债务余额，而不是平均余额，而平均余额又取决于可用于提前还款的现金流，而现金流又取决于固定费用，包括利息支出。

如果您将模型放大，并且错误会一直传播，则您将其更改为 false，大多数问题都会得到解决，您会修复少数真正错误的问题，然后重新拨动开关。

Answer 3

当我通过更改循环依赖中使用的单元格公式意外创建错误（例如#NUM 或#DIV/0）然后修复它时，Excel 无法重新计算工作表。

但事实并非如此，Excel 可以正确地重新计算（至少在您发布的示例中）：

• 当您输入循环引用时，如您的示例中所示：
  =B1*(C1+C2)在 C2 中
• Excel 会将其解释为仅采用前一时刻单元格的值，而 C2 中的前一个值是错误的，因此您的公式将是：
  =B1*(C1+#ERR)，这会导致错误，并且无论您重新计算多少次，它仍然会是一个错误，因此您描述的行为是正确的
• 如果你按下 F2，那么你将重置 C2 中的值，因此它将被视为0，这就是它起作用的原因
• 您可能会说您想重置所有有错误的公式，但是当 Excel 尝试解决不可能的计算时，这很容易陷入无限循环。

我只进行过几次迭代计算，但我强烈建议不要这样做，除了当前的问题之外，还可能存在其他问题：

• 失去可追溯性：由于计算结果不仅取决于屏幕上的公式和常数，还取决于之前的状态，因此很难（甚至不可能）重现情况
• 如果您意外更改了任何值（这是循环引用公式的先例），它将立即被更改，并且撤消它并不总是那么容易（特别是当您在几个步骤后意识到这一点时）。
• 我认为，对于财务报表而言，无论它是理论性的/科学性的/非现实的，你都希望完全控制它。