Excel表格为什么那么慢以及怎么解决（二）常见求和公式的效率分析
我们对Excel的直观感受就是公式一多，Excel计算速度就会很慢。但是实际上并不是这样的。Excel中很多公式并不必然导致计算速度变慢，让计算速度变慢的原因是你对公式的选择以及公式的写法。
我们这一系列文章都是基于这个工具进行的。这个工具是用VBA写的，你可以通过下面的方式获得这个工具：
关注本公众号：ExcelEasy 回复：计算速度分析工具
常用的求和公式及计算速度比较
我们现在有一份数据如上图所示，记录了逐日的销售明细，共20000行。
我们需要制作如下的报表：
我们需要分别统计每类产品在每个季度的销售量。
如果我们要用函数实现这个报表（这是很多人的第一选择），我们有以下的函数可以选择：
SUM和IF的组合
SUMIFS
SUMPRODUCT
我们下面先为大家介绍一下如何使用这些函数完成这个报表。
首先，为了简化将来的公式，我们添加了两行数据，分别表示每个季度的开始日期和结束日期：
">="&Index!C$22,数据!$B$3:$B$20002,"<="&Index!C$23)
--(数据!$B$3:$B$20002>=Index!C$22),--(数据!$B$3:$B$20002<=Index!C$23))
(--(数据!$B$3:$B$20002>=Index!C$22))*(--(数据!$B$3:$B$20002<=Index!C$23)))
(数据!$B$3:$B$20002<=Index!C$23),数据!$F$3:$F$20002,0))
可以看出，条件求和的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
当然了，就Excel本身的潜力来说，这个数据量和公式计算量再扩大个几十倍也是可以轻松应对的。
如何将速度提高10倍甚至更多
要想从根本上提高计算速度，还需要从我们上次讲到的Excel那个根本秘密出发，想方设法减少引用的单元格数量。
我们来看这个报表中的一个单元格，
然后，在报表中添加两个辅助行：起始行和行数。分别代表在数据表中每个季度的起始行号和每个季度的总行数，分别使用下面两个公式：
起始行号公式：
=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（返回部分区域）减少了引用的单元格数量。而且，我们只是把其中符合日期条件的数据挑出来了，如果再从其中把满足小类条件的数据挑出来，计算速度还会提高一个数量级。
总结
这个公式的调整策略在所有的场景下都非常有效。而且方法大同小异，只要你用到了求和（计数），那么就要想方设法减少所引用的单元格数量。只不过在不同场景中减少的方法有所差异，我后面会陆续给大家介绍。
取得本文案例文件的方式：
关注本公众号
点击底部菜单“联系客服”，与客服取得联系，索取“求和公式效率分析”案例文件