循环引用是垃圾功能吗?不,它可以像编程一样高效迭代运行!

你好,我是刘卓。欢迎来到我的公号,excel函数解析。在工作学习中,我们有时会遇到循环引用的警告提示,它说计算结果可能不正确。这时有些小伙伴可能会有些疑惑或者担心,为什么会这样?其实这是正常的,可能只是你对循环引用不太了解。今天就来分享一下有关循环引用的知识。
-01-

什么是循环引用

那什么是循环引用呢?循环引用是指公式直接或间接地引用它本身所在的单元格。比如:在A1单元格输入公式=A1+1,就会形成循环引用,因为公式直接引用了它本身所在的单元格A1。如果下图所示。

还有一种是间接引用。比如在A1单元格输入公式=B1+1,在B1单元格输入公式=A1+1,此时这两个公式互相引用,也会间接地形成循环引用。

默认情况下,Excel不允许在公式中使用循环引用,这是为了避免出现死循环。当公式中出现循环引用时,会弹出上图所示的对话框。

既然不允许在公式中使用循环引用,那它是不是一个废物功能呢?不不不,我们只是说默认情况下是不允许使用的。那当然有例外的情况了,下面我们就来看下怎样使用循环引用。


-02-

怎样使用循环引用

Excel的循环引用是一种特殊的计算模式。通过设置【启用迭代计算】来实现对变量的循环引用和计算,从而依照设定的条件对参数多次计算直到得出我们想要的结果。

要使用循环引用,必须先开启迭代计算,并设置最多迭代次数。可以单击【文件】-【选项】,打开【excel选项】对话框,点【公式】,勾选【启用迭代计算】,根据需要填写最多迭代次数,最后点击确定。如下图所示。

开启迭代计算后,就可以使用循环引用了。如下图所示,在A1单元格输入公式=A1+1,按enter,得到结果为100。为什么结果是100呢?因为我之前设置的最多迭代次数是100。

这样说,有些小伙伴可能还是不明白。那我先来说一下迭代的意思,迭代是重复执行一系列的运算步骤。每一次对运算步骤的重复称为一次迭代,并且每一次迭代得到的结果会作为下一次迭代的初始值。

其实人的成长就是一个不断迭代的过程,每一天可以有一系列的过程,起床-吃饭-上班-学习-下班,到了晚上,一次迭代就完成了,结果就是你比早上有所成长。并且这个成长的结果会作为明天的初始值,继续进行这样的一系列过程,……,不断迭代成长。

有了上面这个“艰难的”类比,就可以说明A1=A1+1这个公式了。A1的初始值是空单元格,可以看做0。每一次迭代的运算只有加1,第一次迭代后的结果就是0+1=1。而第一次迭代后的1会作为第二次迭代的初始值,第二次迭代的结果就是1+1=2,……,第一百次迭代的结果就是99+1=100。

学过vba的小伙伴很容易理解这个过程,n=n+1,可以把它看做一个计数器。在使用循环引用的过程中,除了使用计数器外,还经常用到启动开关

启动开关一般是通过if函数结合表单控件中的“复选框”来进行控制,我们只需通过对“复选框”的勾选或去除勾选,就能控制循环引用的启动或关闭。下面通过几个案例来说明一下启动开关,计数器和循环引用的操作方法。

-03-
具体应用

1.自动记录操作时间

如下图所示,当在A列输入操作记录时,会在B列相应的单元格内记录当前的操作时间。
首先在excel选项中启用迭代计算,并设置最多迭代次数为1。然后点击【开发工具】-【插入】,在表单控件中选择【复选框】。在B2单元格拖出一个复选框,改名为“开关”。
右键单击复选框,然后点击设置控件格式,在弹出的对话框中选【控制】,单元格链接选A2,点确定。当勾选复选框时,A2为true;去除勾选时,A2为false。这样启动开关就做好了。
在B5单元格输入公式=IF(A$2*(A5<>""),IF(B5="",TEXT(NOW(),"e-mm-dd hh:mm:ss"),B5),""),下拉填充。

勾选开关后,在A列输入内容时,就会自动在B列记录当前的时间。

2.文字反转

需要在B列的单元格中将A列的文字反转,如下图所示。只需勾选开关,即可得到结果。有没有觉得挺神奇的?

首先在excel选项中启用迭代计算,并设置最多迭代次数为100。然后从【开发工具】中插入【复选框】,改名“开关”,并设置控件格式,单元格链接为A16,这样就创建了开关。

在C16单元格中输入公式=IF(A16,C16+1,),作为计数器。

在B19单元格输入公式=IF(A$16*(A19<>""),MID(A19,C$16,1)&B19,""),下拉填充。

启用开关后,即可使excel执行迭代计算,得到最后的结果。

最后小结一下:

循环引用不是垃圾功能,我个人认为它更像编程一样可以迭代运行,只不过用函数的方式编写代码。下次分享几个循环引用的其他案例。
链接:

https://pan.baidu.com/s/1E5leXy9piFk7s9zkOm1_zw

提取码:rx7e
(0)

相关推荐