|
今天我们把主题聚焦在一类特殊函数造成的问题上——volatile function。 这一类函数有人把他们翻译成“易变函数”，其实挺贴切的。 本系列其他文章： Excel表格为什么那么慢已经应该如何解决（二）常见求和公式效率分析 Excel表格为什么那么慢已经应该如何解决（三）查找公式的效率分析 一个Excel文件中，很可能会包含大量的公式，在一般的情况下，Excel可以保持一个过得去的性能（当然，在有些情况下，你必须仔细设计这些公式，我们在“Excel表格为什么那么慢已经应该如何解决”系列前面的文章中介绍过一些场景）。Excel之所以可以做到这一点，是因为，对于大部分函数，Excel并不会在每一次表格数据发生变化的时候都去计算一遍，只有当该公式引用（直接或间接）的单元格发生变化的时候，Excel采取计算该公式。 采用这个方法，Excel就可以保证每次只计算很小比例的公式，降低了计算量。 但是，如果你的公式中使用了“易变函数”，每次Excel有变化时（比如输入数据），这些公式都会被重新计算，这样就会增加Excel的计算时间。如果有很多这样的公式，Excel的使用体验就会很差。 例如，Rand和Randbetween是两个易变函数。在下面的数据中，B3:E10000使用这两个函数填充： 同时，在另一个表的B5:B33使用Sumif函数计算条件求和，引用的区域就是上图的B:E列： 正常情况下，如果我们修改别的单元格的值，按说不会引起公式的计算。但是，下面的动图告诉我们，每次我们修改其他单元格，就会导致重算： 在当前普遍使用的Excel各个版本中，易变函数有下面这些：
这个清单不是很长。但是有一些经常使用的函数（例如，前面6个函数，尤其是前面四个函数）。 INFO和CELL不常用，而且依赖于参数。我们会在以后适当的时候为大家介绍这两个函数的用法。 这里比较奇怪的是SUMIF函数。在我们的印象里这个函数不应该是易变函数。但是在某些情况下，它会变成易变函数，我们在本文后面会为大家介绍。 另外，这个清单还得添加一个成员
出乎所有人的意料。不管你在条件格式里使用什么样的函数，条件格式都可以看作是易变函数。也就是说，条件格式里的函数总是会重新计算。 如果是你自己做的表格，你根据上面的清单对比自然就知道有没有易变函数了。 如果你忘了，或者是别人做的表格，这里有一个简单的方法可以判断是否可能有易变函数： 打开Excel文件，点击保存按钮。然后按F9，然后马上点击关闭按钮，如果Excel提示你需要保存，那么很可能这个文件里有易变函数。
并不是所有操作都会导致易变函数重新计算。下面是一些会引起易变函数及相关单元格重新计算的操作：
需要强调的是，易变函数本身的计算速度其实是很快的（大部分情况下），它们对速度的影响主要是会导致跟它们相关的所有单元格都会重新计算，影响的是每次操作Excel的响应速度。 很多人会采用一个显而易见的操作：将计算选项从“自动”改为“手动”：
这样做可以立竿见影的改进Excel的响应时间。 但是，我必须要强调的是这是一个我绝对不会推荐的做法。实际上，我强烈建议： 一定要把计算选项设为“自动” 之所以这么建议，是因为两个原因：首先，你很可能在Excel中将某些报表作为中间结果，这样在你的某些数据修改了的情况下，由于是手动计算模式，这些中间报表并没有更新，从而导致你后续的其他分析错误。其次，你很可能要将这个文件分享给其他人，而其他人并不知道这个计算选项是手动的，这样她就会得到错误的表格数据。 对于大部分的易变函数来说，都有减少负面影响的方法。不过需要针对不同的函数采用不同的方法，篇幅过长，我们放到后续的文章中介绍。今天我们只介绍一下SUMIF的问题。
一般情况下，SUMIF不是一个易变函数，只有它引用单元格发生变化时，才会重新计算。但是，下面的SUMIF公式是一个易变函数： =SUMIF(Sheet1!$B$3:$D$10000,Index!$H$2,Sheet1!$E$3:$E$10000) 仔细观察，就会发现这个公式跟我们通常所用的公式是不一样的，它引用了两个区域，但是这两个区域的大小是不一样的，一个是从B到D，共3列，一个是只有E一列。 当SUMIF中各个参数区域引用的单元格个数不一致时，这个公式就会变成易变函数。 解决方法其实也很简单，将这个公式改写成各区域大小一致就可以了： =SUMIF(Sheet1!$B$3:$D$10000,Index!$H$2,Sheet1!$E$3:$G$10000)
关注本公众号，点击底部菜单“联系客服”，与客服取得联系，索取“计算性能分析—volatile”案例文件
|
|
|
