轻松控制循环引用完全教程

gouweicao78

【声明】原创作品，未经允许，不得转载！
【说明】原文地址 http://www.exceltip.net/thread-1953-1-1-2.html
在Excelhome出版的《Excel 2007实战技巧精粹精粹》图书中，将有一章以此为蓝本的《轻松掌控循环引用》。此文也可以算是对新书的先睹为快吧

本文将通过实例（点击蓝色字体的链接查阅）告诉读者，不需要害怕，通过几个貌似吓人实际上很简单的概念的理解，和动手“做”，您也可以轻松领略她的妙处。

阅读本文之前，请先做一下功课，阅读：《【循环引用】之求解多元一次方程等

3、 启动开关：我们当然不想看到Excel自己乱算，尤其是“循环引用”公式自身单元格初始值经常会变成0，在输入、修改过程中往往看不到、得不到想要的结果。因此，我们干脆在一个固定的单元格内输入非0数字或清空这样简单的操作来实现启动，让“循环引用”更乖一些。这个“启动开关”也并非必要，只是有了它，我们就可以随时控制公式结果区域。

4、注意事项：迭代次数与第一个打开的工作簿设定有关。比如：关闭所有Excel文件，打开A.xls，设置迭代次数为100，再打开未设置迭代次数的B.xls，则视为B.xls也接受这个设置。反之亦然，打开了B.xls之后再打开A.xls则会提示“循环引用”错误，因为此时视为接受B.xls的设置。因此，建议单独使用设置了“循环引用”功能文件。


【应用实例】
组合方案型：
【循环引用】之求解多元一次方程（此过程因为看不到，很多人会觉得很神秘。个人认为：Excel迭代计算n次，设置了方程式之后，自动找一些比较合理数据（不像前一个我们规定了变量的区间）不断测试，并由此得到最终的结果。这个运算模型要说到最终原理，恐怕就跟“规划求解”要懂开发的才知道了）。

记录单元格过程值：
【循环引用】之记录单元格历史值及最高、最低值

单变量求解型：
【循环引用】之估算土地地价和增值税


补充内容 (2017-10-29 08:53):
在ExcelHome技术论坛的最佳学习方法：http://club.excelhome.net/thread-117862-1-1.html

gouweicao78

说了这么多，给了这么些实例，头快昏了。不怕，我们抓一个小实例来：如何将一个字符串逆序返回？

【解剖】所谓逆序返回字符，就是将字符串从右到左倒过来。这里使用了几个简单的函数：RIHGT、LEFT、LEN和IF。
【制作步骤】
1、公式核心分析：假设公式放在C2，逆转“我爱EH”——我们希望，第1次运算得到最右一个字符T，第2次运算得到T&右边倒数2个字符E，第3次得到TE&右边倒数第3个字符“爱”……
即：
第1次运算C2得到的是LEFT(RIGHT(A2,1))，第2次运算得到LEFT(RIGHT(A2,1))&LEFT(RIGHT(A2,2))，第3次运算得到(RIGHT(A2,1))&LEFT(RIGHT(A2,2))&LEFT(RIGHT(A2,3))。注意，这里的1、2、3是一个需要的变量。

与此同时，C2单元格的字符数也随之变化为1、2、3……那么我们就可以利用这个特性，“循环引用”C2自身的字符长度LEN(C2)

因此，在C2输入

1. 
   
2. =IF(D$1,C2&LEFT(RIGHT(A2,1+LEN(C2))),"")
   

复制代码

注意：D1是启动开关。确保公式输入过程不会有乱七八糟的东西出现
输入完毕，按F9不放——居然成功了一半：得到“HE爱我我我我我我我我我我我我我我我我我我我……”

哈哈，故意甩个破绽，绕个圈子。思路一转：问题出在LEFT(RIGHT())最终一直在取第一个字符。那么改过来：

1. 
   
2. =IF(D$1,C2&RIGHT(LEFT(A2,LEN(A2)-LEN(C2))),"")
   

复制代码

在D1输入1，按F9，终于成功！

