|
Excel文件的计算“速度”是一个被忽视的问题。你可以很轻易的找到为某个特定问题如何写一个公式，但是你上网搜“我的Excel文件为什么这么慢？”，你会得到一堆似是而非，完全不能解决你的问题的“答案”。 这是百度上的前几个答案，不用打开链接也会发现这些操作几乎不可能真正解决Excel的计算速度问题。 在看某乎上的几个答案： ？？？ 20秒以内是正常的？ 我实在是没法接受这种答案。 如果你再到所谓的一些专业Excel论坛中去问，你还会得到这样的答案： 整理一下磁盘碎片 是不是中毒了 是不是数据太多了 该换机器了 …… 如果你尝试过通过上面的各种方法想提高Excel的计算速度，我相信基本上你会得到下面的结论： Excel是不是就这样，公式多了（或者数据多了）就会慢的要死！！！ 可真的是这样吗？ 为什么我们应该注重速度呢？ Excel的计算速度从两个方面影响我们的工作效率。首先，速度慢本身就带来了效率的降低。其次，计算速度慢还影响我们的心情，降低对进行该项工作的意愿，甚至产生畏惧心理。想象一下，如果你每天都要打开一个Excel文件，记录几条当天发生的数据，但是每次在一个单元格中输入一个数据，Excel都要等20秒才能响应…… 多慢才算是慢呢？ 关于这个问题，不同人有不同的答案。根据某些机构做的研究结果，下面这个表格大概描述了普通人对不同计算响应速度的感受： 实际上关于这个问题有一个直观的对比： 比如，每次上网，如果不是马上打开网页，所有人的感受就是一字：慢！ 为什么你的Excel文件计算速度会慢？需要说明的是，在上面列举的那些原因（加载项，病毒，机器慢）等都可能造成Excel文件响应慢，但是这些并不是常见的原因，只不过是最容易指出的一些可能而已。同时，他们造成的慢往往是文件打开的时候速度慢，而计算速度受他们的影响相对较少。 也不是因为数据太多了。数据量的变化是会对速度造成影响。比如，一个含有1000行5列数据的Excel文件与含有100000行20列的数据相比，前者的相应速度一定比后者快。但是这里的“快”应该只在文件打开时有感受（略微快一些），在计算速度的相应方面，二者应该相差不大（如果没有本文后面说的原因的话）。 很多人觉得公式太多造成了速度的降低。其实不然，我做过一个表，有30万行15列左右的源数据，结果用公式计算，大概有2万多行6列都是使用公式，计算速度基本上就是理解反应，最慢也不到0.5秒。而我见过太多的表，只有几百行源数据，公式充其量也就是1000以内的级别，结果每次计算都需要超过10秒钟（很多超级慢的表可能会需要好几分钟）。 真正的原因是公式用的不对。在Excel中实现一个事情有很多方法。大部分人就会使用自己最熟悉的那种方法。这种方法可能是上网搜了一个公式，或者自己通过学习写了一个公式。一般来说这么用没太大问题，因为可以得到正确的结果。但是这么写公式就会造成 计算效率太低，这个计算速度随着数据量的增加成几何级数的增加。这时就需要寻找计算速度最快的方法了 这里举两个例子说明一下不同的函数的写法在计算速度上会有多大的差别。 1、YTD销售额合计 这里，我们为了计算截止到当前的累计销售额，我们写了如下的公式： =Sum($C$3:C3) 这个公式当然很漂亮，很有技巧性，重要的是，它也得到了正确的结果。 但是当你的数据超过10000行的时候，这个计算大概需要0.5秒左右的时间。 我们可以试试另外的写法： 这里G列的公式实现了和E列公式同样的功能。不过很多人可能不喜欢，因为这个公式有两个缺点：第一，第一行和其他的公式不一致。第二，这个公式一点也不“高级”，不过如果同样是10000行数据，这个公式大概可以在不到0.01秒的时间内计算完成。 实现同样功能的两个公式，前一个公式的计算时间是后一个的大约600-800倍。而且随着数据量的增加，这个差距会变得更大。 2、计算唯一项的个数 如果我们想要计算B列中唯一值有多少个，有一个非常经典的公式（这是一个数组公式，，需要按CTRL SHIFT ENTER输入）： {=SUM(IF(LEN(B3:B10000)>0,1/COUNTIF(B3:B10000,B3:B10000)))} 想象很多人都见过这个公式，而且几乎一定的是对这个公式和写公式的人都佩服的五体投地：这个公式太巧妙了，太高级了。 唯一的问题就是，这个公式大约需要15秒左右的时间才能计算出结果。 （要得到同样的结果方法太多，我们这里只是比较不同公式的差别） 考虑一下这份数据，我们可以给它做一个排序（排序是一个非常快速的操作），然后添加一个辅助列，如下图： 这样只需要写一个简单的公式： =sum(H2:H10000) 这个计算大约可以在0.02秒左右完成。两者的差距大约在800倍左右。 都有哪些公式会造成计算速度的问题？ 深入理解这个问题需要非常大的篇幅，这里不能详细的分析。总体来说，我们最常使用的那些函数和公式都存在着一个正确的使用方式的问题：包括vlookup，sumif，countif，sumifs，countifs，sumproduct等等，有时甚至是一个简单sum都会造成性能问题。 这里所说的正确使用方式包括： ● 使用“正确”的函数（像上面的两个例子） ● 尽可能避免工作表间的互相引用，如果有可能，就把他们放在一个工作表中。 ● 尽量避免工作簿间的互相引用，如果有，尽可能放在一个工作簿中 ● 如果有工作簿间的互相引用，就把他们同时打开。 ● 在使用一个工作簿时，关闭其它无关（没有引用到的）的工作簿文件。 ● 尽可能避免使用数组公式（数组公式在大多数情况下会导致非常长时间的计算，但是在某些情况下，精心设计的数组公式可以极大的减少计算时间） 注：有些人会建议将Excel的计算模式修改为手动。 在很多情况这么做可以缓解计算速度慢带来的困扰。不过这么做有两个缺陷，第一是这个过程有可能被打断（例如你不小心按了ESC键或者点了鼠标键），但是你还以为已经计算过了，这样就会导致计算错误。第二，这个方法在文件保存时或者打开时，或者F9重算时的计算速度仍然很慢。 所以，这个方法治标不治本，要想一劳永逸的解决问题，还是应该努力优化我们的表格。 上面的做法当然不能完全让你将你的表格优化到理想的性能，但是至少可以起到相当大的 帮助。要想做到理想的优化，你需要非常深入的理解Excel的计算机制（我们会在后续的文章中继续揭示Excel是如何完成计算过程的），即使如此，优化也是一个艰巨的任务。 幸运的是，在绝大多数情况，你需要面对的公式很少。即使一个庞大的文件，计算速度非常慢，但是其中的主要问题往往只是几个公式而已。 为了说明这个问题，我们设计了一个“Excel文件计算性能分析工具”，通过这个工具，可以分析到底这个文件的计算瓶颈在哪里。 以前两天有一个朋友的文件为例。这是一个公司的会员管理表格，大概管理着6000左右的会员数据，其中通过一些公式和数据透视做各种报表和分析。每次输入数据大概都需要10-15秒左右的时间。于是我们做了一个分析，分析结果如下： 分析显示，这个表格的每次计算时间大约在16秒钟左右。 其中有5个工作表，最主要的计算时间在会员资料这个表中，计算时间13秒多，其次是业务记录表，计算时间不到3秒。其余的表计算时间可以忽略不计（根据经验和分析，计算时间在0.02秒一下的都可以忽略）。 进一步的分析可以帮助我们找到真正的计算瓶颈了： 可以看出，会员资料的G，H，I列和业务记录的D、E列都是需要优化的重点列，P列也需要根据情况判断是否需要优化。 下面列出了相应的计算公式：
接下来就是针对性的优化这些公式就行了。经过差不多3个小时左右的努力，优化完成。成果显著。下面是针对优化后的文件的计算性能分析：
这个速度应该说还是提高的挺快的。如果研究时间再长一些，应该可以得到更加满意的效果。 如果您的Excel文件计算很慢，请查看本周公众号所发的第二篇文章，或者跟我们的客服联系，我们可以帮您分析Excel文件的计算瓶颈在哪里。 |
|
|
