![修复错误后如何强制 Excel 重新计算循环依赖?](https://linux22.com/image/1555755/%E4%BF%AE%E5%A4%8D%E9%94%99%E8%AF%AF%E5%90%8E%E5%A6%82%E4%BD%95%E5%BC%BA%E5%88%B6%20Excel%20%E9%87%8D%E6%96%B0%E8%AE%A1%E7%AE%97%E5%BE%AA%E7%8E%AF%E4%BE%9D%E8%B5%96%EF%BC%9F.png)
我有一个复杂的财务工作表,其中故意使用了循环引用。Excel 在计算这些引用时表现很好,因为我启用了迭代计算。
问题:当我通过更改循环依赖中使用的单元格公式意外创建错误(例如#NUM 或#DIV/0)然后修复它时,Excel 无法重新计算工作表。
问题很容易通过简单的场景重现,其中税收=税率*(收入+税收):
修复C1中的错误后,C2保留原来的错误,不会重新计算。
使用Calculate now
没有帮助。我发现恢复功能工作表的唯一方法是编辑(F2)C2 公式,然后点击Enter。
但是这个解决方案不适用于我的复杂工作表:我一次得到几十个错误单元格,并且找不到我应该“刷新”的单元格(F2然后(Enter)。
我该如何解决这个问题?我只想强制 Excel真的修复错误后重新计算一切。
答案1
唯一的解决方案是使用 =IFERROR([cell formula here], 0) 填充模型。这是我能想出解决此迭代问题的唯一方法。其背后的原理是 Excel 将尝试多次迭代工作表计算。第一次迭代将导致每个导致错误的单元格都为 0。第二次迭代将正确启动。
答案2
一个好的解决方案是在您的模型中添加一个“断路器”。
这是一个包含下拉布尔值(TRUE/FALSE)的单元格,循环引用通过 IF 语句依赖于它。
一个典型的例子是,在杠杆收购模型中,你可能有一个断路器:“利息取决于平均余额?”如果为假,利息支出将取决于年初债务余额,而不是平均余额,而平均余额又取决于可用于提前还款的现金流,而现金流又取决于固定费用,包括利息支出。
如果您将模型放大,并且错误会一直传播,则您将其更改为 false,大多数问题都会得到解决,您会修复少数真正错误的问题,然后重新拨动开关。
答案3
当我通过更改循环依赖中使用的单元格公式意外创建错误(例如#NUM 或#DIV/0)然后修复它时,Excel 无法重新计算工作表。
但事实并非如此,Excel 可以正确地重新计算(至少在您发布的示例中):
- 当您输入循环引用时,如您的示例中所示:
=B1*(C1+C2)
在 C2 中 - Excel 会将其解释为仅采用前一时刻单元格的值,而 C2 中的前一个值是错误的,因此您的公式将是:
=B1*(C1+#ERR)
,这会导致错误,并且无论您重新计算多少次,它仍然会是一个错误,因此您描述的行为是正确的 - 如果你按下 F2,那么你将重置 C2 中的值,因此它将被视为
0
,这就是它起作用的原因 - 您可能会说您想重置所有有错误的公式,但是当 Excel 尝试解决不可能的计算时,这很容易陷入无限循环。
我只进行过几次迭代计算,但我强烈建议不要这样做,除了当前的问题之外,还可能存在其他问题:
- 失去可追溯性:由于计算结果不仅取决于屏幕上的公式和常数,还取决于之前的状态,因此很难(甚至不可能)重现情况
- 如果您意外更改了任何值(这是循环引用公式的先例),它将立即被更改,并且撤消它并不总是那么容易(特别是当您在几个步骤后意识到这一点时)。
- 我认为,对于财务报表而言,无论它是理论性的/科学性的/非现实的,你都希望完全控制它。