2、有了启动开关，LEN(C2)+1实际上也是一个计数器，但不此题不好用而已，同理，LEN(A2)-LEN(C2)也可以视为计数器。那么，是否非要按F9呢，岂不是小米加半自动步枪。
此时，设置迭代次数为100（假设A列需逆序反转的文本不超过100字），清空D1，在E1输入=E1+1，然后在D1输入1启动，看看，奇迹发生了，E1跟公式一点关系也没有，却乖乖地帮你按了100次的F9。

这一个简单的【循环引用】之逆序返回字符串的东东就这么做成了（见附件）

你掌握了吗？去找点儿东西来试验一下，别忘了把应用的案例发到社区中来分享哦！

aqkr

好贴，收藏，慢慢消化！！

lzzgzby

学习了，草兄辛苦了。
尤其是你给的几个实例使我受益匪浅

biaotiger1

收藏，晚上回家慢慢看

likangcrmh

真是大看眼界，还能这么用。下载收藏，谢谢分享。

gouweicao78

启动开关、计数器、结束语句——这是新书中介绍的控制循环引用的三法宝

待续……

笨元圈

我好象会用了，实际上还不会

gouweicao78

1、迭代计算。即重复计算，每一次都将上一次迭代变量的计算结果作为新的变量代入计算，得出新的计算结果，作为下一次计算的迭代变量。——看起来很拗口，呵呵。不过看一下例子就知道很简单，比如：

在B1输入=B1+1，则此时，B1单元格包含了循环引用的公式，因此B1就是一个迭代变量。
如果设置了迭代计算次数为1，则每按1次F9键或其他引发Excel重新计算的动作，B1就会在上一次的结果上+1，第一次B1的默认初始值为空单元格（可以认为是0或""）迭代运算1次，就得到1；迭代2次，就使用了上次的结果1再+1得到2，迭代第3次就使用了上次的结果2再+1得到3……

2、迭代变量。即包含循环引用公式的单元格自身，如上面的B1单元格。

3、迭代变量的初始值。即什么时候开始第1次迭代计算，那时迭代变量的值。每一次编辑包含循环引用的公式，都会让公式回到初始值——默认情况下，这个值就相当于清空了迭代变量单元格的值，也就是可以认为是0或空文本""

gouweicao78

循环引用，因为每一次都会将公式自身返回的结果当做下一个初始值代入计算，而不像普通的公式计算——变量在别的单元格，如果没有设置一个开关来控制，基本上就只有靠进入循环引用公式的编辑状态后再回车来调试了。

实际上，启动开关的设置很简单，只是在循环引用的公式外层加一个IF判断，并通过控制其它单元格的值，当符合条件时才进入循环引用状态，否则公式就控制循环引用三法宝之一：启动开关“赋予”一个初始值。

例如2楼例子中公式：

1. =IF(D$1,C2&RIGHT(LEFT(A2,LEN(A2)-LEN(C2))),"")

复制代码

D1作为启动开关，当D1的条件不满足时（比如为空单元格或0），这个公式返回的是""，没有进入循环引用状态。
此处，""就是给迭代变量（包含循环引用公式的单元格C2）“赋予”的初始值。

怎样设置启动开关
由于启动开关只是用于IF判断使用的，与循环引用公式的迭代计算部分并没有关系，因此：
1、只需要可以用于IF判断：
①可以用手工在D1输入1，因为非0数值在逻辑运算中，等价于TRUE；
②可以用数据有效性做“启动”“关闭”，然后在循环引用公式中外面套：=IF(D1="启动",……
③可以使用窗体的复选框，通过鼠标点击“勾选”或取消勾选，并链接到D1单元格得到TRUE或FALSE。
等等，都非常容易设置。
2、需要注意的是，在启用迭代计算之后，公式的运算顺序是先上后下、先左后右的顺序，因此，需要将作为启动开关的单元格，放在循环引用公式的上方或左侧的单元格区域。

（可以参阅：不同单元格公式计算是否有先后顺序？一文的讨论）
点击看图
点击下载讨论附件