（在excel中循环引用默认的迭代次数）

，原文标题：每天学一点Excel2010 (37)——循环引用和迭代计算。

先来回答上期的问题

• 百钱买白鸡：根据题目得出

公鸡 母鸡 小鸡=100

公鸡*5 母鸡*3 小鸡/3=100

未知数多于已知条件数，属于不定方程，很可能有满足给定条件的多个解。

• 韩信点兵

一个未知数同时满足三个条件，也有多个解，不满足迭代计算的条件。

上述问题靠人工的“方案管理器”和“单变量求解”无能为力，使用“模拟运算表”来解决。具体过程不讲了，直接上两张图

百钱买百鸡

韩信点兵（公式里面没有用到行值，列值填充到1000）

050 循环引用和迭代计算

如果公式引用自己所在的单元格，则无论是直接引用还是间接引用，都会构成循环引用。

有兴趣的可以上网搜一下芝诺悖论（Zeno's paradox）。中国古人也曾说过，“一尺之棰，日取其半，万世不竭”。不管是否收敛（存在极限值）循环引用可以进行无限次的迭代。

1）新建一个工作簿，在Sheet1工作表的A1输入“=A1 1”，回车后显示0，这是直接循环引用，状态栏会显示“循环引用：A1”。

2）在A2中输入“1”，B2中输入“=A2/2”，再回到A2，输入“=B2/2”，结果还是显示0，这是间接循环引用。

3）循环引用会导致比较奇怪的结果，Excel默认是关闭迭代计算的，现在我们依次点击“开始”、“选项”、“公式”，勾选“手动重算”单选框、“启用迭代计算”复选框，将最多迭代次数改为2，后确定。

2次迭代计算的结果

4）每按一次F9，便进行两次公式计算（如果开启了自动重算，我们每做一次操作，上面的公式便自动重算2次）。

5）我们在C1输入1，C2输入“=C1/2”，点击C2右下角的填充柄向下填充公式，按F9看看“万世不竭”的效果，和旁边的A2、B2比较一下。一直按F9，最后变成0了，已经超出计算机所能运行的极限了，有兴趣的把C2公式继续往下填充，如图最后也变成0，小的不能再小了。

超出运算范围

6）这种循环引用和迭代计算在编程时经常用到，比如循环变量（i=i 1）。为了防止无限次的迭代计算，在Excel选项里面可以通过设置“最多迭代次数”、“最大误差”（两次相邻计算之间）来终止计算。

7）出现循环引用系统会出现警告，一般要删除。确实要保留必须启用迭代计算并设置条件，否则会无休止的进行计算。

8）最后介绍几个和大量数据计算相关的Excel选项

• 计算。依次点击“开始”、“选项”、“公式”，在计算选项区域设置，有三个选项（“公式”选项卡的“计算区也有）。如果工作簿中包含大量公式、模拟运算表、函数、外部引用时，重新计算可能会持续较长时间，工作不方便，设为手动计算可以控制何时进行重新计算。

F9 手动计算所有打开的工作簿中，自上次计算后进行了更改的公式。

Shift F9 重新计算活动工作表中，自上次计算后进行了更改的公式。

Ctri Alt F9 重新计算所有打开的工作簿中的所有公式。

Ctri Shift Alt F9 重新检查相关的公式，然后计算所有打开的工作簿中的所有公式。

• 迭代。依次点击“开始”、“选项”、“公式”，勾选“启用迭代计算”复选框后有两个选项。在计算选项区域设置主要用于模拟分析中控制迭代的停止条件。
• 精度。依次点击“开始”、“选项”、“高级”，在“计算此工作簿”区域，勾选“将精度设为所显示的精度”。默认情况下，Excel计算的是存储值而非显示值，在数字格式的时候讲过显示的值只是样子改变，所以我们有时按照显示值使用计算器进行人工计算时，会因为舍入误差而略有差异，一定要注意（有时做工资会对不上账）。

显示的数值加法结果有误

• 启用多线程处理。依次点击“开始”、“选项”、“高级”，在“公式”区勾选“启用多线程计算”复选框，将使用计算机上面的所有处理器，加快速度，缩短时间。

（待续）

随机文章