|
我们对Excel的直观感受就是公式一多，Excel计算速度就会很慢。但是实际上并不是这样的。Excel中很多公式并不必然导致计算速度变慢，让计算速度变慢的原因是你对公式的选择以及公式的写法。
上一篇我们为大家揭示了Excel公式计算的一个秘密：影响Excel公式计算效率的主要因素是该公式中引用的单元格数量。今天我们就基于这个原则为大家分析一下最常用的各种求和公式的效率。我们这一系列文章都是基于这个工具进行的。这个工具是用VBA写的，你可以通过下面的方式获得这个工具： 回复：计算速度分析工具
我们现在有一份数据如上图所示，记录了逐日的销售明细，共20000行。 我们需要制作如下的报表：
我们需要分别统计每类产品在每个季度的销售量。 如果我们要用函数实现这个报表（这是很多人的第一选择），我们有以下的函数可以选择：
SUM和IF的组合
SUMIFS SUMPRODUCT 我们下面先为大家介绍一下如何使用这些函数完成这个报表。
首先，为了简化将来的公式，我们添加了两行数据，分别表示每个季度的开始日期和结束日期：
然后，我们就可以使用下面公式了。先看SUMIFS的写法：SUMIFS(数据!$F$3:$F$20002,数据!$E$3:$E$20002,Index!$B7,数据!$B$3:$B$20002,">="&Index!C$22,数据!$B$3:$B$20002,"<="&Index!C$23) 这个公式非常简单，不多加解释了。在本文中，我们就称呼这个公式为“条件求和公式”。再看SUMPRODUCT的写法（SUMPRODUCT是一个非常有用的函数，可以解决很多Excel的实际问题，我们会在其他文章中为大家详细介绍这个函数）：SUMPRODUCT(数据!$F$3:$F$20002,--(数据!$E$3:$E$20002=Index!$B7),--(数据!$B$3:$B$20002>=Index!C$22),--(数据!$B$3:$B$20002<=Index!C$23)) 其实，从写法上看这个公式与SUMIFS差不多，只不过判断条件的写法不太一样。我们称呼这个公式为“SUMPRODUCT第一种写法”。
还可以换用另外一个写法的SUMPRODUCT公式：
SUMPRODUCT(数据!$F$3:$F$20002*(--(数据!$E$3:$E$20002=Index!$B7))*(--(数据!$B$3:$B$20002>=Index!C$22))*(--(数据!$B$3:$B$20002<=Index!C$23))) 这个写法与第一种写法的区别是把“,”换成了“*”，从实际计算效果上看，这两个写法是等价的。之所以把这个写法单独拿出来介绍，是因为这两个不同的写法，计算速度是不同的。我们称呼这个公式为“SUMPRODUCT第二种写法”。
SUM(IF((数据!$E$3:$E$20002=Index!$B7)*(数据!$B$3:$B$20002>=Index!C$22)*(数据!$B$3:$B$20002<=Index!C$23),数据!$F$3:$F$20002,0)) 先用IF判断是否满足条件，满足条件的返回原数值，不满足条件的返回0，然后对这些返回值求和。
要注意的是，这是一个数组公式，在输入后需要按Ctrl+Shift+Enter键。这个公式是以前版本的Excel中没有SUMIFS函数时经常使用的，时至今日，还有很多人在很多场景中使用，我们称呼它为“数组SUM”。好了，现在我们可以使用“计算速度分析工具”来计算一下这几个公式的分别的计算时间（单位是秒）：
可以看出，条件求和的SUMIFS最快，只要0.74秒的时间，而数组SUM的计算最慢，需要2.12秒的时间。SUMPRODUCT的两种写法居于中间，接近差不多2秒左右，第二种写法比第一种写法慢10%左右。 一个表格本身2秒左右的计算时间已经算是比较慢的了，尤其是考虑到我们只有168个公式。如果这个报表中还有其他计算公式，比如再做一个计算的分析，按照销量区间的分析等等，那么这个报表的计算时间很容易就会超过10秒了。即使你用最慢的SUMIFS也不会好很多（会快200%左右，大约3-4秒，也是个很慢的计算速度）。
我们稍微分析一下就会发现，这四个公式在引用单元格的数量上是一致的，把那些作为参数的比如季度起始日期和产品列除掉不考虑（数量太少，对公式计算速度的影响可以忽略不记），那么他们引用你的单元格分别是： 数据列：数据!$F$3:$F$20002 小类列：数据!$E$3:$E$20002 日期列：数据!$B$3:$B$20002 
引用单元格的数量=20000+20000+20000=60000。既然引用单元格数量都一样，那么这个计算速度为什么会有差距的呢？原因是这些公式的内部处理方式是不同的。
以数组SUM为例，它的计算方式是先用IF判断每一个单元格是否满足条件，然后再把根据条件返回的结果集（数组）进行求和，相当于每个单元格都用到了。SUMPRODUCT的两种写法也是这样，每个单元格都用枚举的方式用到了。而之所以比数组SUM快一些的原因是因为数组SUM多了内存中开辟数组的开销。
SUMPRODUCT的两种写法的计算速度的差别则是它们内部不同处理方式造成的，具体原因不详，大家只要记住尽量采用第一种写法就好了，因为这两种写法在结果上是一样的。而SUMIFS的处理方式与另外3个公式不同。它首先去结果集筛选出满足条件的记录（从实现方式上看，我推测应该是使用了类似SQL的数据库算法），然后再进行求和，从而导致速度大幅提高（大约节约了200%左右的时间）。
如果你要在这四个公式中做选择，请选择使用SUMIFS（COUNTIFS, AVERAGEIFS, SUMIF, COUNTIF）。有心的朋友可能会问，我们的公式只引用了60000个单元格，为什么会需要2秒的时间？要注意，我们有168个公式，所以总共引用单元格的数字是：
你可以自己留意一下，如果你是使用这些普通公式的写法，那么只要引用的总单元格超过一千万，就会感受到明显的速度影响了。
当然了，就Excel本身的潜力来说，这个数据量和公式计算量再扩大个几十倍也是可以轻松应对的。 通过上面的分析我们知道，尽管这些公式之间有速度的差异（差异从10%到300%不等），但是只要你引用的单元格数太多了，那么想通过换个公式来降低速度是收效甚微的。在实际中，即使使用SUMIFS这个速度最快的求和公式，那么速度也是不太容易接受的）。要想从根本上提高计算速度，还需要从我们上次讲到的Excel那个根本秘密出发，想方设法减少引用的单元格数量。 我们来看这个报表中的一个单元格， 
我们发现，其实这个单元格计算我们根本不需要用到数据表中的整列日期和整列小类，和整列数量。如果我们能够把所有的2017年Q2的上衣销售记录挑出来，那么他们只有83行，只要将这83行进行简单的求和，那么引用单元格就从60000变成了83+83+83=249，而168个这样的公式加起来，引用的单元格数量也不过是41832，还不如原来一个公式引用的单元格数量多，效率的改进是显然的。首先，我们需要将源数据按照日期进行升序排序。点击数据区域日期列的任意单元格，然后点击“数据”选项卡下的“升序排序”按钮：
然后，在报表中添加两个辅助行：起始行和行数。分别代表在数据表中每个季度的起始行号和每个季度的总行数，分别使用下面两个公式：
起始行号公式： =MATCH(C22,数据!$B$3:$B$20002,0) 行数公式： =MATCH(C23,数据!$B$3:$B$20002,1)-C25+1
然后在C7输入公式： =SUMIFS(OFFSET(数据!$F$2,C$25,0,C$26,1), OFFSET(数据!$E$2,C$25,0,C$26,1),$B7) 并填充到整个报表区域：
这个公式还是用了SUMIFS函数，但是区域发生了变化，用了OFFSET从整个列中返回一部分区域。第一个黄色加亮的是销量列，第二个绿色加量的是小类列。返回的区域行数比原来少多了，只有1777列。 我们看到这个公式的计算结果跟前面介绍的公式是一样的。那么计算速度呢？
经过计算速度分析工具的计算，耗时为：
只有0.08秒。是原来公式中最快的条件求和（SUMIFS）耗时的1/10。我们没有选用特别的公式，还是用的SUMIFS，只不过通过MATCH(辅助行数据）和OFFSET（返回部分区域）减少了引用的单元格数量。而且，我们只是把其中符合日期条件的数据挑出来了，如果再从其中把满足小类条件的数据挑出来，计算速度还会提高一个数量级。 这个公式调整策略的威力是惊人的。作为比较，我们可以看看数据透视表的计算速度。我们知道，数据透视表一旦创建完成，一般情况下，刷新速度是很快的，
在我们的例子中，这个透视表的计算速度是：0.14秒左右。比我们上面的速度慢了整整一倍。这个公式的调整策略在所有的场景下都非常有效。而且方法大同小异，只要你用到了求和（计数），那么就要想方设法减少所引用的单元格数量。只不过在不同场景中减少的方法有所差异，我后面会陆续给大家介绍。
关注本公众号 点击底部菜单“联系客服”，与客服取得联系，索取“求和公式效率分析”案例文件
|
