[函数用法讨论系列17] 关于易失函数

zw1028

谢谢！！

Kelidai

谢谢分享

apolloh

以下是引用[I]cnchenjun[/I]在2006-1-13 13:11:25的发言：[BR]

今天发现这个帖子，我有如下问题：

我的工作表里用到好多vlookup函数，每次操作工作表都有短暂等待，请问

vlookup是易失函数吗，用match+index替代是否效率更高？？

index我记得是excel95前是易失性函数，之后的版本就不是了，vlookup和match都不是易失性函数。

十年磨一剑

学习ing，虽然没看多懂，

至少知道易失是指：“使用这些函数后，会引发工作表的重新计算。”

czzqb

关于使用易失函数而影响EXCEL的运算速度问题，论坛上已经有过一些讨论。

    什么是易失函数？在顶楼的帖子里已经有了一些说明和测试，不再赘述。

    我们都知道易失函数会引发重算。但重算范围到底有多大？哪些公式参与了重算？我一直不大清楚。<br>最近在为工作制作一个管理系统的过程中，碰见了这个问题，由于数据量比较大，公式较多，又在不少公式中难以避免地使用了易失函数，导致速度问题凸显。
    为弄清易失函数对速度的影响，我作了一系列的测试，发现一些以前没注意过的现象，提出来供大家讨论。

    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<br>改变BOOK3和BOOK1的打开顺序，测试结果不变。

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

    根据上面的测试，我们是不是应该这样来表述易失函数引发的重算：
    易失函数（包括易失性操作）会引发所有打开工作簿里含易失函数的公式全部重算，但不含易失函数的公式不会参与重算(注：这里说的“公式”，应该包括定义名称里的公式）

注：有关“关系链”的概念见另一个相关帖子：
http://club.excelhome.net/dispbbs.asp?BoardID=3&ID=112024&replyID=&skin=0

[ 本帖最后由 czzqb 于 2009-12-24 22:12 编辑 ]

gouweicao78

czzqb兄研究东西可真透！可惜就是太忙，不然都把东西翻个底儿朝天了，哈哈。

qqincai

谢谢

hjins

收了!感谢!

liyh67

看了czzqb兄的研究受益很大。以前用公式只要结果正确就行了，最近建了一个工作簿，因为有好多易失函数公式，速度很慢， 看了本贴该想办法改改了。

四海飘零

对czzqb在15楼的测试做一个补充：

如果另开一个进程，对两个进程分别运行的话，就会发现：两个进程没有关联。一个进程的易失性，不会影响另一个进程。