设为首页收藏本站|繁體中文

Excel 技巧网

QQ登录

只需一步,快速开始

手机号码,快捷登录

查看: 31936|bwin官网地址: 49

[函数与公式] 轻松控制循环引用完全教程

  [复制链接]
发表于 2009-3-30 15:44:12 | 显示全部楼层 |阅读模式
  • 署名作者: gouweicao78
  • 版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
  • 本文来自:
  • 引用作品:
  • 适用版本: 2010 2007 2003以前版本 
  • 语言环境: 简体中文
  • 学习方法: 掌握Excel技巧的关键是动手操作 | 下载 ≠ 知识


  • 免费注册成为本站会员,享用更多功能,结识更多Office办公高手!

    您需要 登录 才可以下载或查看,没有帐号?注册

    x
    本帖最后由 gouweicao78 于 2009-11-4 11:57 编辑

    【声明】原创作品,未经允许,不得转载!
    阅读本文之前,请先做一下功课,阅读:《什么是循环引用?如何理解这个概念?

    “循环引用”这是很多初学者经常犯的一种“错误”,当我们为Excel弹出的循环引用错误提示而烦恼时,却不知道在使用得当的情况下,“循环引用”却能产生意想不到的效果。那么,怎么才能控制好这个功能,让它为我们服务呢?

    “循环引用”的应用原理就是对公式本身所在单元格已经通过公式返回的结果进行再处理的过程。下面,我们将循环引用涉及的几个知识点进行分解介绍(注:如有对本帖的技术疑问,请跟帖bwin官网地址并将问题的相应楼层链接短信告知本人)

    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中的“调试”)。例如【循环引用】之中文大写人民币逆转为阿拉伯数字例子中的C2单元格,产生一个1~12的循环数字,每一步,都将替换对应的字符。

    注意:许多地方的“循环引用”并不需要要设置“计数器”,比如【循环引用】之求解多元一次方程

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

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

    本帖第13楼~15楼为“控制循环引用三法宝”的具体阐述






    【应用实例】
    组合方案型:
    [原创]【循环引用】之钢筋下料方案组合计算器
    如何用公式算出规定总和的数据组合

    字符替换型:
    [原创]【循环引用】之文本、数字、汉字分离器
    【循环引用】之中文大写人民币逆转为阿拉伯数字
    【循环引用】之交集、并集、反交集数字串

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

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

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


    [ 本帖最后由 gouweicao78 于 2009-4-1 22:59 编辑 ]

    评分

    参与人数 4魅力值 +16 收起 理由
    + 5
    思路巧妙:)
    + 2
    感谢帮助:)
    + 4
    知识讲的非常清楚。
    + 5
    大工程

    查看全部评分

     楼主| 发表于 2009-3-30 16:35:00 | 显示全部楼层
    说了这么多,给了这么些实例,头快昏了。不怕,我们抓一个小实例来:如何将一个字符串逆序返回?

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

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

    【解剖】所谓逆序返回字符,就是将字符串从右到左倒过来。这里使用了几个简单的函数: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输入

    1. =IF(D$1,C2&LEFT(RIGHT(A2,1+LEN(C2))),"")
    复制代码
    注意:D1是启动开关。确保公式输入过程不会有乱七八糟的东西出现
    输入完毕,按F9不放——居然成功了一半:得到“TE爱我我我我我我我我我我我我我我我我我我我……”

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

    1. =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。

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

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

    [ 本帖最后由 gouweicao78 于 2009-3-30 16:39 编辑 ]

    【循环引用】之逆序返回字符串.rar

    6.19 KB, 下载次数: 311, 下载积分: 消费券 -5 Ti币

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

    评分

    参与人数 4魅力值 +21 收起 理由
    + 5
    太帅了,原来早就有答案了,收藏学习!
    + 5
    太帅了!
    + 5
    我学会循环就是看这个帖子的,太精彩了
    + 6
    我的偶像:)

    查看全部评分

    发表于 2009-3-30 16:59:01 | 显示全部楼层
    顶,好帖,草兄辛苦了。
    发表于 2009-3-30 21:05:59 | 显示全部楼层
    学习一下。
    发表于 2009-3-31 14:50:23 | 显示全部楼层
    坐下来好好听草兄讲故事
    发表于 2009-3-31 15:57:49 | 显示全部楼层
    我想当和尚
    发表于 2009-4-3 16:39:38 | 显示全部楼层
    学习了,谢谢
    发表于 2009-4-9 15:39:47 | 显示全部楼层
    收藏~~~
    发表于 2009-7-3 16:19:40 | 显示全部楼层
    这个没搞懂,在实际工作中有应用实例吗?如有还好懂点。否则就是为技术而技术,并不可取啊!

    因无知而妄语,勿怪啊!
    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    Excel技巧网的会员探讨问题仅代表其个人意见,与网站的立场无关。任何违反国家和地方相关法律法规的言论,本站有义务协助政府相关部门追究发言者的责任!
    本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者,本站之外任何单位或个人未经允许,不得将其用于商业用途。
    若非原文作者,任何形式的非商业性转载必须获得Excel技巧网或作者允许,并注明作者和出处。
    会员发表的帖子如涉及版权纠纷,须自行负责。详情请参考注册时的网站服务条款。
    本站特聘法律顾问:沈学律师

    Powered by Discuz! X3.4

    © 2001-2017 Comsenz Inc.

    快速bwin官网地址 返回顶部 返回列表