土木工程二本大学排名:易失函数相关知识

来源:百度文库 编辑:我要文章网 时间:2019/11/20 14:19:24
 

    在所有EXCEL的三百多个函数中,有一些函数很特殊,称之为“易失函数”。 

    在一个工作表中使用了这类函数以后,每输入(或删除)一个数据,整个工作表就要重算一次。甚至当我们打开一个工作簿,不作任何操作,直接关闭,也会弹出一个对话框,询问“是否保存更改”,这是易失函数使然。看见这种情况,我们就可以断定:这个工作簿使用了易失函数。

要了解易失函数,首先我们要知道,EXCEL是按什么次序计算的。在一个工作表中写了很多一个套一个的公式,EXCEL是如何决定计算次序的呢?

在打开工作表时,EXCEL扫描所有的公式,理清其相互关系,在内存中创建一个“关系链”。比如我们在C1的公式中引用B1:B20,而B1又依赖于A1的数据,B10依赖于A10的数据,那么我们就说A1,A10,B1:B20及C1一起组成了一个关系链,计算顺序当然就是A1,A10 => B1:B20 => C1,所有这些单元格,只要有一个改动了数据,整个关系链就要重算一次。  

但注意这一点:关系链之外的公式不会重算。这是EXCEL的“聪明”的计算,这一点很重要。正是由于这一点,EXCEL的整体运算速度才得以保证。  

 

但易失函数却偏偏不“遵循”这个规矩。     

——只要工作表中有一个单元格变化了,所有的包含易失函数的公式就会全部重算,不管改动的那个单元格是不是在关系链上。

 ——甚至,象我们前面说到的,只要打开和关闭工作表,都会引发全部重算。  

为了减少这种易失函数造成的速度影响,我们可以:     

1、设置重算方式为“手工重算”(菜单:工具-选项-重新计算-点选“手工重算”,并勾选“保存前自动重算”)

这样,改动数据,所有的公式都不会进行计算了。需要看结果时,再按下F9。

【但如果表中使用了宏表函数,有些情况按F9也不予重算,这时要按ctrl+alt+shift+F9,进行“全部重算”】

【有些自定义函数也需按ctrl+alt+shift+F9才能重算】    

2、在公式中尽可能少用易失函数。     

但很多易失函数不可能完全不用,这时尤其要注意减小它的引用范围和使用范围。

       

那么,哪些是易失函数?      

对于OFFICE XP版和OFFICE 2003版,下面这些是易失函数:    

OFFSET,INDIRECT,TODAY,NOW,RAND,CELL,INFO    

对于较低版本,INDEX也曾经是易失函数。(最近有人指出:INDEX在最新版本里也是个“半易失函数”)

【附1】

    我们都知道易失函数会引发重算。但重算范围到底有多大?哪些公式参与了重算?我一直不大清楚。

    最近在为工作制作一个管理系统的过程中,碰见了这个问题,由于数据量比较大,公式较多,又在不少公式中难以避免地使用了易失函数,导致速度问题凸显。

    为弄清易失函数对速度的影响,我作了一系列的测试,发现一些以前没注意过的现象,提出来供大家讨论。

    1、一般都认为:易失函数会引发工作表的全部重算——过去我理解这句话的意思是:整个工作表的全部公式都重算一遍。但在我的测试中,所表现出的特性不是这样。
    测试方法:设置一个大数据量的工作表,再作几千行范围的数组公式(如C1:C5000=SUMPRODUCT(A1:A5000*B1:B5000))【其中范围5000行可根据自己计算机的配置自行决定,不要太大,以免时间过长,但也不能太小,否则不容易测量时间】
    第一步:先运行数组公式,记下运行时间T1;
    第二步:再写一个易失函数公式,如:D1=NOW(),D2=RAND()之类,运行它(按F9),运行时间:T2(非常小)
    显然两次运行时间有天壤之别。这就是说,含有易失函数公式,不会导致非易失函数公式重算。

    2、含有易失函数公式的重算会不会导致其关系链上的所有公式重算?
    测试方法:在第一次测试的基础上,增加一个公式:E1=OFFSET(C1,,,5000)这样E1和C1:C500就组成了一个关系链。E1的运行会不会导致C1:C5000重算?运行一下,得到时间T3,也是非常小,远远小于T1。这就是说,易失函数的重算也不会导致其关系链上的非易失函数公式重算

    3、从上面的测试来看,易失函数的重算,指的仅仅是易失函数公式本身的重算,与所有的不含易失函数的公式无关。   
    那么我们可以说“易失函数会引发工作表的易失函数公式全部重算”吗?还不能。
    再作一个测试:
第一步,把上面的C1:C5000=SUMPRODUCT(A1:A5000*B1:B5000)改为:C1:C5000=SUMPRODUCT(A1:A5000*B1:B5000)*NOW(),这样我们就有了5000个含易失函数的公式。运行一下,看看时间(T4)【要想运行它,随便在一个空单元格输入一个数据,回车就开始运行】这个时间和T1差不多。
    切换到下一个空的工作表中去。在一个单元格随便输入一个数据,它也会引发重算!
    ——这就是说,易失函数引发的重算不是工作表级别的,而是工作簿级别的!

    4、下一个测试:
    把上面作的这个工作簿存为BOOK1,再将它拷贝成另一个文件BOOK2,同时打开两个文件。
    随便在一个工作表中输入一个数据,记下运行时间T5,显然T5=2*T4
    ——这就是说,易失函数引发了两个打开的工作簿都在重算!尽管两个表根本没有关联。(如果打开更多的工作簿,会发现所有工作簿都在同时重算。)

    5,再作进一步的测试:关闭BOOK2,新建一个BOOK3,现在我们知道BOOK1有几千个易失函数公式,运行时间是T4,而BOOK3是空表。在BOOK3里输入一个数据,我们会看到他们也在重算,重算的时间=T4

    改变BOOK3和BOOK1的打开顺序,测试结果不变。

    上面只说到了易失函数,实际上对易失性操作的测试结果也完全相同。

    根据上面的测试,我们是不是应该这样来表述易失函数引发的重算:

    易失函数(包括易失性操作)会引发所有打开工作簿里含易失函数的公式全部重算,但不含易失函数的公式不会参与重算(注:这里说的“公式”,应该包括定义名称里的公式)

 

【附2】

在代码编辑窗口中,单击菜单栏“插入” →“模块”,粘贴以下代码:

Option Explicit

Option Base 1

Option Compare Text

Public jCalcSeq As Long

 

Public Function CalcSeqCountRef(theRange As Range) As Variant

    jCalcSeq = jCalcSeq + 1

    CalcSeqCountRef = jCalcSeq + theRange - theRange

End Function

 

Sub resetcounter()

    jCalcSeq = 0

End Sub

    每按下一次F9键,易失函数的计算次数计数器将会发生变化,而非易失函数就不会变化,如下图所示: