平淡

什么是迭代计算

Excel的迭代计算是指将计算结果代回原变量进行重复计算，直到满足特定的数值条件为止。举个简单的例子，在A1单元格中输入“1”，在B1单元格中输入公式“=a1+b1”，由于在B1单元格的公式中引用了其自身进行迭代计算，Excel会弹出循环引用的提示（图1）。

为了允许迭代，需要依次点击“文件→选项”，在打开的选项设置窗口中切换到“公式”选项卡，在右侧的窗格中勾选“启用迭代计算”，并将“最多迭代次数”设置为2（图2）。

这样，图1中共进行了2次迭代运算（第1次：1+0，第2次：1+1+0），所以结果为2。由此可见，通过开启迭代计算，可以将原来循环引用的数据固定为指定的数值。下面介绍迭代计算的实际应用。

应用场景1：固定人员来访的时间

为了便于管理来访人员，很多公司都会要求来访者在前台进行登记。借助迭代计算可以实现来访时间的自动录

1自定义时间格式

打开来访人员登记文档，选中B列并右击，依次选择“设置单元格格式→自定义”，在右侧窗格的“类型”下输入“ y y y y / m / dh：mm”，即将该列数据的显示格式指定为“年月日 时：分”样式（图3）。

2固定时间数据

定位到B 2单元格并输入公式“=IFS（A 2=""，""，B2=""，NOW（），B2<>""，B2）”，然后下拉填充。這样只要在A列中输入来访者的姓名，那么在B列对应的单元格中就会自动录入A列中输入姓名时对应的时刻（图4）。

公式解释：

这里使用IFS函数对B2单元格的值进行多条件判断，如在A 2单元格中输入“张三”，此时由于A 2单元格不为空，所以第一条件值为Fal se，不执行;接着执行第二条件，此时在B 2 单元格中显示NOW函数的数值，因为B2单元格也不为空，所以值同样为Fal se，不执行;继续执行第三条件，因为此时B2单元格中显示NOW函数的数值（即不为空），所以此时的值为Tr ue，执行迭代计算后显示A2单元格中输入数据时的时间值。它和直接在B2单元格中输入NOW公式不同，后者的数值会随当前时刻同步变化，通过迭代计算后则可以固定不变了。

应用场景2：固定不重复的随机数字

在执行一些随机性的抽查工作时，我们经常需要生成一些不重复的随机数字。比如公司组织的生产安全知识比赛（总共100道题），现在需要在1∽100之间生成30个不重复的随机数字，然后让员工随机选择作答。

如果要生成随机数字，使用RANDBETWEEN函数即可。但是，如果直接在A2单元格中输入公式，可以默认会生成很多重复的数字，而且由于RANDBETWEEN是易失性函数，生成随机数字后，若文档中进行了任何数据的更改（如增加其他数据），生成的数字又会再次发生变化，使用起来极为不便（图5）。此时，可以使用迭代生成不重复且可以固定使用的数字。

1公式设置

定位到A 2单元格并输入公式“=IF（SUM（$B$2：$B$31）<>30，RANDBETWEEN（1，100），A2）”，定位到B2单元格并输入公式“=COUNTIF（$A$2：$A$31，A2）”，然后选中A2：B2数据区域并下拉填充公式到A31：B31数据区域，这样在A列中就可以看到生成的随机数字了。不过，默认仍然会有重复的数字（图6）。

公式解释：

使用I F函数对B 2 ： B 3 1数据区域的和进行判断，如果不等于3 0（即B列显示重复数字有>1），那么就使用R ANDBETWEEN函数再生成随机数字，否则直接显示A2单元格中的数字。这里同样利用迭代计算，可以实现A列只显示不重复的数字。

2设置条件格式

为了方便查看重复数据，选中A列并依次点击“开始→ 条件格式→ 突出显示单元格规则→重复值→选择默认设置”，这样重复的数字就会被自动填充为浅红色标注，B列会显示重复次数（图7）。

3生成所需数据

打开图2所示的窗口，将“最多迭代次数”设置为1000，接着返回图6所示的窗口，按下F9键再次加载运算，可以看到每一次按下F9键，随机数字会随之发生变化。一般只要按F9键2～3次，此时A列就不出现红色重复数字了。此时无论再按多少次F9键都不会再发生变化。最后按提示将A列的数据复制后粘贴为数值使用即可（图8）。