在所有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】 我们都知道易失函数会引发重算。但重算范围到底有多大?哪些公式参与了重算?我一直不大清楚。 最近在为工作制作一个管理系统的过程中,碰见了这个问题,由于数据量比较大,公式较多,又在不少公式中难以避免地使用了易失函数,导致速度问题凸显。 为弄清易失函数对速度的影响,我作了一系列的测试,发现一些以前没注意过的现象,提出来供大家讨论。 改变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键,易失函数的计算次数计数器将会发生变化,而非易失函数就不会变化,如下图所示:
|
|