| 楼主 gouweicao78 |
【声明】原创作品，未经允许，不得转载！ 阅读本文之前，请先做一下功课，阅读：《 什么是循环引用？如何理解这个概念？ “循环引用”这是很多初学者经常犯的一种“错误”，当我们为Excel弹出的循环引用错误提示而烦恼时，却不知道在使用得当的情况下，“循环引用”却能产生意想不到的效果。那么，怎么才能控制好这个功能，让它为我们服务呢？ “循环引用”的应用原理就是对公式本身所在单元格已经通过公式返回的结果进行再处理的过程。下面，我们将循环引用涉及的几个知识点进行分解介绍（注：如有对本帖的技术疑问，请跟帖回复并将问题的相应楼层链接短信告知本人）： 1、 迭代运算：Excel2003单击工具→选项→重新计算→设置“最多迭代次数”，Excel2007单击Office按钮→Excel选项→公式，可进行设置。Excel将“重新计算”该处设置的次数。比如设置为100次，则按一次F9，Excel将重新计算100次。 2、计数器：计数器是什么？说白了，就是让某个单元格得到一个我们规定范围的整数，每次重新计算都让它变化，比如在A1中输入=A1+1，迭代次数为100，则按1次F9之后，A1=100，按2次之后，A1=200，此间Excel计算了200次，A1分别产生了1、2、3、……200的数字。 计数器主要是为了方便理解每一步Excel干了什么（设置迭代次数为1，一次次按F9查看过程，有点儿类似VBA中的“调试”）。例如 【循环引用】之中文大写人民币逆转为阿拉伯数字 注意：许多地方的“循环引用”并不需要要设置“计数器”，比如【循环引用】之求解多元一次方程 3、 启动开关：我们当然不想看到Excel自己乱算，尤其是“循环引用”公式自身单元格初始值经常会变成0，在输入、修改过程中往往看不到、得不到想要的结果。因此，我们干脆在一个固定的单元格内输入非0数字或清空这样简单的操作来实现启动，让“循环引用”更乖一些。这个“启动开关”也并非必要，只是有了它，我们就可以随时控制公式结果区域。 4、注意事项：迭代次数与第一个打开的工作簿设定有关。比如：关闭所有Excel文件，打开A.xls，设置迭代次数为100，再打开未设置迭代次数的B.xls，则视为B.xls也接受这个设置。反之亦然，打开了B.xls之后再打开A.xls则会提示“循环引用”错误，因为此时视为接受B.xls的设置。因此，建议单独使用设置了“循环引用”功能文件。 本帖第13楼~15楼为“控制循环引用三法宝”的具体阐述 【应用实例】 组合方案型： |
| 2楼 gouweicao78 |
说了这么多，给了这么些实例，头快昏了。不怕，我们抓一个小实例来：如何将一个字符串逆序返回？ 【解剖】所谓逆序返回字符，就是将字符串从右到左倒过来。这里使用了几个简单的函数：RIHGT、LEFT、LEN和IF。 【制作步骤】 1、公式核心分析：假设公式放在C2，逆转“我爱ET”——我们希望，第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输入
输入完毕，按F9不放——居然成功了一半：得到“TE爱我我我我我我我我我我我我我我我我我我我……” 哈哈，故意甩个破绽，绕个圈子。思路一转：问题出在LEFT(RIGHT())最终一直在取第一个字符。那么改过来：
2、有了启动开关，LEN(C2)+1实际上也是一个计数器，但不此题不好用而已，同理，LEN(A2)-LEN(C2)也可以视为计数器。那么，是否非要按F9呢，岂不是小米加半自动步枪。 此时，设置迭代次数为100（假设A列需逆序反转的文本不超过100字），清空D1，在E1输入=E1+1，然后在D1输入1启动，看看，奇迹发生了，E1跟公式一点关系也没有，却乖乖地帮你按了100次的F9。 这一个简单的【循环引用】之逆序返回字符串的东东就这么做成了（见附件） 你掌握了吗？去找点儿东西来试验一下，别忘了把应用的案例发到社区中来分享哦！  【循环引用】之逆序返回字符串.rar |
| 3楼 水星钓鱼 |
顶，好帖，草兄辛苦了。 |
| 4楼 BIN_YANG168 |
学习一下。 |
| 5楼 apolloh |
万古流芳之作  |
| 6楼 lfspecter |
坐下来好好听草兄讲故事 |
| 7楼 wanglei569 |
我想当和尚 |
| 8楼 xiaoyver |
学习了,谢谢 |
| 9楼 冰风萧萧 |
收藏～～～ |
| 10楼 雷鸣 |
这个没搞懂，在实际工作中有应用实例吗？如有还好懂点。否则就是为技术而技术，并不可取啊！ 因无知而妄语，勿怪啊！ |
| 11楼 gouweicao78 |
那您可以先看一下1楼几个应用案例的链接，呵呵 |
| 12楼 ambit0 |
学习一下。看的有点费劲，底子差啊？要常来社区逛逛。 |
| 13楼 gouweicao78 |
循环引用，因为每一次都会将公式自身返回的结果当做下一个初始值代入计算，而不像普通的公式计算——变量在别的单元格，如果没有设置一个开关来控制，基本上就只有靠进入循环引用公式的编辑状态后再回车来调试了。 实际上，启动开关的设置很简单，只是在循环引用的公式外层加一个IF判断，并通过控制其它单元格的值，当符合条件时才进入循环引用状态，否则公式就“赋予”一个初始值。 例如2楼例子中公式：
D1作为启动开关，当D1的条件不满足时（比如为空单元格或0），这个公式返回的是""，没有进入循环引用状态。 此处，""就是给迭代变量（包含循环引用公式的单元格C2）“赋予”的初始值。 |
| 14楼 gouweicao78 |
编程经常会看到类似使用变量i，经过一个计算后i=i+1的循环语句。 我称之为“计数器”，每次计算，都会在前次结果累加1。 作为“遍历”的一种工具，这个东西很有用。 比如： 设置D1单元格为启动开关，以B1为计数器，可以用：
一个非常简单易懂的公式——当D1启动后，B1=B1+1的循环引用，每一次都会在前次结果累加1，如果D1被关掉（比如清空或输入0），则这个循环语句赋予B1一个初始值0——也可以称之为“计数器归零”。 有了B1这个计数器，我们可以从A1单元格中“遍历”每一个字符，比如在B2输入一个公式：
当D1未启动时，赋予B2一个初始值——空文本"" 当D1启动后，随着每一次迭代，B1将依次变化为=0+1、=1+1、=2+1、=3+1……即1、2、3、4…… 因此，使用MID(A1,B1,1)就可以依次将A1的字符取出。 |
| 15楼 gouweicao78 |
从楼上的“计数器”例子来看：
D1启动后，假设设置的迭代计算次数为X，只要按一次F9刷新，这个公式就会运算X次，无穷无尽。 虽然还有D1可以控制停止循环引用，而当B1的值超过一定数量之后，对于使用计数器的循环引用公式来说，并没有什么意义。 结束语句，也是IF，比如公式：
则B1<10作为循环的条件，可以当B1的值达到10的时候，停止再累加——也就限定了B1的上限。 同理，还可以设置使用计数器的循环引用公式来作为结束语句。 |
| 16楼 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或空文本"" |
| 17楼 gouweicao78 |
由于启动开关只是用于IF判断使用的，与循环引用公式的迭代计算部分并没有关系，因此： 1、只需要可以用于IF判断： ①可以用手工在D1输入1，因为非0数值在逻辑运算中，等价于TRUE； ②可以用数据有效性做“启动”“关闭”，然后在循环引用公式中外面套：=IF(D1="启动",…… ③可以使用窗体的复选框，通过鼠标点击“勾选”或取消勾选，并链接到D1单元格得到TRUE或FALSE。 等等，都非常容易设置。 2、需要注意的是，在启用迭代计算之后，公式的运算顺序是先上后下、先左后右的顺序，因此，需要将作为启动开关的单元格，放在循环引用公式的上方或左侧的单元格区域。 |
| 18楼 syz105729913 |
|
| 19楼 myliujjj |
下载了，慢慢琢磨，谢谢超级版主。 |
| 20楼 yuezc |
讲的好，谢谢。 |
| 21楼 wjc2090742 |
就以这个帖为基点，开始爬循环引用吧。 |
| 22楼 wnianzhong |
好好学习,先收藏起来! |
| 23楼 高玉甫 |
学习了，谢谢。 |
| 24楼 xihabang |
好的 ..前来学习了 |
| 25楼 wangqilong1980 |
非常好用，谢谢版主。 |
| 26楼 qll |
不顶不行 |
| 27楼 gouweicao78 |
本帖的想法是：总结一个成熟的应用模式，虽然只有三板斧，却可以让原本“慎用”的功能不再令人发怵。 |
| 28楼 庭院幽幽 |
高手云集呀，学习了，太强势了 |
| 29楼 梦竹 |
这个不 错，我要好好学习，谢谢楼主。 |
| 30楼 biaotiger1 |
顶贴，再看。 |
| 31楼 gouweicao78 |
【声明】原创作品，未经允许，不得转载！ 阅读本文之前，请先做一下功课，阅读：《 什么是循环引用？如何理解这个概念？ “循环引用”这是很多初学者经常犯的一种“错误”，当我们为Excel弹出的循环引用错误提示而烦恼时，却不知道在使用得当的情况下，“循环引用”却能产生意想不到的效果。那么，怎么才能控制好这个功能，让它为我们服务呢？ “循环引用”的应用原理就是对公式本身所在单元格已经通过公式返回的结果进行再处理的过程。下面，我们将循环引用涉及的几个知识点进行分解介绍（注：如有对本帖的技术疑问，请跟帖回复并将问题的相应楼层链接短信告知本人）： 1、 迭代运算：Excel2003单击工具→选项→重新计算→设置“最多迭代次数”，Excel2007单击Office按钮→Excel选项→公式，可进行设置。Excel将“重新计算”该处设置的次数。比如设置为100次，则按一次F9，Excel将重新计算100次。 2、计数器：计数器是什么？说白了，就是让某个单元格得到一个我们规定范围的整数，每次重新计算都让它变化，比如在A1中输入=A1+1，迭代次数为100，则按1次F9之后，A1=100，按2次之后，A1=200，此间Excel计算了200次，A1分别产生了1、2、3、……200的数字。 计数器主要是为了方便理解每一步Excel干了什么（设置迭代次数为1，一次次按F9查看过程，有点儿类似VBA中的“调试”）。例如 【循环引用】之中文大写人民币逆转为阿拉伯数字 注意：许多地方的“循环引用”并不需要要设置“计数器”，比如【循环引用】之求解多元一次方程 3、 启动开关：我们当然不想看到Excel自己乱算，尤其是“循环引用”公式自身单元格初始值经常会变成0，在输入、修改过程中往往看不到、得不到想要的结果。因此，我们干脆在一个固定的单元格内输入非0数字或清空这样简单的操作来实现启动，让“循环引用”更乖一些。这个“启动开关”也并非必要，只是有了它，我们就可以随时控制公式结果区域。 4、注意事项：迭代次数与第一个打开的工作簿设定有关。比如：关闭所有Excel文件，打开A.xls，设置迭代次数为100，再打开未设置迭代次数的B.xls，则视为B.xls也接受这个设置。反之亦然，打开了B.xls之后再打开A.xls则会提示“循环引用”错误，因为此时视为接受B.xls的设置。因此，建议单独使用设置了“循环引用”功能文件。 本帖第13楼~15楼为“控制循环引用三法宝”的具体阐述 【应用实例】 组合方案型： |
| 32楼 gouweicao78 |
说了这么多，给了这么些实例，头快昏了。不怕，我们抓一个小实例来：如何将一个字符串逆序返回？ 【解剖】所谓逆序返回字符，就是将字符串从右到左倒过来。这里使用了几个简单的函数：RIHGT、LEFT、LEN和IF。 【制作步骤】 1、公式核心分析：假设公式放在C2，逆转“我爱ET”——我们希望，第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输入
输入完毕，按F9不放——居然成功了一半：得到“TE爱我我我我我我我我我我我我我我我我我我我……” 哈哈，故意甩个破绽，绕个圈子。思路一转：问题出在LEFT(RIGHT())最终一直在取第一个字符。那么改过来：
2、有了启动开关，LEN(C2)+1实际上也是一个计数器，但不此题不好用而已，同理，LEN(A2)-LEN(C2)也可以视为计数器。那么，是否非要按F9呢，岂不是小米加半自动步枪。 此时，设置迭代次数为100（假设A列需逆序反转的文本不超过100字），清空D1，在E1输入=E1+1，然后在D1输入1启动，看看，奇迹发生了，E1跟公式一点关系也没有，却乖乖地帮你按了100次的F9。 这一个简单的【循环引用】之逆序返回字符串的东东就这么做成了（见附件） 你掌握了吗？去找点儿东西来试验一下，别忘了把应用的案例发到社区中来分享哦！ 【循环引用】之逆序返回字符串.rar |
| 33楼 水星钓鱼 |
顶，好帖，草兄辛苦了。 |
| 34楼 BIN_YANG168 |
学习一下。 |
| 35楼 apolloh |
万古流芳之作 |
| 36楼 lfspecter |
坐下来好好听草兄讲故事 |
| 37楼 wanglei569 |
我想当和尚 |
| 38楼 xiaoyver |
学习了,谢谢 |
| 39楼 冰风萧萧 |
收藏～～～ |
| 40楼 雷鸣 |
这个没搞懂，在实际工作中有应用实例吗？如有还好懂点。否则就是为技术而技术，并不可取啊！ 因无知而妄语，勿怪啊！ |
| 41楼 SeCReTJuN |
哇塞,好棒的帖子 |
| 42楼 CJH-龙年快乐 |
收藏了慢慢看！ |
| 43楼 淡淡的季风 |
请教一个问题，为何我按照楼主的思路自己做了一遍，单元格最前面会有个0？而且我把2L的附件下载后，如果重新点双击一下公式所在的单元格再回车，也会多一个0出来。 这是为何？ |
| 44楼 hl_irnt |
收收收 |
| 45楼 把心撑起 |
真的是大手笔啊 |
| 46楼 keven |
什么叫干货 这就是**thank you very much for your sharing** |