易失函数相关知识

易失函数相关知识

在所有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、从上面的测试来看，易失函数的重算，指的仅仅是易失函数公式本身的重算，与所有的不含易失函数的公式无关。

那么我们可以说“易失函数会引发工作表的易失函数公式全部重算”吗？还不能。

再作一个测试：<br>第一步，把上面的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 FunctionCalcSeqCountRef(theRange As Range) As Variant

jCalcSeq = jCalcSeq + 1

CalcSeqCountRef = jCalcSeq + theRange - theRange

End Function

Sub resetcounter()

jCalcSeq = 0

End Sub

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