在 Excel 中，“迭代法”通常指的是启用“迭代计算”功能，以解决包含循环引用（Circular Reference）的公式计算问题。
默认情况下,Excel 会禁止循环引用，因为传统电子表格是单向计算的，但在某些工程、财务或数学模型中，我们需要让一个单元格的值依赖于它自己（或依赖包含它的其他单元格），这时就需要启用迭代计算。
什么是迭代法？
迭代法是一种通过重复计算逐步逼近最终结果的方法,在 Excel 中：
- 初始值：从某个初始值（如 0 或上一次保存的值）开始。
- 重复计算：Excel 会根据公式重新计算，直到满足“最大变化量”条件或达到“最大迭代次数”。
- 收敛：当连续两次计算结果的差异小于设定的“最大变化量”时，迭代停止，返回当前值。
如何启用迭代计算？
Excel 2010 及以后版本（包括 Microsoft 365）：
- 点击 文件 > 选项。
- 选择 公式。
- 在“计算选项”部分，勾选 启用迭代计算。
- 设置参数：
- 最多迭代次数：默认 100 次。
- 最大变化：默认 0.001（即当两次计算结果差异小于 0.001 时停止）。
- 点击 确定。
Excel 2007 及更早版本：
路径类似：工具 > 选项 > 计算 选项卡 > 勾选 迭代计算。
典型应用场景
场景 1：递归计算（如斐波那契数列）
假设 A1 和 A2 初始为 1，A3 公式为 =A1+A2，但你想让 A1 成为前两个数的和（即 A1 = A2 + A3），这就形成了循环引用。
更实际的例子：复利计算中，本金依赖于上一期的本息和。
场景 2：目标求解（类似 Goal Seek 但更灵活）
计算一个设备的折旧值,其中本期折旧额依赖于上期净值，而净值又依赖于折旧额。
场景 3：迭代求解方程
求解方程：
x = cos(x)
在 Excel 中：
- 在 A1 输入初始值
1。 - 在 A2 输入公式
=COS(A1)。 - 启用迭代计算。
- A2 会不断用 A1 的 cos 值更新自己，最终收敛到约
739085。
⚠️ 注意：如果公式是
=COS(A1)且 A1 依赖 A2，则需设置 A1 的公式为=COS(A2)，并启用迭代。
场景 4：财务模型中的内部收益率（IRR）近似
虽然 Excel 有内置 IRR 函数，但自定义迭代模型可用于更复杂的现金流结构。
注意事项与风险
-
非收敛风险：
- 如果公式不收敛（如 x = x + 1），迭代将一直进行直到达到“最大迭代次数”，结果可能无意义。
- 建议设置合理的“最大迭代次数”（如 100~1000）和“最大变化量”（如 0.0001）。
-
性能影响：
启用迭代计算后,每次工作表更改都会触发迭代，可能显著降低计算速度，尤其是大型模型。
-
调试困难：
循环引用难以追踪,建议使用“公式”选项卡中的“错误检查”或“追踪引用单元格”功能辅助调试。
-
替代方案：
- 如果可能,尽量使用辅助列或Power Query、VBA 来避免循环引用。
- 对于简单目标求解,使用 数据 > 模拟分析 > 目标值（Goal Seek）或 规划求解（Solver）更稳定。
示例：简单迭代计算
目标：计算 x = √(2 + x) 的收敛值（理论解为 2）
- 启用迭代计算。
- 在 A1 输入初始值
0。 - 在 A2 输入公式：
=SQRT(2 + A1) - 将 A2 的公式向下填充，或让 A2 自引用（需小心设置）。
更直接的自引用示例：
- 在 A1 输入
0。 - 在 A1 的公式改为：
=IF(A1=0, 0, SQRT(2 + A1))但这仍不是自引用。
正确自引用示例：
- 在 A1 输入
0。 - 在 A1 的公式改为：
=SQRT(2 + A1) - 启用迭代计算。
- A1 将逐步收敛到
2。
✅ 每次按 F9 或更改工作表时，Excel 会迭代计算 A1，直到满足收敛条件。
| 项目 | 说明 |
|---|---|
| 用途 | 解决循环引用，实现递归、迭代求解 |
| 启用路径 | 文件 > 选项 > 公式 > 启用迭代计算 |
| 关键参数 | 最大迭代次数、最大变化量 |
| 优点 | 灵活，可建模复杂依赖关系 |
| 缺点 | 可能不收敛、性能下降、调试困难 |
| 建议 | 优先使用 Solver 或辅助列，仅在必要时启用迭代 |
如有具体模型或公式需要帮助,请提供详细场景，我可以为你设计迭代逻辑。
首发原创文章，作者：王坚，如若转载，请注明出处：https://test.idctop.com/article/476221.html
评论列表（1条）
这篇文章说到点子上了，尤其是关于启用迭代计算那段，我直接截图存了。话说回来有些地方还可以再深入，不过整体已经很顶了，赞一